AggregatingMergeTree Use Cases and Pre-Aggregation Trade-Offs
Fast queries require choosing dimensions at write time, locking in analytical flexibility.
AggregatingMergeTree use cases and pre-aggregation trade-offs turn on one choice. The engine does repeated aggregation work in storage so fixed queries run faster, but the pipeline is then tied to choices made at write time. It is an architectural commitment. Whether that commitment pays off depends on how much data the rollup compresses, how stable the analytical requirements are, and how well the team can operate it.
What AggregatingMergeTree actually does at the storage level
AggregatingMergeTree stores binary intermediate aggregation states. A column can have the type AggregateFunction(sum, UInt64), which describes a state that can be combined with compatible states. It is not an ordinary numeric column.
Raw events first enter a MergeTree source table. A materialized view intercepts each INSERT, applies -State functions, and writes the resulting states into an AggregatingMergeTree summary table. Background merges then combine compatible states for the same key. Those merges may still be running when a query starts. Because several parts can still contain partial states for one key, queries need a GROUP BY and matching -Merge functions to combine them and produce answers.
The -State/-Merge combinator pair and why getting it wrong produces silent garbage
The write and read functions form a contract. sumState() pairs with sumMerge(), while uniqState() pairs with uniqMerge(). The materialized view produces the intermediate representation, and the query applies the function that knows how to combine and finish it. Treating the stored column as a final value breaks that contract, even when the SQL looks like a familiar analytical query.
For example, a view that writes sumState(views) into total_views calls for sumMerge(total_views) at query time, grouped by the required dimensions. Ordinary sum(total_views) is not an interchangeable expression. An answer that looks usable but is wrong is dangerous because it lets the consumer carry on, while an obvious failure would stop them.
This discipline buys support for more than sums. AggregatingMergeTree can maintain states for uniq, quantile, and avg, which simpler additive rollups cannot handle in the same way. That range explains why the contract exists, but it also raises a practical question for the rest of the design. Support for these functions must be worth the work of teaching everyone who writes queries how to read the table.
The workload shapes where pre-aggregation delivers outsized gains
A page-view rollup with an ORDER BY key of (event_date, page) combines many individual visit rows into states for each date and page. Repeated queries then work over those rollups, avoiding repeated scans of the visits. A small set of dimensions must serve frequent questions while collapsing a meaningful number of raw rows.
The same pattern fits daily product revenue over millions of transactions, where the same GROUP BY runs thousands of times a day. An online marketplace can use (event_date, product_id) to keep the key space small relative to its transaction volume. Financial summaries and KPI dashboards also fit when metric definitions are fixed and updated continuously as events arrive.
High-ingest IoT streams offer another reason to aggregate when raw retention is expensive and dashboards need only summarized readings. Yet high volume alone proves very little. If almost every key combination is unique, the engine has to store a state for nearly every input row. The useful test is therefore not how many events arrive, but how many of them each rollup key can absorb.
The real costs: schema rigidity, query complexity, and ingest overhead
The materialized view’s GROUP BY fixes the dimensions available in the summary at write time. For the page-view example, an omitted user segment cannot later become a filter on the stored rollup. Adding that dimension or changing the time grain requires rebuilding the materialized view and backfilling historical state. At petabyte scale, that work can be prohibitively expensive.
The strategic cost is easy to underestimate because a new filter can sound like a small product change. In this design, it can mean reconstructing the pipeline. A fast answer to a settled question is valuable, but it does not preserve the freedom to ask every new question of the summary. Choosing the schema shapes what the analytical product can do as well as how its data is stored.
The query and ingest costs follow from the same mechanism. Application code, BI connectors, and automated query authors must all apply the combinator contract and query-time grouping. Using FINAL to avoid the GROUP BY forces synchronous merging at read time and is significantly slower. Meanwhile, ingestion can outrun background merges and leave parts accumulating. Cloudflare’s billing pipeline encountered this after a partitioning change, with part accumulation causing lock contention during query planning and stressing ZooKeeper, the coordination layer.
When to use a simpler engine instead
For purely additive metrics, SummingMergeTree offers a simpler fit. It provides background merging for sum-type rollups without binary state columns or the -State/-Merge discipline. Choosing it avoids paying for aggregate-state machinery when the workload needs only sums.
When the questions themselves keep changing, raw MergeTree with well-designed indexes and projections preserves arbitrary filters and exploratory grouping at the cost of query-time CPU. That can be the right bargain for early-stage products or internal tooling. AggregatingMergeTree is also a poor fit when key cardinality is unbounded or combinations are mostly unique, because serialization and merging add work without reducing rows. The decision depends on the flexibility and capabilities the workload actually needs.
Operational failure modes and how to monitor them in production
The most dangerous failures can remain quiet. Inserts keep returning OK, and queries keep returning answers, even as the work behind them falls behind. A merge storm develops when parts arrive faster than the merge pool can clear them. Once part counts pass a healthy threshold, query planning degrades and the system can enter a reinforcing spiral.
Replica drift creates a related gap between responsiveness and freshness. A lagging replica can still answer while its aggregate states fall behind. TTL cleanup can also silently stop aging data out as intended during merge storms, leaving disk usage climbing. These are not failures that a successful query alone will expose.
Parts per table should stay low, with alerts above a configurable threshold, and replication lag should remain minimal. Disk growth needs attention in the context of age-based data cleanup, while storage_policy separates hot and cold tiers as states age.
ReplicatedAggregatingMergeTree for production high-availability
ReplicatedAggregatingMergeTree adds availability to the aggregate-state design. ReplicatedAggregatingMergeTree automatically replicates both data and merge operations across the configured replicas so summary tables stay available when a replica goes offline. This is especially relevant to application observability metrics, where dashboards consume aggregated readings from high-ingest streams. Replication helps when a replica is lost, but the monitoring described above still needs to show when replicas lag or background work stalls.
Recovery objectives need a separate, explicit decision. Before deployment, teams should define RTO and RPO and test cross-region replication and point-in-time recovery plans. Binary aggregate-state columns require handling that differs from ordinary row data. An availability design is incomplete if its recovery plan treats those states as though they were ordinary exported values.
AggregatingMergeTree in agentic analytics pipelines and why the stakes are higher
AI agents make the query contract more consequential. An agent using an MCP server can generate SQL from schema descriptions and natural-language intent, defaulting to familiar expressions such as sum(total_views) where sumMerge(total_views) is needed. Without explicit guidance, it may produce a plausible query and silently wrong answers at scale. A conversational response gives the reader little reason to inspect the aggregation method underneath it.
Funnels, retention, cohorts, and journeys leave room for methodology errors that are difficult to spot in a polished reply. If an agent needs a dimension absent from the rollup, it must scan raw tables or risk returning a wrong result. The materialized view thus defines part of what the agent can responsibly answer and how quickly it can answer.
At the same time, agents strengthen the case for pre-computation. Multi-second cold queries do not suit conversational or autonomous probing, while repeated access to the same rollup dimensions can make millisecond-scale responses achievable. For teams building these workloads, isolated read-only compute with full query logging is the minimum accountability layer. It provides a way to catch combinator misuse and wrong-dimension queries before their results propagate into downstream decisions.
How pre-aggregation interacts with storage architecture and query routing
Pre-computation cannot remove every source of query latency. Its benefit depends on aggregate states being available in fast local storage when the query runs. Routing -Merge queries through S3 reintroduces object-storage round trips, even though the aggregation work has already been reduced.
For this architecture, S3 serves long-term durability while NVMe or an in-memory cache holds hot aggregate states. Queries against recently merged rollup parts can then avoid object-storage round trips. Binary aggregate-state columns have no standard serialization format outside the engine’s own merge machinery, so moving the pipeline takes more than moving the schema and raw data. The states cannot simply be treated as portable values for export and re-import.
The shift to ingest-time work also changes the cost model. -State computation and background merges consume sustained compute. For high-ingest pipelines, compute-hour pricing aligns with that continuous workload more predictably than per-query pricing. Evaluating the engine on query speed alone would miss both where that work runs and how it is charged.
Decision framework: committing to AggregatingMergeTree vs. deferring it
Adoption should turn on workload shape, schema stability, and operational readiness. A strong candidate has fixed dimensions, repeated high-frequency grouping over data too large to scan within acceptable latency, and substantial row reduction per key. Functions such as uniq, quantile, and avg strengthen the case when simple additive rollups are insufficient. The team must also be ready to enforce the read contract across human and automated consumers.
Deferring is sensible when dimensions or time grains are still changing, key cardinality is uncertain, or mostly unique keys erase the compression benefit. Missing monitoring for merge storms, accumulating parts, and replica lag is another reason to wait. An undefined migration plan for binary states leaves a further commitment unresolved.
Teams should validate the materialized view’s GROUP BY against foreseeable analytical requirements and version it with schema-as-code tooling so changes are explicit and auditable. That work ties storage choices to the questions applications and agents are allowed to answer. The rollup schema should be treated as a product contract, with the same care given to its definition, operation, and revision.
