Materialized Views

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

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.

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.