> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Import and export data from Postgres to a wide variety of data formats and object stores. # chdb Extension reference documentation

Introduction

This library provides PostgreSQL extensions for executing [chDB] queries in Postgres, and for copying data to and from a variety of formats anb object stores.

chdb Extension

The `chdb` extension runs [chDB] queries. The `chdb_query()` function executes a single query. For example, this query: ```sql theme={null} SELECT * FROM chdb_query($$ SELECT * FROM s3('s3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv') $$) AS (id int, months int, days int); ``` Outputs: ``` id | months | days ----+--------+------ 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 (3 rows) ``` See the [chdb documentation](/products/managed-postgres/extensions/chdb/chdb) for details.

chdb\_hook Module

The `chdb_hook` module hooks into the [COPY] command to copy data to or from an S3, GCS, Azure Blob, file, or http URL. This example loads records from multiple CSV files on S3 in a single [COPY] command: ```sql theme={null} CREATE TABLE times ( id INT NOT NULL, months INT NOT NULL, days INT NOT NULL ); LOAD 'chdb_hook'; COPY times FROM 's3://datasets-documentation/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv'; ``` After which the `times` table contains the records from each file it loaded: ```pgsql theme={null} # SELECT * FROM times; id | months | days ----+--------+------ 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 1 | 2 | 3 3 | 2 | 1 4 | 5 | 6 (18 rows) ``` A [CREATE TABLE] may also derive its columns, and load its rows, from such a URL: ```sql theme={null} CREATE TABLE reviews () WITH ( copy_from = 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_2015.snappy.parquet' ); ``` See the [chdb\_hook documentation](/products/managed-postgres/extensions/chdb/chdb_hook) for details.

Benchmarking Formats

A [benchmark] compare the performance of [chdb\_hook][chdb_hook] `COPY` to that of [aws\_s3][aws_s3], [pg\_duckdb][pg_duckdb], and [pg\_lake][pg_lake] for ca. 1m rows of [NYC Taxi dataset] in a variety of formats. NYC Taxi Data Benchmark Of the four extensions, [chdb] exhibits the most consistent performance. [pg\_duckdb][pg_duckdb] and [pg\_lake][pg_lake], both backed by \[DuckDB], take around 2-3x as long to import data from CSV, JSON, and Parquet. Only [aws\_s3][aws_s3] approaches [chdb\_hook][chdb_hook]'s performance, but it supports a much more limited array of data formats: | 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][formats]! | Additional benchmarking demonstrates relatively consistent performance importing the [NYC Taxi dataset] in a variety of formats: Import Benchmark The benchmark uses 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.

Architecture

The chdb and chdb\_hook extensions rely on a `chdb_helper` process to execute [chDB] queries. The helper keeps the resource consumption of [chDB] separate from the main Postgres process, an advantage for an occasionally-used workflow such as loading data from a data lake. ``` +-------------+ | helper | +----------+ | app | +------+ | Postgres | | +---------+ | | chDB | | Backend |----->| | chDB | |----->| Data | +----------+ | | Library | | +------+ | +---------+ | +-------------+ ``` Unlike a background worker, the helper holds no Postgres shared memory and the postmaster does not manage it. This isolates crashes from affecting Postgres. A helper that dies triggers an error only in the backend that started it, leaving other sessions untouched. For each query, the helper connects to a new temporary chDB database on disck to execute it. As a consequence, each query currently runs in completae isolation from all other queries. Don't create a table and expect to query it in a subsequent query.

Dependencies

The `chdb` extension requires PostgreSQL 15 or higher and the [chDB] library v26.7.0 or greater (currently available only for Linux and macOS). The simplest way to install it is via the [lib.chdb.io] shell script: ```sh theme={null} curl -sL https://lib.chdb.io | bash ``` To statically compile [chDB] into the helper app, set the following variables before running the [Installation](#compile-from-source) `make` commands. ```sh theme={null} export BUNDLE_LIBCHDB=1 LIBCHDB_BUILD=static ``` The `Makefile` will download the static `libchdb` library and compile it into the app. On Linux, you can also have the installation process download and install the dynamic `libchdb` library by setting `export BUNDLE_LIBCHDB=1` before running the [Installation](#compile-from-source) `make` commands.

Compile from source

To build chdb, just do this: ```sh theme={null} make make installcheck make install ``` If you encounter an error such as: ``` "Makefile", line 8: Need an operator ``` You need to use GNU make, which may well be installed on your system as `gmake`: ```sh theme={null} gmake gmake install gmake installcheck ``` If you encounter an error such as: ``` make: pg_config: Command not found ``` Be sure that you have `pg_config` installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the `-devel` package is also installed. If necessary tell the build process where to find it: ```sh theme={null} env PG_CONFIG=/path/to/pg_config make && make installcheck && make install ``` If you encounter an error such as: ``` chdb_helper.c:22:10: fatal error: 'chdb.h' file not found ``` You either need to install [chDB] or tell the compiler where to find it. If, for example, you installed it via the [lib.chdb.io] shell script, point to `/usr/local`: ```sh theme={null} make CFLAGS=-I/usr/local/include \ LDFLAGS=-L/usr/local/lib ``` If you encounter an error such as: ``` ERROR: must be owner of database regression ``` You need to run the test suite using a super user, such as the default "postgres" super user: ```sh theme={null} make installcheck PGUSER=postgres ``` To install the extension in a custom prefix on PostgreSQL 18 or later, pass the `prefix` argument to `install` (but no other `make` targets): ```sh theme={null} make install prefix=/usr/local/extras ``` Then ensure that the prefix is included in the following [`postgresql.conf` parameters]: ```ini theme={null} extension_control_path = '/usr/local/extras/postgresql/share:$system' dynamic_library_path = '/usr/local/extras/postgresql/lib:$libdir' ```

Authors

* [David E. Wheeler](https://justatheory.com/) * [serprex](https://github.com/serprex) Copyright (c) 2026, ClickHouse [chDB]: https://clickhouse.com/chdb "chDB - fast, reliable, and scalable in-process database" [COPY]: https://www.postgresql.org/docs/current/sql-copy.html "Postgres Docs: COPY" [CREATE TABLE]: https://www.postgresql.org/docs/current/sql-createtable.html "Postgres Docs: CREATE TABLE" [lib.chdb.io]: https://lib.chdb.io "curl -sL https://lib.chdb.io | bash" [`postgresql.conf` parameters]: https://www.postgresql.org/docs/devel/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-OTHER [chdb_hook]: https://pgxn.org/dist/chdb/doc/chdb_hook.html "chdb_hook Docs on PGXN" [aws_s3]: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html "Importing data from Amazon S3 into an RDS for PostgreSQL DB instance" [pg_duckdb]: https://github.com/duckdb/pg_duckdb "DuckDB-powered Postgres for high performance apps & analytics" [pg_lake]: https://github.com/Snowflake-Labs/pg_lake "pg_lake: Postgres with Iceberg and data lake access" [formats]: https://clickhouse.com/docs/reference/formats/index "ClickHouse Docs: Formats for input and output data" [NYC Taxi dataset]: https://clickhouse.com/docs/get-started/quickstarts/tutorial "ClickHouse Docs: Advanced tutorial" [benchmark]: https://github.com/ClickHouse/pg_chdb/tree/main/dev/benchmark "Postgres Lake Copy Benchmark"