ClickHouse 26.10 lets IN subqueries spill to disk instead of failing

ClickHouse shipped version 26.10 on 6 November: 104 new features, 225 performance optimisations and 739 bug fixes, by its own count. The one worth checking first: a large IN (subquery) no longer has to fail outright when the set it builds doesn’t fit in memory.

Previously, a query comparing rows against a big subquery result would die with MEMORY_LIMIT_EXCEEDED once that set got too large to hold in RAM. A new setting, max_bytes_before_external_set, lets ClickHouse spill the set to disk instead:

SELECT count() FROM events
WHERE id IN (SELECT id FROM events WHERE date < '2024-01-01')
SETTINGS max_memory_usage = '512Mi',
         max_bytes_before_external_set = '128Mi';

In ClickHouse’s own benchmark, a query that needed 1.96GiB to finish in memory completed with a 203MiB peak once capped this way. The release also speeds up GROUP BY on multiple LowCardinality columns by just over 30% (255ms to 171ms in their test), and adds a function composition operator, so arrayMap(lower | domainWithoutWWW | upper, urls) replaces a nested chain of calls.

Why it matters: if you’ve ever abandoned an IN (subquery) because it blew your memory budget, this setting trades a slower query for one that actually finishes, which matters more than raw speed when the alternative is no answer at all.

The caveat: disk spilling needs the setting turned on explicitly, it isn’t the default, and ClickHouse warns it’s slower than keeping the set in memory.