Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 35 ยท Aggregation and Materialized Views

AggregatingMergeTree and SummingMergeTree

Learn how SummingMergeTree and AggregatingMergeTree roll up rows during background merges, how -State and -Merge store aggregates, and why you must re-aggregate.

A materialized view writes small partial aggregates into a target table every time rows are inserted. Left alone, that table would grow by one row per key per insert. Two special MergeTree engines solve this: SummingMergeTree and AggregatingMergeTree. During the background merges that every MergeTree table performs, they collapse rows with the same sorting key into one.

The product runs only on a server, so ClickHouse statements here are read-only. The runnable parts use plain SQL to show the same collapse logic. Understanding this lesson is the key to building fast rollup tables for dashboards.

Merges do the aggregation, eventually

A MergeTree table stores inserts as immutable parts. In the background, ClickHouse merges small parts into bigger ones. A plain MergeTree merges by concatenating and sorting. The special engines add one step: when two rows share the same ORDER BY key, they are combined.

flowchart LR
    P1["Part 1: (a, 3)"] --> M["Background merge"]
    P2["Part 2: (a, 5)"] --> M
    P3["Part 3: (b, 1)"] --> M
    M --> R["Merged part: (a, 8), (b, 1)"]

Rows with the same key are combined only when their parts are merged.

This happens at an unpredictable time. Until the merge, several rows with the same key may exist. So a query must always aggregate again with GROUP BY and sum(). The merge is a storage optimization, not a guarantee.

SummingMergeTree

SummingMergeTree adds up numeric columns of rows with the same sorting key:

SQL
CREATE TABLE daily_traffic
(
    day     Date,
    site    LowCardinality(String),
    hits    UInt64,
    bytes   UInt64
)
ENGINE = SummingMergeTree
ORDER BY (site, day);

Rules to remember:

  • All numeric columns not in the sorting key are summed (or only the ones you list in SummingMergeTree((hits, bytes))).
  • Non-numeric columns outside the key keep an arbitrary value from the merged rows, so keep them out or accept that.
  • Rows whose summed numeric columns are all zero may be deleted during a merge.
  • The sorting key defines the grouping. Choose ORDER BY (site, day), not ORDER BY ts, or no rows will ever collapse.

Reading it correctly:

SQL
SELECT site, sum(hits) AS hits, sum(bytes) AS bytes
FROM daily_traffic
WHERE day >= today() - 7
GROUP BY site;

SummingMergeTree also supports nested Map columns, summing values per key, which is useful for counters like {status: count}.

AggregatingMergeTree and state columns

Sums are easy to combine. An average, a unique count or a quantile is not: you cannot add two averages. The solution is to store the intermediate aggregation state and merge states later. This is what the -State and -Merge combinators are for.

SQL
CREATE TABLE site_stats
(
    day        Date,
    site       LowCardinality(String),
    visitors   AggregateFunction(uniq, UInt64),
    latency    AggregateFunction(quantile(0.95), UInt32),
    avg_bytes  AggregateFunction(avg, UInt32),
    hits       SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (site, day);

Two column types appear:

  • AggregateFunction(func, argTypes) stores an opaque binary state. It needs -State to write and -Merge to read.
  • SimpleAggregateFunction(func, type) is for functions whose state equals the result (sum, min, max, any, anyLast, groupArrayArray). It is stored as a normal value and is faster and easier to read.

Feeding it from a materialized view uses -State:

SQL
CREATE MATERIALIZED VIEW site_stats_mv TO site_stats AS
SELECT
    toDate(ts)                      AS day,
    site,
    uniqState(user_id)              AS visitors,
    quantileState(0.95)(latency_ms) AS latency,
    avgState(bytes)                 AS avg_bytes,
    count()                         AS hits
FROM events
GROUP BY day, site;

And querying uses -Merge, which finalizes the combined state:

SQL
SELECT
    site,
    uniqMerge(visitors)           AS visitors,
    quantileMerge(0.95)(latency)  AS p95_latency,
    avgMerge(avg_bytes)           AS avg_bytes,
    sum(hits)                     AS hits
FROM site_stats
WHERE day >= today() - 30
GROUP BY site;

Selecting a state column directly shows unreadable binary, which is normal. Always wrap it in the matching -Merge. This design means a dashboard over billions of events reads a few thousand rollup rows.

Choosing the engine

Need Engine
Plain counters and totals SummingMergeTree
Unique counts, percentiles, averages AggregatingMergeTree with AggregateFunction
Keep only the latest row per key ReplacingMergeTree
Cancel earlier rows with a sign CollapsingMergeTree

Average deserves a warning: summing an avg column across merged rows gives nonsense. Store sum and count as two summed columns and divide when reading, or use avgState.

Stand-in: combining partial aggregates

The collapse logic is "group by key and combine". The first editor stores partial rows (as separate inserts would) and reads them correctly; the second shows why averaging averages is wrong. Both are SQLite stand-ins.

SQL Stand-in: partial rows combined at read time

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: why you store sum and count, not average

Press Run (or Ctrl + Enter) to see the result here.

The second result (180) is the true mean over ten requests; averaging the averages gives 150. An avgState keeps exactly the sum and count internally, so avgMerge returns 180.

Common mistakes

  • Querying without GROUP BY/-Merge and trusting that merges already happened.
  • Using the raw event timestamp in ORDER BY, so keys never match.
  • Selecting an AggregateFunction column directly.
  • Using uniqMerge over a column written with uniqExactState (the function names must match).
  • Changing the aggregate function in the view without altering the target column type.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When are rows with the same sorting key combined in a SummingMergeTree?

    Collapsing happens during background merges. Until then several rows per key may exist, so queries must still aggregate.
  2. 2. Which combinator writes an intermediate state for an AggregatingMergeTree column?

    -State produces the state to store, and -Merge combines and finalizes stored states when you read.
  3. 3. Why can you not store avg(latency) per row in a SummingMergeTree and sum it?

    Averages do not add up. Store a sum and a count (or an avgState) so partial results combine correctly.
  4. 4. What is SimpleAggregateFunction used for?

    For sum, min, max, any and similar functions the state is just the value, so it can be stored and read as a plain column.

Summary

  • Special MergeTree engines combine rows with equal sorting keys during background merges.
  • SummingMergeTree adds numeric columns; AggregatingMergeTree stores -State values merged with -Merge.
  • Because merging is not immediate, always re-aggregate at read time.
  • Use SimpleAggregateFunction for sum, min, max and similar functions.
  • Feed these tables from materialized views for real-time rollups.

Next: The Sparse Primary Index