SummingMergeTree and AggregatingMergeTree: Pre-Aggregation Trade-offs

The trade-off

Every query that answers "total revenue by region" or "distinct visitors per day" is doing an aggregation — collapsing many rows down to few. That collapsing can happen in exactly one of two places: at query time, scanning and reducing raw rows fresh on every read, or at merge time, letting ClickHouse's background merge process do some of that reduction ahead of time, so a query has fewer rows left to finish reducing. SummingMergeTree and AggregatingMergeTree are ClickHouse's tools for the second option — engines that fold matching-key rows together during a merge instead of leaving that entirely to the query.

Neither engine eliminates query-time work; both shrink it. And both inherit the exact timing caveat replacingmergetree already taught: merges run on ClickHouse's own schedule, not yours, so "how much pre-reduction has happened" is never a guarantee you can query-plan around — it's a moving, eventually-consistent target.

The alternatives, steelmanned

Plain MergeTree, aggregated fresh at query time. The case for doing nothing special: a SELECT sum(revenue), uniqExact(user_id) FROM events WHERE ... GROUP BY region against raw data is always exactly correct, needs zero pipeline machinery beyond the table itself, and can answer any question the data supports — a new metric next quarter is just a new query, not a new table or a redesigned materialized view. For a table that's small enough, or queried rarely enough, that a full aggregation scan finishes well within whatever latency budget matters, this is not a compromise — it's the simplest correct answer, and every lesson in this course that's warned against premature complexity applies here too. The real cost only shows up as data and query volume grow: the work an aggregation query does scales with raw row count forever, with no mechanism to make tomorrow's version of the same query cheaper than today's.

SummingMergeTree — sum, and only sum, done automatically. When a merge combines parts, rows sharing the same sorting key get folded into one, with every numeric, non-key column summed. Nothing about writing to it differs from an ordinary MergeTree table — no special insert-time function calls, no serialized state to reason about — which makes it close to a drop-in swap for a plain MergeTree table whose queries are dominated by sum(). It can even work over a Map column, summing values across matching keys inside the map, which is exactly the trick Cloudflare's pipeline (below) relies on. The real limitation is total: SummingMergeTree sums. It has no mechanism for a distinct count, an average, a quantile, or any aggregate that isn't "add the numbers together" — reach for it on a workload that needs uniq() anywhere and it simply cannot do that column's job, full stop, not slowly or approximately.

AggregatingMergeTree — any aggregate function, at a real complexity cost. Where SummingMergeTree is fixed to one operation, AggregatingMergeTree generalizes to arbitrary aggregate functions by storing, per matching key, a combination of aggregate function states rather than a finished value. That state lives in a column typed AggregateFunction(func, ArgType), populated on insert with a -State combinator (uniqState(user_id)) and read back with the matching -Merge combinator (uniqMerge(visitors)). This is real, honest complexity: a column of AggregateFunction type isn't human-readable data, every write path needs the matching -State call, and every read path needs the matching -Merge call, or the result is meaningless rather than merely imprecise. The payoff is that it can pre-reduce anything — Cloudflare needed this specifically because their unique-visitor counts had no home in SummingMergeTree at all.

There's a real middle ground worth knowing before deciding "full AggregateFunction or nothing": for functions where the intermediate state and the final answer are the same value — min, max, sum, any, anyLast, and a handful of others — SimpleAggregateFunction(func, Type) gets the same merge-time pre-reduction without any -State/-Merge ceremony at all, because there's nothing extra to compute once you have the running min or max. A table can mix SimpleAggregateFunction columns for its min/max/sum-shaped metrics with full AggregateFunction columns only for the genuinely stateful ones like uniq or quantile — the complexity tax is per-column, not all-or-nothing.

CREATE TABLE events
(
    event_time DateTime,
    event_type LowCardinality(String),
    region     LowCardinality(String),
    user_id    UInt64,
    revenue    Decimal(10, 2)
)
ENGINE = MergeTree ORDER BY (region, event_time);

-- SummingMergeTree: revenue is a plain sum, no state needed
CREATE TABLE revenue_by_region
(
    day     Date,
    region  LowCardinality(String),
    revenue Decimal(10, 2)
)
ENGINE = SummingMergeTree
ORDER BY (region, day);

CREATE MATERIALIZED VIEW revenue_by_region_mv TO revenue_by_region AS
SELECT toDate(event_time) AS day, region, revenue
FROM events;

-- AggregatingMergeTree: distinct visitors needs real aggregate state
CREATE TABLE visitors_by_region
(
    day      Date,
    region   LowCardinality(String),
    visitors AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (region, day);

CREATE MATERIALIZED VIEW visitors_by_region_mv TO visitors_by_region AS
SELECT toDate(event_time) AS day, region, uniqState(user_id) AS visitors
FROM events
GROUP BY day, region;

-- Reading each back:
SELECT region, sum(revenue) FROM revenue_by_region
WHERE day = today() GROUP BY region;

SELECT region, uniqMerge(visitors) FROM visitors_by_region
WHERE day = today() GROUP BY region;

Decision framework

The reasoning a senior engineer runs through, in order:

1. Is raw-data query-time aggregation actually too slow, at today's volume and tomorrow's projected volume — or does it just feel like the "less proper" option? This is the question the alternatives section's steelman for plain MergeTree exists to make you actually answer, not skip. Building a materialized-view pipeline to solve a performance problem you don't have yet is real, avoidable cost.
2. What aggregate functions does the workload actually need? If everything is a sum, SummingMergeTree is the least-complexity option that solves the problem. The instant one metric needs uniq, avg, quantile, or anything stateful, SummingMergeTree is off the table for that column — no amount of clever schema design routes around this limitation, per Cloudflare's own experience below.
3. For the functions needing more than SummingMergeTree, do they fall into the SimpleAggregateFunction set (min/max/sum/any/anyLast and similar) or do they need real state (uniq/avg/quantile)? Defaulting straight to full AggregateFunction for every column pays the -State/-Merge tax even where SimpleAggregateFunction would have gotten the same pre-reduction for free.
4. Is the team ready to own the materialized-view plumbing this requires, including getting -State/-Merge right on every write and read path? This isn't a CREATE TABLE decision alone — it's a commitment to a pipeline shape, and a -Merge forgotten on one query is a silent correctness bug, not a slow query.
5. Does collapsing to this granularity foreclose a question you'll want to ask later? A rollup keyed by (region, day) can't answer an hourly question after the fact if raw data wasn't kept. Deciding whether to retain raw data alongside the rollup — as Cloudflare explicitly weighed — is part of this decision, not a detail to defer.

SELECT toDate(event_time) AS day, count() AS orders, uniqExact(user_id) AS buyers
FROM events
WHERE event_type = 'purchase' AND event_time > now() - INTERVAL 7 DAY
GROUP BY day;

Case studies

A good fit: Cloudflare's HTTP analytics pipeline, revisited. The same pipeline discussed in designing-events-tables — over 100 columns, millions of requests a second — used exactly this lesson's split. Their primary aggregated tables ran on SummingMergeTree, chosen because it "allowed us to significantly reduce the number of tables required," including summing values inside Map-typed columns for dimensional data. But unique-visitor counts had no home there: their own account of the decision states plainly that "SummingMergeTree allows you to create column with such data type, it will not perform aggregation on it for records with same primary keys" for uniques, so "we had to put uniques into separate materialized view, which uses the ReplicatedAggregatingMergeTree engine and supports merge of AggregateFunction states for records with the same primary keys." That's this lesson's decision framework step 2, playing out exactly as described, at production scale: sum-shaped metrics on the simpler engine, the one metric that genuinely needed aggregate state on the more complex one — not one engine chosen for the whole pipeline. The storage math behind choosing aggregation over keeping only raw data was stark even at the time: a full year of raw request logs was estimated at "273.93 PiB," against "18.52 PiB (RF x3)" for the aggregated tables — roughly $28M a year versus $1.9M. Even so, the team's own account notes they were "still considering to store raw (non-aggregated) requests logs" alongside the aggregates — a direct, first-party illustration of decision-framework step 5: pre-aggregating doesn't have to mean discarding the ability to ask a new question of history later, if the cost of keeping both is one you're willing to pay.

A bad fit: pre-aggregating a table that never needed it. A team with a table in the low tens of millions of rows, queried a few dozen times a day by an internal reporting tool, builds a full AggregatingMergeTree pipeline — materialized view, -State columns, the works — because a blog post described it as "how ClickHouse does real analytics." The plain GROUP BY query it replaced was already returning in well under a second. What the team gained: nothing measurable a user would notice. What it cost: a second table to keep in sync, a materialized view that has to be understood by whoever debugs a discrepancy later, and a -Merge combinator every future query against that table now has to remember to include correctly. This is decision-framework step 1 skipped entirely — reaching for pre-aggregation because it's the more sophisticated-sounding tool, not because query-time aggregation was measured and found wanting.

Anti-pattern gallery

Querying an AggregateFunction column without -Merge. Unlike SummingMergeTree, where forgetting GROUP BY/sum() still returns real (if not-fully-folded) numbers, querying a raw AggregateFunction column returns its internal serialized state — not a usable number at all. This fails loudly rather than silently, but it fails on literally the first query anyone new to the table runs, which makes it a near-universal onboarding trap for a table built this way.

Assuming SummingMergeTree or AggregatingMergeTree removes the need for GROUP BY entirely. Both engines only fold rows together within a merge, and merges are eventual, exactly like replacingmergetree's deduplication. A query against a SummingMergeTree table that skips sum()/GROUP BY because "the engine already summed it" will silently undercount or overcount whenever unmerged parts happen to still exist — which, per ClickHouse's own guidance, should be assumed possible at any time, not treated as a rare edge case.

Choosing SummingMergeTree, then discovering a uniq requirement later. A table designed around SummingMergeTree for its sum-shaped metrics, then asked to also report a distinct count, can't grow that capability in place — SummingMergeTree has no partial-state mechanism to extend. The fix is a structural one (a second AggregatingMergeTree table or column, as Cloudflare's own pipeline needed), not a configuration tweak, which is exactly why decision-framework step 2 belongs early, before committing to an engine, not after.