Materialized views in production

A normal view is a saved query: every SELECT runs it again. A ClickHouse incremental materialized view is something else, closer to an AFTER INSERT trigger. When rows are inserted into a source table, the view’s query runs on those rows and the result is written into another table. Reading the result later costs one read of a small table. That model is simple, and almost every production surprise with materialized views (wrong averages, vanished join rows, an empty view on an old table, a half-failed insert) comes from forgetting one of its consequences. This guide walks through all of them with queries that were run on ClickHouse 26.4, and ends with the refreshable variant that works the other way round.

Contents

• The rule: a view sees the block, not the table
• Targets: explicit TO versus the hidden inner table
• Pre-aggregation with SummingMergeTree
• Non-additive aggregates: -State and -Merge
• Chained materialized views
• JOINs inside a materialized view
• Adding a view to a table that already has data
• Refreshable materialized views
• What a view costs, and how it fails
• When not to use a materialized view

All examples run in a database called analytics (CREATE DATABASE analytics ENGINE = Atomic; USE analytics;) and use the demo events table:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

The rule: a view sees the block, not the table

An INSERT forms a block in memory, sorts it, and writes it to the source table as a new part. In the same operation, every materialized view attached to that table runs its SELECT against that block and writes its output into its own target table.

INSERT --> insert block --> source table
                       \--> view 1 query --> target table 1
                       \--> view 2 query --> target table 2

So one insert fans out to several tables, and the view never sees the rest of the source table. The rule to memorise: a materialized view’s query runs only against the block being inserted. Here is the proof. The view below writes how many rows it saw, and also asks for count() of the source table in a scalar subquery:

CREATE TABLE scratch AS events;
CREATE TABLE block_log (block_rows UInt64, source_rows_visible UInt64) ENGINE = MergeTree ORDER BY tuple();

CREATE MATERIALIZED VIEW block_log_mv TO block_log AS
SELECT count() AS block_rows, (SELECT count() FROM scratch) AS source_rows_visible
FROM scratch;

INSERT INTO scratch SELECT now(), number, 'view', '/', 1, 'DE' FROM numbers(1000);
INSERT INTO scratch SELECT now(), number, 'view', '/', 1, 'DE' FROM numbers(10);

SELECT block_rows, source_rows_visible, (SELECT count() FROM scratch) AS table_rows
FROM block_log ORDER BY block_rows DESC;

┌─block_rows─┬─source_rows_visible─┬─table_rows─┐
│       1000 │                1000 │       1010 │
│         10 │                  10 │       1010 │
└────────────┴─────────────────────┴────────────┘

Even the subquery on scratch inside the view body saw only the block (1000 and 10 rows), while the table held 1010: inside the view’s query, the source table name stands for the inserted block, wherever it appears. Everything else on this page follows from that:

• The view is a trigger on inserts. Deletes, updates, mutations and merges of the source do not propagate.
• There is no look-back: no window over yesterday’s data, no “rows I have seen before”.
• Rows that were in the source before the view existed are not processed.
• Whatever the view computes is computed once per block; blocks are not re-evaluated when the view’s logic changes.

Targets: explicit TO versus the hidden inner table

You create the target table yourself and attach the view with TO:

CREATE TABLE events_per_day (day Date, event_type LowCardinality(String), events UInt64)
ENGINE = SummingMergeTree ORDER BY (day, event_type);

CREATE MATERIALIZED VIEW events_per_day_mv TO events_per_day AS
SELECT toDate(event_time) AS day, event_type, count() AS events
FROM events
GROUP BY day, event_type;

You choose the target’s engine, ORDER BY, partitioning and TTL, you can alter them later with normal DDL, you can drop and recreate the view without touching the data, and you query a table whose name says what it is. A common convention is <target>_mv for the view.

The alternative omits TO and gives an engine. ClickHouse then creates a hidden table:

CREATE MATERIALIZED VIEW mv_implicit
ENGINE = SummingMergeTree ORDER BY event_type
AS SELECT event_type, count() AS c FROM events GROUP BY event_type;

SELECT name, engine FROM system.tables
WHERE database = 'analytics' AND (name LIKE '%inner%' OR name = 'mv_implicit') ORDER BY name;

┌─name───────────────────────────────────────────┬─engine───────────┐
│ .inner_id.b98501c0-1236-4a96-9733-3ad31720d697 │ SummingMergeTree │
│ mv_implicit                                    │ MaterializedView │
└────────────────────────────────────────────────┴──────────────────┘

The inner table is named .inner_id.<uuid> in Atomic databases (the default). Its lifecycle is welded to the view: DROP VIEW mv_implicit removes the data with it (the .inner% count in system.tables drops back to 0), the table is awkward to alter, and you have to know the hidden name to inspect it. The only thing the implicit form offers over TO is POPULATE, which is not safe on a live table (see the backfill section). Use TO everywhere. If you want the server to enforce that, a request for a setting that forbids implicit views is open and no such setting exists yet.

Pre-aggregation with SummingMergeTree

The most common use is keeping an aggregate current as rows arrive, so that dashboards read a small table instead of scanning raw data. Insert a million rows spread over 30 days into events (with the events_per_day_mv above already in place):

INSERT INTO events
SELECT toDateTime('2026-03-01 00:00:00') + intDiv(number * 2592000, 1000000), number % 5000,
       ['view','click','purchase'][1 + number % 3], concat('/p/', toString(number % 7)),
       10 + (number * 7) % 500, ['DE','TR','US'][1 + number % 3]
FROM numbers(1000000);

SELECT count() AS source_rows FROM events;         -- 1000000
SELECT count() AS target_rows FROM events_per_day; -- 90

The target has 90 rows (30 days times 3 event types) against a million in the source, and the numbers agree with the raw query:

SELECT day, event_type, sum(events) AS events FROM events_per_day
WHERE day < '2026-03-03' GROUP BY day, event_type ORDER BY day, event_type;

┌────────day─┬─event_type─┬─events─┐
│ 2026-03-01 │ click      │  11111 │
│ 2026-03-01 │ purchase   │  11111 │
│ 2026-03-01 │ view       │  11112 │
│ 2026-03-02 │ click      │  11111 │
│ 2026-03-02 │ purchase   │  11111 │
│ 2026-03-02 │ view       │  11111 │
└────────────┴────────────┴────────┘

Notice sum(events) and the GROUP BY when reading the target. Every insert produces its own aggregated block, so two inserts that both contain 2026-03-01 / view write two rows with the same key:

INSERT INTO events VALUES ('2026-03-01 12:00:00', 1, 'view', '/', 1, 'DE');
INSERT INTO events VALUES ('2026-03-01 12:01:00', 1, 'view', '/', 1, 'DE');

SELECT day, event_type, events FROM events_per_day
WHERE day = '2026-03-01' AND event_type = 'view';

┌────────day─┬─event_type─┬─events─┐
│ 2026-03-01 │ view       │  11112 │
│ 2026-03-01 │ view       │      1 │
│ 2026-03-01 │ view       │      1 │
└────────────┴────────────┴────────┘

SummingMergeTree collapses rows with the same sorting key by adding their numeric columns, but only when background merges run, which is not immediately, and not necessarily ever for parts that stay separate. OPTIMIZE TABLE events_per_day FINAL forces it (avoid that on big tables) and leaves one row of 11114. So the rule for reading a pre-aggregate is: always GROUP BY the key and sum() the value columns, even when the table looks already merged. The same logic explains why many small inserts produce many small partial rows: the view groups per block, so large batched inserts keep the target compact (see insert batching).

Non-additive aggregates: -State and -Merge

SummingMergeTree adds columns up. That is right for counts and sums and wrong for everything that is not additive. Averaging per block and then summing the averages is nonsense, and here is the failure in its natural habitat:

CREATE TABLE avg_wrong (day Date, avg_ms Float64) ENGINE = SummingMergeTree ORDER BY day;
CREATE MATERIALIZED VIEW avg_wrong_mv TO avg_wrong AS
SELECT toDate(event_time) AS day, avg(duration_ms) AS avg_ms FROM events GROUP BY day;

INSERT INTO events VALUES ('2026-04-01 00:00:00', 1, 'view', '/', 100, 'DE'), ('2026-04-01 00:00:00', 1, 'view', '/', 300, 'DE');
INSERT INTO events VALUES ('2026-04-01 00:10:00', 1, 'view', '/', 200, 'DE');
OPTIMIZE TABLE avg_wrong FINAL;

SELECT avg_ms AS wrong_avg,
       (SELECT avg(duration_ms) FROM events WHERE toDate(event_time) = '2026-04-01') AS true_avg
FROM avg_wrong WHERE day = '2026-04-01';

┌─wrong_avg─┬─true_avg─┐
│       400 │      200 │
└───────────┴──────────┘

The first block averaged to 200, the second to 200, and the merge summed them to 400. Before the merge runs, the table holds two rows (200 and 200) and a naive avg() over them happens to look right, which is what makes this bug survive code review: it appears only after merges, and only for keys that were inserted more than once.

The correct tool is AggregatingMergeTree with aggregate function states. The view stores the intermediate state with the -State combinator, and you finish it at query time with -Merge:

CREATE TABLE duration_per_day (
    day Date,
    country LowCardinality(String),
    avg_duration AggregateFunction(avg, UInt32),
    p95          AggregateFunction(quantile(0.95), UInt32)
)
ENGINE = AggregatingMergeTree ORDER BY (day, country);

CREATE MATERIALIZED VIEW duration_per_day_mv TO duration_per_day AS
SELECT toDate(event_time) AS day, country,
       avgState(duration_ms)            AS avg_duration,
       quantileState(0.95)(duration_ms) AS p95
FROM events
GROUP BY day, country;

INSERT INTO events VALUES ('2026-04-02 00:00:00', 1, 'view', '/', 100, 'DE'), ('2026-04-02 00:00:00', 1, 'view', '/', 300, 'DE');
INSERT INTO events VALUES ('2026-04-02 00:10:00', 1, 'view', '/', 200, 'DE');

SELECT day, country, avgMerge(avg_duration) AS avg_ms, quantileMerge(0.95)(p95) AS p95_ms
FROM duration_per_day GROUP BY day, country;

┌────────day─┬─country─┬─avg_ms─┬─p95_ms─┐
│ 2026-04-02 │ DE      │    200 │    290 │
└────────────┴─────────┴────────┴────────┘

The average is 200 across the two blocks because each state carries a sum and a count rather than a finished number. The same pattern works for uniqState/uniqMerge (distinct counts), quantileState, minState and the rest. The price is that the column is an opaque binary state: you can only read it with the matching -Merge function, and the state of uniq or quantile is far bigger than one number.

When the aggregate’s state is a single plain value (sum, min, max, any), SimpleAggregateFunction is lighter: no -State/-Merge, and you read the column with the plain function.

CREATE TABLE usage_per_day (
    day Date, country LowCardinality(String),
    events SimpleAggregateFunction(sum, UInt64),
    max_ms SimpleAggregateFunction(max, UInt32))
ENGINE = AggregatingMergeTree ORDER BY (day, country);

CREATE MATERIALIZED VIEW usage_per_day_mv TO usage_per_day AS
SELECT toDate(event_time) AS day, country, count() AS events, max(duration_ms) AS max_ms
FROM events GROUP BY day, country;

INSERT INTO events VALUES ('2026-04-03 00:00:00', 1, 'view', '/', 100, 'DE'), ('2026-04-03 00:00:00', 1, 'view', '/', 300, 'DE');
INSERT INTO events VALUES ('2026-04-03 00:10:00', 1, 'view', '/', 900, 'DE');

SELECT day, country, sum(events), max(max_ms) FROM usage_per_day GROUP BY day, country;

┌────────day─┬─country─┬─sum(events)─┬─max(max_ms)─┐
│ 2026-04-03 │ DE      │           3 │         900 │
└────────────┴─────────┴─────────────┴─────────────┘

Which engine for which numbers:

You store	Engine	Column type	Read with
counts and sums only	SummingMergeTree	plain numeric	sum(col) ... GROUP BY key
sum/min/max/any alongside other aggregates	AggregatingMergeTree	SimpleAggregateFunction(f, T)	f(col) ... GROUP BY key
avg, quantiles, uniq, anything non-additive	AggregatingMergeTree	AggregateFunction(f, T)	fMerge(col) ... GROUP BY key

Design notes. The aggregate’s ORDER BY is the grouping key, so choose it for the questions you ask. Several views on one source, each with a different grain (per day, per hour, per country), are cheap and common. Pre-aggregation loses detail: you cannot get a finer grain than the view’s GROUP BY, so keep the raw table unless a TTL on it is acceptable. And check that the view earns its keep: if the target ends up with a large fraction of the source’s row count (the grain is too fine, or a high-cardinality key sneaked into the GROUP BY), you are paying for a second copy of the data and not for a summary.

Chained materialized views

A view’s target is a table like any other, so another view can read from it. That is the clean way to build several grains: hourly counts from raw events, daily counts from the hourly table.

CREATE TABLE clicks AS events;
CREATE TABLE clicks_per_hour (hour DateTime, event_type LowCardinality(String), events UInt64)
ENGINE = SummingMergeTree ORDER BY (hour, event_type);
CREATE TABLE clicks_per_day (day Date, event_type LowCardinality(String), events UInt64)
ENGINE = SummingMergeTree ORDER BY (day, event_type);

CREATE MATERIALIZED VIEW clicks_per_hour_mv TO clicks_per_hour AS
SELECT toStartOfHour(event_time) AS hour, event_type, count() AS events
FROM clicks GROUP BY hour, event_type;

CREATE MATERIALIZED VIEW clicks_per_day_mv TO clicks_per_day AS
SELECT toDate(hour) AS day, event_type, sum(events) AS events
FROM clicks_per_hour GROUP BY day, event_type;

INSERT INTO clicks
SELECT toDateTime('2026-03-01 00:00:00') + intDiv(number * 172800, 100000), number % 5000,
       ['view','click','purchase'][1 + number % 3], '/', 10 + number % 500, 'DE'
FROM numbers(100000);

SELECT (SELECT count() FROM clicks) AS source, (SELECT count() FROM clicks_per_hour) AS hourly,
       (SELECT count() FROM clicks_per_day) AS daily;

┌─source─┬─hourly─┬─daily─┐
│ 100000 │    144 │     6 │
└────────┴────────┴───────┘

One insert into clicks triggered the hourly view, whose output block (an insert into clicks_per_hour) triggered the daily view. system.tables records the graph in dependencies_table, which is the quickest way to see what an insert will cascade into:

SELECT name, dependencies_table FROM system.tables
WHERE database = 'analytics' AND name IN ('clicks', 'clicks_per_hour') ORDER BY name;

┌─name────────────┬─dependencies_table─────┐
│ clicks          │ ['clicks_per_hour_mv'] │
│ clicks_per_hour │ ['clicks_per_day_mv']  │
└─────────────────┴────────────────────────┘

Two things to keep in mind. The second view sees the first view’s output block, not the original rows, so everything in the block rule applies one level down; here that is why the daily view uses sum(events) and not count(). And the whole chain runs inside the original insert: a slow or failing view at the bottom makes the user’s INSERT slow or failing. If the finer grain is not needed anywhere else, a single view at the coarser grain does less work than two.

JOINs inside a materialized view

Say the rule out loud once more: the view’s query runs only against the block of rows inserted into its source. With a single table in FROM the source is obvious. With a join, the source is the left-most table. Inserts into it trigger the view; every other table in the join is just read at that moment, as it exists right then, like in a normal query. Consider events and a small dimension table:

CREATE TABLE users (user_id UInt64, plan LowCardinality(String))
ENGINE = MergeTree ORDER BY user_id;

CREATE TABLE events_j AS events;

CREATE TABLE events_by_plan (day Date, plan LowCardinality(String), events UInt64)
ENGINE = SummingMergeTree ORDER BY (day, plan);

CREATE MATERIALIZED VIEW events_by_plan_mv TO events_by_plan AS
SELECT toDate(e.event_time) AS day, u.plan AS plan, count() AS events
FROM events_j AS e
INNER JOIN users AS u ON e.user_id = u.user_id
GROUP BY day, plan;

Here events_j is the source. Watch what each insert does:

-- users 0..4 exist
INSERT INTO users SELECT number, 'free' FROM numbers(5);

-- 100 events from users 0..9: only users 0..4 can match
INSERT INTO events_j SELECT toDateTime('2026-03-01 00:00:00') + number, number % 10, 'view', '/', 1, 'DE' FROM numbers(100);
SELECT plan, sum(events) FROM events_by_plan GROUP BY plan ORDER BY plan;      -- free 50

Half of the events had no matching user at that time, and the inner join dropped them. Now the missing users arrive:

INSERT INTO users SELECT number, 'pro' FROM numbers(10) WHERE number >= 5;
SELECT plan, sum(events) FROM events_by_plan GROUP BY plan ORDER BY plan;      -- free 50, still

Nothing changed. Inserting into users does not trigger the view, because users is not the source, and the earlier events are not re-processed. Only new events see the new users:

INSERT INTO events_j SELECT toDateTime('2026-03-01 00:00:00') + number, number % 10, 'view', '/', 1, 'DE' FROM numbers(100);
SELECT plan, sum(events) FROM events_by_plan GROUP BY plan ORDER BY plan;

┌─plan─┬─sum(events)─┐
│ free │         100 │
│ pro  │          50 │
└──────┴─────────────┘

Compare with the truth, computed from the raw tables, where free and pro both have 100:

SELECT u.plan, count() FROM events_j e LEFT JOIN users u ON e.user_id = u.user_id GROUP BY u.plan ORDER BY u.plan;

The target is permanently short by 50 pro events and nothing will ever correct it. The consequences:

• A view with a join is correct only if the right-hand data is complete before the left-hand rows arrive and never changes afterwards (or you accept that history keeps the old value).
• An INNER JOIN silently drops unmatched rows. A LEFT JOIN keeps them but writes default values for the right columns, which is also permanent. Prefer the LEFT JOIN and a visible “unknown” bucket, so missing dimensions show up instead of vanishing.
• The right table is read once per inserted block, and each insert builds a hash table of it. A large right side makes inserts slow or memory-hungry; keep it small or filter it in a subquery. See join algorithms and memory.
• A join key that matches several right rows multiplies the left rows, and the aggregate counts them all.

Better options, in the order I would try them:

A dictionary lookup. For key-to-attribute lookups a dictionary replaces the join. It lives in memory, refreshes on its own schedule and is read with dictGet, so there is no per-block hash table. Unknown keys fall back to a default you choose:

DROP VIEW events_by_plan_mv;

CREATE DICTIONARY users_dict (user_id UInt64, plan String)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(DB 'analytics' TABLE 'users'))
LAYOUT(HASHED())
LIFETIME(MIN 0 MAX 300);

CREATE MATERIALIZED VIEW events_by_plan_mv TO events_by_plan AS
SELECT toDate(event_time) AS day,
       dictGetOrDefault('users_dict', 'plan', user_id, 'unknown') AS plan,
       count() AS events
FROM events_j GROUP BY day, plan;

INSERT INTO events_j SELECT toDateTime('2026-03-02 00:00:00') + number, number % 12, 'view', '/', 1, 'DE' FROM numbers(120);
SELECT day, plan, events FROM events_by_plan WHERE day = '2026-03-02' ORDER BY plan;

┌────────day─┬─plan────┬─events─┐
│ 2026-03-02 │ free    │     50 │
│ 2026-03-02 │ pro     │     50 │
│ 2026-03-02 │ unknown │     20 │
└────────────┴─────────┴────────┘

Users 10 and 11 do not exist, and their 20 events are counted under unknown instead of disappearing. It is still a snapshot at insert time (the value at that moment is baked into the row), but you control staleness with LIFETIME, and a late-arriving dimension can be handled by re-running the affected days. More on dictionaries.

Join at read time. Store only the source facts in the view (user_id and counts) and join the small pre-aggregated target to the dimension when you query. The dimension is then always current, and the join is cheap because it runs on aggregated rows.

A refreshable view, below, when the result must reflect the current state of both tables.

If you really need a view that reacts to inserts on the right side of a join, create a second view with that table as its own source and write to the same target. Each view only sees its own inserted block, so you have to design what each side contributes (rows with “side” markers merged by an AggregatingMergeTree), and it is rarely simpler than the options above.

Adding a view to a table that already has data

A materialized view processes rows inserted after it exists. Create one on a table with a billion rows and the target stays empty. The implicit-target form has a POPULATE keyword that backfills at creation time, and it is a trap on a live table:

• Rows inserted into the source while the view is being created and populated may be missed, because the view is attached only when population finishes and you cannot know the exact moment.
• There is no chunking: the whole table goes through one query, which can exhaust memory or time out and leave you unsure what was written.
• It is not available with the TO form.

The dependable approach has two steps: make the view handle everything from a known point in the data onwards, and load everything before that point by hand. You need a column that tells old from new, a Date/DateTime or any increasing value.

1. Pick a cutoff value in the future, one no source row can match yet.
2. Create the target and the view with a WHERE that only lets rows at or after the cutoff through. The view is live, but nothing matches, so nothing is lost or duplicated.
3. When the cutoff has passed and the rows below it have all arrived, run the same query with the inverted condition as an INSERT ... SELECT, in chunks.

The cutoff answers “where does the view start?” without needing to know how long CREATE takes, because the view starts at a point in the data, not a point in wall-clock time. An existing table with data from January to March 2026, partitioned by month:

CREATE TABLE metrics (ts DateTime, sensor_id UInt32, metric LowCardinality(String), value Float64)
ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (metric, sensor_id, ts);

INSERT INTO metrics
SELECT toDateTime('2026-01-01 00:00:00') + number * 3600, number % 4, 'temp', 15 + (number % 10)
FROM numbers(2000);

Averages are not additive, so the daily summary stores the sum and the sample count (both sum fine in a SummingMergeTree) and computes the mean on read:

CREATE TABLE metrics_daily (
    day Date, sensor_id UInt32, metric LowCardinality(String),
    value_sum Float64, samples UInt64)
ENGINE = SummingMergeTree ORDER BY (metric, sensor_id, day);

CREATE MATERIALIZED VIEW metrics_daily_mv TO metrics_daily AS
SELECT toDate(ts) AS day, sensor_id, metric, sum(value) AS value_sum, count() AS samples
FROM metrics
WHERE ts >= '2026-06-01 00:00:00'
GROUP BY day, sensor_id, metric;

The cutoff, 2026-06-01, lies after the newest row (2026-03-25 07:00:00); in real life pick a little time in the future. The target is still empty. Backfill the history with the inverted condition, one partition at a time so each statement stays small and can be retried on its own:

-- one chunk = one partition; {p:UInt32} is a query parameter
INSERT INTO metrics_daily
SELECT toDate(ts), sensor_id, metric, sum(value), count()
FROM metrics
WHERE ts < '2026-06-01 00:00:00' AND toYYYYMM(ts) = {p:UInt32}
GROUP BY toDate(ts), sensor_id, metric;

Save that statement as backfill-chunk.sql and drive it from a shell loop, one run per partition, so a failed chunk can be retried alone (--param_p binds the parameter):

for p in 202601 202602 202603; do
  clickhouse-client --param_p="$p" --queries-file backfill-chunk.sql
done

Filtering by toYYYYMM(ts) matches the partition key, so each chunk reads one partition (more on that in ORDER BY, PARTITION BY and pruning). New rows now flow through the view:

INSERT INTO metrics VALUES ('2026-06-01 10:00:00', 1, 'temp', 30), ('2026-06-01 11:00:00', 1, 'temp', 32);

Compare the target against the source before you trust it:

SELECT 'mv path', sum(samples), round(sum(value_sum) / sum(samples), 4) FROM metrics_daily;
SELECT 'raw', count(), round(avg(value), 4) FROM metrics;

mv path	2002	19.5115
raw	2002	19.5115

For a finer check, join per day and count mismatches. It returned 0 here:

SELECT count() AS mismatched_days FROM (
    SELECT toDate(ts) AS day, sensor_id, count() AS c FROM metrics GROUP BY day, sensor_id) AS r
FULL JOIN (
    SELECT day, sensor_id, sum(samples) AS c FROM metrics_daily GROUP BY day, sensor_id) AS t
USING (day, sensor_id)
WHERE r.c != t.c;

Caveats worth writing on the wall:

• Late data. The method assumes rows arrive roughly in order of the cut column. A row with ts < cutoff inserted after you ran the backfill never reaches the target, because the view filters it out. Backfill after the cutoff has passed and after your ingestion lag, or re-run the chunk for the affected partition. Re-running a chunk on a SummingMergeTree adds to what is there, so delete the target’s rows for that range first, or backfill into a fresh table and swap.
• Big history. If even one chunk is too large for memory, shrink the chunks (days instead of months), or let aggregation spill with max_bytes_before_external_group_by.
• No gaps, no overlap. The two WHERE conditions must be exact complements. Use the same literal in both places.
• Changing a view later. ALTER TABLE ... MODIFY QUERY changes only future behaviour; it does not rewrite old rows. To change a view’s logic properly, create the new view with its own target and cutoff, backfill, and drop the old one. A deploy script that has to re-create a view can use CREATE OR REPLACE from 26.5 onwards (the request that added it); before that it needs an existence check and a DROP.

Refreshable materialized views

An incremental view reacts to inserts into one table. A refreshable materialized view works the other way round: on a schedule it re-runs a complete SELECT over whatever it reads and puts the result into a target table. It is much closer to a PostgreSQL materialized view than to a trigger, and in current releases it is no longer experimental.

	Incremental MV	Refreshable MV
Trigger	INSERT into the source table	schedule
Data processed	only the inserted block	the whole query, every time
Joins	only the left-most table triggers	any joins, unions, subqueries
Freshness	as inserts happen	up to one refresh interval old
Target update	per insert block	atomic swap of the whole target (REPLACE)
Scales to	very large sources (cost is per insert)	what a full query can read within the interval

Incremental views stay the default for aggregating high-volume data. Reach for a refreshable one when the result depends on several tables that all change (the join problem above), when you need a denormalised “current state” table that is recomputed rather than accumulated, when you want to cache an expensive top-N or report, when you want periodic snapshots (APPEND), or when you want to chain batch steps without an external scheduler.

Refreshable views need a database with the Atomic or Replicated engine, which is the default on servers. Fill users with some more rows first, then:

INSERT INTO users SELECT number, if(number % 2 = 0, 'free', 'pro') FROM numbers(10, 4990);

CREATE TABLE plan_summary (plan LowCardinality(String), events UInt64,
                           avg_duration_ms Float64, refreshed_at DateTime)
ENGINE = MergeTree ORDER BY plan;

CREATE MATERIALIZED VIEW plan_summary_mv
REFRESH EVERY 1 HOUR
TO plan_summary AS
SELECT u.plan AS plan, count() AS events, avg(e.duration_ms) AS avg_duration_ms, now() AS refreshed_at
FROM events AS e
INNER JOIN users AS u ON u.user_id = e.user_id
GROUP BY u.plan;

SYSTEM WAIT VIEW plan_summary_mv;   -- blocks until the first refresh completes
SELECT plan, events, round(avg_duration_ms, 1) FROM plan_summary ORDER BY plan;

┌─plan─┬─events─┬─round(avg_duration_ms, 1)─┐
│ free │ 500011 │                       259 │
│ pro  │ 500000 │                       260 │
└──────┴────────┴───────────────────────────┘

By default the view refreshes immediately on creation and then on its schedule. Queries against plan_summary always see a complete result: during a refresh they read the previous one, and the new data replaces it in one atomic step. A view can also define its own storage without TO (with an ENGINE clause); you then query the view itself. With TO, a target table can belong to only one refreshable view.

Syntax and schedules

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]name [ON CLUSTER cluster]
REFRESH EVERY|AFTER interval [OFFSET interval]
[RANDOMIZE FOR interval]
[DEPENDS ON [db.]other_view [, ...]]
[SETTINGS name = value [, ...]]
[APPEND]
[TO [db.]target_table] [(columns)] [ENGINE = engine]
[EMPTY]
AS SELECT ...

Order matters: APPEND comes before TO, and EMPTY after it. An interval is a number and a unit from SECOND to YEAR.

• REFRESH EVERY is aligned to the clock, like cron: EVERY 1 DAY runs at midnight, EVERY 1 DAY OFFSET 2 HOUR at 02:00, EVERY 30 MINUTE at :00 and :30.
• REFRESH AFTER is relative to the end of the previous refresh. OFFSET is not allowed with it.
• RANDOMIZE FOR adds jitter around the scheduled time so many views, or many replicas, do not all start in the same second.
• EMPTY skips the refresh at creation; the target stays empty until the first scheduled or manual refresh. Use it for an expensive query created at a bad time.

CREATE TABLE country_totals (country LowCardinality(String), events UInt64)
ENGINE = MergeTree ORDER BY country;

CREATE MATERIALIZED VIEW country_totals_mv
REFRESH EVERY 1 DAY OFFSET 2 HOUR
TO country_totals EMPTY AS
SELECT country, count() AS events FROM events GROUP BY country;

SELECT count() AS rows_after_create FROM country_totals;   -- 0
SYSTEM REFRESH VIEW country_totals_mv;
SYSTEM WAIT VIEW country_totals_mv;
SELECT count() AS rows_after_manual_refresh FROM country_totals;   -- 3

Only one refresh of a view runs at a time. Keep the interval comfortably longer than the query’s runtime; a view that is always refreshing keeps a permanent load on the server.

REPLACE versus APPEND

REPLACE (the default) swaps the whole target atomically on each refresh: use it when only the current state matters. APPEND adds the result to the target and keeps what is there, which gives you snapshots and history:

CREATE TABLE page_snapshots (snapshot_time DateTime, url String, views UInt64)
ENGINE = MergeTree PARTITION BY toYYYYMM(snapshot_time) ORDER BY (url, snapshot_time)
TTL snapshot_time + INTERVAL 90 DAY;

CREATE MATERIALIZED VIEW page_snapshots_mv
REFRESH EVERY 1 HOUR APPEND
TO page_snapshots AS
SELECT now() AS snapshot_time, url, count() AS views
FROM events GROUP BY url;

Each refresh adds one row per URL stamped with the refresh time, so you can ask what the table looked like six hours ago. Two cautions. An APPEND write is not an atomic swap, so a reader may see a partial result mid-refresh and a failed refresh may leave one behind. And the target grows without bound unless you give it a TTL, as above, or a retention job.

DEPENDS ON

DEPENDS ON makes one refreshable view wait for another, which is how you chain batch steps without an external scheduler:

CREATE TABLE stage1 (user_id UInt64, plan LowCardinality(String), events UInt64)
ENGINE = MergeTree ORDER BY user_id;
CREATE TABLE stage2 (plan LowCardinality(String), events UInt64, users UInt64)
ENGINE = MergeTree ORDER BY plan;

CREATE MATERIALIZED VIEW stage1_mv REFRESH EVERY 1 DAY OFFSET 2 HOUR
TO stage1 AS
SELECT e.user_id, u.plan, count() AS events
FROM events AS e INNER JOIN users AS u USING (user_id)
GROUP BY e.user_id, u.plan;

CREATE MATERIALIZED VIEW stage2_mv REFRESH EVERY 1 DAY OFFSET 2 HOUR
DEPENDS ON stage1_mv
TO stage2 AS
SELECT plan, sum(events) AS events, uniq(user_id) AS users FROM stage1 GROUP BY plan;

Use the same schedule for both so stage2_mv runs after stage1_mv finishes, and list only other refreshable views as dependencies; a regular table is not a meaningful dependency.

Operating them

SYSTEM REFRESH VIEW plan_summary_mv;   -- refresh now
SYSTEM STOP VIEW plan_summary_mv;      -- pause the schedule
SYSTEM START VIEW plan_summary_mv;     -- resume
SYSTEM CANCEL VIEW plan_summary_mv;    -- abort a refresh in progress
SYSTEM WAIT VIEW plan_summary_mv;      -- block until the current refresh ends

ALTER TABLE plan_summary_mv MODIFY REFRESH EVERY 2 HOUR;

system.view_refreshes has one row per refreshable view. After the examples above, with all three chains refreshed once:

SELECT view, status, last_success_duration_ms, exception, read_rows, written_rows
FROM system.view_refreshes ORDER BY view;

┌─view──────────────┬─status────┬─last_success_duration_ms─┬─exception─┬─read_rows─┬─written_rows─┐
│ page_snapshots_mv │ Scheduled │                        5 │           │   1000011 │            8 │
│ plan_summary_mv   │ Scheduled │                       14 │           │   1005011 │            2 │
│ stage1_mv         │ Scheduled │                       18 │           │   1005011 │         5000 │
│ stage2_mv         │ Scheduled │                        4 │           │      5000 │            2 │
└───────────────────┴───────────┴──────────────────────────┴───────────┴───────────┴──────────────┘

(country_totals_mv is omitted: it was created EMPTY, so until it is refreshed last_success_time is empty.) A stopped view shows Disabled, a waiting one Scheduled. The columns also include last_success_time, last_refresh_time, next_refresh_time and retry. Alert on a non-empty exception, on last_success_time falling behind the schedule, and on last_success_duration_ms approaching the interval. In a Replicated database the replicas coordinate so that one of them runs each refresh; last_refresh_replica shows which.

Pitfalls: a runtime close to the interval means the load never goes away; APPEND without a TTL grows forever; the default of refreshing at CREATE makes a heavy first run land at a bad time (use EMPTY); many views on one schedule want RANDOMIZE FOR; and refreshable views are not real time, because the data is as old as the last refresh.

What a view costs, and how it fails

Insert cost. Every view attached to a source runs on every block of every insert, inside the insert. Inserting five million rows into a source took about 1.0 s with no views and 1.1 to 1.5 s with six aggregating views (avgState, quantileState, uniqState) on a small multi-core machine; the views ran in parallel, so the overhead was modest. It grows with the cost of the states (uniq and quantile are not free) and with the number of views, and on a busy server it is paid by the client waiting for its insert acknowledgement. A source with many views multiplies the work.

Partial failure. The insert is not transactional across tables. If a view’s query fails, the client gets an error, but the source table may already have its rows. Reproduce it with a view that throws:

CREATE TABLE sink (x UInt64) ENGINE = MergeTree ORDER BY x;
CREATE TABLE audit AS events;
CREATE MATERIALIZED VIEW bad_mv TO sink AS SELECT throwIf(user_id = 7, 'boom') AS x FROM audit;

INSERT INTO audit SELECT now(), number, 'view', '/', 1, 'DE' FROM numbers(10);
-- Code: 395. DB::Exception: boom: ... while pushing to view analytics.bad_mv

SELECT (SELECT count() FROM audit) AS source_rows, (SELECT count() FROM sink) AS sink_rows;

┌─source_rows─┬─sink_rows─┐
│          10 │         0 │
└─────────────┴───────────┘

The client saw an error, the source kept all 10 rows, and the target has none. A client that retries the insert, as a client should after an error, now writes the source rows twice. With several views, some targets can have the block and others not. So keep view queries cheap and total: no throwIf, no casts that can fail without a fallback (toUInt32OrZero rather than toUInt32), no joins against a table that can be missing. If exactness matters, reconcile the targets against the source periodically with the same comparison queries used in the backfill section. For streaming ingestion the same risk shows up as duplicates, see Kafka and ClickHouse.

Provenance. When several views write into one target, nothing records which one wrote a row; a _view virtual column has been requested, and until then put a literal marker column in each view’s SELECT.

When not to use a materialized view

• The result must follow updates or deletes of the source. An incremental view never learns about them. Use a refreshable view, or a query.
• The result depends on a dimension that changes. The join section above: the fact table’s inserts are the only trigger. Dictionary, read-time join or refreshable view.
• The grain barely shrinks the data. If the target has a large fraction of the source’s rows, you duplicated the table. A projection keeps a differently-sorted or aggregated copy inside the same table and is maintained by the engine, with no second table to keep consistent.
• The query is fast enough already. A dashboard hitting a 50 million row table in 20 ms does not need a pipeline to babysit. Measure first, with rows_read in system.query_log.
• You need exact-once guarantees with non-additive logic across failures. See the partial-failure section: either design the target to be idempotent (deduplicating engines, deterministic block keys) or reconcile.
• Freshness is “whatever the last query says”. For an expensive report that several people run, the query cache or a refreshable view is simpler than an incremental one.

If you do build one, the checklist is short: an explicit TO target, an engine that matches the arithmetic (sum, or -State/-Merge), a read path that always GROUP BYs the key, a view query that cannot throw, a documented backfill procedure with a cutoff, and a reconciliation query you actually run.