Projections
Pre-sorted alternate layouts
A table can only be physically sorted one way. If half your queries
filter by user_id and the other half filter by event_type, one
of those query patterns will never get the sparse index's help — no
matter how you pick ORDER BY. Projections solve this by keeping
additional physical copies of the data, sorted differently, that
the query optimizer chooses between automatically.
INSERT updates both copies atomically.Declaring one
ALTER TABLE events
ADD PROJECTION by_event_type
(
SELECT *
ORDER BY (event_type, ts)
);
ALTER TABLE events MATERIALIZE PROJECTION by_event_type;ADD PROJECTION only registers the definition; it applies to new
parts as they're written. MATERIALIZE PROJECTION builds it for
existing data. Once built, it's maintained automatically on every
future insert and merge — you never write to it directly.
How the optimizer uses it
You keep querying the base table as normal. ClickHouse examines the
query's WHERE/GROUP BY and picks whichever physical layout — the
base table or one of its projections — would read the least data to
answer it. This is transparent: no query rewriting on your end, no
risk of querying "stale" data, since both copies are updated as part
of the same insert.
Projections vs. materialized views
Both maintain a second copy of data incrementally. The difference is what that copy is for:
- A projection stores the same rows (or an aggregate of them), physically re-sorted, and is chosen automatically by the query planner against the same table name.
- A materialized view writes into a separate, explicitly named target table that you query directly yourself. See Materialized Views.
Every projection roughly doubles (or more) the storage and insert cost for that table, since every write now maintains N physical copies. Add projections deliberately for specific, high-value query patterns — not speculatively for every column combination you might someday filter on.