DATABASES Signal 283
What's new in pg_clickhouse v0.10.0: Subqueries, TPC-H Speedups, C Driver, and Aggregates
↗
pg_clickhouse v0.10.0 expands subquery pushdown, raising fully pushed TPC-H queries from 12 to 16, rebuilds the C-based binary driver, and widens aggregate support.
Engineers using PostgreSQL-to-ClickHouse federation can now run more analytical queries entirely inside ClickHouse, cutting latency by orders of magnitude for workloads that previously required row-by-row evaluation. The updated C driver reduces maintenance overhead and fixes concurrency bugs that could cause intermittent failures under load. Knowing the remaining unsupported TPC-H shapes helps teams plan further query rewrites or await future pg_clickhouse enhancements.
Written by elseif from the cluster below · every claim links back to a sourceThe three things worth knowing
Fully pushed TPC-H queries increased from 12 of 22 to 16 of 22, with Q17 dropping from 32.7 seconds to 37 milliseconds.
The binary driver was rewritten on a plain-C client library and hardened against concurrency issues.
Pushdown now covers more functions and aggregates, but ClickHouse 25.8 or newer is required for correlated subquery shapes.
THE READ
What the cluster adds up to.
The release pushes more of the TPC-H benchmark into ClickHouse, moving the full-pushdown count from 12 to 16 out of 22 queries. This shift means the planner can send the entire query text to ClickHouse for those statements, eliminating the need to fetch rows back to PostgreSQL for local evaluation. The latency improvements shown for Q25, Q17 and Q22 illustrate the impact: execution times fall from seconds or hundreds of milliseconds to a few tens of milliseconds. These gains are measured against the same hardware and scale factor used in prior reports.
A major internal change is the replacement of the previous binary driver with a new implementation built on a plain-C client library. This rewrite was undertaken to simplify the codebase and to address concurrency bugs that could surface when multiple sessions simultaneously issued pushdown requests. The hardened driver now handles shared state more safely, reducing the chance of crashes or incorrect results under heavy load. Engineers upgrading to v0.10.0 will see the same API but with improved reliability.
Subquery pushdown now works by folding correlated EXISTS or scalar subqueries into a single remote SQL statement that PostgreSQL still records as a SubPlan node for bookkeeping. The planner converts the subquery into a LEFT SEMI JOIN or LEFT ANTI JOIN in ClickHouse, allowing the whole comparison to be shipped in one statement. However, certain TPC-H queries remain unpushed because PostgreSQL flattens their subqueries into anti/semi-joins whose inputs are themselves joins, and the current deparser cannot walk a join tree on both sides of such a join. This limitation affects Q13, Q15, Q16, Q18, Q20 and Q21.
The pushdown mechanism depends on ClickHouse version 25.8 or newer; older servers lack the correlated-subquery SQL shape and cause pg_clickhouse to fall back to local evaluation, preserving the previous behavior. Additionally, the team corrected the handling of NOT IN to respect PostgreSQL’s three-valued logic, ensuring that expressions involving NULL produce the same results locally and remotely. Without this correction, naïve pushdown could invert filter outcomes when NULLs appear in the right-hand side of an IN list.
Written by elseif from the cluster below · checked for specifics the sources never containedTHE CLUSTER