Analytics PipelineClickHouse & Materialized Views
Guide ClickHouse Core Architecture & Analytics Fundamentals

ReplacingMergeTree vs AggregatingMergeTree vs SummingMergeTree

Three specialized MergeTree engines get confused constantly because they all “collapse rows on merge,” but they solve genuinely different problems: ReplacingMergeTree keeps the newest version of a row, SummingMergeTree adds numeric columns per key, and AggregatingMergeTree merges arbitrary aggregate states. This guide draws the sharp lines between them — the distinct problem each was built for, how to design the ORDER BY key, what the query has to do at read time, and a mechanical decision procedure so you pick right the first time.

Prerequisites

The one question that separates them

Each engine answers a different question about two rows that share an ORDER BY key. ReplacingMergeTree asks which row is current and discards the rest. SummingMergeTree asks what is the total and adds the metrics. AggregatingMergeTree asks what is the combined aggregate and merges partial states — which is the only one of the three that can answer “how many distinct users” or “what is the 95th percentile,” because those cannot be produced by summing pre-computed numbers.

ReplacingMergeTree vs AggregatingMergeTree vs SummingMergeTree across five dimensions A five-row comparison matrix. Rows are the problem solved, the ORDER BY key meaning, the merge rule, the read-time query, and the anti-fit. Columns are ReplacingMergeTree, AggregatingMergeTree, and SummingMergeTree. Replacing keeps the newest row and reads with FINAL or argMax; Aggregating merges partial states and reads with -Merge; Summing adds numeric columns and reads with SUM and GROUP BY. ReplacingMergeTree AggregatingMergeTree SummingMergeTree PROBLEM ORDER BY = MERGE RULE READ WITH ANTI-FIT Latest-state dedup; one current row per key. Row identity — the natural primary key. Keep the max-version row; drop is_deleted tombstones. SELECT … FINAL or argMax(col, version). Additive metrics — it does not add, only replaces. Compound aggregates: uniq, quantile, avg, argMax. Grouping dimensions of the rollup. Merge partial aggregate states per key. uniqMerge(state) any matching -Merge fn. A plain sum — the -State machinery is overkill. Additive totals: counts, bytes, revenue. Grouping dimensions of the rollup. Add every numeric column per key. SUM(x) … GROUP BY still sum at read time. Uniques or averages — sums cannot express them.

ReplacingMergeTree — the deduplication engine

ReplacingMergeTree exists to hold the current version of each entity. The ORDER BY key is the entity’s identity — user_id, (tenant_id, order_id), a natural business key — and the engine keeps exactly one row per key when a merge fires, choosing the row with the highest value of the version argument. A companion is_deleted UInt8 column lets you tombstone a key: on a FINAL merge, the row is physically dropped rather than kept as the winner.

sql
CREATE TABLE analytics.customer_current
(
    `customer_id` UInt64,
    `tier`        LowCardinality(String),
    `updated_at`  DateTime64(3),
    `version`     UInt64,
    `is_deleted`  UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(version, is_deleted)
PARTITION BY tuple()               -- single logical partition keeps every version of a key together
ORDER BY customer_id;

The version argument is what makes deduplication deterministic. Without it, ReplacingMergeTree keeps an arbitrary row from the most recently inserted part — fine for a strict upsert stream, dangerous when writes can arrive out of order. Use a monotonically increasing version (an event timestamp cast to a number, a Kafka offset, a CDC log-sequence number) so the newest logical write always wins regardless of insert order. The full treatment of version columns, tombstones, and the FINAL cost model is in deduplicating rows with ReplacingMergeTree.

At query time you must collapse duplicates yourself, because merges are asynchronous. The two idioms are SELECT … FINAL, which forces the merge on the read path, and a GROUP BY key with argMax(col, version), which reproduces the newest-wins rule without merging:

sql
SELECT customer_id, argMax(tier, version) AS tier
FROM analytics.customer_current
GROUP BY customer_id
HAVING argMax(is_deleted, version) = 0;   -- exclude tombstoned keys

SummingMergeTree — the additive rollup engine

SummingMergeTree collapses rows with the same ORDER BY key into one row whose numeric columns are the sums. Here the ORDER BY is not an identity but a set of grouping dimensions — (day, host), (minute, endpoint, status) — and everything numeric outside that key is a measure that gets added. It is the lightest-weight way to maintain running totals, and it is correct only for values that are meaningful to add.

sql
CREATE TABLE analytics.api_calls_hourly
(
    `hour`        DateTime,
    `endpoint`    LowCardinality(String),
    `status`      UInt16,
    `calls`       UInt64,
    `error_bytes` UInt64
)
ENGINE = SummingMergeTree((calls, error_bytes))
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, endpoint, status);

Two design rules matter. First, list the summed columns explicitly — SummingMergeTree((calls, error_bytes)) — so a numeric column that happens to be an identifier is never accidentally added. Second, keep querying with SUM(...) GROUP BY key even though the engine sums for you; parts that have not merged yet still contain several rows per key, so a bare SELECT * would under-report. The engine is an optimization that shrinks the data merges have to carry, not a substitute for aggregating at read time.

SummingMergeTree cannot compute a distinct count or an average, because those are not additive: uniq over two partial groups is not the sum of the two uniqs, and an average of averages is wrong. The moment a rollup needs anything beyond a straight sum, it belongs in AggregatingMergeTree.

AggregatingMergeTree — the general aggregate engine

AggregatingMergeTree generalizes summing to any aggregate function by storing intermediate state instead of a final number. Its columns are typed AggregateFunction(fn, argtypes), and a merge combines the partial states per key using the aggregate’s own combine logic. This is what lets it maintain a rolling uniq, a quantile, an avg, or an argMax correctly across merges. The trade-off is that you never read the column directly — it holds binary state — and every write must produce state via a -State combinator, which in practice means a materialized view.

sql
CREATE TABLE analytics.endpoint_stats
(
    `hour`      DateTime,
    `endpoint`  LowCardinality(String),
    `hits`      AggregateFunction(sum, UInt64),
    `users`     AggregateFunction(uniq, UInt64),
    `p99_ms`    AggregateFunction(quantile(0.99), Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, endpoint);

The -State / -Merge pair is the contract. A view writes sumState, uniqState, quantileState(0.99) on the insert path; a reader finalizes with sumMerge, uniqMerge, quantileMerge(0.99) and a GROUP BY. Note that AggregatingMergeTree with a sum state is a strict superset of SummingMergeTree — it can do everything summing does — but it costs more storage and CPU, so reserve it for rollups that genuinely need compound aggregates. The end-to-end view-fed pipeline is walked through in pre-aggregating rollups with AggregatingMergeTree, and the view-authoring conventions live in materialized view creation patterns.

The decision procedure

Work through these in order and stop at the first match:

  1. Do rows represent an entity whose latest state is what you want, and older versions should vanish? Use ReplacingMergeTree(version[, is_deleted]), key on the entity identity, read with FINAL or argMax.
  2. Do rows represent events you want totaled, and every measure is a pure sum (count, bytes, amount)? Use SummingMergeTree((measures…)), key on the grouping dimensions, read with SUM … GROUP BY.
  3. Does the rollup need a distinct count, a quantile, an average, an argMax, or any non-additive aggregate? Use AggregatingMergeTree with AggregateFunction columns, fed by a -State view, read with -Merge.
  4. Do rows mutate and you already hold the prior row so you can cancel it out? That is the CollapsingMergeTree/VersionedCollapsingMergeTree case — see the engine-variants overview — not one of these three.

A useful tie-breaker: if you can express the metric as SUM, prefer SummingMergeTree for its simplicity; escalate to AggregatingMergeTree only when a single non-additive measure appears in the same rollup, at which point put all measures in aggregate state for consistency.

Verification

Confirm each engine is doing what you expect by comparing a raw read against the collapse-aware read. For a ReplacingMergeTree, the gap between the two counts is the un-merged duplicate backlog:

sql
SELECT
    (SELECT count() FROM analytics.customer_current)        AS raw_rows,
    (SELECT count() FROM analytics.customer_current FINAL)  AS current_rows;

For an AggregatingMergeTree, verify the column really holds state and finalizes cleanly:

sql
SELECT hour, endpoint,
       sumMerge(hits)  AS hits,
       uniqMerge(users) AS users,
       quantileMerge(0.99)(p99_ms) AS p99_ms
FROM analytics.endpoint_stats
GROUP BY hour, endpoint
ORDER BY hour DESC
LIMIT 5;

Check part collapse progress across any of these tables from system.parts, since a large un-merged part count widens the window where raw and collapsed reads disagree:

sql
SELECT table, count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE database = 'analytics'
  AND table IN ('customer_current', 'api_calls_hourly', 'endpoint_stats')
  AND active
GROUP BY table;

Gotchas and edge cases

  • All three defer collapse to merge time. None of them deduplicate or aggregate on insert (unless optimize_on_insert collapses within a single block). Reads must always apply the collapse rule; a dashboard that trusts the base table double-counts during the merge window.
  • SummingMergeTree silently sums unexpected columns. Any numeric column not in the ORDER BY and not excluded by an explicit sum list gets added — including columns you meant to treat as attributes. Always pass the explicit measure list.
  • Reading an AggregateFunction column without -Merge returns binary state. It will look like garbage bytes or throw a type error in a client. The column type is the tell: toTypeName() shows AggregateFunction(...).
  • ReplacingMergeTree without a version is order-dependent. With no version argument it keeps a row from the last-inserted part, so an out-of-order late write can win. Supply a monotonic version whenever insert order is not guaranteed.
  • Partitioning can strand duplicates forever. Merges never cross partitions, so if the same collapse key lands in two partitions it will never collapse. Keep the collapse key partition-local, which for a ReplacingMergeTree state table often means PARTITION BY tuple().

Up: MergeTree Engine Variants