When ClickHouse Feels Slow, the Answer Is Often a Materialized View

A dashboard that takes 40 seconds to load is usually not a ClickHouse problem. ClickHouse scans billions of rows happily — but if every chart re-aggregates raw events from scratch, you're paying full-scan cost on every refresh. The fix that comes up again and again in production systems is a materialized view: pre-aggregate once at insert time, and let queries read a tiny rollup table instead of the raw firehose.

This post walks through a concrete setup, the one design decision that matters most (the target table's engine and key), and the write-amplification trade-off you accept in exchange.

The concrete problem

Say you have an events table tracking API usage — one row per request, columns like tenant_id, endpoint, duration_ms, and event_time. The analytics team wants per-tenant, per-hour latency percentiles and request counts. The naive query scans everything:

SELECT tenant_id,
       toStartOfHour(event_time) AS hour,
       count() AS requests,
       quantile(0.95)(duration_ms) AS p95
FROM events
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY tenant_id, hour;

On a few hundred million rows per week this works, but it's seconds-to-tens-of-seconds per query, and it gets worse linearly as data grows. Worse, every dashboard panel and every scheduled report repeats the same scan.

How a ClickHouse materialized view actually works

A ClickHouse materialized view is not a stored query result that you refresh. It's an insert trigger: when rows land in the source table, the view's SELECT runs over just that inserted block, and the result is written into a separate target table. Existing data is untouched unless you backfill it explicitly.

Two consequences follow:

• The view adds work on every insert — this is the write overhead you'll need to measure.
• The target table is a real table with its own engine, ordering key, and partitions. Designing it well is where the performance win comes from.

A worked example: hourly rollups with SummingMergeTree

Create the target table first. For rollup tables, SummingMergeTree (or AggregatingMergeTree for non-additive states like quantiles) is the standard choice — it merges rows with the same ordering key by summing numeric columns:

CREATE TABLE events_hourly
(
    tenant_id   UInt64,
    hour        DateTime,
    requests    UInt64,
    total_ms    Float64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, hour);

Then the view that feeds it:

CREATE MATERIALIZED VIEW events_hourly_mv
TO events_hourly
AS
SELECT tenant_id,
       toStartOfHour(event_time) AS hour,
       count() AS requests,
       sum(duration_ms) AS total_ms
FROM events
GROUP BY tenant_id, hour;

Run these in clickhouse-client or any SQL console with a user that has CREATE TABLE privileges on the database. The TO clause points the view at the pre-created target table — this is the recommended pattern, because it lets you control the engine and key explicitly instead of inheriting defaults.

Now the dashboard query reads the rollup:

SELECT tenant_id, hour, requests,
       total_ms / requests AS avg_ms
FROM events_hourly
WHERE hour >= now() - INTERVAL 7 DAY;

The rollup table has at most one row per tenant per hour — thousands of rows instead of hundreds of millions — so this returns in milliseconds. Note the trade-off baked into this example: exact quantiles don't survive summation, so the rollup stores averages. If you need p95, use AggregatingMergeTree with quantileState(0.95)(duration_ms) in the view and quantileMerge at query time.

Why partitioning and the ordering key do the heavy lifting

PARTITION BY toYYYYMM(hour) means a 7-day query prunes to one monthly partition (two at a boundary), and ClickHouse can drop entire partitions cheaply for retention instead of running deletes. Keep partitions coarse — monthly is right for most rollup tables; daily partitions on high-volume tables create too many parts and slow merges.

ORDER BY (tenant_id, hour) determines both the merge key for summation and the data skip granularity. Put the column you filter or group by most first. If most of your queries filter by time range across all tenants, (hour, tenant_id) is the better key — the ordering key should match your dominant access pattern, not the source table's.

The trade-off: write amplification and backfills

Every insert into events now also runs the aggregation and writes to events_hourly. For typical analytical ingest (batches of thousands of rows), this overhead is small — but it is not free, and a view with a heavy JOIN or many downstream views can noticeably slow inserts. Measure insert latency before and after enabling the view.

The sharper edge is backfilling: the view only sees inserts that happen after it was created. To populate history, run an explicit insert:

INSERT INTO events_hourly
SELECT tenant_id, toStartOfHour(event_time) AS hour,
       count() AS requests, sum(duration_ms) AS total_ms
FROM events
WHERE event_time < (SELECT min(event_time) FROM events_hourly_mv_source_cutoff)
GROUP BY tenant_id, hour;

In practice, record the view creation time and backfill everything before it, otherwise you double-count rows inserted concurrently. On very large tables, run the backfill in monthly chunks during low-traffic windows — a single giant INSERT ... SELECT competes with merges and live queries for CPU and I/O.

Verifying the view is doing its job

Three quick checks:

• SELECT * FROM system.tables WHERE name = 'events_hourly_mv' — confirm the view exists and inspect its definition.
• Insert a known batch into events, then query events_hourly for those rows — confirms the trigger path works end to end.
• Compare query plans: EXPLAIN FORMAT = TREE on your dashboard query should show a read from events_hourly, not events. ClickHouse does not automatically rewrite queries to use materialized views — you must point queries at the rollup table yourself.

That last point surprises people most. The view maintains the rollup; routing queries to it is your job, whether in application code, a BI tool's dataset definition, or a proxy layer.

The actionable takeaway: if a ClickHouse query is slow because it re-aggregates the same raw data repeatedly, don't tune the query — change when the aggregation happens. One materialized view over a well-keyed SummingMergeTree or AggregatingMergeTree target table turns seconds into milliseconds, at the cost of modest insert overhead and one careful backfill.