How to Speed Up ClickHouse Dashboards with Materialized Views
A dashboard that queries raw event tables straight from ClickHouse works fine at a million rows and falls apart at a billion. The fix is not a bigger server. It is pre-aggregation: materialized views that maintain a summary table as data arrives.
This is the step most teams skip, then spend a quarter rediscovering.
Why are my ClickHouse dashboard queries slow?
Usually because they scan more than they should. A GROUP BY hour over 90 days of raw events touches every one of those rows. Even at roughly 100 million rows per second per core for a simple aggregate, a 5-billion-row table takes seconds and competing dashboards multiply that.
Check with EXPLAIN indexes = 1 before guessing. If ReadFromMergeTree shows the primary index selecting all granules, your WHERE clause is not hitting the sort key and no amount of tuning elsewhere will help.
How does a materialized view actually work in ClickHouse?
It is not a cached query. It is an insert trigger. When rows land in the source table, the view’s SELECT runs over just those inserted blocks and writes results to a target table.
You define both the target table and the view:
CREATE TABLE hourly_stats (
hour DateTime,
country LowCardinality(String),
events UInt64,
uv AggregateFunction(uniq, UInt64)
) ENGINE = SummingMergeTree
ORDER BY (hour, country);
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS
SELECT toStartOfHour(ts) AS hour, country, count() AS events,
uniqState(user_id) AS uv
FROM events
GROUP BY hour, country;
The AggregateFunction column type is the part people miss. You store uniqState and read it with uniqMerge. That lets aggregates merge correctly across hourly buckets instead of double-counting.
Critical caveat: the view only sees data inserted after it is created. Existing rows are not backfilled. If you need history, insert the old range into the view’s source with a INSERT INTO ... SELECT or rebuild from a copy.
When should you use a projection instead?
A projection is an alternative physical ordering stored inside the same table. It is cheaper to manage than a separate view because it stays attached to the table’s lifecycle.
ALTER TABLE events ADD PROJECTION by_country (SELECT country, toStartOfHour(ts), count() GROUP BY country, toStartOfHour(ts))
Use a projection when the aggregate is small and the source table is not enormous. Use a materialized view when you need to join across tables or maintain multiple rollup levels. Do not stack both on the same column set; the optimizer gets confused and maintenance doubles.
How do you build a fast dashboard layout?
Three layers:
- Raw table — full event granularity, sorted by
(user_id, ts). Queried for debugging only. - Hourly rollup — materialized view as above. Answers anything from 1 hour to 30 days.
- Daily rollup — second materialized view reading from the hourly table, not the raw one. Answers 90-day and year-to-date charts.
That daily-on-hourly chain keeps the second view cheap to maintain. Chaining materialized views is supported and is the standard pattern for multi-resolution dashboards.
Query the layer that matches the time range. A 7-day chart should never touch the raw table.
What are the costs and traps?
Every materialized view doubles your write amplification for the columns it aggregates. With three views on a 100k-rows-per-second stream, inserts become the bottleneck before queries do. Measure insert throughput after adding each view.
Merge pressure also rises. SummingMergeTree merges parts in the background; if parts accumulate faster than merges complete, system.parts will show a growing count and queries slow down. Watch that table.
And one design trap: do not put a high-cardinality column like user_id into the GROUP BY of an hourly rollup unless you really need per-user hourly granularity. That converts a 5,000-row-per-hour summary into millions of rows and deletes the entire benefit.
Teams running big dashboards usually pair this with a shorter raw retention — 90 days raw, rollups forever. Keeping raw data forever is the most common way a ClickHouse cluster doubles in size for no analytical gain.
Quick Summary
- ClickHouse materialized views are insert triggers over a target table, not cached queries.
- Store aggregates with
AggregateFunction(...State)and read with the matchingMergefunction. - Views do not backfill; load history separately or accept the start date.
- Chain hourly to daily rollups rather than building both from the raw table.
- Keep raw retention short (about 90 days) and rollups long; that controls cluster growth.
