SummingMergeTree and AggregatingMergeTree in ClickHouse

SummingMergeTree and AggregatingMergeTree: Painless Incremental Aggregation

1. Why Aggregation Engines Are Needed — The Problem of Calculations on Huge Data

Let's go back to our online casino. Every day, players place millions of bets. The dashboard owner needs to see: how many bets each user made per day and for what total amount.

In a regular database (PostgreSQL, MySQL), you would write:

SELECT user_id, date, COUNT(*), SUM(amount)
FROM bets
GROUP BY user_id, date

On a table with 100 million rows, such a query would take... well, you get it — a long time. Very long. Because the database has to read ALL rows, sort or hash them, and then calculate aggregates.

ClickHouse is faster, of course, but it's not magic either. The more data, the longer GROUP BY takes. And if there are many reports and they are needed "right now" — performance becomes a problem.

Idea: What if we pre-calculate aggregates and store them? So that the query "how many bets did user_id=123 make yesterday" is just a SELECT from a row, not a full aggregation?

For this, ClickHouse has two special table engines: SummingMergeTree and AggregatingMergeTree. They do the heavy lifting for you — in the background, during data part merges.

Real-life analogy: Imagine you keep track of sales in a store. Each sale is a receipt. If the owner asks for a report "how much did we sell today", you could go through all the receipts every time. Or you could keep a notebook where you write the total at the end of the day: "today 150 sales for 5000 rubles." SummingMergeTree is like keeping that notebook automatically.

2. SummingMergeTree — Automatic Summer

How It Works

SummingMergeTree is an engine that, during data part merges (in the background), sums numeric values for rows with the same sorting key (ORDER BY).

Summation rules:

• All numeric columns (types: UInt*, Int*, Float*, Decimal*) are automatically summed.
• Other columns (strings, dates, arrays) are taken from the first encountered row — this is important to remember, it may not be what you expect.
• If a column is not numeric but you want to aggregate it somehow — SummingMergeTree is not suitable, you need AggregatingMergeTree.

Why "Summing": because multiple rows with the same key are collapsed into one, and the numbers in it are the sum of the numbers from the original rows.

CREATE TABLE — Breaking It Down

-- Create a table for daily statistics by user
CREATE TABLE daily_stats
(
    date         Date,                -- Day of statistics
    user_id      UInt64,              -- Player ID
    bets_count   UInt64,              -- Number of bets per day (will be summed)
    total_amount Decimal(18,2)       -- Total bet amount (will be summed)
)
ENGINE = SummingMergeTree()           -- Engine for automatic summation
ORDER BY (date, user_id)              -- Grouping key: by date and user

What's important here:

• ENGINE = SummingMergeTree() — you can specify columns to sum in parentheses: SummingMergeTree(bets_count, total_amount). If not specified, all numeric columns are summed (except columns from ORDER BY, their values determine uniqueness).

• ORDER BY (date, user_id) — these columns determine which rows will be merged into one. That is, all rows with the same date and same user_id will collapse into one row during merge, where bets_count and total_amount are sums.

What happens if ORDER BY is too broad? If you include, for example, bets_count, then each unique bet (with a different count) will remain a separate row. No summation will occur because the keys are different. Pitfall #1 (we'll come back to it).

How to Insert Data

Insert raw events (each row is one bet):

-- Insert three bets for user 123 on 2025-06-01
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- one bet for 100 rubles
    ('2025-06-01', 123, 1, 250.00),   -- second bet for 250 rubles
    ('2025-06-01', 456, 1, 50.00);    -- another user

-- You can insert NON-aggregated data — the engine will handle it

After a background merge (can take from seconds to hours), rows with date='2025-06-01' and user_id=123 will merge into one: ('2025-06-01', 123, 2, 350.00).

Reading Without GROUP BY — Magic and Its Limits

The idea is that after the merge has happened, you can read data without aggregation:

-- If merge has already occurred, this query returns one row per user per day
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

But there's a problem. Between merges, data may reside in different parts with duplicate keys. Therefore, in practice, you still write with SUM:

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
WHERE date = '2025-06-01'
GROUP BY date, user_id;

Why does this work? Because even if the data hasn't merged, SUM will add everything correctly. And if it has merged, you have one row per group, and SUM simply returns its value. The query still reads data, but now there is LESS of it (aggregated rows instead of raw ones).

Analogy: SummingMergeTree is like an assistant that pre-glues identical receipts. But you still ask "show the total for each day." If the receipts are already glued, the total matches the number in one row. If not, you still get the correct total. The main thing is that you read not millions of receipts, but thousands of summaries.

3. The Problem: Between Merges You Need SUM in SELECT

This is a key point that is often misunderstood.

Naive approach (incorrect):

-- Thinking that after merge data is already aggregated, a beginner writes:
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- And gets multiple rows for one user (if merge hasn't happened)

Correct approach (safe):

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
GROUP BY date, user_id;

Why?

1. You always get the correct result — both before and after merge.
2. The data size is still smaller than in the raw bets table.
3. ClickHouse optimizes such queries well.

When can you omit SUM? Only if you are absolutely sure that the needed data has already been merged. For example, after a forced OPTIMIZE TABLE daily_stats (but this is an expensive operation, don't do it for every little thing).

4. AggregatingMergeTree — When Simple Summation Is Not Enough

SummingMergeTree can only add numbers. But what if you need:

• Count unique users (not sum)?
• Find maximum or minimum?
• Calculate average?
• Use approximate algorithms like uniq for counting unique values?

For this, there is AggregatingMergeTree. It stores not just values, but states of aggregate functions — special intermediate data that allows you to get the final result later.

Analogy: SummingMergeTree stores only the final sum. But AggregatingMergeTree stores not only the sum but also a counter (to later compute the average), or a hash table of unique values (to later tell how many there were). It's like the difference between "I have the total" and "I have a notebook where all data is recorded, but in compressed form."

CREATE TABLE with AggregateFunction

-- Create a table for aggregated dashboard statistics
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- Hour of the event
    sport_type     String,                                 -- Sport type (football, basketball...)
    total_bets     AggregateFunction(sum, UInt64),         -- Sum of bet count
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- Sum of money
    unique_users   AggregateFunction(uniq, UInt64),        -- Unique players (approximate)
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- Average bet size
    max_bet        AggregateFunction(max, Decimal(18,2))   -- Maximum bet
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

Breaking down the unfamiliar:

• AggregateFunction(sum, UInt64) — a column type that stores the state of the aggregate function sum for data of type UInt64. It's not a number, but an internal ClickHouse structure.
• Why not just UInt64? Because for some functions (uniq, avg), you need to store more data than just the final result. avg stores both sum and count. uniq stores a hash table.
• When merging two rows with the same ORDER BY (same hour and sport type) — the states of aggregate functions are combined. For sum, it's simply adding intermediate totals. For uniq, it's merging two hash tables of unique values.

Inserting Data via INSERT SELECT with State Functions

You cannot insert a regular value into an AggregateFunction column. You need to use special *State functions that create a state from a raw value.

-- Insert aggregated data from the raw bets table
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- Round time to hour
    sport_type,
    sumState(bets_count) AS total_bets,                    -- State of sum
    sumState(amount) AS total_amount,                      -- State of sum of money
    uniqState(user_id) AS unique_users,                    -- State for unique
    avgState(amount) AS avg_bet_amount,                    -- State for average
    maxState(amount) AS max_bet                            -- State for maximum
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

What's happening here:

• toStartOfHour(event_time) — a ClickHouse function that truncates the time to the hour: 2025-06-01 12:34:56 → 2025-06-01 12:00:00.
• sumState(amount) — instead of SUM(amount), you write sumState(amount). The result is a state of the aggregate function, type AggregateFunction(sum, ...).
• GROUP BY is mandatory in the insert query! Because you are aggregating data from the raw table into groups (hour+sport), and then inserting each group as one row into dashboard_hourly.

Reading via Merge Functions

To read the data, use *Merge functions:

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- From state → number
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- Unique users
    avgMerge(avg_bet_amount) AS avg_bet_amount,
    maxMerge(max_bet) AS max_bet
FROM dashboard_hourly
WHERE event_hour >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;   -- Grouping is still needed (if data hasn't merged)

Why GROUP BY again? Same reason as with SummingMergeTree: between merges, there may be multiple rows with the same ORDER BY. GROUP BY with *Merge gives the correct result in any state.

5. Usage Pattern with Materialized Views

The most powerful way to use AggregatingMergeTree is in combination with a Materialized View. You insert raw data into a regular table, and the view automatically aggregates it and stores it in the aggregating table.

Analogy: It's like setting up a conveyor belt: raw receipts go into one box, and an automatic sorter every minute collects daily totals and puts them into another box. Analysts only look at the second box — fast and without GROUP BY on the fly.

Full Example: Hourly Statistics for Operator Dashboard

Step 1: Raw table — events (bets) will be inserted here

CREATE TABLE raw_bets
(
    event_time DateTime,
    sport_type String,
    user_id UInt64,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY event_time;

Step 2: Aggregating table — ready statistics will be stored here

CREATE TABLE bets_hourly_agg
(
    hour DateTime,
    sport_type String,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    avg_bet AggregateFunction(avg, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_type);

Step 3: Materialized View — the bridge between them

CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
    toStartOfHour(event_time) AS hour,
    sport_type,
    sumState(1) AS total_bets,                    -- Each row is one bet
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;

What happens now?

1. You insert rows into raw_bets with regular INSERT.
2. ClickHouse automatically (almost instantly) runs them through the materialized view.
3. The view aggregates data only from the inserted batch and inserts the results into bets_hourly_agg.
4. In bets_hourly_agg, multiple rows with the same (hour, sport_type) may temporarily accumulate — but they will merge during background merges.

Reading for the dashboard:

SELECT
    hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,
    avgMerge(avg_bet) AS avg_bet
FROM bets_hourly_agg
WHERE hour >= today() - 7
GROUP BY hour, sport_type;

This query will read only aggregated data, which takes up thousands of times less space than raw bets.

6. Real Example: Sports Events Dashboard

Imagine you are a bookmaker operator. On the dashboard, you need to show:

• For each match (football, Champions League, "Real" vs "Bayern")
• How many bets were placed in the last 5 minutes
• Total amount of all bets
• Number of unique players
• Average bet

Raw data: 5000 bets per second. Storing everything and aggregating from scratch each time is madness.

Solution:

-- Table for aggregates by match with 5-minute intervals
CREATE TABLE match_stats_5min
(
    match_id String,
    interval_5min DateTime,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    max_bet AggregateFunction(max, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (match_id, interval_5min);

-- Materialized View
CREATE MATERIALIZED VIEW match_stats_mv TO match_stats_5min AS
SELECT
    match_id,
    toStartOfFiveMinute(event_time) AS interval_5min,
    sumState(1) AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    maxState(amount) AS max_bet
FROM raw_bets
GROUP BY match_id, interval_5min;

Now the dashboard queries match_stats_5min — and gets responses in milliseconds instead of seconds.

7. When NOT to Use SummingMergeTree and AggregatingMergeTree

When SummingMergeTree is suitable:

• You only need to sum numeric values.
• You are okay with using GROUP BY with SUM between merges.
• The grouping key is not too high-cardinality (e.g., not a billion unique users — though that's okay, just more data).

When SummingMergeTree is NOT suitable:

• You need to count unique users (uniq, count(DISTINCT)) — only AggregatingMergeTree works.
• You need other aggregates: avg, min, max — again only AggregatingMergeTree.
• You expect data to always be in an already aggregated state — that's not how it works.
• Your data is updated (not just inserted) — these engines are not for update semantics.

When AggregatingMergeTree is suitable:

• You need different types of aggregations (sums, uniques, averages, maximums).
• You are willing to write INSERT with *State and SELECT with *Merge.
• You use materialized views for automatic aggregation.
• The volume of raw data is huge, and aggregates are orders of magnitude smaller.

When AggregatingMergeTree is NOT suitable:

• You are not ready to explain to the team what AggregateFunction is and how to work with it. The learning curve is higher.
• You need exact uniqueness, not approximate (uniq is a probabilistic structure, error ~2%). For exact, use groupBitmap or count in another system.
• The data volume is small (millions of rows) — a regular GROUP BY is simpler.
• You frequently change the aggregation schema (add new metrics) — recreating the materialized view is painful.

8. Comparison with Regular MergeTree + GROUP BY

Characteristic	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
Insert speed	Maximum	High	Medium (due to state)
Read speed (large range)	Low (reads everything)	High (reads aggregates)	High
Read speed (point)	Medium	High	High
Storage space	Maximum	Minimum (aggregates)	Slightly more (states)
Code complexity	Low	Low (just a table)	High (*State, *Merge)
Aggregation flexibility	Any	Only sums	Any (via AggregateFunction)

9. Common Pitfalls

Pitfall #1: ORDER BY does not include enough fields

-- BAD: only date, without user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- During merge, ALL rows for one day will collapse into one
-- You lose user-level detail

Correct: Include in ORDER BY all fields by which you want to aggregate.

Pitfall #2: Forgot GROUP BY in SELECT

-- BAD: without GROUP BY, even if data hasn't merged
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- If there are two rows with the same date but different user_id — you'll get an error
-- ClickHouse doesn't know which user_id to show

Correct: Always group by the same fields as in ORDER BY.

Pitfall #3: Non-stochastic uniq

uniq in ClickHouse is a probabilistic function. Error ~2-3%. If you need exact uniqueness, use uniqExact or groupBitmap.

Pitfall #4: Updating old data

SummingMergeTree and AggregatingMergeTree do not like updates. If you need to correct a bet from yesterday, it's easier to insert a new row with the opposite sign (via CollapsingMergeTree).

10. What's Next

Now that you've mastered the aggregating engines, the next topics:

• How to choose the right engine for your task — comparison of all *MergeTree engines.
• Materialized Views in detail — how to debug, how to update the schema.
• Tuning background merges — to make aggregates collapse faster.

Bottom line: SummingMergeTree and AggregatingMergeTree are tools for those who don't want their dashboard to lag on terabytes of data. They require a bit more understanding upfront, but pay off many times over under real workloads. The main rule: always use GROUP BY and aggregate functions when reading — then you are safe before and after merges.

---

← Previous: ReplacingMergeTree: How to Beat Duplicates in ClickHouse Without Pain
→ Next: CollapsingMergeTree: How to Update Aggregates Without UPDATE in ClickHouse

— Editorial Team