Introducing chdb Postgres extension: High-performance imports from cloud storage

Summary

The new chdb Postgres extension uses the chDB library, an in-process ClickHouse engine, to import and export data between Postgres and files on cloud storage. In benchmarks using the one-million-row NYC Taxi dataset, chdb ran on four-vCPU, 32-GB ClickHouse Managed Postgres instances and showed the most consistent performance; pg_duckdb and pg_lake took about two to three times longer for CSV, JSON, and Parquet, while aws_s3 supported fewer formats. Its chdb_query() function executes chDB queries, while chdb_hook integrates with COPY for AWS S3, Google Cloud Storage, Azure Blob Storage, local files, and HTTP URLs, with wildcard expansion for multiple files. The extension launches a separate helper application on demand and exchanges ClickHouse Native results through file descriptors, limiting resource use and isolating the PostgreSQL cluster.

Takeaways

1. 1

   chdb supports formats including TSV, CSV, JSON, BSON, Prometheus, Protobuf, Avro, Parquet, Arrow, XML, CapnProto, Markdown, MsgPack, and ORC, along with gzip, zstd, lz4, bz2, snappy, and brotli compression.

2. 2

   In the NYC Taxi benchmark, pg_duckdb and pg_lake took about two to three times longer than chdb for CSV, JSON, and Parquet imports; aws_s3 approached chdb's performance but supported fewer formats.

3. 3

   The helper application starts on demand, communicates through STDIN, STDOUT, STDERR, and a configuration file descriptor, then exits after passing results to the PostgreSQL backend.