When does adding a ClickHouse projection speed up a query, and what does it cost?

## The problem projections solve A MergeTree table has exactly one physical sort order, given by its `ORDER BY`. Queries that filter or group along that order are fast; queries that need a different order scan far more data. The classic bind is a table sorted by `(event_date, user_id)` that also serves a dashboard filtering by `domain` — the second query cannot prune, and you cannot give the table two sort orders. A **projection** is ClickHouse's answer: an additional, automatically maintained copy of the data stored *inside the same parts*, in its own order or pre-aggregated. ## The two kinds **Normal projection** — the same rows in a different sort order: ```sql ALTER TABLE hits ADD PROJECTION p_by_domain ( SELECT * ORDER BY domain, event_time ); ``` Queries filtering on `domain` can now prune within the projection instead of scanning the base data. **Aggregate projection** — a pre-aggregated rollup: ```sql ALTER TABLE hits ADD PROJECTION p_domain_daily ( SELECT domain, event_date, count(), sum(revenue) GROUP BY domain, event_date ); ``` A matching query reads the small rollup instead of aggregating raw rows. Either way you must materialise it for data that already exists: ```sql ALTER TABLE hits MATERIALIZE PROJECTION p_domain_daily; ``` Without that step, only newly written parts carry the projection, and queries fall back to the base data for older parts. That fallback is per part and silent, which surprises people. ## How selection works The optimizer decides, per query and per part, whether a projection can answer it — the projection must contain the columns the query needs, and its ordering or grouping must fit the query's filters and aggregation. `optimize_use_projections` enables this consideration. Because the fallback is silent, verification matters: run the query with `force_optimize_projection = 1` and it will raise an error if no projection was used, or compare `read_rows` in `system.query_log` before and after — a working aggregate projection collapses that number by orders of magnitude. ## What it costs **Write amplification.** Every insert builds the projection's data alongside the base data, and every background merge merges the projections too. On an ingest-heavy table this is a measurable throughput cost, and it multiplies with each projection you add. **Storage.** A normal projection is effectively a second copy of the columns it names — compressed, but a copy. An aggregate projection is usually small, which is why it is the better-value shape. **Merge pressure.** More data per part means longer merges and more I/O in the background pool, which competes with query execution on the same machine. **Maintenance surface.** Projections are part of the table definition, so schema evolution has to account for them. ## Projection or materialized view? They overlap, and the distinction is the interview's real target. A ClickHouse **materialized view** is a trigger on insert that writes into a *separate* target table; you query that table explicitly, and it sees only the rows that flowed through the insert path. A **projection** lives inside the source table's parts, is chosen by the optimizer transparently, and is kept consistent by the same merge machinery as the base data — including for data inserted before it existed, once materialised. Rule of thumb: choose a projection when you want one table to answer several query shapes without the application knowing; choose a separate rollup table when you want an independently sized, independently retained artefact that other systems read directly. ## When it is the wrong tool - When the query is already fast because it matches the table's sort order — you are paying writes for nothing. - When the workload is ad-hoc and unpredictable: a projection helps a *shape*, and an unpredictable shape has none. - When ingest throughput is the binding constraint. Adding projections to a table at its write ceiling makes the ceiling lower. - When a plain rollup table maintained by the pipeline is simpler to reason about and retain separately. ## The practical routine Identify the one or two expensive query shapes from `system.query_log` — sort by `read_bytes`, not duration. Add the narrowest projection that serves them, materialise it, and measure the same queries' `read_rows` again. Then measure ingest throughput and part sizes to see what you paid. If either number disappoints, drop it: `ALTER TABLE ... DROP PROJECTION` is cheap, and an unused projection is pure write tax.