Supercharging Dashboard Queries with ClickHouse Materialized Views

Shifting heavy analytical aggregations from query time to ingestion time turned our sluggish multi-second dashboard loads into five-millisecond lookups.

VW
Viery Wahyu•Author
•9 min read

Raw columnar scan speed can make engineering teams lazy during the first six months of a ClickHouse deployment. When your primary event table only holds fifty million rows, running a complex GROUP BY aggregation across thirty days of customer transactions or market tick telemetry finishes in two hundred milliseconds without breaking a sweat. The architectural reckoning arrives when that same table grows past two billion rows and five hundred concurrent merchants or traders open their live analytics dashboards at the exact same second. Even if a single full-partition scan only takes half a second of CPU time, multiplying that scan across hundreds of simultaneous dashboard refreshes forces ClickHouse to read and decompress the exact same immutable historical blocks thousands of times a minute. When our cluster CPU utilization pinned at ninety-five percent during morning traffic spikes, we stopped scanning raw events on the read path and redesigned our OLAP aggregation strategy around ClickHouse Materialized Views.

Why ClickHouse Materialized Views Are Actually Insert Triggers

If you come to ClickHouse from a traditional PostgreSQL background, the phrase materialized view carries a very specific mental model. In PostgreSQL, a materialized view is a static cached table created from a slow query that immediately goes stale until a background cron job runs REFRESH MATERIALIZED VIEW, re-executing the entire expensive query over the whole dataset from scratch. If you try to apply that mental model to ClickHouse, nothing in the documentation will make sense.

In ClickHouse, a Materialized View does not periodically scan the underlying source table at all. Instead, it operates as a real-time, in-memory insert trigger attached directly to the ingestion pipeline. Whenever a batch of fifty thousand new events is inserted into your raw source table, ClickHouse intercepts that exact block of incoming rows in memory, runs your transformation SELECT query exclusively over that tiny new batch, and writes the pre-aggregated summary rows straight into a target storage table. Historical rows sitting on disk are never touched again, which means your ingestion overhead remains tiny and predictable whether your raw table holds ten megabytes or fifty terabytes of data.

Decoupling Target Storage Tables From the Transformation View

The first rule we enforced when rolling out Materialized Views across our production telemetry and financial engines was banning implicit inner storage tables. Most introductory tutorials show a shorthand syntax where you define CREATE MATERIALIZED VIEW ... ENGINE = SummingMergeTree() in a single statement, which causes ClickHouse to generate a hidden table named .inner_id.uuid under the hood. While convenient for quick experiments, hidden inner tables are a nightmare during schema migrations because dropping the view to tweak a SELECT expression immediately destroys all your accumulated historical aggregations along with it.

A resilient production architecture always separates the physical storage container from the streaming transformation pipeline using the TO keyword. First, you create a normal, explicitly named destination table using a specialized rollup engine like AggregatingMergeTree. Second, you attach a stateless Materialized View that routes transformed insert blocks from the raw source stream into that destination table.

sql
1-- 1. The raw high-volume ingestion table receiving millions of events
2CREATE TABLE analytics.raw_market_executions
3(
4 executed_at DateTime64(3, 'UTC'),
5 symbol LowCardinality(String),
6 venue LowCardinality(String),
7 account_id String,
8 notional_usd Float64,
9 slippage_bps Float64
10)
11ENGINE = MergeTree()
12PARTITION BY toYYYYMM(executed_at)
13ORDER BY (symbol, venue, executed_at);
14
15-- 2. The explicit target table storing pre-aggregated hourly states
16CREATE TABLE analytics.hourly_market_rollup
17(
18 hour_bucket DateTime('UTC'),
19 symbol LowCardinality(String),
20 venue LowCardinality(String),
21 total_volume_usd SimpleAggregateFunction(sum, Float64),
22 execution_count SimpleAggregateFunction(sum, UInt64),
23 unique_traders_state AggregateFunction(uniq, String),
24 p99_slippage_state AggregateFunction(quantileTDigest(0.99), Float64)
25)
26ENGINE = AggregatingMergeTree()
27PARTITION BY toYYYYMM(hour_bucket)
28ORDER BY (symbol, venue, hour_bucket);

Separating hourly_market_rollup as an independent table gives us surgical operational freedom. If our product team decides next month to change how a filter condition inside the transformation query works, we can drop the stateless Materialized View, recreate it with the updated SELECT logic, and attach it right back to hourly_market_rollup in fifty milliseconds without losing a single byte of historical rollup data.

Mastering State and Merge Combinators for Complex Metrics

Look closely at the column types inside hourly_market_rollup above, because they solve the hardest mathematical trap in OLAP pre-aggregation. Anyone can pre-aggregate simple sums or row counts using SummingMergeTree or SimpleAggregateFunction(sum, Float64), because addition is associative. If batch one has a volume of one hundred dollars and batch two has a volume of two hundred dollars, adding them together during a background MergeTree compaction yields the exact three-hundred-dollar total.

That simple math breaks completely when your dashboard needs to display unique user counts (COUNT DISTINCT) or ninety-ninth percentile latency and slippage metrics across arbitrary time ranges. You cannot calculate the true unique trader count for an entire day by summing the integer counts of twenty-four separate hours, because the same trader executing orders in both hour one and hour two would be counted twice. Similarly, averaging two pre-calculated P99 numbers produces mathematically invalid garbage.

ClickHouse solves non-additive aggregations through binary state serialization powered by -State and -Merge function combinators. Instead of storing a final scalar integer or float inside the target rollup table, AggregateFunction(uniq, String) and AggregateFunction(quantileTDigest(0.99), Float64) store the intermediate binary HyperLogLog sketch and T-Digest histogram centroid structure directly on disk. When our Materialized View processes an incoming batch of raw executions, it appends the -State suffix to the aggregation functions to emit those mergeable binary states.

sql
1-- 3. The stateless Materialized View acting as a real-time insert trigger
2CREATE MATERIALIZED VIEW analytics.hourly_market_rollup_mv
3TO analytics.hourly_market_rollup
4AS SELECT
5 toStartOfHour(executed_at) AS hour_bucket,
6 symbol,
7 venue,
8 sum(notional_usd) AS total_volume_usd,
9 count() AS execution_count,
10 uniqState(account_id) AS unique_traders_state,
11 quantileTDigestState(0.99)(slippage_bps) AS p99_slippage_state
12FROM analytics.raw_market_executions
13GROUP BY
14 hour_bucket,
15 symbol,
16 venue;

Whenever Vector or our Kafka consumers flush a chunk of twenty thousand raw trade executions into raw_market_executions, hourly_market_rollup_mv compresses those twenty thousand rows down into a few dozen hourly summary rows per symbol and writes the intermediate sketches into hourly_market_rollup. Behind the scenes, as ClickHouse merges data parts on disk inside AggregatingMergeTree, it automatically combines overlapping hour_bucket sketches for the same symbol and venue into a single consolidated row without losing statistical accuracy.

Querying Rollup States With Sub-Ten-Millisecond Speed

Once the binary states are accumulating inside the target table, your customer-facing dashboard API no longer touches raw_market_executions at all. However, querying an AggregatingMergeTree table requires respecting two crucial rules. First, because ClickHouse merges background data parts asynchronously, there may still be two or three unmerged parts on disk for the current active hour at the exact millisecond a user runs a query. Therefore, your read query must still wrap the columns in a final GROUP BY clause rather than doing a raw SELECT *. Second, to unpack the binary AggregateFunction sketches into human-readable numbers, you pair each state column with its corresponding -Merge combinator.

sql
1-- Fast dashboard query unpacking binary states across a 30-day window
2SELECT
3 symbol,
4 venue,
5 sum(total_volume_usd) AS gross_volume_usd,
6 sum(execution_count) AS total_trades,
7 uniqMerge(unique_traders_state) AS distinct_traders,
8 round(quantileTDigestMerge(0.99)(p99_slippage_state), 2) AS p99_slippage_bps
9FROM analytics.hourly_market_rollup
10WHERE symbol = 'HYPE-USD'
11 AND hour_bucket >= now() - INTERVAL 30 DAY
12GROUP BY
13 symbol,
14 venue
15ORDER BY gross_volume_usd DESC;

Notice the elegance of uniqMerge and quantileTDigestMerge in that dashboard query. Even though the underlying hourly_market_rollup table stores rows bucketed by hour, our query groups exclusivamente by symbol and venue over a thirty-day window. ClickHouse reads the seven hundred and twenty hourly HyperLogLog sketches and T-Digest histograms for that month, merges their binary structures in memory in less than four milliseconds, and outputs the exact thirty-day unique trader count and ninety-ninth percentile slippage without ever touching the hundreds of millions of raw execution rows.

Navigating Backfills and Memory Limits in Production

Operating ClickHouse Materialized Views at scale did teach us a few operational lessons around historical backfills and ingestion memory. When you create a brand-new Materialized View on top of an existing multi-billion-row source table, the view only triggers on new inserts arriving from that moment forward. If you try to backfill three years of historical data by running a single giant INSERT INTO analytics.hourly_market_rollup SELECT ... FROM analytics.raw_market_executions, ClickHouse will attempt to hold millions of aggregation states in RAM simultaneously and throw an out-of-memory exception. The clean way to backfill is partitioning your historical INSERT ... SELECT script by month or week while temporarily pausing live ingestion—or filtering the backfill strictly below a fixed cutoff timestamp WHERE executed_at < '2026-11-01 00:00:00'—so historical batches and live streaming triggers never overlap.

You also want to resist the temptation of attaching fifteen different Materialized Views to a single high-throughput source table. Because ClickHouse executes attached Materialized Views synchronously during the INSERT pipeline, chaining too many complex views onto one table will gradually increase write latency and memory pressure on your ingestion nodes. Consolidating related metrics into two or three wide AggregatingMergeTree rollup tables keeps your write path fast while still serving every chart on your frontend dashboard in single-digit milliseconds.

Shifting Compute to Where It Costs the Least

Supercharging our dashboard queries with ClickHouse Materialized Views fundamentally changed the economics of our real-time analytics stack. Scanning raw columnar data on demand is brilliant for ad-hoc engineering investigations, but using brute-force scans to power customer dashboards that ask the exact same aggregate questions thousands of times a day is architectural waste. By decoupling explicit AggregatingMergeTree target tables from stateless view triggers and harnessing -State and -Merge combinators for complex percentiles and cardinality sketches, our dashboard API responses dropped from over two seconds to under five milliseconds while cutting our cluster CPU load by more than seventy percent.

VW

Written by

Viery Wahyu

📍 Bali, Indonesia

Software Engineer

Software engineer based in Bali with over a decade of experience building fast web applications

The Journal

View all →