The evolution of ClickHouse to an analytics platform for all workloads

NATURAL JOIN

ClickHouse now includes support for NATURAL JOIN, simplifying queries by automatically matching columns with the same name and removing duplicate fields from the output.

JOIN spilling to disk

Large hash joins now gracefully spill to disk when memory thresholds are exceeded, allowing queries to continue executing instead of failing under memory pressure.

Statistics by default

ClickHouse now generates basic column statistics automatically for new tables, giving the optimizer better cardinality estimates and pruning decisions without impacting insert performance.

Adaptive hash join selection

ClickHouse now automatically picks the optimal hash join strategy at runtime based on cardinality estimates, reducing query planning overhead.

Faster lazy reading with join-style execution model

Deferred data reads during join processing reduce unnecessary I/O and memory consumption for complex joins.

Faster joins with a more powerful join reordering algorithm

A global join reordering algorithm evaluates entire join graphs to choose more efficient multi-table execution plans.

Bloom filters in joins

Runtime Bloom filter pre-filters on join keys eliminate non-matching rows before the actual join phase.

Lazy columns replication in joins

Lazy columns replication avoids processing repeated join key values, reducing CPU and memory usage significantly.

Push-down of complex conditions in joins

Complex OR conditions inside join predicates are now pushed down earlier, reducing rows participating in joins.

Automatic global join reordering

The optimiser now determines the best join order for queries with multiple joined tables, improving multi-table performance.

Optimisations for hash joins

Low-level optimisations to the parallel hash join improve CPU utilisation and throughput across multiple cores.

Faster parallel hash join — build phase

Improvements to the build phase reduce thread contention and improve scalability for large join workloads.

Faster parallel hash join — probe phase

Enhancements to the probe phase and internal hash map structures improve performance consistency and overall join efficiency.

Automatic join reordering

The optimiser now selects better join execution plans automatically, without requiring manual query restructuring.

Optimisation of JOIN expressions

JOIN expressions are automatically optimised and common expressions extracted and simplified algebraically.

Non-equi joins supported by default

Non-equal join conditions (experimental since 24.05) are now enabled by default in all deployments.

Parallel hash join becomes default

The parallel hash join algorithm is now the default join strategy, enabling better multi-core utilisation out of the box.

JOIN improvements

Expanded non-equal JOIN strictness variants and a new OPTIMIZE TABLE for Join-engine tables reduce memory footprint by up to 30%.

Merge join algorithm for ASOF JOIN

ASOF JOIN now works with the non-memory-bound full sorting merge join algorithm for large-scale time-series joins.

Faster parallel hash join

Optimised hash table allocation and reuse strategies reduce overhead and improve execution speed for repeated join queries.

Cross JOIN improvements

Performance and efficiency improvements to cross joins reduce resource usage and make Cartesian joins more practical.

Non-equal JOIN support

Support for non-equal join conditions (<, >, <=, >=) enables more expressive analytical queries beyond equi-joins.

Recursive CTE support

Recursive common table expressions enable hierarchical and graph-like traversals directly in SQL.

Join performance improvements

General join performance enhancements reduce inefficiencies and improve predicate handling across all join types.

PASTE JOIN

PASTE JOIN joins datasets by row number rather than key, useful when equivalent rows across datasets refer to the same item.

Grace hash JOIN & reduced memory for hash join

The Grace Hash join overcomes memory limits of partial merge join, and hash join memory overhead is also reduced.

Join improvements begin

Baseline TPC-H benchmark established. Many queries still TIMEOUT or fail with unsupported correlated-subquery syntax — the starting point for the multi-year journey of join improvements that follows.