ClickHouse Projections vs Materialized Views: A Practical Benchmark

I benchmarked ClickHouse projections against materialized views (MVs) on a 200M-row web analytics dataset: query latency, storage overhead, and ingest cost. I also tested what happens when background merges fall behind, and that turned out to be the most interesting result.

The short version:

1. For aggregation queries, both work equally well. ~50 ms instead of 470 ms on 200M rows, a 9.4× speedup, and projection vs MV is a tie.
2. Storage cost is the same too: +8% for an aggregating projection, +8% for an MV target. Re-sort projections are different: they store a full copy of the table (+129%).
3. Ingest is the hidden price. In my measurement, projections cost 73% of write throughput, MVs 69%.
4. With unmerged parts, both are slower than the base table. In a 55-part state, queries against the projection and the MV were 1.5–1.7× slower than a plain scan. After merging, the expected 4.2× speedup appeared.
5. Below a few tens of millions of rows, you don’t need either. A 10M-row scan takes 36 ms.

All data, scripts and plots: comino/blog-experiments. Numbers are median ± IQR over 10 runs.

The Difference in 30 Seconds

Projections live inside the table: an alternative sort order or a pre-aggregation stored per part. The optimizer picks them automatically, queries stay plain SQL. But it only picks them when GROUP BY and aggregation functions match exactly.

Materialized views write into a separate target table on every INSERT. You get full control over schema, engine and TTL, but queries against the target need -Merge() combinator functions like countMerge() and avgMerge().

Both store partial aggregate states that ClickHouse merges in the background. That shared mechanism is exactly why both fall over when merging lags, as you’ll see below.

Setup

• Server: Hetzner CX53 with 16 vCPU (AMD EPYC), 32 GB RAM, NVMe SSD
• ClickHouse: 25.11.3.54 (the multi-part experiment ran later on 26.1.2)
• Dataset: 200M synthetic web analytics rows (110M for the multi-part experiment)
• Cold runs: all ClickHouse and OS caches dropped before each measurement
• Data fits in RAM, so “cold” means NVMe re-read, not sustained disk I/O

Data Generation

INSERT INTO exp02_projections.web_analytics_base
SELECT
    toDateTime('2024-01-01') + rand(number) % (365 * 86400) AS timestamp,
    rand(number + 1) AS user_id,
    concat('/page/', toString(rand(number + 2) % 1000)) AS page,
    rand(number + 3) % 10000 AS duration_ms,
    ['US','UK','DE','FR','JP','CN','BR','IN','CA','AU','MX','KR','IT','ES','NL',
     'SE','NO','DK','FI','PL','CZ','AT','CH','BE','PT','IE','RU','TR','ZA','EG',
     'NG','KE','AR','CL','CO','PE','TH','VN','PH','MY','SG','ID','TW','HK','NZ',
     'IL','SA','AE','QA','UA'][1 + rand(number + 4) % 50] AS country,
    ['desktop', 'mobile', 'tablet'][1 + rand(number + 5) % 3] AS device_type
FROM numbers(200000000);

Timestamps span exactly one year (8,760 distinct hours), pages take 1,000 distinct values. That means the pre-aggregated key space is 1,000 × 8,760 = 8.76M (page, hour) combinations. This number shows up again and again below.

All tables use ORDER BY (page, timestamp). After insert, OPTIMIZE TABLE … FINAL merged each table into a single part (except in the multi-part experiment).

The Contenders

Base table, no tricks:

CREATE TABLE web_analytics_base (
    timestamp DateTime,
    user_id UInt32,
    page LowCardinality(String),
    duration_ms UInt32,
    country LowCardinality(String),
    device_type LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY (page, timestamp);

Projection table: same schema plus two projections. One re-sorts the full data by country, one pre-aggregates hourly stats:

CREATE TABLE web_analytics_proj (
    -- ... same 6 columns ...
    PROJECTION proj_country_time (
        SELECT * ORDER BY country, timestamp
    ),
    PROJECTION proj_hourly_stats (
        SELECT page,
               toStartOfHour(timestamp) AS hour,
               count() AS hits,
               avg(duration_ms) AS avg_duration,
               sum(duration_ms) AS sum_duration
        GROUP BY page, hour
    )
) ENGINE = MergeTree()
ORDER BY (page, timestamp);

MV: a target table with aggregate-state columns, fed by a materialized view:

CREATE TABLE hourly_stats_mv_target (
    page LowCardinality(String),
    hour DateTime,
    hits AggregateFunction(count, UInt64),
    avg_duration AggregateFunction(avg, UInt32),
    sum_duration AggregateFunction(sum, UInt32)
) ENGINE = AggregatingMergeTree()
ORDER BY (page, hour);

CREATE MATERIALIZED VIEW hourly_stats_mv
TO hourly_stats_mv_target AS
SELECT
    page,
    toStartOfHour(timestamp) AS hour,
    countState() AS hits,
    avgState(duration_ms) AS avg_duration,
    sumState(duration_ms) AS sum_duration
FROM web_analytics_mv_source
GROUP BY page, hour;

Note the -State functions. They store intermediate aggregation states, not final values. Reading them back requires the matching -Merge() functions.

Storage

The aggregating projection and the MV target cost the same: ~108 MB for 8.76M pre-aggregated rows. The +129% case is the re-sort projection proj_country_time: it stores a complete second copy of all six columns, sorted differently. Know that before you add one.

Query Benchmark

Three queries against the 200M-row tables:

-- Q1: Point lookup (one page, one day)
SELECT count(), avg(duration_ms)
FROM web_analytics_base          -- or web_analytics_proj
WHERE page = '/page/0'
  AND timestamp BETWEEN '2024-01-15' AND '2024-01-16';

-- Q2: Country filter (one month). Country is not in the base sort key.
SELECT count(), avg(duration_ms)
FROM web_analytics_base          -- or web_analytics_proj
WHERE country = 'US'
  AND timestamp BETWEEN '2024-01-01' AND '2024-02-01';

-- Q3: Full aggregation (top pages by avg duration)
SELECT page, avg(duration_ms) AS avg_d
FROM web_analytics_base          -- or web_analytics_proj
GROUP BY page ORDER BY avg_d DESC LIMIT 10;

-- Q3 against the MV target needs -Merge():
SELECT page, avgMerge(avg_duration) AS avg_d
FROM hourly_stats_mv_target
GROUP BY page ORDER BY avg_d DESC LIMIT 10;

Results (median ± IQR, ms, n=10):

What this says:

• Q1: the base ORDER BY (page, timestamp) already handles point lookups well. Nothing to win here. EXPLAIN confirms the optimizer doesn’t even use a projection for this query.
• Q2: the re-sort projection saves 3 ms cold. That’s what +129% storage buys on this query mix.
• Q3 is the whole story: instead of scanning 200M rows, both pre-aggregation approaches read the 8.76M-row summary. 470 ms becomes 50 ms.

For a clean head-to-head I also ran an aggregating projection against an MV on two more query shapes (n=10): filtering one page is a tie (70 vs 69 ms cold), and a full scan across all pages has the MV ~10% ahead (511 vs 570 ms cold). Its dedicated AggregatingMergeTree table has a small edge over the projection, which shares storage with the base table.

What the Optimizer Actually Picks

Projections only fire when the query matches. I tested 8 patterns against the projection table:

A sum() query will not use a projection that stores avg(). An extra GROUP BY column breaks the match. Always check with EXPLAIN indexes=1 instead of assuming.

When Does Pre-Aggregation Pay Off?

Same aggregation query (Q3) at three dataset sizes, single merged part, cold:

At 10M rows the base scan takes 36 ms. Nobody needs to optimize that. The point where pre-aggregation starts to matter is somewhere between 10M and 50M rows for this query on this hardware.

The mechanism behind the growing speedup: the base scan grows linearly with table size, but the pre-aggregated summary is capped by its key space (8.76M rows, no matter how big the base table gets). The bigger the table, the bigger the win.

The Multi-Part Reality Check

Everything above used OPTIMIZE FINAL: one merged part, the best case. Production doesn’t look like that. Data streams in, parts pile up, and background merges have to catch up. So I built the bad case deliberately: 110M rows inserted in 11 batches with background merges stopped, leaving 55 unmerged parts.

The state before merging, from system.parts:

The MV target should have 8.76M rows. It has 98.3M, eleven times too many, because every unmerged part carries its own partial aggregates for the same (page, hour) keys.

Same aggregation query as before, on this state:

With 55 unmerged parts, the base table wins. The projection and MV queries had to read 98M rows of aggregate-state data, which is wider and more expensive per row than the base table’s plain integers. After OPTIMIZE FINAL, the MV target collapsed from 98.3M to 8.76M rows and both approaches delivered their 4.2× speedup.

What I take from this:

1. Merging is a prerequisite, not a cleanup step. Unmerged projections and MVs can actively hurt.
2. The MV target row count is your canary. SELECT count() FROM mv_target should be close to your key-space size. Mine was 11× over.
3. High ingest with lagging merges is the danger zone. My 55-part state is an extreme; a production burst might create 5–20 parts with proportionally milder damage. But the direction is the same.

Ingest Cost

Ingest throughput, fresh tables, rand()-generated rows, n=10:

That is a lot. Both approaches compute aggregations on every insert, and it shows.

The absolute overhead depends on how you measure: in a second test that copied rows from an existing table instead of generating them (n=5), the projection cost 49% and the MV 30%. The ordering was the same in both tests: the MV is consistently the cheaper of the two on ingest, because it writes its aggregates to a separate table instead of computing them inside every part of the base table.

If ingest is your bottleneck, look at Refreshable Materialized Views (ClickHouse 23.12+): they recompute periodically instead of on every insert, trading freshness for write throughput. I didn’t benchmark them here.

Which One?

My default: start with the base table. When a specific aggregation query gets too slow, add an aggregating projection first, because it’s transparent to every query and every team member. Move to an MV when you need cascading, separate TTL, or cheaper ingest.

And whichever you pick: watch your parts. An unmerged MV target with 98M rows instead of 8.76M is not an optimization, it’s a regression.

Do you need an alternative sort order?
  → Yes: Re-sort projection (costs a full data copy)
  → No: Do you need pre-aggregation at all?  (< ~50M rows: probably not)
    → Ingest-critical, cascading rollups, or own TTL?
      → Yes: Materialized View (requires -Merge() syntax)
      → No:  Aggregating projection (transparent SQL)

Either way: monitor system.parts and keep background merges healthy.

Limitations

1. Synthetic uniform data. Real traffic has power-law pages and geographic clustering. The 8.76M-key saturation behavior depends on my uniform distribution.
2. Data fits in RAM. With data well beyond memory, pre-aggregation’s I/O advantage grows.
3. The 55-part state is artificial. I stopped background merges to build it. It shows the mechanism, not a typical magnitude. The multi-part run also used a newer ClickHouse (26.1.2); the effect follows from the MergeTree architecture, not the version.
4. Single writer. Concurrent inserts interact with merge scheduling in ways I didn’t measure.
5. Refreshable MVs and MV chaining untested. Both would matter in a real decision.
6. Projection operational caveats not explored: adding a projection later requires a full MATERIALIZE PROJECTION rescan, and projections share the base table’s TTL and partition lifecycle.

References

1. Projections vs Materialized Views (ClickHouse Docs)
2. Projections Deep Dive (ClickHouse Docs)
3. Altinity: Projection Examples
4. Refreshable Materialized Views (ClickHouse Docs)

Tested on ClickHouse 25.11.3.54 / 26.1.2, Hetzner CX53 (16 vCPU, 32 GB RAM, NVMe SSD). Raw data, scripts and plots: github.com/comino/blog-experiments.

Questions, corrections, or a consulting project? Mail me or find me on GitHub.