--- title: "Introducing chdb Postgres extension: High-performance imports from cloud storage" slug: introducing-chdb-postgres-extension-high-performance-imports-from-cloud-storage url: https://listedarticles.com/articles/introducing-chdb-postgres-extension-high-performance-imports-from-cloud-storage canonical_url: https://clickhouse.com/blog/introducing-chdb-postgres content_type: blog_post language: en published_at: 2026-09-08T12:00:00.000Z updated_at: 2026-09-19T18:16:03.665Z author: "David Wheeler" author_url: https://clickhouse.com/blog authored_by: human publisher: "ClickHouse" publisher_url: https://clickhouse.com/ topics: ["Databases", "PostgreSQL", "Data Engineering", "Performance", "Open Source"] license: all-rights-reserved word_count: 1599 reading_minutes: 7 citation: "David Wheeler, ClickHouse. \"Introducing chdb Postgres extension: High-performance imports from cloud storage.\" 8 Sept 2026. https://clickhouse.com/blog/introducing-chdb-postgres (all-rights-reserved)" # The full text follows. The web page shows an extract and sends readers # to the source above; quote the citation and link the canonical URL. --- # Introducing chdb Postgres extension: High-performance imports from cloud storage > ClickHouse announces the chdb Postgres extension: fast imports and exports across cloud storage and formats, powered by the embedded ClickHouse engine and usable via COPY-style workflows. We're happy to announce a new Postgres extension: [chdb](https://pgxn.org/dist/chdb/). This extension expands Postgres import and export features via the [chDB library](https://clickhouse.com/docs/chdb), an in-process [ClickHouse](https://github.com/ClickHouse/ClickHouse) engine, providing efficient, flexible conversion to and from a wide array of [data formats](https://clickhouse.com/docs/reference/formats/index) living on your favorite cloud storage systems. ## Benchmark # And _boy howdy_ do we mean _efficient_! We compared [chdb](https://pgxn.org/dist/chdb/)'s performance importing the [NYC Taxi dataset](https://clickhouse.com/docs/get-started/quickstarts/tutorial) (1m rows, wide table) in a number of data formats to three other Postgres extensions, all reading from a regionally-colocated [AWS S3](https://aws.amazon.com/s3/) bucket. To the chart! > In order to minimize differences and to optimize for measurement of extension performance rather than infrastructure, the [chdb](https://pgxn.org/dist/chdb/), [pg_lake](https://github.com/Snowflake-Labs/pg_lake), and [pg_duckdb](https://github.com/duckdb/pg_duckdb) benchmarks ran on `r8id.xlarge` [ClickHouse Managed Postgres](https://clickhouse.com/cloud/postgres) services with 4 vCPUs and 32 GB RAM; the [aws_s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html) benchmark ran on a `db.r8g.xlarge` [AWS RDS](https://aws.amazon.com/rds/postgresql/) host, also with 4 vCPUs and 32 GB RAM. Results average three runs for each import. See the [benchmark source code](https://github.com/ClickHouse/pg_chdb/blob/main/dev/benchmark/) for details. Of the four extensions, [chdb](https://pgxn.org/dist/chdb/) exhibits the most consistent performance. [pg_duckdb](https://github.com/duckdb/pg_duckdb) and [pg_lake](https://github.com/Snowflake-Labs/pg_lake), both backed by [DuckDB](https://duckdb.org), take around 2-3x as long to import data from CSV, JSON, and Parquet. Only [aws_s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html) approaches [chdb](https://pgxn.org/dist/chdb/)'s performance, but it supports a much more limited array of data formats. ## Data formats # Did we mention data formats? The [chdb](https://pgxn.org/dist/chdb/) extension can read and write a slew of data formats --- all those that [ClickHouse itself supports](https://clickhouse.com/docs/reference/formats/index). This table summarizes the supported data formats and compression algorithms of the extensions we compared; note that this [chdb](https://pgxn.org/dist/chdb/) list of formats is but a subset of the [formats it supports](https://clickhouse.com/docs/reference/formats/index): 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](https://clickhouse.com/docs/reference/formats/index)! As [ClickHouse](https://github.com/ClickHouse/ClickHouse) and the [chDB library](https://clickhouse.com/docs/chdb) add more, the [chdb](https://pgxn.org/dist/chdb/) extension will get them for free! We benchmarked [chdb](https://pgxn.org/dist/chdb/) performance loading the [NYC Taxi dataset](https://clickhouse.com/docs/get-started/quickstarts/tutorial) for a number of these formats, where it demonstrated quite consistent performance: We used the [JSONCompact](https://clickhouse.com/docs/reference/formats/JSON/JSONCompact) format for compatibility with the other extensions. Other JSON formats, such as [JSONCompactEachRow](https://clickhouse.com/docs/reference/formats/JSON/JSONCompactEachRow), will more closely approximate the performance of the other formats. ## Usage # The [chdb](https://pgxn.org/dist/chdb/) package ships with two extensions: a [CREATE EXTENSION](https://www.postgresql.org/docs/current/sql-createextension.html) extension named chdb and a [hook module](https://wiki.postgresql.org/wiki/PostgresServerExtensionPoints#Hooks) named chdb_hook. ### chdb extension # The chdb extension ([docs](https://pgxn.org/dist/chdb/doc/chdb.html)) provides the `chdb_query()` function, which executes a single chDB query. For example, this query: 1SELECT * FROM chdb_query($$ 2 SELECT * FROM s3('s3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv'); 3$$) AS (id int, months int, days int); Copy command Outputs: 1id | months | days 2----+--------+------ 3 1 | 2 | 3 4 3 | 2 | 1 5 4 | 5 | 6 6(3 rows) Copy command ### chdb_hook module # The `chdb_hook` module ([docs](https://pgxn.org/dist/chdb/doc/chdb_hook.html)) hooks into the [COPY](https://www.postgresql.org/docs/current/sql-copy.html) command to copy data to or from an [AWS S3](https://aws.amazon.com/s3/), [Google Cloud Storage](https://cloud.google.com/storage), [Azure Blob Storage](https://azure.microsoft.com/en-us/products/storage/blobs/), file, or http URL. This example loads records from a CSV file on S3: 1CREATE TABLE times ( 2 id INT NOT NULL, 3 months INT NOT NULL, 4 days INT NOT NULL 5); 6 7LOAD 'chdb_hook'; 8COPY times FROM 's3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv'; Copy command After which the `times` table contains the records from the file: 1# SELECT * FROM times; 2 id | months | days 3----+--------+------ 4 1 | 2 | 3 5 3 | 2 | 1 6 4 | 5 | 6 7(3 rows) Copy command A [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html) 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): 1CREATE TABLE reviews () WITH ( 2 copy_from = 's3://datasets-documentation/amazon_reviews/amazon_reviews_2015.snappy.parquet' 3); Copy command The resulting, fully-loaded table has this structure: 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 ## Data types # Like [pg_clickhouse](https://clickhouse.com/docs/products/managed-postgres/extensions/pg_clickhouse/), [chdb](https://pgxn.org/dist/chdb/) relies on the [pg-clickhouse-c](https://github.com/ClickHouse/pg-clickhouse-c/) headers-only library to convert values from ClickHouse to Postgres, including its [type mapping](https://github.com/ClickHouse/pg-clickhouse-c/#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](https://pgxn.org/dist/chdb/) what type to use. For example, [pg-clickhouse-c](https://github.com/ClickHouse/pg-clickhouse-c/) maps a Postgres JSON value to ClickHouse String, because [ClickHouse JSON](https://clickhouse.com/docs/reference/data-types/newjson) 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: 1CREATE TABLE projects ( 2 name TEXT PRIMARY KEY, 3 meta JSON NOT NULL CHECK (json_typeof(meta) = 'object') 4); 5 6INSERT INTO projects 7VALUES ( 'chdb', '{"status": "release"}' ), 8 ( 'walrus', '{"status": "revise"}' ); Copy command To benefit from the increased flexibility and storage for object-aware storage formats such as [Parquet JSON](https://parquet.apache.org/docs/file-format/types/logicaltypes/#json), use the `structure` option to map it to [ClickHouse JSON](https://clickhouse.com/docs/reference/data-types/newjson): 1COPY projects to 'file:///tmp/projects.parquet' ( 2 structure 'name String, meta JSON' 3); Copy command ## Cloud storage URLs # The [chdb_hook](https://pgxn.org/dist/chdb/doc/chdb_hook.html) extension reads and writes to all your favorite storage platforms. It determines the appropriate protocol from the URL scheme. Schemes| Target ---|--- `file`| Absolute path on the Postgres server `http`, `https`| HTTP URL `s3`| [AWS S3](https://aws.amazon.com/s3/) `gs`, `gcs`, `oss`| [Google Cloud Storage](https://cloud.google.com/storage) `az`, `azure`, `abfss`, `abfs`| [Azure Blob Storage](https://azure.microsoft.com/en-us/products/storage/blobs/) or [Azure ABFS](https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction-abfs-uri) `hdfs`| [Hadoop Distributed File System](https://en.wikipedia.org/wiki/Apache_Hadoop#Overview) URLs may also use a number of [wildcards](https://pgxn.org/dist/chdb/doc/chdb_hook.html#Path.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 ( 2 copy_from = 's3://datasets-documentation/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv' 3); Copy command After which the `times` table contains the records from each file it loaded: 1SELECT * FROM times; 2 c1 | c2 | c3 3----+----+---- 4 1 | 2 | 3 5 3 | 2 | 1 6 4 | 5 | 6 7 1 | 2 | 3 8 3 | 2 | 1 9 4 | 5 | 6 10 1 | 2 | 3 11 3 | 2 | 1 12 4 | 5 | 6 13 1 | 2 | 3 14 3 | 2 | 1 15 4 | 5 | 6 16 1 | 2 | 3 17 3 | 2 | 1 18 4 | 5 | 6 19 1 | 2 | 3 20 3 | 2 | 1 21 4 | 5 | 6 22 (18 rows) Copy command ## Architecture # Support 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](https://clickhouse.com/docs/chdb). 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. Data loading extensions thus take a variety of approaches to managing the size and complexity of such a library by a variety of means: * [aws_s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html) simply downloads files to the local file system and passes control to [COPY](https://www.postgresql.org/docs/current/sql-copy.html); hence its limitation to [AWS S3](https://aws.amazon.com/s3/) sources and the formats that Postgres [COPY](https://www.postgresql.org/docs/current/sql-copy.html) supports * [pg_duckdb](https://github.com/duckdb/pg_duckdb) embeds the [DuckDB](https://duckdb.org) engine in the Postgres backend, overkill for occasional [COPY](https://www.postgresql.org/docs/current/sql-copy.html) needs * [pg_lake](https://github.com/Snowflake-Labs/pg_lake) runs a separate [DuckDB](https://duckdb.org)-powered service and communicates with it via the [libpq protocol](https://www.postgresql.org/docs/current/libpq.html), which permanently consumes resources on the Postgres host The [chdb extension](https://pgxn.org/dist/chdb/) adopts its own distinctive architecture: It embeds the [chDB library](https://clickhouse.com/docs/chdb) into a separate helper application. Neither the extension nor [chdb_hook](https://pgxn.org/dist/chdb/doc/chdb_hook.html) 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](https://clickhouse.com/docs/chdb) from consuming any more resources than necessary to carry out a single command. It also isolates the PostgreSQL cluster itself from [out of memory](https://en.wikipedia.org/wiki/Out_of_memory) issues that using shared memory with a [background worker](https://www.postgresql.org/docs/current/bgworker.html) would suffer. When the helper app finishes executing a command and has passed all its results to the backend (in [ClickHouse Native](https://clickhouse.com/docs/reference/formats/Native) format, straight from the source), it simply cleans up and exits, leaving the server resources to the service that most matters: PostgreSQL. 1+-------------+ 2 | helper | 3+----------+ | app | +------+ 4| Postgres | | +---------+ | | chDB | 5| Backend |<---->| | chDB | |<---->| Data | 6+----------+ | | Library | | +------+ 7 | +---------+ | 8 +-------------+ Copy command ## What's next? # We plan to continue making [chdb](https://pgxn.org/dist/chdb/) better. Potential roadmap items include: * Complete type mapping. We're gradually filling in the gap between Postgres and ClickHouse data types, to the benefit of both [chdb](https://pgxn.org/dist/chdb/) and [pg_clickhouse](https://clickhouse.com/docs/products/managed-postgres/extensions/pg_clickhouse/). * Access control to object storage via credential chain. Currently credentials required to read and write object stores must be passed explicitly in each [chdb](https://pgxn.org/dist/chdb/) call. We'd like to allow transparent, server-configured credentialing to work as well. * Support for `COPY (query) TO` * Support for a `WHERE` condition on [COPY](https://www.postgresql.org/docs/current/sql-copy.html) * Support for all of the existing [COPY](https://www.postgresql.org/docs/current/sql-copy.html) options * Support for the [Iceberg](https://iceberg.apache.org) format * Query files directly from storage ## Give it a try # Find the [chdb extension](https://pgxn.org/dist/chdb/) in all the usual places, including [GitHub](https://github.com/ClickHouse/pg_chdb) and [PGXN](https://pgxn.org/dist/chdb/). We also provide it as part of the broader [pg_clickhouse](https://clickhouse.com/docs/products/managed-postgres/extensions/pg_clickhouse/) package on [ClickHouse Managed Postgres](https://clickhouse.com/cloud/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! ### Get started with ClickHouse Managed Postgres today Interested in seeing how ClickHouse Managed Postgres works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits. [Sign up](https://console.clickhouse.cloud/signUp?intent=pg&loc=blog-cta-1837-get-started-with-clickhouse-managed-postgres-today-sign-up&utm_blogctaid=1837)