11. Materialised views and projections

Chapter 7 said the way to make a repeated aggregation fast is to compute it before it is asked. This chapter is how.

It is also the chapter with the biggest naming trap in ClickHouse, so let us deal with that first.

A materialised view is an insert trigger

It does not read your existing data

A ClickHouse MATERIALIZED VIEW is not a stored query result that stays in sync with a table. It is a trigger that fires on every insert into the source table, runs a query over only the newly inserted block, and writes the result somewhere else.

Consequences:

• It sees only new data. Creating a view over a table with 40 billion existing rows gives you an empty target. You must backfill historical data yourself.
• It never sees updates or deletes. Mutations on the source do not propagate.
• It fires per inserted block, so the query inside it must be expressible over one block at a time — which is exactly why aggregate states exist.

Read that as “insert trigger” every time you see the words, and everything else in this chapter is straightforward.

The basic form

CREATE MATERIALIZED VIEW mv_hourly
TO hourly_stats                        -- ← the target table (explicit: do this)
AS
SELECT
    toStartOfHour(ts) AS hour,
    country,
    count()           AS views,
    sum(duration)     AS total_duration
FROM page_views
GROUP BY hour, country;

Now every insert into page_views also aggregates that block and inserts the result into hourly_stats.

Always use the TO table form. The alternative, where ClickHouse creates a hidden .inner. table for you, makes the target harder to manage, alter and back up. Create the target table explicitly:

CREATE TABLE hourly_stats (
    hour           DateTime,
    country        LowCardinality(String),
    views          UInt64,
    total_duration UInt64
) ENGINE = SummingMergeTree
ORDER BY (country, hour);

Note the engine: SummingMergeTree. Each insert block produces its own partial row for a given (country, hour), and the engine adds them up over time (chapter 4). Query it with sum() … GROUP BY and the answer is correct regardless of merge state.

The pattern that matters — AggregatingMergeTree

SummingMergeTree handles counts and sums. For averages, uniques and quantiles you need aggregate states, and this is the pattern worth learning properly because it is the backbone of every fast ClickHouse dashboard.

1. The raw table

CREATE TABLE page_views (
    ts       DateTime,
    tenant_id UInt32,
    country  LowCardinality(String),
    user_id  UInt64,
    url      String,
    duration UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, country, ts)
TTL ts + INTERVAL 90 DAY;

2. The rollup target

CREATE TABLE hourly_rollup (
    hour        DateTime,
    tenant_id   UInt32,
    country     LowCardinality(String),
    views       AggregateFunction(count),
    users       AggregateFunction(uniq, UInt64),
    avg_ms      AggregateFunction(avg, UInt32),
    p95_ms      AggregateFunction(quantileTiming(0.95), UInt32)
) ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, country, hour)
TTL hour + INTERVAL 2 YEAR;       -- keep rollups far longer than raw data

That TTL asymmetry is the point of the whole exercise: 90 days of raw events, two years of hourly rollups. The rollup is a tiny fraction of the size.

3. The view

CREATE MATERIALIZED VIEW mv_hourly_rollup TO hourly_rollup AS
SELECT
    toStartOfHour(ts) AS hour,
    tenant_id,
    country,
    countState()                      AS views,
    uniqState(user_id)                AS users,
    avgState(duration)                AS avg_ms,
    quantileTimingState(0.95)(duration) AS p95_ms
FROM page_views
GROUP BY hour, tenant_id, country;

4. Querying it

SELECT
    hour,
    country,
    countMerge(views)   AS views,
    uniqMerge(users)    AS unique_users,
    avgMerge(avg_ms)    AS avg_duration,
    quantileTimingMerge(0.95)(p95_ms) AS p95
FROM hourly_rollup
WHERE tenant_id = 7 AND hour >= now() - INTERVAL 7 DAY
GROUP BY hour, country
ORDER BY hour;

And the property that makes states worth the trouble — roll up further, correctly:

SELECT
    toStartOfMonth(hour) AS month,
    uniqMerge(users)     AS monthly_unique_users     -- ← correct, not a sum
FROM hourly_rollup
GROUP BY month;

You cannot sum hourly unique-user counts to get a monthly figure; a user active in six hours would count six times. Merging the HyperLogLog states gives the right answer. That is the single best argument for aggregate states, and it is worth stating to anyone who asks why the schema looks like this.

5. Backfilling

The view only sees new data, so populate history once:

INSERT INTO hourly_rollup
SELECT
    toStartOfHour(ts), tenant_id, country,
    countState(), uniqState(user_id), avgState(duration),
    quantileTimingState(0.95)(duration)
FROM page_views
WHERE ts < '2026-08-20 00:00:00'         -- ← up to when the view was created
GROUP BY 1, 2, 3;

Mind the boundary. Backfill up to the moment the view started firing, or you will double-count the overlap. The safe procedure is: create the view, note the time, then backfill strictly before that time.

For a large backfill, do it partition by partition rather than in one statement — chapter 6’s warning about max_partitions_per_insert_block applies.

Chained views

A view can read from another view’s target, giving you a rollup hierarchy:

   page_views  ──MV──▶  hourly_rollup  ──MV──▶  daily_rollup  ──MV──▶  monthly_rollup
    90 days               2 years                 5 years              forever

Use -MergeState in the intermediate steps so each level produces states for the next:

CREATE MATERIALIZED VIEW mv_daily TO daily_rollup AS
SELECT toStartOfDay(hour) AS day, tenant_id, country,
       countMergeState(views) AS views,
       uniqMergeState(users)  AS users
FROM hourly_rollup
GROUP BY day, tenant_id, country;

Storage at each level is a fraction of the one below, and a query picks whichever granularity it needs.

Multiple views on one table

Several materialised views can watch the same source table, each producing a different rollup:

                    ┌──▶ hourly_by_country
   page_views ──────┼──▶ hourly_by_url
                    └──▶ daily_by_tenant

This is the answer to chapter 5’s constraint that a table has only one sort order: each view gets its own target table with its own ordering key.

The cost is that every insert into page_views now runs three aggregation queries. Views are not free — they are a tax on write throughput. Watch insert latency after adding them; a table with a dozen views ingests noticeably more slowly.

Also, if a materialised view’s query throws an error, the insert fails. A badly written view can block ingestion into the source table entirely. Test them under real data before deploying. materialized_views_ignore_errors exists as an escape hatch and should be a deliberate choice, not a default.

Refreshable materialised views

Newer ClickHouse versions support the other kind of materialised view — the one people expected all along:

CREATE MATERIALIZED VIEW mv_summary
REFRESH EVERY 1 HOUR
ENGINE = MergeTree ORDER BY day
AS SELECT toDate(ts) AS day, count() FROM page_views GROUP BY day;

This periodically re-runs the whole query and replaces the target. It handles joins, updates and deletes correctly, because it recomputes from scratch — and it costs a full recomputation each time.

Use refreshable views for small results derived from expensive queries that involve joins or must reflect mutations. Use incremental (trigger) views for high-volume streaming rollups. They solve different problems.

This feature has moved from experimental to supported across recent releases — check your version before designing on it.

Projections

A projection is an alternative physical ordering of the same table, stored inside the table’s parts and chosen automatically by the optimiser.

ALTER TABLE page_views ADD PROJECTION proj_by_url (
    SELECT url, country, count(), sum(duration)
    GROUP BY url, country
);

ALTER TABLE page_views MATERIALIZE PROJECTION proj_by_url;

Now a query grouping by url uses the projection instead of scanning the base table — without changing the query.

Projections come in two shapes:

Normal projections — the same rows, sorted differently. This directly solves “my table has one sort order but two access patterns”:

ALTER TABLE page_views ADD PROJECTION proj_by_user (
    SELECT * ORDER BY user_id, ts
);

Aggregate projections — pre-aggregated, like a materialised view but managed inside the table.

Projections versus materialised views

	Projection	Materialised view
Chosen by the optimiser	yes, automatically	no — you query the target directly
Lives	inside the table’s parts	a separate table
Consistency with source	always, by construction	depends on the view firing
Backfill needed	MATERIALIZE PROJECTION handles it	you write the INSERT
Survives DROP PARTITION	yes, dropped with it	no — the rollup keeps the rows
Cross-table	no	yes
Different retention	no	yes
Cost	insert time + storage	insert time + storage

Prefer projections when the alternative view is over the same table with the same retention — they are simpler, automatic, and cannot drift. Use materialised views when you need different retention, a different table, chained rollups, or a source that is not a MergeTree table (like the Kafka engine in chapter 6).

That fifth row deserves emphasis: a projection is deleted along with its partition, which is usually what you want. A materialised view’s target is an independent table, which is exactly why the 90-days-raw/2-years-rollup pattern works.

What you would type

-- what views exist and where they write
SELECT database, name, engine, as_select
FROM system.tables WHERE engine = 'MaterializedView';

-- projections on a table
SELECT name, parts, formatReadableSize(sum(bytes_on_disk))
FROM system.projection_parts WHERE table = 'page_views' AND active
GROUP BY name, parts;

-- is a query actually using a projection?
EXPLAIN indexes = 1 SELECT url, count() FROM page_views GROUP BY url;
-- look for the projection name in the plan

-- what a rollup costs versus its source
SELECT table, formatReadableSize(sum(bytes_on_disk)) AS size, sum(rows)
FROM system.parts WHERE active AND table IN ('page_views','hourly_rollup')
GROUP BY table;

That last query is the one to show people: a rollup is typically a fraction of a percent of the raw table, and it answers the dashboard’s questions in milliseconds.

What goes wrong

Expecting a view to backfill. It does not. The target sits empty and someone concludes the feature is broken.

Double-counting during backfill. Overlap between the view’s first firing and the backfill range.

A broken view blocking inserts. An error inside the view fails the insert into the source table.

Too many views on a hot table. Ingestion slows in proportion. Measure.

Reading an AggregatingMergeTree without -Merge. You get binary state blobs, not numbers.

Summing pre-aggregated uniques. Wrong answers that look plausible. Use uniqMerge.

A projection that is never chosen. Check with EXPLAIN; if the query does not match the projection’s shape, it scans the base table and you are paying storage for nothing.

Forgetting MATERIALIZE PROJECTION after adding one — it only applies to new parts until you do.

What to take from this chapter

• A MATERIALIZED VIEW is an insert trigger over the new block, not a synchronised query result. It sees no history, no updates, no deletes.
• Always use the TO table form and create the target explicitly.
• The core pattern: AggregatingMergeTree target + -State functions in the view + -Merge at query time, which lets you roll hourly states up into monthly answers correctly.
• Keep raw data briefly and rollups for years — different TTLs on the two tables is the whole point.
• Backfill history manually, and mind the boundary so you do not double-count.
• Chain views with -MergeState for a rollup hierarchy; run several views on one table to get several sort orders.
• A failing view fails the insert. Test before deploying.
• Refreshable views re-run the whole query periodically — right for small results over joins and mutable data.
• Projections are alternative orderings inside the table, chosen automatically. Prefer them when retention is shared; use materialised views when it is not.

Next: running all of this across more than one machine.