ClickHouse Index vs Projection vs Materialized View

In ClickHouse®, pick based on what the mechanism costs you. A data-skipping index costs metadata per granule. A lightweight projection costs a sorting key and a _part_offset pointer. A full projection costs roughly twice the storage. A materialized view costs write time and is the only one that can reshape the data. Before reaching for any of them, check the sorting key.

Since version 25.5, a lightweight projection stores the sorting key plus a _part_offset pointer, and uses its own primary index to prune granules while reading rows from the base table. A full projection stores a complete second copy of the data in a different order. The lightweight one has more in common with a data-skipping index than with the full projection sitting under the same syntax.

Picking the wrong one of those two costs you either a doubled storage bill you did not need, or a query that stays slow no matter what you tune.

Check the sorting key before anything else

Most tables that get a projection did not need one.

The sorting key sits in front of the ladder. Changing it means rebuilding the table, which puts it in a different category from anything you can add with ALTER TABLE. You weigh a skip index against a projection. The sort order is a decision you already made when you created the table.

Column order inside that key is where the cheap wins hide. We've seen cases where this single change translates into 400 times less data read for the same query, with no new objects to maintain and nothing extra to keep in sync. PREWHERE sits in the same category: free, already on by default for most cases, and worth confirming before you add machinery.

The mechanics for both live in query optimization: what makes ClickHouse fast. If your ORDER BY already fits the access pattern and the query is still slow, keep reading.

Choosing between an index, a projection, and a materialized view

A skip index tells ClickHouse which granules it can ignore. A projection gives ClickHouse a different set of granules to read. A materialized view gives it a different table entirely, written at insert time.

	Skip index	Lightweight projection	Full projection	Materialized view
Storage cost	Metadata per granule	Sorting key + _part_offset	~2x the table	Size of the target table
Write cost	Small, per index	Small	Data written twice	Insert-time trigger
Reshapes data	No	No	No	Yes: JOINs, filters, routing
Query change needed	None	None	None	Query the target table
Consistency	Atomic with the part	Atomic with the part	Atomic with the part	Diverges if an insert fails
Chainable	Not applicable	No	No	Yes
Separate TTL	No	No	No	Yes
Breaks when	Values scatter across granules	Every granule matches	Storage budget runs out	Fan-out on insert

Two rows carry most of the decision. Reshaping data is the hard boundary: projection definitions prohibit JOINs and WHERE clauses, so anything requiring a lookup or a filter at write time belongs to a materialized view.

Depth for each one lives elsewhere: skip index variants and their sizing in query optimization, both projection types in the complete guide to projections, and insert-time behaviour in materialized views: patterns and pitfalls.

285 milliseconds and 180 seconds on the same table

The same lightweight projection answered one query in 285 milliseconds and timed out after 180 seconds on the next, on the same table with the same query shape. The only difference was which value sat in the WHERE clause.

The table holds 194 billion rows across 13.3 TiB on SharedReplacingMergeTree, running ClickHouse 26.3 over object storage. Users look up token transfers by receiver, which is not in the sort key, so the table got a lightweight projection: PROJECTION by_receiver_id INDEX (receiver, id) TYPE basic.

That was our recommendation. Storage was the binding constraint, so we took the rung that costs almost nothing and planned to climb only if the numbers forced us. They did.

For a typical receiver it works. The projection prunes down to 26 thousand rows and 369 KB, and the query returns in 285 milliseconds with the filesystem cache warm.

Then someone queried an exchange address. That single receiver holds 1.57 billion rows, roughly 0.8% of the table. Spread across the table's physical order, about 65 of its rows land in each granule of 8,192. Every granule contains at least one matching row, so every granule survives pruning, and the query reads 828 GB before timing out at 180 seconds.

Nothing about that is fixable with settings.

A granule-pruning mechanism can only help when the matching rows are concentrated in a small share of granules, and concentration is a property of how your data is distributed against the table's sort order. Your index type and your tuning have no effect on it. Skip indexes and lightweight projections both prune granules, so they fail on the same values for the same reason.

The fix was climbing to a full projection, ordered by (receiver, block_time, id), so the rows for one receiver physically sit together. That costs roughly another 13.3 TiB. At that point the storage row in the comparison table is the entire decision.

The test takes ten minutes. Take your most common filter value and your worst one, the heaviest account or the noisiest tenant. Estimate matching rows per granule for each. In a big table, if the worst value matches in most granules, a skip index and a lightweight projection are both ruled out before you build either, and you can go straight to pricing the storage for a full projection or a materialized view.

Where each one stops working

A skip index on values that scatter across granules skips nothing. You paid the write overhead and got the scan you already had.

A projection can be slower than having none. Past a few terabytes on the table, the planner opens projection metadata inside every part before deciding what to read, and each of those reads takes time. We ran into this on a 20+ TB table and only reached a p50 of 213 ms after keeping every mark and index file resident in RAM, which is its own write-up.

Materialized views fail differently, and worse. A failed insert into an MV target leaves that target inconsistent with the source, while a projection failure is handled in the background without splitting your data in two. Fan-out is the other bill: one insert feeding six views is six writes.

The last one comes up in most of these conversations. Instead of a projection, store the rows twice in the same table under two different sorting keys. It costs the same disk as a full projection, but it is not scalable for more access patterns.

The rule worth saving

Fix the sorting key first, then price the rest by what it costs you.

Take your worst filter value rather than your average one and estimate matching rows per granule. Concentrated in a few granules: a skip index or a lightweight projection will do, and both are cheap enough to test in an afternoon. Spread across most granules: skip both, go to a full projection, and budget the storage before you promise anyone a latency number. Needs a JOIN, a filter, or its own retention: that is a materialized view, and query the target table directly.

The middle case is where this bites you. A lightweight projection holds up in staging against evenly distributed test data, then falls over in production on the one account that holds 1% of the rows.

If you are running projections on a table past 10 TB, we are happy to look at it with you.

FAQ