Using ClickHouse Materialized Views for Sub‑Second Dashboards on High‑Cardinality Event Streams

Problem: Dashboards lag behind incoming event streams

When you ingest millions of events per day and need to show roll‑ups (e.g., hourly sums per user and event type) in a BI tool, a plain SELECT with GROUP BY can take seconds. The latency comes from scanning raw rows and computing aggregates on‑the‑fly, which hurts user experience and increases load on the cluster.

Thesis: A correctly defined Materialized View (MV) gives you incremental, pre‑aggregated storage that the optimizer can rewrite queries to, turning multi‑second scans into sub‑second reads while keeping the ingestion path simple.

How Materialized Views work in ClickHouse

An MV is not a separate table you fill manually; it is a trigger‑like INSERT hook. Every INSERT into the source table automatically writes transformed rows to an internal target table. The target table uses a MergeTree‑family engine (usually AggregatingMergeTree) that stores aggregate state columns such as sumState, countState, and uniqState. Because the state columns are additive, the MV can be merged later without losing precision.

The optimizer can rewrite a query that reads from the source table to read from the MV when the query’s GROUP BY list and aggregate functions exactly match the MV’s definition. This rewrite is controlled by the setting optimize_rewrite_materialized_views (default = 1). No application change is required.

Worked example: hourly roll‑up for a high‑cardinality event stream

Assume you ingest events with the following schema:

CREATE TABLE events (
    user_id UInt64,
    event_type String,
    ts DateTime,
    value UInt32,
    session_id UInt64
) ENGINE=MergeTree ORDER BY (user_id, ts);

You want a dashboard that shows, per hour, the total value, the event count, and the number of distinct users. The MV definition is:

CREATE MATERIALIZED VIEW events_hourly
ENGINE=AggregatingMergeTree
ORDER BY (user_id, event_type, hour)
AS SELECT
    user_id,
    event_type,
    toStartOfHour(ts) AS hour,
    sumState(value)   AS sum_val,
    countState()      AS cnt,
    uniqState(session_id) AS uniq_sessions
FROM events
GROUP BY user_id, event_type, hour;

Insert a few rows to see the MV populate:

INSERT INTO events (user_id, event_type, ts, value, session_id) VALUES
    (123, 'click', now(), 10, 1001),
    (123, 'click', now(), 5,  1001),
    (456, 'view',  now(), 2,  2002);

Now query the source table; with rewrite enabled the plan should read from events_hourly:

SET optimize_rewrite_materialized_views=1;
EXPLAIN SYNTAX
SELECT user_id, event_type, toStartOfHour(ts),
       sum(value), count(), uniq(session_id)
FROM events
GROUP BY user_id, event_type, toStartOfHour(ts);

Look for a ReadFromMergeTree step on events_hourly in the output. If you see it, the optimizer successfully rewrote the query.

Trade‑offs and operational considerations

• Insert latency: By default the MV write is synchronous; the INSERT blocks until the MV target part is written. Measure p99 insert latency before enabling on high‑throughput paths.
• Asynchronous mode: Setting materialized_view_async_insert=1 decouples MV writes from the INSERT, reducing insert latency but introducing eventual consistency. Dashboards may show stale counts for seconds to minutes; you can flush with SYSTEM FLUSH LOGS or wait for the background thread.
• Schema changes: ALTER MODIFY QUERY is not supported. To add a column or change the GROUP BY, you must drop the MV, recreate it with the new query, and backfill: INSERT INTO mv_new SELECT … FROM source. For zero‑downtime swaps, create a shadow MV, backfill, then RENAME TABLE mv_old TO mv_old_backup, mv_new TO mv_old.
• Storage overhead: The MV size equals the compressed size of the aggregated data. For hourly roll‑ups it is often 5‑20 % of raw size, but grows with the cardinality of the GROUP BY keys. Very high‑cardinality keys (e.g., user_id + session_id) can approach the raw size, diminishing the benefit.
• JOINs in MVs: You can enrich with a dictionary or a tiny MergeTree table, but large JOINs during INSERT will stall ingestion because the MV build happens on the write path.

Actionable steps to try today

1. Identify a frequent aggregate query in your workload (e.g., hourly sums per dimension).
2. Create a source table if you don’t have one, using a MergeTree engine ordered by the columns you filter on.
3. Define an MV with an AggregatingMergeTree target, placing the exact GROUP BY list and using *State functions for each aggregate.
4. Enable optimize_rewrite_materialized_views (default) and run EXPLAIN to confirm the rewrite.
5. Monitor insert latency with system.query_log or system.metrics; if it exceeds your SLA, test materialized_view_async_insert=1 and measure staleness tolerance.
6. Periodically check MV size: SELECT table, formatReadableSize(sum(bytes)) FROM system.parts WHERE table IN ('source','mv') GROUP BY table;

By following these steps you turn expensive, on‑the‑fly aggregations into cheap reads from a pre‑computed store, keeping dashboards responsive without adding external pipelines.