How to Speed Up ClickHouse Dashboards with Materialized Views

November 16, 2026

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:

  1. Raw table — full event granularity, sorted by (user_id, ts). Queried for debugging only.
  2. Hourly rollup — materialized view as above. Answers anything from 1 hour to 30 days.
  3. 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 matching Merge function.
  • 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.