"Just denormalize" used to be the honest answer to almost every ClickHouse join question. Not because denormalizing is bad practice (it often still is the right call), but because for years it was close to the only reliable way to keep a join from falling over. The default hash join loaded the entire right-hand table into memory on a single thread, there was no automatic join reordering, and putting the larger table on the wrong side of the JOIN could turn a query that should take seconds into one that either ran for minutes or died with Memory limit exceeded. Teams learned to flatten everything into wide tables, lean on dictionaries for lookups, and treat multi-table joins as something to route around, not something to write.
That reputation is dated. Over roughly two years of focused engineering work, ClickHouse's own benchmarks show the join-heavy TPC-H SF100 workload getting on the order of 26x faster comparing version 22.4 to 26.4, and the newest wave of that work — automatic build-side selection, statistics-based join reordering, runtime filtering — landed mostly in the last year. None of it required rewriting your queries. Here's what's actually different, with the settings and an example you can run.
The Analyzer Made the Rest Possible
Most of what follows depends on a piece of plumbing that doesn't show up in any benchmark: the new query analyzer, which replaced the old AST-based query interpreter and became the default starting in ClickHouse 24.3. The old interpreter treated a query mostly as a tree of text to rewrite; the analyzer builds a proper query plan with type information and statistics attached to it, which is what makes cost-based decisions possible in the first place. Join reordering, automatic build-side selection, and runtime filters are all downstream of having an actual plan to optimize instead of a syntax tree to pattern-match against. If you're running a ClickHouse version from before 24.3, or you've explicitly disabled the analyzer, none of the automatic behavior described below applies to you — you're still on the old rules.
Parallel Hash Became the Default, and It Scales
Standard hash join builds its hash table on a single thread: the entire right-hand side gets read and hashed before probing starts, no matter how many cores the box has. Parallel hash join splits the right-hand input into buckets — one per thread, governed by max_threads — and builds them concurrently. As of ClickHouse 24.12, parallel_hash is what the default join_algorithm priority list resolves to for most equi-joins, so this isn't something you have to opt into anymore.
The catch is memory. Building N hash tables in parallel costs more than building one: parallel hash join can use more than 2x the memory of plain hash for the same right-hand table. That's a fair trade on a query where the right side comfortably fits in RAM and you want the wall-clock win, and a bad one on a query where it doesn't. The ClickHouse JOINs memory-bound performance guide covers the failure mode in detail — the short version is that a join that used to fit under the old single-threaded hash default can now OOM under the new one, precisely because it got faster.
SELECT e.event_id, u.name
FROM events AS e
INNER JOIN users AS u ON e.user_id = u.id
SETTINGS join_algorithm = 'parallel_hash';
The Planner Reorders Joins For You Now
The right side of a hash join is the build side, and its size determines memory use and build time. Getting that backwards — big table on the right, small table on the left — used to be a manual thing to get wrong, and a lot of people did. Starting in 24.12, the query planner automatically reorders two-table joins to put the smaller table on the right, controlled by query_plan_join_swap_table (default 'auto'). You can still write the join whichever way reads best; the planner fixes the physical order for you.
That covers two tables. Real schemas usually have more, and join order across three or more tables is a much harder combinatorial problem — get it wrong and an intermediate result set can blow up before the final filter ever gets applied. ClickHouse 25.9 added global join reordering using column statistics and a cost-based search, controlled by query_plan_optimize_join_order_limit and allow_statistics_optimize. The improvement on multi-table queries isn't subtle: one documented six-table query went from roughly 65 minutes to under 3 seconds after the optimizer picked a sane order — about 1,450x, from the same tables and the same result. ClickHouse 26.3 extended the same cost-based build-side selection beyond INNER and LEFT/RIGHT joins to ANTI, SEMI, and FULL as well, which used to be excluded from automatic reordering because swapping their sides isn't just a memory optimization — it can change which rows come back.
None of this replaces judgment. EXPLAIN PLAN still tells you what the planner actually decided; check it on anything that matters:
EXPLAIN PLAN
SELECT e.event_id, u.name
FROM events AS e
INNER JOIN users AS u ON e.user_id = u.id
SETTINGS join_algorithm = 'parallel_hash';
Expression (Project names)
Join (JOIN FillRightFirst)
Expression (Change column names to avoid duplicates)
ReadFromMergeTree (default.events)
Expression (Change column names to avoid duplicates)
ReadFromMergeTree (default.users)
FillRightFirst is the plan telling you which side gets built first — the right side, as the name implies. On a query with more than two tables, run this before and after enabling allow_statistics_optimize if you're on an older minor version where it defaults off, and compare.
Runtime Bloom Filters Prune Before the Join Even Starts
This is the newest piece, and the biggest win for star-schema-shaped queries: a fact table joined against a much smaller dimension table. Runtime bloom filters, enabled by default since February 2026, build a compact bloom filter from the join key values on the build side and push it down into the storage scan on the probe side, before the probe side's rows are even fully read. Rows that can't possibly match get filtered out at scan time instead of being read, streamed through the pipeline, and discarded at the join. ClickHouse's own release benchmarking reports a roughly 2x speedup and a large drop in peak memory on star-schema workloads where the filter is effective. The setting is enable_join_runtime_filters if you need to turn it off for a specific query — it's on by default, and there's rarely a reason to disable it.
Six Algorithms, One Setting
join_algorithm now exposes six distinct algorithms: direct, hash, parallel_hash, grace_hash, full_sorting_merge, and partial_merge, plus auto as a mode that starts with hash and can switch mid-query. Each one targets a different constraint — direct for point lookups against a dictionary or Join engine table, grace_hash for a right side that might exceed memory and needs to spill to disk gracefully, full_sorting_merge for data that's already sorted on the join key. The join_algorithm setting reference walks through when each one applies; the ClickHouse JOIN performance guide has the full selection table and benchmarks for each.
A warning about auto: it is not a general-purpose safety net. It falls back to partial_merge, not grace_hash, and only if you've set max_bytes_in_join to something other than the default of 0 (unlimited). If you want graceful disk spill under memory pressure, ask for grace_hash directly:
SELECT e.event_id, u.name
FROM events AS e
INNER JOIN users AS u ON e.user_id = u.id
SETTINGS join_algorithm = 'grace_hash',
grace_hash_join_initial_buckets = 8,
max_bytes_in_join = 10000000000;
What Didn't Change
The improvements are real, but they don't move the floor, only the ceiling. max_bytes_in_join and max_rows_in_join still default to 0 — unlimited — so a join with no explicit memory bound will still run until it hits max_memory_usage and throws, exactly as it always has. The planner got smarter about ordering and algorithm selection, but it didn't start protecting you from an unbounded query by default; that's still a setting you have to reach for.
GLOBAL JOIN on a distributed cluster still broadcasts the right side to every shard rather than doing a proper shuffle join across nodes — fine for a small dimension table, a real bottleneck for a large one, and that hasn't changed. Dictionaries and materialized views are still the right answer for the hottest lookup paths in your workload; a direct join against a dictionary is still dramatically faster than any hash-based algorithm when it applies, because it skips hash table construction entirely. "The joins got better" is not a reason to stop denormalizing your hottest queries. It means the queries that used to need denormalizing just to survive now have a reasonable path without it.
Where This Leaves You
Run EXPLAIN PLAN before trusting a join's plan blindly, set a max_bytes_in_join on anything running against a table that could grow past what you've tested, and check system.query_log for ExternalJoinWritePart if you suspect a query spilled to disk without you noticing. NeverBlink watches ClickHouse's join behavior over query_log and system tables continuously and flags when a join starts spilling, when parallel_hash's memory overhead is about to become a problem on a growing table, or when a dictionary would eliminate a join outright — as a recommendation you review and apply, not a setting it changes underneath you. The algorithms are genuinely better than their reputation. Knowing which one is running, and why, is still on you.
