In ClickHouse, when would you choose SummingMergeTree over AggregatingMergeTree?
answer
- ask whether every metric is additive
- an average of averages is wrong
- some columns store working memory, not answers
- two sketches can be merged, two counts cannot
- both are eventual — still write GROUP BY
basics
~10 sSummingMergeTree when every measure is additive and you only need sums; AggregatingMergeTree when you need non-additive results like distinct counts, quantiles or averages, which require storing mergeable aggregate states instead of plain numbers.
solid answer
~50 sBoth are rollup engines: a merge collapses rows sharing the `ORDER BY` key into one. They differ in **what a merge can combine**. `SummingMergeTree` adds up the numeric columns. That works only for additive measures — counts, revenue, bytes — because summing two partial sums is a valid sum. The stored values are plain numbers, so the table is small and directly readable. `AggregatingMergeTree` stores columns of type `AggregateFunction(...)`, written with the `-State` combinator and read with `-Merge`. Because it keeps the aggregate's internal state rather than a number, it can combine anything: `uniqState` sketches, quantile digests, `avgState`, `argMaxState`. That is the point — an average of averages is wrong, so `avg` cannot live in a summing table. Rule of thumb: if every metric is a sum, take the simpler engine; the moment one metric is a distinct count or a percentile, use aggregate states. In both cases queries still `GROUP BY` the key, because merges are eventual.
code
sql · 14 lines-- Additive measures only: plain numbers, simple reads
CREATE TABLE traffic_daily
(
day Date,
country LowCardinality(String),
hits UInt64,
bytes UInt64
)
ENGINE = SummingMergeTree
ORDER BY (day, country);
SELECT day, country, sum(hits) AS hits
FROM traffic_daily
GROUP BY day, country;go deeper
Know that both engines pre-aggregate rows sharing the sorting key, and that one adds numbers while the other stores aggregate states. Remember queries still need GROUP BY.
Explain additivity as the deciding test and describe what an aggregate state is — internal working memory that can be merged — with concrete examples like uniq sketches and avg's sum-and-count pair.
Be ready to design the rollup grain, catch the trap of unlisted numeric columns being summed, and explain why the aggregating engine subsumes the summing one when metrics are mixed.
Own the interface question: opaque state columns bind every consumer to a convention. Weigh readability, storage cost and the number of downstream teams before making aggregate states the standard shape of your serving tables.
## Both engines are rollups `SummingMergeTree` and `AggregatingMergeTree` are MergeTree variants that pre-aggregate. They store data exactly like `MergeTree` — immutable sorted parts, sparse index, partitions — and change one thing: when a background merge finds several rows with the same **sorting key**, instead of keeping them all it produces a single combined row. The sorting key therefore defines the *grain* of the rollup: `ORDER BY (day, country)` means one row per day and country. ## SummingMergeTree ```sql CREATE TABLE traffic_daily ( day Date, country LowCardinality(String), hits UInt64, bytes UInt64 ) ENGINE = SummingMergeTree ORDER BY (day, country); ``` During a merge, rows with the same `(day, country)` are replaced by one row whose numeric columns are the sums. If you supply an explicit column list — `SummingMergeTree((hits, bytes))` — only those columns are summed; otherwise ClickHouse sums **every** numeric column that is not part of the sorting key. Non-numeric columns outside the key get an arbitrary value from one of the collapsed rows, which is almost always a modelling mistake waiting to happen. The attraction is simplicity and size: the table holds ordinary integers, is easy to inspect, and compresses well. The constraint is that summation must be the correct combining operation for every column, which means every measure must be **additive**. ## AggregatingMergeTree ```sql CREATE TABLE traffic_daily_agg ( day Date, country LowCardinality(String), hits AggregateFunction(sum, UInt64), uniq_users AggregateFunction(uniq, UInt64), p95_ms AggregateFunction(quantile(0.95), Float64) ) ENGINE = AggregatingMergeTree ORDER BY (day, country); ``` Here the columns hold **aggregate states**, not results. A state is the internal working memory of an aggregate function — for `uniq` a probabilistic sketch, for `quantile` a digest, for `avg` a running sum and count. States are written with the `-State` combinator and read back with `-Merge`: ```sql INSERT INTO traffic_daily_agg SELECT day, country, sumState(hits), uniqState(user_id), quantileState(0.95)(ms) FROM raw GROUP BY day, country; SELECT day, country, sumMerge(hits), uniqMerge(uniq_users), quantileMerge(0.95)(p95_ms) FROM traffic_daily_agg GROUP BY day, country; ``` This is what makes non-additive metrics possible. You cannot add two distinct-user counts and get the distinct count of the union, but you *can* merge two sketches. You cannot average two averages correctly, but you can merge two sum-and-count states. That is the whole reason the engine exists. The cost is ergonomics and size: the columns are opaque binary states, a plain `SELECT` on them is meaningless without `-Merge`, and a sketch or digest is larger than a single integer. Every consumer must know the convention. ## Choosing between them Ask one question: **is every measure additive?** - All sums and counts → `SummingMergeTree`. Smaller, readable, no combinator discipline for consumers. - Any distinct count, percentile, average, median, or last-value-wins → `AggregatingMergeTree`. - A mix → you can put a `sum` into an `AggregatingMergeTree` as `sumState`, so the aggregating engine subsumes the summing one; the reverse is not possible. Do not split one grain across two tables just to keep some columns plain. ## The part both engines share: merges are eventual Neither engine pre-aggregates at insert time. Rows sit unrolled in their own parts until a background merge combines them, and merges never cross partitions. Therefore **every query against either table must still aggregate**: ```sql SELECT day, sum(hits) FROM traffic_daily WHERE day = today() GROUP BY day; ``` Omitting the `GROUP BY` and reading raw rows is the classic bug: results are correct only by accident, when a merge happens to have run. This applies just as much to `AggregatingMergeTree`, where reading a state column without `-Merge` returns something unusable rather than a plausible wrong number — which at least fails loudly. ## How to answer Name the additivity test first, then explain that aggregate states are what make non-additive metrics mergeable, then close with the shared caveat that both are eventual and queries must still group. A candidate who says "AggregatingMergeTree is just SummingMergeTree with more functions" has missed the point about states.
- Why can a distinct count not be stored in a SummingMergeTree column?Because summation is not the right combining operation for it. Two partial distinct counts cannot be added — the same user may appear in both, and the union's cardinality is not the sum. Adding them silently overcounts. A `uniq` state stores a sketch whose merge deduplicates across partial results, which is exactly why `AggregatingMergeTree` exists.
- What happens to a numeric column in a SummingMergeTree table that you did not intend to be summed?If you did not give the engine an explicit column list, ClickHouse sums every numeric column that is not in the sorting key — including one holding a rate, a ratio or a pre-computed average. You get a silently meaningless value. Either list the columns to sum explicitly, move the column into the sorting key, or store it as an aggregate state instead.
- Can you query a SummingMergeTree table without a GROUP BY?Only if you are willing to be wrong. Rows are collapsed by background merges, so at any moment the table may hold several unmerged rows for the same key. Reading them raw returns partial values that happen to look plausible. Always aggregate with `sum(...)` and `GROUP BY key`. The grouping is cheap precisely because merges have already reduced most of the rows.
Summing is like keeping a running till total; aggregating is like keeping the whole till roll, because some questions — how many distinct customers, what was the 95th percentile basket — cannot be answered from the total alone.
saying these in an interview costs you the question
- Stores an average or a rate in a SummingMergeTree column
- Thinks distinct counts can be added across partial rollups
- Reads an AggregatingMergeTree table without -Merge combinators
- Queries a rollup table without GROUP BY and trusts the numbers
- Believes either engine aggregates at insert time