Incremental Materialized Views

Materialized Views as Incremental Aggregation

A ClickHouse 29,491 materialized view is not PostgreSQL 1,289 's stored snapshot (Materialized vs Regular Views). It is an insert trigger: each block inserted into the source table runs through the view's SELECT, and the result is inserted into a target table. There is no refresh, so a dashboard reads a small aggregate that is always current.

1444-mv.sql: a daily genre aggregate kept current on every insertSQL

DROP TABLE IF EXISTS booknest.genre_day_mv;
DROP TABLE IF EXISTS booknest.genre_day;
CREATE TABLE booknest.genre_day (order_date Date, genre LowCardinality(String),
                                 lines UInt64, gross Decimal(18, 2))
ENGINE = SummingMergeTree ORDER BY (genre, order_date);   -- merges add up rows with equal keys
CREATE MATERIALIZED VIEW booknest.genre_day_mv TO booknest.genre_day AS
SELECT order_date, genre, count() AS lines, sum(gross_amount) AS gross
FROM booknest.sales_x10 GROUP BY order_date, genre;
INSERT INTO booknest.genre_day                              -- backfill existing rows once
SELECT order_date, genre, count(), sum(gross_amount) FROM booknest.sales_x10 GROUP BY ALL;
SELECT order_date, genre, sum(lines) AS lines, sum(gross) AS gross FROM booknest.genre_day
WHERE genre = 'Travel' AND order_date = '2026-06-30' GROUP BY ALL;
INSERT INTO booknest.sales_x10 (order_id, line_no, order_date, channel, status, customer_id,
  country, book_id, title, author, genre, qty, unit_price, gross_amount)
VALUES (2000001, 1, '2026-06-30', 'web', 'paid', 7, 'US', 4, 'Small Steps to Big Summits',
        'Jonas Berg', 'Travel', 2, 18.75, 37.50);           -- the trigger fires on this block
SELECT order_date, genre, sum(lines) AS lines, sum(gross) AS gross FROM booknest.genre_day
WHERE genre = 'Travel' AND order_date = '2026-06-30' GROUP BY ALL;
SELECT count() AS stored_rows FROM booknest.genre_day;     -- 2,911 + 1 until the next merge

Output

   ┌─order_date─┬─genre──┬─lines─┬─gross─┐
1. │ 2026-06-30 │ Travel │   310 │  6750 │
   └────────────┴────────┴───────┴───────┘
   ┌─order_date─┬─genre──┬─lines─┬──gross─┐
1. │ 2026-06-30 │ Travel │   311 │ 6787.5 │
   └────────────┴────────┴───────┴────────┘
...

The view sees only new inserts, so existing rows were backfilled once. The insert added a second row for the same key: the target held 2,912 rows for 2,911 keys until a merge summed them, so queries must still sum() and GROUP BY. For measures that do not add up, such as distinct customers, use AggregatingMergeTree with uniqState() and uniqMerge().

MENU