We're happy to announce a new Postgres extension: chdb. This extension expands Postgres import and export features via the chDB library, an in-process ClickHouse engine, providing efficient, flexible conversion to and from a wide array of data formats living on your favorite cloud storage systems.
\nAnd boy howdy do we mean efficient! We compared chdb's performance importing the NYC Taxi dataset (1m rows, wide table) in a number of data formats to three other Postgres extensions, all reading from a regionally-colocated AWS S3 bucket. To the chart!
\n\nIn order to minimize differences and to optimize for measurement of extension performance rather than infrastructure, the chdb, pg_lake, and pg_duckdb benchmarks ran on
r8id.xlargeClickHouse Managed Postgres services with 4 vCPUs and 32 GB RAM; the aws_s3 benchmark ran on adb.r8g.xlargeAWS RDS host, also with 4 vCPUs and 32 GB RAM. Results average three runs for each import. See the benchmark source code for details.
Of the four extensions, chdb exhibits the most consistent performance. pg_duckdb and pg_lake, both backed by DuckDB, take around 2-3x as long to import data from CSV, JSON, and Parquet. Only aws_s3 approaches chdb's performance, but it supports a much more limited array of data formats.
\nDid we mention data formats? The chdb extension can read and write a slew of data formats --- all those that ClickHouse itself supports. This table summarizes the supported data formats and compression algorithms of the extensions we compared; note that this chdb list of formats is but a subset of the formats it supports:
\n| Extension | Compression | Data Formats |
|---|---|---|
| aws_s3 | none | Text (TSV), CSV, Postgres Binary |
| pg_lake | gzip, zstd, snappy (Parquet only) | CSV, JSON, Parquet |
| pg_duckdb | gzip, zstd, snappy (Parquet only) | CSV, JSON, Parquet |
| chdb | gzip, zstd, lz4, bz2, snappy, brotli | TSV, CSV, JSON, BSON, Prometheus, Protobuf, Avro, Parquet, Arrow, XML, CapnProto, Markdown, MsgPack, ORC, and more! |
As ClickHouse and the chDB library add more, the chdb extension will get them for free!
\nWe benchmarked chdb performance loading the NYC Taxi dataset for a number of these formats, where it demonstrated quite consistent performance:
\nWe used the JSONCompact format for compatibility with the other extensions. Other JSON formats, such as JSONCompactEachRow, will more closely approximate the performance of the other formats.
\nThe chdb package ships with two extensions: a CREATE EXTENSION extension named chdb and a hook module named chdb_hook.
\nThe chdb extension (docs) provides the chdb_query() function, which executes a single chDB query. For example, this query:
1SELECT * FROM chdb_query($$\n2 SELECT * FROM s3('s3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv');\n3$$) AS (id int, months int, days int);Copy command
\nOutputs:
\n1id | months | days\n2----+--------+------\n3 1 | 2 | 3\n4 3 | 2 | 1\n5 4 | 5 | 6\n6(3 rows)Copy command
\nThe chdb_hook module (docs) hooks into the COPY command to copy data to or from an AWS S3, Google Cloud Storage, Azure Blob Storage, file, or http URL. This example loads records from a CSV file on S3:
1CREATE TABLE times (\n2 id INT NOT NULL,\n3 months INT NOT NULL,\n4 days INT NOT NULL\n5);\n6\n7LOAD 'chdb_hook';\n8COPY times FROM 's3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv';Copy command
\nAfter which the times table contains the records from the file:
1# SELECT * FROM times;\n2 id | months | days\n3----+--------+------\n4 1 | 2 | 3\n5 3 | 2 | 1\n6 4 | 5 | 6\n7(3 rows)Copy command
\nA CREATE TABLE command may also derive its columns, and load its rows, from such a URL. Try this one (all the URLs in this piece point to legit data files):
\n1CREATE TABLE reviews () WITH (\n2 copy_from = 's3://datasets-documentation/amazon_reviews/amazon_reviews_2015.snappy.parquet'\n3);Copy command
\nThe resulting, fully-loaded table has this structure:
\n| Column | Type |
|---|---|
| review_date | integer |
| marketplace | text |
| customer_id | numeric(20,0) |
| review_id | text |
| product_id | text |
| product_parent | numeric(20,0) |
| product_title | text |
| product_category | text |
| star_rating | smallint |
| helpful_votes | bigint |
| total_votes | bigint |
| vine | boolean |
| verified_purchase | boolean |
| review_headline | text |
| review_body | text |
Like pg_clickhouse, chdb relies on the pg-clickhouse-c headers-only library to convert values from ClickHouse to Postgres, including its type mapping, as in the CREATE TABLE example above. The current release maps nearly all of the ClickHouse types to Postgres types and vice versa. These mappings work most of the time; when they don't, use the structure option to tell chdb what type to use.
For example, pg-clickhouse-c maps a Postgres JSON value to ClickHouse String, because ClickHouse JSON currently recognizes only JSON objects , while Postgres JSON supports objects, arrays, and JSON scalar values. But perhaps you're confident your JSON columns contain only objects, thanks to a check constraint:
\n1CREATE TABLE projects (\n2 name TEXT PRIMARY KEY,\n3 meta JSON NOT NULL CHECK (json_typeof(meta) = 'object')\n4);\n5\n6INSERT INTO projects\n7VALUES ( 'chdb', '{"status": "release"}' ),\n8 ( 'walrus', '{"status": "revise"}' );Copy command
\nTo benefit from the increased flexibility and storage for object-aware storage formats such as Parquet JSON, use the structure option to map it to ClickHouse JSON:
1COPY projects to 'file:///tmp/projects.parquet' (\n2 structure 'name String, meta JSON'\n3);Copy command
\nThe chdb_hook extension reads and writes to all your favorite storage platforms. It determines the appropriate protocol from the URL scheme.
\n| Schemes | Target |
|---|---|
file | Absolute path on the Postgres server |
http, https | HTTP URL |
s3 | AWS S3 |
gs, gcs, oss | Google Cloud Storage |
az, azure, abfss, abfs | Azure Blob Storage or Azure ABFS |
hdfs | Hadoop Distributed File System |
URLs may also use a number of wildcards to concurrently fetch multiple files. Revisiting the CREATE TABLE example above, this command finds and imports six files from S3:
1CREATE TABLE times () WITH (\n2 copy_from = 's3://datasets-documentation/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv'\n3);Copy command
\nAfter which the times table contains the records from each file it loaded:
1SELECT * FROM times;\n2 c1 | c2 | c3 \n3----+----+----\n4 1 | 2 | 3\n5 3 | 2 | 1\n6 4 | 5 | 6\n7 1 | 2 | 3\n8 3 | 2 | 1\n9 4 | 5 | 6\n10 1 | 2 | 3\n11 3 | 2 | 1\n12 4 | 5 | 6\n13 1 | 2 | 3\n14 3 | 2 | 1\n15 4 | 5 | 6\n16 1 | 2 | 3\n17 3 | 2 | 1\n18 4 | 5 | 6\n19 1 | 2 | 3\n20 3 | 2 | 1\n21 4 | 5 | 6\n22 (18 rows)Copy command
\nSupport for such a vast array of data formats and cloud platforms demands a panoply of dependencies. We avoid managing those dependencies by delegating the problem to the chDB library. But loading that library into a Postgres backend would be excessive, especially for typically occasional or periodic tasks such as loading from a data source once a day.
\nData loading extensions thus take a variety of approaches to managing the size and complexity of such a library by a variety of means:
\nThe chdb extension adopts its own distinctive architecture: It embeds the chDB library into a separate helper application. Neither the extension nor chdb_hook link chDB. Instead, they start the helper app on demand and communicate with it via an efficient, in-memory channel: file descriptors (STDIN, STDOUT, and STDERR, plus another for configuration information).
This design prevents the chDB library from consuming any more resources than necessary to carry out a single command. It also isolates the PostgreSQL cluster itself from out of memory issues that using shared memory with a background worker would suffer.
\nWhen the helper app finishes executing a command and has passed all its results to the backend (in ClickHouse Native format, straight from the source), it simply cleans up and exits, leaving the server resources to the service that most matters: PostgreSQL.
\n1+-------------+\n2 | helper |\n3+----------+ | app | +------+\n4| Postgres | | +---------+ | | chDB |\n5| Backend |<---->| | chDB | |<---->| Data |\n6+----------+ | | Library | | +------+\n7 | +---------+ |\n8 +-------------+Copy command
\nWe plan to continue making chdb better. Potential roadmap items include:
\nCOPY (query) TOWHERE condition on COPYFind the chdb extension in all the usual places, including GitHub and PGXN. We also provide it as part of the broader pg_clickhouse package on ClickHouse Managed Postgres; ask your support contact to add chdb_hook to your default configuration, or just connect to a superuser account via psql or your favorite client, run CREATE EXTENSION chdb; or LOAD 'chdb_hook'; and get started!
Interested in seeing how ClickHouse Managed Postgres works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.
\n","headings":[{"level":2,"text":"Benchmark","id":"benchmark"},{"level":2,"text":"Data formats","id":"data-formats"},{"level":2,"text":"Usage","id":"usage"},{"level":3,"text":"chdb extension","id":"chdb-extension"},{"level":3,"text":"chdb_hook module","id":"chdb-hook-module"},{"level":2,"text":"Data types","id":"data-types"},{"level":2,"text":"Cloud storage URLs","id":"cloud-storage-urls"},{"level":2,"text":"Architecture","id":"architecture"},{"level":2,"text":"What's next?","id":"what-s-next"},{"level":2,"text":"Give it a try","id":"give-it-a-try"},{"level":3,"text":"Get started with ClickHouse Managed Postgres today","id":"get-started-with-clickhouse-managed-postgres-today"}]}}