Using ClickHouse Materialized Views for Minute‑Level Event Aggregation

Problem: High latency on raw event tables

When a dashboard queries a table that stores every event as a separate row, each request must scan millions of records. The scan consumes CPU and I/O, pushing response times into seconds or minutes, especially as traffic grows.

Solution: Continuous aggregation with a materialized view

A materialized view can automatically roll up incoming events into a summary table. The view runs on every insert, updating the aggregate so that later queries read only a few pre‑computed rows instead of scanning the raw data.

Worked example: Setting up the source table, view, and aggregated table

1. Create a database for the demo.
2. Define the raw event table using the MergeTree engine.
3. Create the aggregated table that will hold minute‑level sums.
4. Create the materialized view that inserts into the aggregated table.

CREATE DATABASE IF NOT EXISTS demo;

CREATE TABLE demo.events
(
    timestamp DateTime64(3),
    metric    LowCardinality(String),
    value     Float64
)
ENGINE = MergeTree()
ORDER BY (timestamp, metric);

CREATE TABLE demo.event_agg_minute
(
    minute      DateTime64(3),
    metric      LowCardinality(String),
    sum_value   AggregateFunction(sum, Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (minute, metric);

CREATE MATERIALIZED VIEW demo.mv_event_agg
TO demo.event_agg_minute
AS
SELECT
    toStartOfMinute(timestamp) AS minute,
    metric,
    sumState(value) AS sum_value
FROM demo.events
GROUP BY minute, metric;

Insert a few rows to see the view in action.

INSERT INTO demo.events (timestamp, metric, value) VALUES
    (now(), 'page_view', 1),
    (now(), 'page_view', 1),
    (now(), 'click',     1);

Query the aggregated table; the view has already updated it.

SELECT
    minute,
    metric,
    sum(sum_value) AS total
FROM demo.event_agg_minute
GROUP BY minute, metric
ORDER BY minute;

Trade‑offs and operational considerations

• Write amplification: every insert to demo.events triggers an insert into demo.event_agg_minute, roughly doubling the write load.
• Extra storage: the aggregated table holds one row per minute per metric, which adds disk usage proportional to the cardinality of metric and the time range kept.
• Merge activity: the AggregatingMergeTree engine merges parts in the background. A high insert rate can increase merge pressure; tune max_bytes_to_merge_at_max_space_in_pool in the server config to control how much disk space merges may consume.
• Version note: the AggregateFunction(sum, Float64) syntax with the sumState combinator is stable from ClickHouse 21.8. Older releases need the legacy SimpleAggregateFunction form.
• Engine compatibility: materialized views only work with MergeTree family source tables. Using a Log or TinyLog source will cause the view to appear created but never receive data.

Verification steps and rollout guidance

1. Create a test database (as above) and insert a known number of rows, e.g., 100 000 events with random timestamps.
2. Run a manual aggregation for a specific minute:

SELECT sum(value)
FROM demo.events
WHERE timestamp >= '2026-09-30 12:00:00' AND timestamp < '2026-09-30 12:01:00';

3. Compare the result with the aggregated table:

SELECT sum(sum_value)
FROM demo.event_agg_minute
WHERE minute = '2026-09-30 12:00:00';

4. If the two numbers match, the view is correctly updating.
5. Monitor the view’s health:
• SELECT * FROM system.materialized_views WHERE database = 'demo' AND table = 'mv_event_agg'; – ensure is_shutdown is 0.
• SELECT * FROM system.mutations WHERE database = 'demo' AND table = 'event_agg_minute'; – watch for failed mutations after a burst of inserts.
6. When satisfied, roll out the view to higher‑traffic streams, starting with low‑cardinality metrics and gradually increasing.

Actionable closing

Start with a single metric, verify the aggregation, then observe system.merge and system.mutations for any spikes. Adjust the merge limits and consider a TTL on demo.event_agg_minute to drop old minutes if you do not need infinite history. With these steps you can turn a latency‑bound raw table into a fast‑responding summary while keeping the data fresh.