ClickHouse Materialized Views for Real-Time Rollups

Last updated: October 2026

A dashboard that aggregates billions of raw events on every page load will eventually be slow, however well the raw table is sorted. Pre-aggregating at insert time means the dashboard reads thousands of rollup rows instead of billions of events, and ClickHouse's materialized views are the usual way to do it.

ClickHouse has two kinds of materialized view, and they behave very differently. This guide covers the incremental kind in depth, because it is the right default for rollups, and then explains when a refreshable view is the better choice. If the raw queries themselves are slow, start with our ClickHouse query tuning guide.

What an incremental materialized view does

ClickHouse's documentation describes a materialized view as a trigger that runs a query on blocks of data as they are inserted into a table. The query sees only the newly inserted rows. Updates, deletes, partition drops and background merges on the source table never reach the view, so a rollup built this way reflects what was inserted, and a correction to the source needs a matching correction to the rollup.

The recommended pattern sends results to an explicit target table with the TO clause. The target is an ordinary table that you create and query directly, and the view only defines how rows get from the source into it. Column names in the view's SELECT must match the target table's columns.

A rollup with AggregatingMergeTree

The example below keeps hourly totals per tenant: the number of events, unique users and revenue. The target table stores aggregate function states instead of final values, which is what allows unique-user counts to combine correctly across separate inserts.

-- Source table
CREATE TABLE events
(
    event_time   DateTime,
    tenant_id    UInt32,
    user_id      UInt64,
    amount       Decimal(18, 2),
    inserted_at  DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY (tenant_id, event_time);

-- Target table holding aggregate states
CREATE TABLE events_hourly
(
    hour       DateTime,
    tenant_id  UInt32,
    events     AggregateFunction(count),
    users      AggregateFunction(uniq, UInt64),
    revenue    AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, hour);

-- The view: runs on every block inserted into events
CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT toStartOfHour(event_time) AS hour,
       tenant_id,
       countState()       AS events,
       uniqState(user_id) AS users,
       sumState(amount)   AS revenue
FROM events
GROUP BY hour, tenant_id;

The view writes states with countState, uniqState and sumState, and each state type matches its target column exactly; we checked all three on ClickHouse 26.9. Each insert into events adds a part to events_hourly with partial states for the hours and tenants in that block, and background merges later combine rows that share the sort key.

Because those merges happen at an unpredictable time, the target can hold several rows for the same hour and tenant when a query runs. Always query it with the -Merge functions and a GROUP BY. ClickHouse's documentation gives the same rule: use the aggregate functions used at insert time, with the -Merge suffix.

SELECT hour,
       countMerge(events) AS events,
       uniqMerge(users)   AS users,
       sumMerge(revenue)  AS revenue
FROM events_hourly
WHERE tenant_id = 42
  AND hour >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

For rollups that only sum and count, SummingMergeTree is a simpler alternative that adds up numeric columns when rows merge. The same rule applies, since merging may be incomplete when you query, so use sum() with GROUP BY. SummingMergeTree also deletes a row during a merge when all of its summed values are zero, which matters if a zero row means something in your reports.

Backfilling history

A new view processes only rows inserted after it was created. The POPULATE option copies existing data at creation time, but ClickHouse warns that population is not atomic on some sources and can miss or duplicate rows inserted while it runs. A safer approach is to create the view first and then insert the history yourself with the same query, writing states into the target table.

-- Run once, after the view exists. The cut-off is the time the view was created,
-- applied to insertion time so that late-arriving events are not counted twice.
INSERT INTO events_hourly
SELECT toStartOfHour(event_time) AS hour,
       tenant_id,
       countState()       AS events,
       uniqState(user_id) AS users,
       sumState(amount)   AS revenue
FROM events
WHERE inserted_at < '2026-10-15 00:00:00'
GROUP BY hour, tenant_id;

The cut-off has to separate rows by when they were inserted. Events that arrive late with an old event_time are processed by the view as they arrive, so a filter on event_time would count them twice, which is why the example table has an inserted_at column. Where no such column exists, pausing ingestion while the view is created and the backfill runs gives the same guarantee.

Joins and lookups in views

A view with a JOIN is triggered only by inserts into the left-most table in its query. Changes to the right-hand tables never trigger it, so a rollup that joins to a tenants table keeps the old region on rows already aggregated, even after a tenant moves region. The right-hand tables are also read in full each time a block is inserted, which slows inserts as those tables grow.

ClickHouse recommends dictionaries for simple lookups, such as mapping a tenant ID to a region, and filtering the right-hand table to the keys in the inserted block where a join cannot be avoided. If lookup values change and the rollup must always show the latest values, a refreshable view is usually the cleaner design.

Refreshable materialized views

A refreshable materialized view runs its query on a schedule over the full dataset, then replaces the target table's contents atomically, or appends to it when APPEND is specified. Refreshable views have been production-ready since ClickHouse 24.10, and release 26.9 added REFRESH ... APPEND INCREMENTAL, which appends only rows committed to the source since the last refresh. DEPENDS ON makes one refreshable view wait for another to finish, which helps with chained summaries.

-- The target table tenant_daily_summary must already exist
CREATE MATERIALIZED VIEW tenant_daily_summary_mv
REFRESH EVERY 15 MINUTE TO tenant_daily_summary AS
SELECT toDate(e.event_time) AS day,
       t.region             AS region,
       count()              AS events
FROM events AS e
INNER JOIN tenants AS t ON t.tenant_id = e.tenant_id
WHERE e.event_time >= today() - 30
GROUP BY day, region;

-- Status of every refreshable view, and a manual refresh
SELECT database, view, status, last_success_time, next_refresh_time, exception
FROM system.view_refreshes;

SYSTEM REFRESH VIEW tenant_daily_summary_mv;

ClickHouse's documentation calls the incremental approach the preferred method because it typically scales much better, so we keep refreshable views for results that genuinely need recomputation, such as joins against tables that change. One operational detail is easy to miss: unless refreshes are coordinated through ClickHouse Keeper, views forget their last refresh time when the server restarts and can all refresh at once.

Insert cost and how many views to attach

Every view on a table runs as part of each insert into it. By default the views run one after another (parallel_view_processing is 0), and running them in parallel raises CPU and memory pressure during inserts. Views that compute many state functions are CPU-intensive on their own. A ClickHouse blog post from February 2026 describes more than 50 views on one table as typically excessive, and well before that point we review whether several views could be combined into one.

Queries against ReplacingMergeTree tables often use FINAL to get deduplicated rows. FINAL has become much faster in recent versions and suits many production workloads, but it still adds overhead. For rollups held in AggregatingMergeTree or SummingMergeTree tables, GROUP BY with -Merge functions or sum() is the documented way to get complete results.