Using ClickHouse Materialized Views for Incremental Pre‑aggregation

The Cost of Scanning Billions of Rows

When your ClickHouse cluster grows to billions of rows, querying raw event data for a dashboard—such as "Total Requests per Hour"—becomes prohibitively expensive. Even with ClickHouse's columnar storage, scanning massive datasets for every page refresh wastes CPU and increases latency. The solution is to move the computation from the read phase to the write phase using Materialized Views (MVs).

How Incremental Maintenance Works

In ClickHouse, a Materialized View is not a virtual query or a cached result set; it is an insert trigger. When data is inserted into a source table, the MV intercepts that specific block of data, transforms it according to a query, and pushes the result into a target table. This ensures your dashboards query a pre‑aggregated summary table containing thousands of rows rather than a raw table containing billions.

Unlike traditional relational databases that might refresh a view on a schedule or during a read, ClickHouse MVs operate incrementally. They only process the data currently being inserted. If you insert 1,000 rows into the source table, the MV processes only those 1,000 rows and updates the target table accordingly.

To implement this effectively, you typically use the SummingMergeTree or AggregatingMergeTree engines for the target table. These engines automatically merge rows with the same primary key in the background, summing up values or updating aggregates, which prevents the summary table from growing as fast as the raw data.

Worked Example: Daily Request Aggregation

Imagine a raw table http_events tracking every single request to a web server. We want a real‑time count of requests per day per endpoint.

Step 1: Create the target table

CREATE TABLE daily_requests_summary (
    event_date Date,
    endpoint String,
    request_count UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (event_date, endpoint);

Step 2: Create the Materialized View

CREATE MATERIALIZED VIEW requests_mv
TO daily_requests_summary
AS SELECT
    toDate(timestamp) AS event_date,
    endpoint,
    count() AS request_count
FROM http_events
GROUP BY event_date, endpoint;

Verification: Insert a test record into http_events. Then query the summary table: SELECT * FROM daily_requests_summary. You should see the aggregated count immediately without having to run a COUNT() over the raw http_events table.

Engineering Trade‑offs and Limitations

• Write amplification: Every INSERT now triggers a second write to the MV target table. In extremely write‑heavy environments, this can increase disk I/O and CPU usage during ingestion.
• No retroactive data: A Materialized View only processes data inserted after the view was created. It does not automatically aggregate existing data in the source table. To populate the MV with historical data, you must manually run an INSERT INTO target_table SELECT ... FROM source_table.
• Schema rigidity: MVs are confined to the same database and cannot perform complex JOIN operations against other tables during the aggregation process. They are designed for simple transformations and aggregations.

Verifying Performance Gains

To confirm that your MV is providing the expected benefit, use the EXPLAIN statement. Compare a query against the raw table versus the summary table:

-- Querying raw data (slow)
EXPLAIN SELECT count() FROM http_events WHERE toDate(timestamp) = '2023-01-01';

-- Querying the MV target (fast)
EXPLAIN SELECT sum(request_count) FROM daily_requests_summary WHERE event_date = '2023-01-01';

The EXPLAIN output for the summary table will show significantly fewer rows scanned, confirming that the pre‑aggregation is working as intended.