Materialized views

A materialized view (MV) is a stored query whose output is kept up to date as new data lands in the source. ClickHouse has three flavours:

Flavour	What triggers a refresh	Source
Incremental MV (the classic)	Each INSERT block on the source table fires the MV's SELECT against that block; results are inserted into the MV's storage.	StorageMaterializedView.cpp
Refreshable MV	A periodic REFRESH EVERY schedule re-runs the entire MV query.	StorageMaterializedView.cpp + RefreshSet.cpp
Window MV (WindowView)	Streaming windowed aggregation.	src/Storages/WindowView/

There is also LIVE VIEW (StorageLiveView) — a deprecated experiment for push-based change subscriptions.

Incremental MVs

CREATE MATERIALIZED VIEW mv_daily_events
ENGINE = SummingMergeTree
ORDER BY (date, event_type)
AS
SELECT
    toDate(event_time) AS date,
    event_type,
    count() AS cnt
FROM events
GROUP BY date, event_type;

When a block of rows is inserted into events:

1. The interpreter handling the INSERT (InterpreterInsertQuery.cpp) finds attached MVs.
2. Each MV is run with the new block as a "virtual source" — PushingToViewsSink.cpp builds a small pipeline that runs the MV's SELECT over just that block.
3. The result is written to the MV's storage (or its TO-target table).

Important properties:

• The MV sees only new blocks, not the full history. Backfilling means inserting the historical data through the source.
• The MV's SELECT must be idempotent for a given block, otherwise replicated retries will skew the result.
• Multi-source MVs are tricky — the MV is attached to one source; joining other tables in the SELECT works but can produce surprising results when the joined data changes.

TO target

CREATE MATERIALIZED VIEW mv TO target_table AS SELECT ... FROM source;

When TO is used, the MV does not own its storage; it just routes blocks into target_table. Useful for sharing one storage between several MVs (e.g. one MV per source, all writing into the same aggregating table).

Aggregating MVs

Pair an MV with AggregatingMergeTree (or SummingMergeTree/ReplacingMergeTree) on the target side. The MV computes partial aggregate states (sumState, uniqState, quantileTDigestState); the engine merges them in the background. Reads finalize via sumMerge, uniqMerge, quantileTDigestMerge. See Aggregate functions.

Replicated MVs

If the source is ReplicatedMergeTree, the MV runs on whichever replica processes the insert; replication of the MV's output is governed by the MV's own engine. A common pattern is Replicated*MergeTree MV on top of Replicated*MergeTree source.

Refreshable MVs

CREATE MATERIALIZED VIEW mv
REFRESH EVERY 1 HOUR
ENGINE = MergeTree ORDER BY id
AS SELECT ... FROM source;

The MV's SELECT is re-run on a schedule. The new result replaces the old data atomically. RefreshSet.cpp and RefreshSchedule.cpp orchestrate the cron-like loop. Deduplication and partial-failure handling are simpler than incremental MVs because each refresh starts from scratch.

Window MVs

WindowView (src/Storages/WindowView/) does streaming windowed aggregation, similar to Flink's tumbling/sliding/session windows. Less commonly used than incremental MVs but useful for low-latency time-bucketed aggregations.

Push-down considerations

The MV's SELECT runs only over the new block. So:

• Filters limited to columns of the source work fine.
• Self-references (the source appearing on both sides of a JOIN) typically won't.
• IN (SELECT ... FROM source) reads the whole source — be careful with cardinality.

Common diagnostics

• system.tables — MVs are listed alongside tables. The engine is MaterializedView.
• system.mv_dependencies — view dependency graph.
• system.replicas (when MV is replicated).
• SHOW CREATE TABLE mv — view definition.

Entry points for modification

• New MV behaviour → edit StorageMaterializedView.cpp and PushingToViewsSink.cpp.
• New REFRESH policy → RefreshSet.cpp, RefreshSchedule.cpp.
• New WindowView window kind → src/Storages/WindowView/.

Related pages

• Storage engine → other engines
• Aggregate functions — *State / *Merge combinators.
• Storage engine → MergeTree — typical target engine.