AggregatingMergeTree State Columns for Incremental Aggregation in Materialized Views

AggregatingMergeTree state columns make incremental aggregation in materialized views possible by storing work that can be combined later. The key is the *State/*Merge contract, which separates writing partial computations from reading final answers. That boundary ties together the lifecycle, schema, retention, and recovery decisions that let small rollup tables answer questions over growing event histories.

What AggregatingMergeTree actually stores and why it differs from a normal MergeTree

What looks like a binary blob in an AggregatingMergeTree column is conceptually a resumable computation. A normal MergeTree stores rows verbatim, leaving aggregation to the query. AggregatingMergeTree instead stores the intermediate state of an aggregate function after it has processed some input. Later work can combine those states.

The column type says what the computation must keep: AggregateFunction holds the richer state that functions such as avg and uniq need because their partial answers cannot safely become a single final value, while SimpleAggregateFunction works for functions such as sum and max because the accumulated scalar has enough information to be combined further.

The two-phase write/merge/query lifecycle end to end

Consider a narrow rollup that keeps a sum and a distinct count for each dimension and time bucket. Its source table uses MergeTree and holds the raw inputs. The target declaration uses ENGINE = AggregatingMergeTree, with the dimension and timestamp bucket in ORDER BY. For this state-based example, its aggregate columns have types AggregateFunction(sum, UInt64) and AggregateFunction(uniq, UInt64). The materialized view’s SELECT groups by those same dimensions and produces sumState and uniqState for the target columns.

When an insert block reaches the source, the view runs against that block alone. It does not recompute the sum or distinct count over the whole source table. Instead, it inserts partial states that hold what the new block adds. So several blocks can leave several rows with the same rollup key. This is normal intermediate storage.

Background merges then combine states in rows with matching ORDER BY keys. The aggregate function supplies the combiner logic, so a distinct-count state is not treated as a number to add. At query time, sumMerge and uniqMerge, with GROUP BY on the rollup keys, combine and finalize whatever states remain. The query therefore completes work that background merging has not yet consolidated. This explains the performance payoff in the notes, where a distinct count over billions of raw rows takes seconds while uniqMerge over precomputed states takes milliseconds.

Schema design: how ORDER BY, column types, and the MV SELECT must align

The lifecycle gives the schema a clear contract. The materialized view’s grouping and the target’s ORDER BY must describe the same rollup grain. Two rows with the same sorting key can be combined, while rows with different keys stay separate. A key that keeps unnecessary distinctions defeats consolidation and lets redundant rows accumulate. The target should therefore be built for the questions the rollup answers.

A short schema checklist starts by asking which dimensions define one answer. It then asks whether the view groups by exactly those dimensions, whether each column preserves the information its aggregate needs, and whether the view reads only the arriving block. In the key order, columns that are often used in filters come first, with low-cardinality dimensions before high-cardinality ones. The time-series pattern (low_cardinality_dim, timestamp_bucket) places the time bucket where range scans can use the sort order. These choices make the key both a merge boundary and a path for useful reads.

Column selection follows the same information test. SimpleAggregateFunction(sum, UInt64) is the compact choice when a sum needs only its accumulated scalar, while AggregateFunction(uniq, UInt64) retains the state needed for a distinct count. Functions such as avg and quantile also need the fuller state. A richer type where a scalar suffices wastes storage, but a scalar where a richer state is required loses correctness. This incremental view contract rules out joins against the source, subqueries over its full history, and expressions that need state from outside the arriving batch.

Multi-tier rollup chaining: passing states across retention levels

The same design extends from one rollup to a chain such as 5-minute → 1-hour → 1-day. A downstream view receives states. It must combine those states without turning them into final answers. Finalizing at an intermediate tier discards information that downstream aggregation may still need. The chain therefore has a different combinator at its internal boundaries.

The raw-to-5-minute view uses sumState and uniqState. The 5-minute-to-1-hour view uses sumMergeState and uniqMergeState, and the 1-hour-to-1-day view uses the same combinators to keep the states intact. Each target remains an AggregatingMergeTree, with its own ORDER BY built around the coarser time bucket. Only the final query uses sumMerge and uniqMerge to produce scalar answers.

Retention can then follow the useful resolution of the data. Raw events can remain for days to weeks, minute-level rollups for months, and hour/day-level rollups for years. TTL expressions on each tier set how long that tier keeps its data. The practical benefit is not just smaller storage, but a durable aggregate history that does not require retaining every raw event at full detail.

The correctness hazards that the two-phase contract cannot prevent on its own

Background merges are asynchronous, so physical consolidation is not a condition a query can assume. Multiple unmerged parts may contain contributions to the same logical group. Query-time GROUP BY with *Merge is therefore required. Treating each row as a complete answer can give results that look right but leave out data.

That requirement should not be confused with a guarantee that an automated decision sees every relevant recent event. The pattern alone does not establish the freshness and isolation contract needed for fraud checks or credit enforcement. Eventual consistency may be acceptable for dashboards and analytical products, but partially stale reads leave a structural gap for enforcement. This is a limit of asynchronous architectures.

By default, a failure in the materialized view’s SELECT or target insert causes the source insert to fail. Setting materialized_views_ignore_errors=1 lets the source write succeed, but a failed view insert is skipped without reporting that failure to the caller. Those contributions are then missing from the rollup. The first sign may be a count below what was expected, even though the source accepted the events.

Recovery and schema changes: what happens to AggregatingMergeTree rollups when something goes wrong

The arriving-block model also explains why changing a materialized view does not rewrite history. A new definition affects new processing, while historical data needs a manual backfill into the target. The backfill strategy should therefore be part of the design from the start, before a schema change makes it urgent. It must build states in the same way as the view.

The preferred approach in the notes is to create the view without POPULATE, then insert historical data into the target from the source in time-ordered batches. Those batches use the same *State expressions as the live view. This keeps the source writable while history is populated. By contrast, the POPULATE path described in the notes blocks source writes during population, making it a poor fit for a high-throughput event stream. The tradeoff is between a planned backfill and a population step that interrupts ingestion.

An incremental backup chain can restore data at daily granularity and takes hours, while partition-level restore can take minutes when partition boundaries line up with the backup point. Frozen part copies can also restore data in minutes at part-level granularity, but they are much harder to manage. A deliberately lagged replica can provide real-time granularity and recovery in minutes if it has not yet applied the problematic operation. All of these paths leave a gap between the source and the rollup during recovery, so some data may still need to be replayed from the source.

How agentic workloads interact with AggregatingMergeTree rollups differently from human queries

Agentic workloads change the number of reads that the design must absorb. A single agent conversation can issue many exploratory queries, and concurrent agents can produce query rates orders of magnitude above human dashboard traffic. Sending those reads to billions-row event tables repeats expensive scans until I/O becomes the constraint. The resulting latency breaks the fast feedback loop on which the agent depends. Pre-aggregation is therefore an architectural requirement for this workload.

AggregatingMergeTree changes that cost by doing the heavy computation incrementally on the write path. Later reads reuse that work and finalize small states. The benefit follows from the size of the data being read. A human dashboard and an agent can use the same rollup. The agent’s concurrency simply makes the value of amortizing computation much more pronounced.

That concurrency also makes access controls and traceability part of the design. Every agent query should be logged with its full text and parameters, with enough context to connect the access decision to its originating conversation. Debugging alone is not enough when many automated reads need to be explained later. Isolated, read-only compute gives agents query capacity without making exploratory traffic compete with production writes and merges.

Operational decisions that determine whether the pattern holds at production scale

The first operational fork is combinator discipline, with the outcomes of composable states or premature finalization. Keeping the *State/*Merge boundary in view definitions, column types, and reads makes each stage’s job clear. Queries that omit GROUP BY against an AggregatingMergeTree deserve an explicit audit. This is especially important once several consumers or retention tiers depend on the same target. A boundary that exists only in the original view definition does not govern the whole pipeline.

The second fork is key stability, with the outcomes of a deliberate rollup grain or a rebuild and history replay. Treating ORDER BY as immutable after the first insert makes that cost visible before deployment. The third fork is error posture, with the outcomes of safe, noisy failures or resilient writes with silent rollup loss. Making source inserts fail when the view has an error keeps accepted input tied to what reaches the rollup. Enabling materialized_views_ignore_errors=1 keeps source writes going instead, and that choice must fit how the rollup is used.

The fourth fork is whether the backfill is ready, so changes can follow a plan, or must be rushed through a population step that blocks writes. Documenting and rehearsing the procedure without POPULATE makes schema change a supported operation. Recovery should be planned along with that procedure because restored targets may also need data replayed from the source. Planning must cover whether the target can be restored and how its missing interval will be filled.

The fifth fork is read isolation and logging, with the outcomes of traceable, isolated query capacity or opaque competition with merge resources. High-concurrency automated reads should use isolated compute that shares data without competing with background merges, and every programmatic query should retain its origin context. These decisions make the two-phase contract an operating practice. That contract lets a single small table answer questions that would otherwise require full scans as the raw history grows.