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.

Context

Postgres foreign data wrapper queries against ClickHouse previously lacked pushdown for JSONB accessors and certain date/time functions, and default whole-result memory buffering risked memory exhaustion on large datasets.

Approach / What changed

The pg_clickhouse extension added pushdown translations for JSONB operators, string functions, and session-aware date/time functions to ClickHouse, along with batch-based result streaming in the HTTP driver.

Takeaways

• JSONB accessor operators (->, ->>) and extraction functions in filtering and sorting clauses push down to ClickHouse sub-column syntax and toJSONString conversions.
• 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.
• 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.

Related reading