Projections

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.

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.