pg_clickhouse v0.10.0: Subquery Pushdown & TPC-H Gains

Subquery Mastery & TPC-H Triumph

The v0.10.0 release of pg_clickhouse marks a significant leap forward, primarily by enhancing subquery pushdown capabilities and achieving substantial speedups on the TPC-H benchmark suite. The ability to push down complex subqueries, especially correlated ones and NOT IN clauses, as single remote queries (LEFT SEMI JOIN and LEFT ANTI JOIN respectively) is a major technical achievement. This not only drastically reduces query latency, as demonstrated by the three orders of magnitude improvement on Q17, but also showcases pg_clickhouse outperforming native PostgreSQL for specific workloads. The architectural shift to a plain-C client library (clickhouse-c) addresses critical issues like C++ exception handling conflicts and memory scaling, while also streamlining build times and reducing footprint. Furthermore, the explicit control over compression and TLS, along with wider type coverage for multidimensional arrays, adds robustness and flexibility. The introduction of clickhouse_query and clickhouse_perform functions provides more granular control for executing arbitrary SQL, enhancing its utility beyond just data retrieval.

However, the article highlights that six TPC-H queries remain unpushed, with the primary blocker being the deparser's inability to traverse join trees on both sides of a join. This suggests that while significant progress has been made, there's still a considerable amount of work to fully achieve parity for complex query structures. The nuanced handling of three-valued logic in NOT IN clauses, while a necessary and impressive feat, also underscores the inherent complexity of bridging the semantic differences between PostgreSQL and ClickHouse. The opt-in nature of built-in function pushdown, while safer, will require ongoing effort to grow the list of supported functions. The deprecation of fetch_size for the HTTP driver due to the native protocol's streaming behavior might require users to adapt their configurations. Finally, while the C driver migration is a clear positive, the long-term maintenance and evolution of this new dependency will be crucial.

This release is highly beneficial for organizations that use PostgreSQL as their primary OLTP database but offload analytical workloads to ClickHouse. Developers and database administrators responsible for performance tuning, especially those dealing with complex analytical queries that involve subqueries or require efficient data integration between PostgreSQL and ClickHouse, will find these improvements invaluable. The focus on TPC-H benchmarks indicates a strong commitment to high-performance analytics, making pg_clickhouse a more compelling solution for data warehousing and business intelligence scenarios where ClickHouse's speed is leveraged. The expanded function and aggregate pushdown also means more SQL logic can be executed directly on ClickHouse, reducing data transfer and improving overall query execution times.

Key Points

• Significant improvement in subquery pushdown, enabling correlated subqueries and NOT IN clauses to be executed as single remote queries.
• Substantial TPC-H benchmark speedups, with 16 out of 22 queries now fully pushed down, demonstrating orders-of-magnitude performance gains.
• Architectural modernization with a switch to a plain-C client library (clickhouse-c) for the binary driver, improving stability and efficiency.
• Expanded coverage of functions, operators, and aggregates that can be pushed down to ClickHouse.
• Introduction of clickhouse_query and clickhouse_perform for executing arbitrary SQL statements and procedures.
• Enhanced driver capabilities including explicit compression and TLS controls, and wider type coverage for multidimensional arrays.

---

📖 Source: What's new in pg_clickhouse v0.10.0: Subqueries, TPC-H Speedups, C Driver, and Aggregates