ClickHouse Projections: A Production Guide to a Powerful Feature

Q: Why isn't my Projection used?

Most common causes: the query uses FINAL; the query references columns the Projection does not include; the part has lightweight deletes and lightweight_mutation_projection_mode is the default throw; the optimizer estimated the base table would be cheaper; or optimize_use_projections is 0. Run EXPLAIN indexes = 1 to confirm.

Q: Can I have a Projection with a JOIN or WHERE?

No. Projection definitions support SELECT, ORDER BY, and GROUP BY only. If you need filtering or joining at write time, use a Materialized View.

Q: How do _part_offset Projections differ from full-row Projections?

Introduced in ClickHouse 25.5, _part_offset Projections store only the sorting key plus a pointer back into the base part. ClickHouse uses the Projection's primary index to locate matching rows, then reads the actual data from the base table. Storage cost drops to roughly that of a skip index; query cost is slightly higher than a full-row Projection because of the second I/O hop.

Q: Can I use Projections with ReplacingMergeTree and FINAL?

Not effectively. Queries with FINAL skip Projection optimization. If your read path depends on FINAL, design around it: use a Materialized View that materializes the deduplicated state, or accept that reads will not be Projection-accelerated.

A production-grade guide to ClickHouse Projections in 2026: when to choose them over materialized views and skip indexes, the gotchas around mutations and replication, and how the new _part_offset projections in 25.5+ change the storage trade-off.

ClickHouse Projections: A Production Guide to a Powerful Feature

A MergeTree table can only be physically sorted one way. That works until the day a product manager asks for "the same dashboard, but filtered by user_id instead of event_date". Suddenly the primary key is useless, every query becomes a near-full scan, and the cluster's I/O budget evaporates. ClickHouse Projections exist to solve exactly this problem, and as of ClickHouse 26.4 they have evolved well past the experimental feature they were a few years ago.

This guide is for engineers who already know what Projections are and want to run them in production. We will compare Projections against Materialized Views and data skipping indexes on the same dataset, walk through the operational gotchas around mutations and replication, and cover the new _part_offset index-style projections introduced in 25.5 that change the storage math.

What a Projection actually is

A Projection is a hidden, query-optimized copy of a table's data that lives inside the same MergeTree parts as the base table. It has its own sorting key, can store pre-aggregated values, and is selected automatically by the query optimizer. You query the original table; ClickHouse decides whether reading from a Projection part is cheaper than reading the base columns.

Two flavors matter in practice. A sorting Projection rewrites the data sorted by a different key, giving you a second primary index for predicate filtering. An aggregating Projection holds pre-computed sum, count, uniq, and similar values via AggregateFunction state columns, so dashboard queries hit pre-rolled rows instead of scanning raw events. Since release 25.5, there is a third flavor: a _part_offset Projection that stores only the new sorting key plus a pointer back into the base part, behaving as a true secondary index without duplicating row data.

The recommended definition style today is inline in CREATE TABLE:

CREATE TABLE events (
      event_id     UInt64,
      event_type   LowCardinality(String),
      user_id      UInt64,
      revenue      Decimal(18, 2),
      event_time   DateTime,
  
      PROJECTION by_user (
          SELECT *
          ORDER BY user_id, event_time
      ),
  
      PROJECTION dashboard_agg (
          SELECT
              toStartOfHour(event_time) AS hour,
              event_type,
              count()        AS events,
              uniq(user_id)  AS users,
              sum(revenue)   AS total_revenue
          GROUP BY hour, event_type
      )
  )
  ENGINE = MergeTree
  ORDER BY (event_date, event_time);

Adding a Projection to an existing table is a two-step operation. The ALTER TABLE ... ADD PROJECTION statement registers the definition, but only new parts will contain it. To backfill, you run ALTER TABLE ... MATERIALIZE PROJECTION p and wait. That command is implemented as a mutation, which has consequences we will cover further down.

Projections vs materialized views vs skip indexes

Engineers often ask which of the three to reach for. The honest answer is "it depends on what's slow", but there is a defensible default order. The table below summarizes the trade-offs as they stand in ClickHouse 26.x. The latency column reflects illustrative orders of magnitude consistent with publicly reported benchmarks for a billion-row events table on commodity hardware, querying SELECT count(), sum(revenue) FROM events WHERE user_id = ? with the filter outside the primary key. Numbers will vary with hardware, data distribution, and query shape - measure on your own data before committing.

Feature	Storage overhead	Insert overhead	Query latency (1.2B rows, off-PK filter)	Used automatically?	JOIN / WHERE in definition	Best for
Base table (no help)	1.0x	baseline	~4.8 s	n/a	n/a	Queries aligned with the primary key
`minmax` / `set` / `bloom_filter` skip index	~1-3%	~2-5%	~1.6 s (granule-level pruning, depends on data layout)	Yes	n/a	Sparse predicates, low-cardinality filters where blocks can be dropped
Sorting Projection (full row copy)	~0.9-1.0x extra	+30-60%	~95 ms	Yes	No JOIN, no WHERE	Off-primary-key filters and `ORDER BY` patterns where you need full row reads
Aggregating Projection	5-30% (depends on grouping cardinality)	+20-40%	~12 ms	Yes	No JOIN, no WHERE	Recurring dashboards, GROUP BY rollups
`_part_offset` Projection (25.5+)	~5-15%	+10-25%	~140 ms	Yes	No JOIN, no WHERE	Off-PK filters where storage cost of a full copy is unacceptable
Materialized View	Separate target table; varies	+20-50% per chained MV	~20-100 ms (querying target table)	No, you query the target	Yes - both supported, chaining allowed	Complex denormalization, multi-stage pipelines, joins at write time

Three rules of thumb fall out of this:

If a skip index can drop entire granules cheaply, that is the lowest-cost win and you should try it first. Skip indexes shine on predicates with strong locality. They are useless when the filter values are scattered across every granule.
If you need orders-of-magnitude reduction on an off-primary-key filter and the predicate is the access pattern (not just sparse), reach for a Projection. Pick _part_offset first when storage is a concern, full-row when query latency dominates and you read most columns.
Use Materialized Views when you genuinely need a different shape of data, when JOINs or WHERE filters belong in the transformation, or when you want to chain stages. Projections cannot express any of those.

A common mistake is treating Projections and Materialized Views as interchangeable speedups. They are not. The official documentation makes the boundary explicit: Projection definitions support neither JOIN nor WHERE, and they cannot be chained. If your read path needs filtering, denormalization, or multi-step processing, that is a Materialized View job.

Production gotchas: mutations, replication, and `FINAL`

Most production incidents we see with Projections trace back to one of three behaviors that are not obvious from the docs.

Mutations rebuild every Projection

A mutation in ClickHouse rewrites entire parts. When a part contains Projections, the mutation rewrites those too. A DELETE that would touch 200 GB of base data on a table with two Projections becomes 600 GB of write amplification, and the mutation queue length grows accordingly. On busy clusters this is the difference between a 10-minute mutation and a 4-hour one.

Lightweight DELETE does not avoid the issue. By default, parts with lightweight deletes are excluded from Projection optimization entirely, so queries silently fall back to scanning. Since 24.7, the lightweight_mutation_projection_mode setting lets you choose throw (the default, fail), drop (skip projection on those parts; queries become slower until the next merge), or rebuild (rebuild projection parts immediately; correct but expensive). Pick the mode that matches your tolerance for stale read paths versus write amplification.

Projections are used by SELECT, not by writes

A Projection is a read-side artifact. It has no role in deduplication, no role in ReplacingMergeTree collapsing, and no role in mutation predicate evaluation. If you ALTER TABLE ... DELETE WHERE user_id = 42, ClickHouse reads the base columns to find matching rows; the by_user Projection sorted by user_id does not help the mutation locate them faster. Plan accordingly: Projections accelerate analytical SELECTs, not the operational write path.

Replication multiplies the storage cost

In a ReplicatedMergeTree setup, Projections are part of the replicated state. Every replica stores the full Projection alongside the base data. With three replicas and two full-row Projections, you are looking at roughly 9x raw row volume on disk before compression effects. Several practitioners report a 3-5x effective overhead in real deployments once compression on the projection helps and the projection is narrower than the base. The lesson is that Projections add fixed cost per replica; do the math before adding three of them to a 50 TB table.

Replication also serializes Projection materialization through ZooKeeper / Keeper. A MATERIALIZE PROJECTION on a large replicated table generates many mutation log entries that every replica must execute. If a replica restarts mid-materialization, it has to wait for the mutation to finish before fully rejoining; on very large parts this can extend recovery windows.

Other operational tripwires

A few smaller items worth knowing:

Queries with FINAL bypass Projections. If most of your reads use FINAL for a ReplacingMergeTree, Projections will not help them.
The optimizer flag is optimize_use_projections (boolean, default 1). Set it to 0 in a session to force the base table path and confirm a Projection is actually doing the work.
EXPLAIN indexes = 1 shows whether a Projection was selected, including which one. If you do not see your Projection name, it was not used.
The system.projection_parts and system.projection_parts_columns tables expose disk usage and column-level sizes per Projection. Monitor them; storage growth from Projections is a slow leak that hits at the worst time.
A Projection part inherits the base part's TTL. You do not need a separate TTL definition, but you also cannot give a Projection a different retention than its base.

A workable benchmark methodology

If you are evaluating Projections on your own data, the following procedure has held up well across customer engagements. The goal is to compare like for like, not to chase a marketing number.

-- 1. Baseline. Force projections off.
  SET optimize_use_projections = 0;
  SELECT count(), sum(revenue)
  FROM events
  WHERE user_id = 12345;
  
  -- 2. Add a sorting projection and materialize it.
  ALTER TABLE events
      ADD PROJECTION by_user (SELECT * ORDER BY user_id, event_time);
  ALTER TABLE events MATERIALIZE PROJECTION by_user
      SETTINGS mutations_sync = 1;
  
  -- 3. Measure with the projection enabled (default).
  SET optimize_use_projections = 1;
  SELECT count(), sum(revenue)
  FROM events
  WHERE user_id = 12345;
  
  -- 4. Verify the projection was actually used.
  EXPLAIN indexes = 1
  SELECT count(), sum(revenue)
  FROM events WHERE user_id = 12345;

Run each query at least five times after a fresh SYSTEM DROP MARK CACHE and SYSTEM DROP UNCOMPRESSED CACHE to remove cache bias, and capture system.query_log rows for read_bytes, read_rows, and query_duration_ms. The interesting metric is usually read_bytes: a 100x reduction there is what gives you the latency improvement, and it is also what predicts how the Projection will behave on cold data.

When comparing against a Materialized View, build the equivalent target table, populate it once with INSERT INTO mv_target SELECT ... FROM events, and benchmark the direct query against the target table. The MV's read latency will often beat an aggregating Projection by a small margin because the target table can have a tighter primary key and no base-table reads, but you pay for that in query rewrite complexity and chaining limitations.

For _part_offset projections (ClickHouse 25.5 and later), expect storage closer to a skip index but query latency closer to a full-copy projection on selective filters. The compact syntax introduced in 26.1 makes them straightforward to define alongside the base table.

FAQ

Are Projections used for mutations or only for SELECTs? Only for SELECTs. ClickHouse never consults a Projection to evaluate a DELETE, UPDATE, or other mutation predicate. Mutations rewrite the affected base parts and rebuild any Projections those parts contain.

Do mutations rebuild every Projection? Yes. Any mutation that rewrites a part also rewrites every Projection inside that part. This is the dominant source of mutation slowdown on tables with Projections, and it is the main reason to use Projections sparingly.

Why isn't my Projection used? Most common causes: the query uses FINAL; the query references columns the Projection does not include; the part has lightweight deletes and lightweight_mutation_projection_mode is the default throw; the optimizer estimated the base table would be cheaper; or optimize_use_projections is 0. Run EXPLAIN indexes = 1 to confirm.

Can I have a Projection with a JOIN or WHERE? No. Projection definitions support SELECT, ORDER BY, and GROUP BY only. If you need filtering or joining at write time, use a Materialized View.

How do _part_offset Projections differ from full-row Projections? Introduced in ClickHouse 25.5, _part_offset Projections store only the sorting key plus a pointer back into the base part. ClickHouse uses the Projection's primary index to locate matching rows, then reads the actual data from the base table. Storage cost drops to roughly that of a skip index; query cost is slightly higher than a full-row Projection because of the second I/O hop.

Do Projections work on replicated tables? Yes. They are part of the replicated state and are stored on every replica. Plan for the storage multiplier: full-row Projections roughly double per-replica storage, and that doubling applies on every replica.

Can I use Projections with ReplacingMergeTree and FINAL? Not effectively. Queries with FINAL skip Projection optimization. If your read path depends on FINAL, design around it: use a Materialized View that materializes the deduplicated state, or accept that reads will not be Projection-accelerated.

Key takeaways

Projections are a read-side optimization for off-primary-key filters and recurring aggregations. They are picked automatically and require no query rewrites.
Compared to Materialized Views, Projections are simpler and transparent, but cannot express JOINs, WHERE filters, or chained pipelines.
Compared to skip indexes, Projections deliver much larger speedups on filters that scan most granules, at meaningful storage and write cost.
Mutations rewrite every Projection in the affected parts. On tables with high mutation rates, this is the dominant operational cost and the main reason to keep the Projection count small.
Replication multiplies storage cost linearly per replica. Two full-row Projections on a three-replica cluster mean roughly 9x raw row volume before compression.
Since ClickHouse 25.5, _part_offset Projections offer index-like storage with most of the query benefit. They are the right starting point when storage is the binding constraint.
Always verify Projection usage with EXPLAIN indexes = 1 and monitor system.projection_parts for storage growth.

Weighing Projections against skip indexes and materialized views for a real workload is the kind of trade-off our ClickHouse consulting team gets asked about a lot, from one-off schema reviews to full query-performance overhauls.