ClickHouseadvanced

ClickHouse Materialized Views

Insert-time transformation into aggregate tables, the pitfalls of the trigger model, and how projections differ.

3 min readAdvancedUpdated 31 Jul 2026Edit this page

A ClickHouse materialized view is an insert trigger, not a cached query result. When rows are inserted into the source table, the view's SELECT runs over that block and writes the result into a target table.

Aggregating view

-- Target table holds aggregate function states, not final values.
CREATE TABLE events_hourly
(
    hour        DateTime,
    tenant_id   UInt64,
    event_type  LowCardinality(String),
    events      AggregateFunction(count),
    users       AggregateFunction(uniq, UInt64),
    duration_p95 AggregateFunction(quantile(0.95), UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, event_type, hour);
 
CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT
    toStartOfHour(event_time)        AS hour,
    tenant_id,
    event_type,
    countState()                     AS events,
    uniqState(user_id)               AS users,
    quantileState(0.95)(duration_ms) AS duration_p95
FROM events
GROUP BY hour, tenant_id, event_type;

Query it by merging the states:

SELECT hour,
       countMerge(events)        AS events,
       uniqMerge(users)          AS users,
       quantileMerge(0.95)(duration_p95) AS p95
FROM events_hourly
WHERE tenant_id = 42 AND hour >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

The -State / -Merge pairing is what makes partial aggregates combinable across merges. Storing finished values instead would produce wrong results as parts merge.

The trigger model has consequences

A materialized view only sees new inserts

Creating a view does not process existing data. Backfill explicitly:

INSERT INTO events_hourly
SELECT toStartOfHour(event_time), tenant_id, event_type,
       countState(), uniqState(user_id), quantileState(0.95)(duration_ms)
FROM events
WHERE event_time < '2026-07-31'
GROUP BY 1, 2, 3;

Backfill in partition-sized batches to bound memory, and take care not to double-count rows that the view has already processed.

Other consequences worth knowing:

The view reads the inserted block, not the table. A JOIN inside a materialized view joins the incoming block against the other table as it is at that moment — it does not re-evaluate when the joined table changes.
Errors in the view fail the insert. A view that throws breaks ingestion into the source table.
Several views on one table all run per insert, multiplying insert cost.
Deletes and updates in the source are not propagated. The view is fed by inserts only.

Projections

A projection is a second physical arrangement of the same table's data, stored inside the table's parts and maintained automatically. Unlike a materialized view it needs no separate table and no query rewrite — the optimiser chooses it when it helps.

ALTER TABLE events ADD PROJECTION proj_by_user
(
    SELECT user_id, event_time, event_type
    ORDER BY (user_id, event_time)
);
 
-- Build it for existing parts.
ALTER TABLE events MATERIALIZE PROJECTION proj_by_user;

Aggregate projections work too:

ALTER TABLE events ADD PROJECTION proj_daily
(
    SELECT tenant_id, toDate(event_time) AS d, count()
    GROUP BY tenant_id, d
);

	Materialized view	Projection
Storage	A separate table you manage	Inside the source table's parts
Query change	Query the target table	Transparent; optimiser selects it
Backfill	Manual `INSERT ... SELECT`	`MATERIALIZE PROJECTION`
Consistency with source	Independent; can drift	Always consistent with the part
Cross-table transformation	Yes	No — same table only