TopolearnMVs move work from query time to insert time, turning slow dashboards into millisecond lookups.
A ClickHouse materialized view is an insert trigger. CREATE MATERIALIZED VIEW mv TO target AS SELECT ... FROM source runs the SELECT over each block inserted into the source table and writes the result into target. It never re-reads the whole source table.
The classic pattern is a rollup:
- Raw events land in a MergeTree table.
- The MV aggregates each insert block (
GROUP BYminute,uniqState,sumStateor plaincount()). - The target is a SummingMergeTree or AggregatingMergeTree that keeps merging partial results; in an AggregatingMergeTree every non-key column must be an
AggregateFunctionorSimpleAggregateFunction. - Dashboards query the small target table with
sumor-Mergefunctions.
Things to know:
- Always use the explicit
TO targetform so you control the target's engine, keys and TTL. - An MV only sees new inserts. Existing rows are not processed; backfill with an
INSERT INTO target SELECT ...over the old data. Avoid thePOPULATEoption, which can miss rows inserted while it runs. - The MV's
GROUP BYruns per insert block, so the target receives many partial rows per key. That is why its engine must merge, and why reads must aggregate again. - If the source has a JOIN, only inserts into the left-most table trigger the view.
Refreshable materialized views (REFRESH EVERY 1 HOUR) instead re-run the whole query on a schedule and replace the target, which suits joins and small summary tables.
Example
CREATE TABLE clicks (ts DateTime, page String, user UInt64)
ENGINE = MergeTree ORDER BY (page, ts);
CREATE TABLE clicks_per_min
(minute DateTime, page String,
hits SimpleAggregateFunction(sum, UInt64), users AggregateFunction(uniq, UInt64))
ENGINE = AggregatingMergeTree ORDER BY (page, minute);
CREATE MATERIALIZED VIEW clicks_mv TO clicks_per_min AS
SELECT toStartOfMinute(ts) AS minute, page,
count() AS hits, uniqState(user) AS users
FROM clicks GROUP BY minute, page;
INSERT INTO clicks VALUES ('2025-05-01 12:00:10', '/a', 1), ('2025-05-01 12:00:20', '/a', 2);
INSERT INTO clicks VALUES ('2025-05-01 12:00:30', '/a', 1);
-- Read with aggregation: the target holds partial rows until merged
SELECT minute, page, sum(hits) AS hits, uniqMerge(users) AS users
FROM clicks_per_min GROUP BY minute, page;
-- 2025-05-01 12:00:00 /a 3 2Remember
- An incremental MV transforms each inserted block into a target table
- Use TO target with a Summing/AggregatingMergeTree and aggregate again on read
- MVs don't backfill: insert historical data into the target yourself
Watch out for
- Using a plain MergeTree target and reading hits without sum(), which double-counts partial rows.