Postgres FDW: Pushdown is a negotiation

ClickhouseKaushik Iska, David Wheeler, Philip DubéMay 14, 2026

Explainer

Summary

Postgres Foreign Data Wrapper extensions allow PostgreSQL to query external datastores like ClickHouse by delegating execution to remote engines. Engineering pg_clickhouse centers on pushdown decisions that determine whether SQL expressions run remotely or stream raw data back across the wire. Because pushdown depends on planner callbacks, deparser translations, and ClickHouse semantic compatibility, a single untranslated clause can block upper-level query pushdown entirely. Resolving pushdown barriers enables complex analytic queries to return hundreds of rows in milliseconds rather than pulling tens of millions of rows for local processing. Ultimately, pushdown development requires an iterative negotiation across differing SQL grammars, occasionally requiring the revocation of translations that fail to maintain strict equivalence.

Context

PostgreSQL Foreign Data Wrappers (FDWs) like pg_clickhouse query remote systems like ClickHouse, but transferring raw data across the network for local aggregation is slow. Engineering efficient FDW queries depends on determining which SQL clauses can be safely pushed down to the remote database without altering query semantics.

Approach / What changed

pg_clickhouse registers planning callbacks such as GetForeignPaths, GetForeignJoinPaths, and GetForeignUpperPaths with the Postgres planner. Its SQL deparser incrementally maps Postgres expressions—such as JSON access, window functions, and filtered aggregates—into ClickHouse SQL, prioritizing semantic correctness and revoking pushdowns when exact behavioral parity cannot be guaranteed.

Takeaways

Pushdown is granular and all-or-nothing at the upper-relation level; a single unsupported sub-expression or aggregate can block an entire grouped query from running remotely.
Foreign Data Wrappers register specific planning callbacks in Postgres, including GetForeignPaths and GetForeignUpperPaths, to offer foreign execution paths before deparsing SQL.
Pushdowns must be revoked if SQL dialect semantics diverge, as seen when pg_clickhouse removed array function pushdowns to prevent returning incorrect results.

Read original post

Postgres FDW: Pushdown is a negotiation

ClickhouseKaushik Iska, David Wheeler, Philip DubéMay 14, 2026

Explainer

Summary

Context

Approach / What changed

Takeaways

Pushdown is granular and all-or-nothing at the upper-relation level; a single unsupported sub-expression or aggregate can block an entire grouped query from running remotely.
Foreign Data Wrappers register specific planning callbacks in Postgres, including GetForeignPaths and GetForeignUpperPaths, to offer foreign execution paths before deparsing SQL.
Pushdowns must be revoked if SQL dialect semantics diverge, as seen when pg_clickhouse removed array function pushdowns to prevent returning incorrect results.

Read original post

Related reading

Clickhouse · Apr 24, 2026
What's New in pg_clickhouse - JSONB Support, SQL value functions, Streaming, and more
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.
David Wheeler
Clickhouse · May 7, 2026
Introducing Postgres Query Insights in ClickHouse Cloud
ClickHouse Cloud introduced Query Insights in preview for Managed Postgres to help teams diagnose slow database queries. Powered by the open-source extension pg_stat_ch, the tool streams per-statement telemetry directly into ClickHouse to enable fast aggregations across billions of execution rows. Postgres normalizes query literals into placeholders during the parse-analyze phase before telemetry leaves the database, preventing sensitive data exposure. Telemetry emission maintains around three percent producer overhead by utilizing a non-blocking try-lock on a shared-memory ring buffer. Users can examine high-level database health metrics, rank query patterns by runtime or latency percentiles, and inspect execution flyouts detailing disk spills, cache hits, and parallel worker behavior.
Amog Iska
Clickhouse · Apr 7, 2026
ClickHouse Release 26.3
ClickHouse 26.3 delivers twenty-seven new features, forty performance optimizations, and over two hundred bug fixes. Common table expressions can now be materialized into temporary tables via the MATERIALIZED clause when enabled, evaluating subqueries once to reduce query execution times and peak memory usage. The query optimizer expands join reordering beyond inner and left or right joins to include anti, semi, and full joins when table statistics are available. In addition, user-defined functions can run compiled WebAssembly code inside sandboxed Wasmtime runtimes. Storage for the Map data type also introduces hash-based bucketing during background merges, accelerating single-key lookups by two to forty-nine times without slowing initial inserts.
ClickHouse
Clickhouse · Mar 26, 2026
Top 10 best practices tips for ClickHouse
ClickHouse delivers rapid analytical queries across massive datasets, but suboptimal schemas and queries can significantly reduce performance and storage efficiency. Aligning table definitions with ClickHouse storage and execution mechanics resolves these bottlenecks. Key practices include placing low-cardinality columns first in the ORDER BY clause to enable granule pruning, and selecting optimal data types while avoiding Nullable wrappers to cut memory overhead. Developers should treat partitioning as a data management tool for TTL drops or merge engines rather than a generic speed booster, preventing fragmented parts from slowing query execution. Recent improvements like the Analyzer query planner have also made joins viable, though dictionaries remain faster for static lookups.
Yonatan Dolan