ClickHouse Materialized Views with AggregatingMergeTree: Real‑Time Aggregation Trade‑offs Explained

Real‑time dashboards over event streams that arrive by the million per day can’t afford full‑table scans on every load. Scanning raw MergeTree tables billions of rows deep adds seconds or worse to query latency, which is unacceptable for user‑facing analytics.

Useful takeaway: ClickHouse materialized views with AggregatingMergeTree pre‑compute aggregates the moment data lands, turning what would be a query‑time scan into a read‑only merge of pre‑computed states. This enables dashboard queries in under 50 ms, but the pattern adds write latency, requires careful schema‑migration planning, and introduces operational constraints around cardinality, TTL, and merge behavior that engineers need to anticipate.

How materialized views pre‑aggregate at ingest time

In ClickHouse, a materialized view is not a passive index. It is a trigger‑based data pipeline that fires on every INSERT into the source table. The view’s SELECT query runs against the incoming block, and the resulting rows are written into a target table—often an AggregatingMergeTree. That target table stores pre‑computed aggregate states (sum, count, uniq, quantiles) instead of raw rows, so queries that group by the view’s key finish in milliseconds even over billions of events.

The view definition uses aggregate functions wrapped in ‑State combinators (sumState, uniqState). This tells ClickHouse to store intermediate aggregation states rather than final values. During background merges, ClickHouse combines partial states from different parts, producing the final aggregate incrementally. By contrast, the ‑Merge combinators (sumMerge, uniqMerge) are used at query time to safely combine those stored states.

Typical pattern and a worked example

A common real‑time dashboard layout lands raw events in a MergeTree table partitioned by day. Columns might include user_id, event, timestamp, and a numeric value. A materialized view groups by (toStartOfHour(timestamp), event, user_id) and writes to an AggregatingMergeTree with AggregateFunction(sum, Int64) for value and AggregateFunction(uniq, String) for user_id. The view’s SELECT uses sumState and uniqState to capture incremental states.

Dashboards query the view with ‑Merge combinators: SELECT event, sumMerge(sum_value), uniqMerge(uniq_users) FROM mv WHERE hour >= now() - 7d GROUP BY event. Because the view stored partial states, the query merges them on the fly and returns aggregated results in under 50 ms even over hundreds of millions of source rows.

Worked example (illustrative): Ingest 100 million events per day with columns (user_id, event, timestamp, value). Materialized view aggregates to (toStartOfHour(timestamp), event, sumState(value), uniqState(user_id)) into an AggregatingMergeTree. Dashboard runs SELECT event, sumMerge(sum_value), uniqMerge(uniq_users) FROM mv WHERE hour >= now() - 7d GROUP BY event and receives results in <50 ms. Note: the timing described is based on typical cluster configurations; actual latency depends on data volume, node count, and merge settings.

Trade‑offs, limitations, and what to check

• Write latency: Materialized views execute synchronously on the INSERT path. A complex MV or heavy aggregation can block or slow ingestion. Keep MV logic simple and monitor insert_latency_seconds quantiles in system.asynchronous_metrics.
• Schema migration gotcha: Adding a column to the source table with ALTER TABLE source ADD COLUMN does not propagate to the materialized view. You must DROP and recreate the MV, which requires a full backfill of the target table. Plan for this during any source‑table evolution.
• Merge non‑determinism: AggregatingMergeTree merges are background and non‑deterministic. Query results are eventually consistent. Use the FINAL modifier (sumMerge(...) FINAL) for accuracy at query‑time cost, or accept eventually consistent reads for dashboards that can tolerate slight staleness.
• Cardinality explosion: High‑cardinality GROUP BY keys (e.g., user_id) in the MV can inflate the target table size dramatically. If approximate distinct counts suffice, prefer uniqCombined or the uniqThetaSketch function with a bounded memory profile.
• TTL mismatch: The TTL configured on the source table does not cascade to the materialized view’s target table. If you need different retention for aggregates, define a separate TTL on the target table.

Risks: Running system.* queries requires appropriate ClickHouse admin privileges. Mis‑filtered logs can produce noise; always scope by database and table name. Merge observations are informational—non‑deterministic merge timing is expected.

Diagnostic command cheat‑sheet

Command	Where to run	Placeholder / Notes	Expected check
Check MV metadata	Any ClickHouse node, preferably a replica with metadata access	SELECT * FROM system.materialized_views WHERE database = 'mydb'	MV name, source table, target table, TTL defined
Inspect INSERT‑trigger query log	Node receiving the INSERT	system.query_log filtered by query_id containing 'MV'	Internal SELECT statements from the MV definition fire on each block
Observe merge activity	Node hosting the target AggregatingMergeTree	SELECT * FROM system.merges WHERE database = 'mydb' AND table = 'mv_target'	Parts merging; aggregate state combination visible in merge metadata

Projections vs. materialized views

ClickHouse projections offer an alternative: they store an alternative sort order of the same table without a separate target table. Projections cannot change granularity, apply different TTLs, or wrap aggregates in state combinators. Choose a materialized view when your dashboard needs pre‑computed aggregates at a granularity different from the source, or when you need a separate TTL for the aggregated data. If you only need a different physical ordering for point‑lookup queries, a projection may add storage overhead with less operational complexity.

Actionable closing: Before adopting a materialized view with AggregatingMergeTree, map your dashboard’s aggregation granularity against the source table’s partitioning key. Keep the MV’s SELECT simple—avoid joins or complex expressions in the ingest path. Monitor insert_latency_seconds after deployment, and document the MV’s source‑target mapping so a future ALTER TABLE on the source doesn’t silently break the pipeline. When approximate distinct counts suffice, replace uniqState with uniqThetaSketch to tame target‑table size. With these guards in place, materialized views become a reliable path to sub‑50 ms real‑time dashboards.