Materialized Views — learning.lab

In Postgres, a materialized view is a query result you periodically REFRESH — it goes stale between refreshes. A ClickHouse materialized view is a completely different mechanism: it's an insert trigger that runs a query against every newly inserted block of rows and pushes the output into a separate target table, continuously, forever.

The mental model: push, not pull

what happens on every INSERT into events

INSERTnew block of rows

events (source)stores rows as usual

MV query runson the new block only

↓ writes

target tablereceives the output

Crucially, the view's SELECT never runs against the whole source table — only against the rows in the block that was just inserted. The cost of maintaining it is proportional to insert volume, not to the size of the source table.

The classic pattern: pre-aggregation

CREATE TABLE events
(
    event_time DateTime,
    event_type LowCardinality(String)
)
ENGINE = MergeTree ORDER BY event_time;

CREATE TABLE events_per_minute
(
    minute     DateTime,
    event_type LowCardinality(String),
    cnt        AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
ORDER BY (minute, event_type);

CREATE MATERIALIZED VIEW events_per_minute_mv
TO events_per_minute
AS
SELECT
    toStartOfMinute(event_time) AS minute,
    event_type,
    countState() AS cnt
FROM events
GROUP BY minute, event_type;

Reads against events_per_minute stay fast forever, because they scan pre-aggregated per-minute rows instead of raw events — even after the source table grows into the billions.

Common mistake

A materialized view only sees rows inserted directly into its source table. If another materialized view (or a distributed insert path) writes into that table indirectly, or if you insert straight into the target table, the trigger may not fire the way you expect. Always trace where writes actually land.

Under the hood

The target table uses AggregateFunction(count) and AggregatingMergeTree, not a plain integer with SummingMergeTree — this lets partial aggregate states from different inserts merge correctly later. Reading it back requires countMerge(), not a plain SUM.

Materialized view vs. projection

Use a materialized view when you want a differently shaped result — aggregated, filtered, joined — living in its own named table you query explicitly. Use a projection when you want the same rows just sorted differently, chosen automatically under the same table name.