Reducing Telemetry Query Latency with ClickHouse Materialized Views

The Cost of Real-Time Aggregation

When managing high-volume telemetry—such as millions of events per second from IoT devices or application logs—running COUNT DISTINCT or SUM across billions of rows in real-time is prohibitively expensive. Even with ClickHouse's columnar storage, calculating a 30-day trend on the fly consumes massive CPU and memory, often leading to query timeouts as your dataset grows.

The solution is to shift the computational burden from read-time to write-time. In ClickHouse, this is achieved using Materialized Views (MVs). Unlike standard SQL views, which are essentially saved queries, a ClickHouse Materialized View acts as an insertion trigger that transforms and aggregates data blocks before they are committed to disk.

How Materialized Views Actually Work

A Materialized View in ClickHouse does not store data itself; instead, it pushes processed data into a target table. When a block of data is inserted into the source table, the MV intercepts that block, applies a SELECT statement to it, and inserts the result into the destination table.

To make this efficient for telemetry, you typically pair the MV with a specialized engine in the target table:

• SummingMergeTree: Automatically sums numeric columns with the same primary key during background merges.
• AggregatingMergeTree: Stores intermediate states of complex functions (like unique counts or quantiles) using the AggregateFunction data type, allowing them to be merged later.

Implementation: Real-Time Event Counting

Consider a scenario where you need to track the total number of requests and unique users per minute across multiple services. Instead of querying the raw logs, you can maintain a pre-aggregated table.

1. The Raw Source Table

Run this on your ClickHouse cluster as a user with CREATE TABLE permissions. This table stores the granular events for short-term debugging.

CREATE TABLE raw_events (
    event_time DateTime,
    service_id String,
    user_id UInt64,
    request_id String
) ENGINE = MergeTree()
ORDER BY (service_id, event_time);

2. The Aggregation Target Table

This table uses SummingMergeTree to keep the storage footprint small. Note that the primary key defines the granularity of your aggregation.

CREATE TABLE events_hourly_agg (
    event_hour DateTime,
    service_id String,
    total_requests UInt64
) ENGINE = SummingMergeTree()
ORDER BY (service_id, event_hour);

3. The Materialized View

The TO keyword explicitly links the view to the target table. This ensures that if you drop the view, the aggregated data remains in the target table.

CREATE MATERIALIZED VIEW events_hourly_mv 
TO events_hourly_agg 
AS SELECT 
    toStartOfHour(event_time) AS event_hour,
    service_id,
    count() AS total_requests
FROM raw_events
GROUP BY event_hour, service_id;

Verification

To verify the setup, insert a batch of data into raw_events. Then, query events_hourly_agg. You should see the total_requests incremented without having to scan the raw table. Risk: If you define the MV after data already exists in the source table, that existing data will not be processed. You must manually insert old data into the target table using INSERT INTO ... SELECT.

Trade-offs and Constraints

While MVs drastically speed up reads, they introduce specific engineering constraints:

• Write Latency: Because the MV trigger is synchronous, adding dozens of MVs to a single source table can increase the time it takes for an INSERT to complete.
• No Retroactive Processing: As mentioned, MVs only see data that arrives after the view is created.
• Eventual Consistency: SummingMergeTree and AggregatingMergeTree merge data in the background. To get a 100% accurate current total, you must still use SUM(total_requests) in your SELECT query, as some rows with the same key may not have merged yet.

Decision Summary

Use Materialized Views when your query patterns involve repetitive aggregations over large time windows. If you only need raw data for sporadic debugging, stick to the MergeTree. If you need a real-time dashboard that updates instantly without scanning billions of rows, the Source Table → Materialized View → Summing/AggregatingMergeTree pattern is the standard architectural choice for ClickHouse.