What's New in pg_clickhouse - JSONB Support, SQL value functions, Streaming, and more

Summary

Recent releases of pg_clickhouse, a Postgres foreign data wrapper for ClickHouse, introduce query pushdown enhancements and streaming result handling. Version 0.1.10 adds pushdown for JSONB accessor operators and extraction functions by mapping them to ClickHouse sub-column syntax and toJSONString calls outside SELECT clauses. In version 0.2.0, date and time functions such as CURRENT_DATE, CURRENT_TIMESTAMP, and clock_timestamp() push down to ClickHouse while honoring the active Postgres session time zone and precision settings. To address memory pressure and out-of-memory risks from large foreign queries, the extension adds query result streaming to the HTTP driver with a default batch buffer of approximately 50MB. Container benchmarking against the NYC taxi dataset showed streaming reduced peak memory consumption from over 600 MiB to under 86 MiB.

Takeaways

1. 1

   JSONB accessor operators (->, ->>) and extraction functions in filtering and sorting clauses push down to ClickHouse sub-column syntax and toJSONString conversions.

2. 2

   Date and time functions such as CURRENT_DATE, CURRENT_TIMESTAMP, and clock_timestamp() push down to ClickHouse equivalents like toDate, now64, and nowInBlock64 using the active Postgres session time zone.

3. 3

   Query result streaming in the HTTP driver buffers results in batches of approximately 50MB, preventing large foreign scans from loading complete datasets into Postgres memory at once.