Aggregation States: Boosting Materialized View Performance by 10x in ClickHouse

ClickHouse has long been celebrated for its lightning-fast analytical query performance, especially when handling massive datasets in real time. One of the most powerful features enabling this performance is the materialized view—a precomputed structure that transforms and aggregates data as it’s ingested. However, traditional materialized views can suffer from data explosion, high storage costs, and performance degradation. The solution? Aggregation states.

By leveraging ClickHouse’s AggregateFunction data types, engineers can store intermediate aggregation states instead of final scalar values. This approach dramatically reduces storage footprint, minimizes I/O, and accelerates query performance—often by up to 10x.

Why Aggregation States Matter

Standard materialized views in ClickHouse compute and store final values (e.g., sum, count, average) for each group. While effective for simple metrics, this approach becomes unwieldy when dealing with high-cardinality dimensions or complex aggregations like quantiles, unique user counts (uniq), or top-k values. Each new combination generates a new row, leading to data explosion and bloated storage.

Aggregation states solve this by storing serialized intermediate representations of ongoing computations. Instead of storing the final count, ClickHouse stores the state of the uniqCombined or quantileTDigest function, which can later be merged with other states or finalized into a result.

For example:

• Instead of storing count = 1000 per group, ClickHouse stores the state of uniqCombinedState(user_id).
• These states can be combined across partitions or time windows, enabling efficient rollups.

Materialized views in ClickHouse are updated in real time as data flows into the base table, functioning more like continuously updating indexes ². By storing aggregation states, these views avoid recomputing everything from raw data and instead merge compact state objects incrementally.

Use Cases and Performance Gains

Storing aggregation states is particularly effective for:

• Time-series rollups: Pre-aggregating metrics over time while preserving the ability to compute percentiles.
• High-cardinality unique counting: Tracking distinct users without exploding group-by combinations.
• Chained materialized views: One view stores raw events, while another stores aggregation states for downstream processing ³.

This method significantly reduces storage requirements. In one documented case, a materialized view that expanded 20GB of raw data into 190GB of precomputed results was optimized using state-based aggregation, cutting both storage and query latency ¹. The result? Faster inserts, smaller tables, and sub-second analytical queries even on petabyte-scale datasets.

Querying Final Results

To retrieve final values from aggregation states, use the -Merge or -MergeState suffixes:

SELECT quantileTDigestMerge(0.95)(value_state) 
FROM daily_quantiles 
WHERE date = '2025-11-20'

Or finalize unique counts:

SELECT uniqCombinedMerge(user_state) 
FROM user_aggregates

These operations efficiently combine all partial states and produce accurate results—without scanning raw data.

Best Practices

• Use AggregateFunction types (e.g., uniqCombinedState, sumMapState) in materialized view destinations.
• Merge states periodically to avoid holding millions of them in memory 4.
• Monitor insert performance using system.query_log to detect CPU spikes or timeouts 1.
• Combine with TTLs and partitioning to manage lifecycle and performance.

Conclusion

ClickHouse’s support for aggregation states transforms materialized views from static summaries into dynamic, space-efficient, and scalable analytics engines. By storing computation states instead of final values, organizations can achieve up to 10x performance gains, reduce storage costs, and maintain high query responsiveness. As data volumes continue to grow, leveraging state-based aggregation isn’t just an optimization—it’s a necessity for real-time analytics at scale.

You might also like:

• How do Data Scientists use Black-Scholes model on ClickHouse?
• ClickHouse Permutation by Recursion and Cross Join
• Setup ClickHouse Cluster Replication with Zookeeper
• Mastering Nested JOINs in ClickHouse: A Complete Guide to Embedding JOINs within JOINs
• Optimizing ClickHouse Performance: Indexing, Query Execution, and Data Organization