clickhouse.guru
Tutorials· intermediate· Tested on ClickHouse 26.4

Projections: hidden tables that speed up the base table

A projection stores the same data re-sorted or pre-aggregated inside the table's own parts, and ClickHouse picks it automatically. How to add, backfill, verify and when to prefer a materialized view.

A table has one primary key, so it is fast for one access pattern. A projection is an extra copy of the data, kept inside the same table, that is sorted differently or pre-aggregated. You query the table as usual; the optimizer decides, per query, whether a projection can answer it more cheaply than the base data.

A good mental model: a projection is a hidden table that lives in each data part of the table, is written and merged together with it, and is chosen automatically. You never name it in a query.

Two kinds

  1. A re-sorted copy: SELECT * ORDER BY other_column (or a subset of columns). Speeds up filters on a column that is not at the front of the table’s ORDER BY.
  2. A pre-aggregation: SELECT ... GROUP BY .... Speeds up repeated aggregations without a separate target table.

Re-sorted copy

The demo table is sorted by (event_type, event_time). A lookup by user_id cannot use that index:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-03-01 00:00:00') + number, number % 5000,
       ['view','click','purchase'][1 + number % 3], concat('/p/', toString(number % 7)),
       10 + (number * 7) % 500, ['DE','TR','US'][1 + number % 3]
FROM numbers(1000000);

EXPLAIN indexes = 1 SELECT count() FROM events WHERE user_id = 42;
        ReadFromMergeTree (default.events)
        Indexes:
          PrimaryKey
            Condition: true
            Parts: 1/1
            Granules: 123/123

Condition: true and 123/123 granules: the whole table is read. Add a projection sorted by the column:

ALTER TABLE events ADD PROJECTION by_user (SELECT * ORDER BY user_id);

ADD PROJECTION applies only to parts written from now on. For existing parts, materialize it (a mutation that rewrites the parts’ projection data):

ALTER TABLE events MATERIALIZE PROJECTION by_user SETTINGS mutations_sync = 2;

Now the same query:

      ReadFromMergeTree (by_user)
      Indexes:
        PrimaryKey
          Keys:
            user_id
          Condition: (user_id in [42, 42])
          Parts: 1/1
          Granules: 1/123
          Search Algorithm: binary search

The plan now reads from by_user and touches 1 granule out of 123. That ReadFromMergeTree (projection_name) line is how you confirm a projection is used.

Pre-aggregation

ALTER TABLE events ADD PROJECTION daily_by_country (
    SELECT toDate(event_time) AS day, country, count() AS c, sum(duration_ms) AS total_ms
    GROUP BY day, country
);
ALTER TABLE events MATERIALIZE PROJECTION daily_by_country SETTINGS mutations_sync = 2;

EXPLAIN SELECT toDate(event_time) AS day, country, count() FROM events GROUP BY day, country;

The plan ends in ReadFromMergeTree (daily_by_country). A query that groups by the same (or a coarser) key and uses aggregates the projection can supply (count(), sum(duration_ms), and what can be derived from them) reads the tiny aggregated data instead of all rows. In the demo, the projection holds 36 rows for a million-row table:

SELECT name, rows, parent_name FROM system.projection_parts WHERE table = 'events' AND active;
┌─name─────────────┬────rows─┬─parent_name─┐
│ by_user          │ 1000000 │ all_1_1_0_3 │
│ daily_by_country │      36 │ all_1_1_0_3 │
└──────────────────┴─────────┴─────────────┘

Note the first projection stores all one million rows again, i.e. roughly doubles the stored data for those columns, while the aggregate one is tiny. Projections are not free.

How the optimizer chooses

  • It considers a projection for a query when the projection can fully answer it: filters on the projection’s key, or the right grouping and aggregates.
  • Among candidates (including the base table), it picks the one estimated to read the least data.
  • SET optimize_use_projections = 0 turns the feature off for a query, useful for comparing timings. force_optimize_projection = 1 makes a query fail if it cannot use a projection, handy in tests; force_optimize_projection_name forces a particular one.
  • EXPLAIN (as above) is the ground truth. Don’t assume.

Managing projections

ALTER TABLE events DROP PROJECTION by_user;
ALTER TABLE events CLEAR PROJECTION by_user;        -- delete data, keep definition
SELECT * FROM system.projection_parts WHERE table = 'events' AND active;

Costs and limits

  • Storage and write amplification. Every insert and every merge also writes the projection data. A projection with SELECT * costs about as much as another copy of the table.
  • Merge semantics. Projections are built per part. With engines that rewrite rows during merges (ReplacingMergeTree, CollapsingMergeTree), a projection can disagree with the base table, and some combinations are restricted or controlled by settings. Check the docs for your engine before using projections there.
  • Lightweight deletes and mutations. Updates and deletes interact with projections, and by default some of them are refused or need a setting. Check lightweight_mutation_projection_mode.
  • No cross-table logic. A projection reads only its own table. There are no joins.
  • Pre-aggregates are one grain. Different GROUP BY shapes need different projections.

Projection or materialized view?

Projection Materialized view
Lives inside the table’s parts in a separate target table
Queried transparently, same table name you query the target explicitly
Consistency always in sync with the parts (same merges, same mutations) updated per insert block; mutations on the source don’t reach it
Flexibility one table, same engine any engine, joins (left-side trigger), different TTL, different retention
Keeps raw data out no, the base table stays yes, you can drop or TTL the source

Choose a projection when you want “the same table, but fast for another filter or a fixed aggregation,” and you want ClickHouse to keep it correct without any pipeline. Choose a materialized view when the aggregate needs its own engine, TTL or retention, or a join, or when the raw table should expire.

If your only goal is to skip data for a selective filter on another column, also consider a data-skipping index: it costs far less storage than a full re-sorted projection.

Reference: projections in the docs.

projectionsperformance

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.