Split View: ClickHouse lazy materialization — LIMIT 10짜리 트릭이 FINAL과 JOIN까지 자란 과정

ClickHouse Lazy Materialization — How a LIMIT 10 Trick Grew Into FINAL and JOIN

• Introduction — How to Read "The Same SQL Got 1,576x Faster"
• What Lazy Materialization Actually Is
• The Vendor Benchmark's Conditions — The 3 Minutes Turns Out to Be the Disk
• Why It Started at LIMIT 10 Only — The History of the Gate Going 10 → 100 → 10,000
• 2026: The Same Idea, New Query Shapes
• Where It Doesn't Apply, and What to Watch For
• Closing
• References

Introduction — How to Read "The Same SQL Got 1,576x Faster"

In April 2025, ClickHouse's official blog carried a sentence like this: without changing a single line of the query, 219 seconds became 0.139 seconds — 1,576x. A number like that invites one of two reactions: say "wow" and move on, or take apart the conditions that produced it. This post takes the second path.

The number's protagonist is lazy materialization. When it was introduced in 25.4, it was an optimization with a very narrow gate, applying only to queries with LIMIT 10 or less. But from late 2025 through the first half of 2026, that gate widened noticeably — to LIMIT 10,000 when the execution model was overhauled in 25.12, to every branch of UNION ALL in 26.2, to ReplacingMergeTree's FINAL in 26.4, and to a selective LIMIT after JOIN in 26.6 (released June 25, 2026). It is the story of a single trick growing into a principle that runs through the whole query planner, and the traces of reverted PRs and bug fixes along the way are still visible.

If ClickHouse's MergeTree structure or vectorized execution is new to you, it may help to read ClickHouse Internals Deep Dive and the real-time OLAP optimization piece first. This post builds on that and covers only what changed across the 2026 releases.

What Lazy Materialization Actually Is

The official docs describe this feature as the last layer of ClickHouse's I/O optimization stack. Stacked from the bottom up, it looks like this.

1. Column-oriented storage. Columns the query doesn't need are never read at all.
2. Sparse primary key index, skipping indexes, projections. Filters on indexed columns discard whole granules (8,192-row blocks by default).
3. PREWHERE. Filters on columns that aren't indexed are also evaluated sequentially, cheapest column first, so later columns are read only for the rows that survive.
4. Query condition cache. On repeated queries, it remembers granules that didn't match last time and skips them.

There's one shared assumption behind everything up to this point — that for a row which passes the filter, every column in the SELECT list has to be read in full before you can sort or aggregate. Lazy materialization breaks that assumption. It reads only the columns needed for sorting first, and only after sorting and LIMIT have settled on the final rows does it fetch the remaining SELECT columns — and only for those rows. In a Top-N query like ORDER BY small_column LIMIT 3 that also drags along a 50GiB text column in the SELECT list, that text column only needs to be read for 3 rows' worth.

Whether it kicks in can be checked directly in the execution plan.

EXPLAIN actions = 1
SELECT helpful_votes, product_title, review_headline, review_body
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3;

Lazily read columns: review_headline, review_body, product_title
  Limit
    Sorting
      ReadFromMergeTree

If you see the Lazily read columns line, it's applied. The docs also make explicit that this kind of row-level lazy reading is only possible because of column-oriented storage in the first place — in a row-oriented database, the whole row gets read together anyway, so the idea doesn't even apply.

The Vendor Benchmark's Conditions — The 3 Minutes Turns Out to Be the Disk

Now let's look at the conditions that produced that 1,576x. Every number below is a vendor-measured figure ClickHouse published on its own blog, and the measurement environment is likewise stated there: an AWS m6i.8xlarge (32 vCPU, 128GiB RAM), fitted with a 1TiB gp3 SSD at default settings — 3,000 IOPS, 125MiB/s maximum throughput — and the OS page cache was cleared before every run (cold cache). The data is 150.96M rows of Amazon reviews, 70.47GiB uncompressed, 30.05GiB after ZSTD(1) compression.

Here's the ladder of stacking optimizations one layer at a time, for a filtered query (date, category, verified-purchase, and star-rating filters plus ORDER BY helpful_votes DESC LIMIT 3).

For a pure Top-N query with all filters removed, 219.071 seconds becomes 0.139 seconds. Data processed goes from 71.38GB to 1.81GB, and peak memory from 1.11GiB to 3.80MiB. This is where the 1,576x comes from.

There's one piece of arithmetic worth doing to interpret this number. If you add up the compressed sizes of the four columns the filter-free query reads, from the table published on the blog, you get roughly 26.8GiB (review_body 21.60GiB + product_title 3.53GiB + review_headline 1.58GiB + helpful_votes 72.11MiB). Divide that by the disk's throughput cap of 125MiB/s and you get about 219 seconds — essentially identical to the measured 219.071 seconds. In other words, the baseline's 3 minutes 39 seconds isn't ClickHouse being slow; it's the physical time it takes to read 27GiB off a throughput-capped disk. The blog itself even sticks a snail emoji next to the disk spec. Because lazy materialization's payoff is "bytes not read ÷ disk speed," this setup is also exactly the setup where that payoff looks its largest.

Let's also be clear about what was not disclosed. There are no comparison numbers in this blog for hot cache (data already sitting in the page cache) — every measurement is cold cache. There are no multiples for faster NVMe or local SSDs either. And this particular query is shaped in the way most favorable to this optimization — roughly 99.7% of the SELECT target's compressed bytes belong to the deferred columns (the three text columns above). The blog is honest about this point too — depending on the dataset and query shape, indexes or PREWHERE may deliver a bigger win instead. If your query only selects a few narrow columns, there are no bytes to defer in the first place, and the multiple comes out completely different.

Why It Started at LIMIT 10 Only — The History of the Gate Going 10 → 100 → 10,000

At launch (25.4, April 22, 2025), the gate was surprisingly conservative. query_plan_max_limit_for_lazy_materialization defaulted to 10 — if LIMIT exceeded 10, the optimization simply didn't kick in at all. The 25.12 release notes explain the reason well, in retrospect. The initial implementation fetched the remaining columns row by row after the Top-N rows were settled. When LIMIT was large, this lookup turned into finely scattered random reads, and the overhead ate up the benefit of the deferred reads.

The vendor's own demo quantifies this. Run SELECT * FROM hits ORDER BY EventTime LIMIT 100000 on the web analytics dataset (hits, 100M rows, same m6i.8xlarge + gp3) with the gate switched off (= 0) — the row-by-row approach from 25.11 turns into roughly 10 million individual lookups (104 non-order columns × 100,000 rows). Across 3 runs, that took 34.2 to 38.7 seconds. Turn off lazy entirely on the same query (eager reads everything up front instead), and it's 7.0 to 7.9 seconds. In other words, at this point the old lazy approach was nearly 5x slower than eager — though it processed overwhelmingly less data, 1.20GB versus 56.83GB, and peak memory of roughly 1GiB versus 74–78GiB. Looking at time alone, it's obvious why the gate was set to 10.

In 25.12 (December 18, 2025), this execution model was overhauled (PR #90309; the release notes call it a "join-style execution model"). Instead of row-by-row lookups, it builds a set of identifiers for the Top-N rows and fetches them in bulk from the base table as if joining — getting the same vectorized, parallel-execution benefits as an ordinary join. The same demo query dropped to 0.513–0.524 seconds (roughly 75x over the old approach and roughly 14x over eager, by the vendor's own figures — though this demo is based on the fastest of 3 repeated runs, and the cache state isn't stated). As a result, the gate's default was raised to 10,000. The settings history file preserves this whole trajectory — introduced at 10 in 25.4, raised to 100 in 25.11 ("More optimal"), and to 10,000 in 25.12 ("Increase the limit after performance improvement").

The reason it stopped at 10,000 is also in the release notes: lazily materialized columns still need to be sorted to match the Top-N row order, and when LIMIT gets very large, that cost starts to show up again. The gate is a confession of the tradeoff — this optimization isn't free; it's a bet that wins when N is small.

One more thing: from 25.8 onward, lazy materialization only applies when the new analyzer is enabled (#83791). The analyzer has been the default for a long time now, but if your environment runs with enable_analyzer = 0 for backward compatibility, none of this post applies to you.

2026: The Same Idea, New Query Shapes

Everything so far is prehistory; the 2026 releases were the work of porting the same idea — "defer reading until after sort and LIMIT" — onto new query shapes. Everything below is confirmed from the changelog and the merged PRs themselves.

26.2 (February 26, 2026) — every branch of UNION ALL. Until now, lazy materialization only applied to the first branch of a UNION ALL. As of #96832, it applies to every branch — a query that combines sorted, LIMIT'd reads from different MergeTree tables now gets the deferred-read I/O savings on each branch. Note that this item never made it into the public changelog, so the fact that it landed in 26.2 rests on the merge record left in the PR (26.2.1.706). It reads more like patching a hole in the implementation than a new feature, but it's a real difference for the common pattern of splitting a time series across several tables and stitching them back together with UNION ALL.

26.4 (April 30, 2026) — ReplacingMergeTree's FINAL. #101647 ported lazy materialization to FINAL reads. The mechanism is summarized in the setting's description — under selective conditions, it builds a set of primary keys and re-runs index analysis against it. One caveat, though: while the changelog lists this as a performance improvement, as of the master branch's Settings.cpp, query_plan_optimize_lazy_final still defaults to false — that is, it's opt-in. On top of that, three guardrail settings shipped alongside it: if the PK set exceeds 10 million rows or 256MB, it falls back to regular FINAL (max_rows_for_lazy_final, max_bytes_for_lazy_final), and if index analysis fails to filter out at least half of the marks, it also falls back (min_filtered_ratio_for_lazy_final, default 0.5). The sheer number of guardrails is itself a signal of the trust level here. In fact, the 26.5 changelog lists two fixes to the lazy FINAL path — a logical error during pipeline expansion (#103230) and a server abort where release builds segfault under a PREWHERE combination (#104177). If FINAL is a bottleneck in your ReplacingMergeTree workload, it's worth turning on and A/B testing — but it's fair to read the off-by-default state as being that way for a reason.

26.6 (June 25, 2026) — selective LIMIT after JOIN. As part of this release's bundle of JOIN optimizations: when a selective LIMIT, TopN, or another JOIN follows a JOIN, the left table's payload columns (the ones that aren't join keys) are no longer materialized immediately — instead they're carried as a selector/replication index (#106566). Materialization happens only after the row count has shrunk, cutting copy costs on queries where most of the join result gets discarded by the LIMIT. There are two gates: at least 3 left-side payload columns (query_plan_min_columns_for_join_lazy_indexing, 0 disables it), and LIMIT no greater than 1,000 (query_plan_max_limit_for_join_lazy_indexing, 0 means unlimited). Starting with a gate far narrower than the main feature's 10,000 is the same pattern seen at the original launch.

This 26.6 feature has a footnote. It was originally merged as #98883 on May 26, 2026, but three days later CI caught a Bad cast from type DB::ColumnReplicated to DB::ColumnString logical error (#106095), it was reverted, and it was reintroduced on June 19 with a fix that materializes the column during the sort-conversion step. The whole cycle played out between the 26.5 and 26.6 releases, so a broken version never shipped in a stable release — but it's a good illustration of the structural difficulty running through this whole family of optimizations: a deferred column representation has to pretend to be a real column everywhere in the pipeline.

It's also interesting that, around the same time, the word "lazy" itself has been spreading to other parts of the codebase. 26.3 brought an experimental feature that handles JSON column type-hint changes as a metadata operation instead of rewriting data (allow_experimental_json_lazy_type_hints, #97412), and 26.6 brought an experimental feature that decodes only as much of a text index's posting list as needed, cursor-based, instead of fully unpacking it into a Roaring Bitmap (allow_experimental_text_index_lazy_apply, #100035). Both are still experimental, but the direction is the same — if you put off the work until the last possible moment, a good chunk of it turns out to never need doing at all.

Where It Doesn't Apply, and What to Watch For

To sum up, as of mid-2026, a query has to meet the following conditions for this optimization to kick in.

• A Top-N shape — it has to be an ORDER BY ... LIMIT N style query, with N at or below 10,000 (default gate). The JOIN variant needs N at or below 1,000 plus 3 or more payload columns. The FINAL variant has to be enabled manually.
• The new analyzer has to be enabled (the default).
• If arrayJoin appears in the plan, it doesn't apply — this was deliberately excluded in 26.5 (#101644). It was a correctness issue where LIMIT might not be respected, so this exclusion is about correctness, not performance.

Let's also spell out the cases where there's structurally no benefit. If every SELECT column is narrow (comparable in size to the sort column), there are no bytes to defer. Queries that consume the entire result (ones that end in an aggregation, or export-style queries with no LIMIT) aren't a target to begin with. And the vendor has never published the size of the benefit in a hot environment, where the working set is entirely in the page cache — meaning you cannot translate the cold-cache 1,576x into how much your own dashboard's latency will improve. You have to measure it with your own data. Fortunately, an A/B test is just one session setting away.

-- turn it off and compare
SELECT ... SETTINGS query_plan_optimize_lazy_materialization = false;
-- force it on for a LIMIT outside the gate (default gate is 10000)
SELECT ... SETTINGS query_plan_max_limit_for_lazy_materialization = 0;

Last, we have to talk about the bug trail. This feature has had fixes land in nearly every release since it launched — reading Variant columns (25.8, #84400), CORRUPTED_DATA when it overlapped with external sorting (25.8, #84738), AMBIGUOUS_COLUMN_NAME when it overlapped with projections (25.6, #80251), handling of old parts for columns added via ALTER (25.12, #91142), and even in 26.6, a fix for a case where the sort column was picked wrong and produced TYPE_MISMATCH (#107060). This isn't meant to scare you — it's meant to point out that a bug in an optimization that's on by default is a bug in a code path you never chose to turn on. That's exactly why you should read the lines mentioning "lazy" in the release notes when you upgrade ClickHouse, and why query_plan_optimize_lazy_materialization = false is a valid first line of isolation when a query dies strangely or slows down.

Closing

Boiled down to one line, lazy materialization's trajectory over the past year or so is this: born in 25.4 as a conservative LIMIT-10 trick (the PR itself was opened back in October 2023 and sat for a year and a half), overhauled into a join-style execution model in 25.12 that widened the gate 1,000x, and through 2026 it's been extending its reach to UNION ALL (26.2), FINAL (26.4, opt-in), and JOIN (26.6). Its practical value is clear in that this is the kind of improvement that gets faster from an upgrade alone, without changing a single line of your query.

At the same time, the boundaries confirmed in this post are just as clear. The headline multiple is a vendor-measured figure that came out of cold cache, a throughput-capped disk, and a query shape where the deferred columns account for most of the bytes — and even the vendor published, alongside it, the figure showing the old approach running 5x slower than eager once LIMIT gets large. The gates, fallbacks, and opt-in defaults aren't decoration; they're honest documentation of this optimization's cost structure.

It's also interesting to look at this alongside what other analytical databases are tinkering with these days — while DuckDB is swapping out its client-server protocol and Elasticsearch is replacing its vector index default, ClickHouse is digging into a single question — "how much can reading be deferred?" — one query shape at a time. Watching how these gates keep moving in future releases — just following SettingsChangesHistory.cpp is enough to keep tracking how mature this feature gets.

References

• ClickHouse gets lazier (and faster): Introducing lazy materialization — official blog, 2025-04
• Lazy materialization — official docs guide
• ClickHouse Release 25.12 — the row-by-row-to-join-style redesign and demo figures
• ClickHouse CHANGELOG.md — 26.1–26.6 source text
• 2025 Changelog — the 25.4 introduction entry (#55518) and the 25.x fix history
• Settings.cpp — current defaults for lazy-related settings / SettingsChangesHistory.cpp — the gate's 10 → 100 → 10,000 history
• Key PRs: #55518 introduction · #90309 25.12 refactor · #96832 UNION ALL · #101647 lazy FINAL · #106566 JOIN lazy indexes reintroduction
• Release presentations — 25.4 / 25.12 / 26.6