4. The MergeTree family

Chapter 3 said merges do useful work along the way. This chapter is what that work can be.

The MergeTree variants all store data identically. They differ in what happens when two rows with the same sorting key meet during a merge. Plain MergeTree keeps both. The others do something more interesting.

The crucial thing to understand before any of them:

Merges happen eventually, or never

Every engine in this chapter gives you a guarantee that applies only after a merge has combined the relevant rows — and there is no promise about when that happens. Two rows in two different parts stay as two rows until those parts merge, which may be seconds or may be never (parts above the merge size limit are never merged, and rows in different partitions are never merged together).

So the rule is: query as if deduplication has not happened yet. Every pattern below tells you how.

ReplacingMergeTree — keep the last version

CREATE TABLE users (
    user_id   UInt64,
    name      String,
    email     String,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

During a merge, rows with the same ORDER BY key are collapsed to one — the one with the largest value of the version column (updated_at). Without a version column, an arbitrary one wins.

This is how you model mutable records in a database that hates updates. To change a user, insert a new row with a newer timestamp:

INSERT INTO users VALUES (42, 'Ada Lovelace', 'ada@new.example', now());

The old row is superseded and will disappear at some point. Meanwhile both exist, so a naive SELECT returns duplicates.

Reading it correctly

Three options, in increasing order of preference:

1. FINAL — force deduplication at query time:

SELECT * FROM users FINAL WHERE user_id = 42;

Correct, and expensive: ClickHouse must merge the relevant parts on the fly. On modern versions FINAL is much better optimised than it used to be (it can skip parts that cannot contain duplicates, and parallelises), but it is still a real cost on large scans.

2. Deduplicate in the query — usually the right answer for aggregate queries:

SELECT user_id, argMax(name, updated_at) AS name, argMax(email, updated_at) AS email
FROM users
GROUP BY user_id;

argMax(x, y) returns the x from the row with the largest y. This is explicit, fast, and does not depend on merge state.

3. is_deleted for deletions (added in later versions):

ENGINE = ReplacingMergeTree(updated_at, is_deleted)

Insert a row with is_deleted = 1 and the merge removes the row entirely rather than keeping a tombstone. Combine with the clean_deleted_rows setting; check your version’s behaviour before relying on it.

The ReplacingMergeTree pattern is the standard way to sink a CDC stream into ClickHouse — Debezium or a change stream produces a row per change, and the engine converges on the latest state. See the Kafka guide, where log compaction does the same job with the same log-versus-table logic.

SummingMergeTree — add up the numbers

CREATE TABLE daily_stats (
    date     Date,
    country  LowCardinality(String),
    views    UInt64,
    clicks   UInt64
) ENGINE = SummingMergeTree
ORDER BY (date, country);

During a merge, rows with the same key are collapsed with the numeric columns summed.

Insert freely without reading anything first:

INSERT INTO daily_stats VALUES ('2026-08-20', 'UK', 1, 0);
INSERT INTO daily_stats VALUES ('2026-08-20', 'UK', 1, 1);
INSERT INTO daily_stats VALUES ('2026-08-20', 'UK', 1, 0);

Eventually these become one row with views = 3, clicks = 1. It is a counter table without read-modify-write, which is a genuinely useful thing.

The same rule applies — query with sum() and GROUP BY, never bare:

SELECT date, country, sum(views), sum(clicks)
FROM daily_stats GROUP BY date, country;

That works whether or not merges have run: partially merged rows still sum to the same total. This is why summing with a GROUP BY is always correct and FINAL is unnecessary here.

You can restrict which columns are summed: SummingMergeTree((views, clicks)). Columns not in the key and not summed take an arbitrary value, which is a source of confusion — keep the schema to key columns plus numeric columns.

AggregatingMergeTree — the powerful one

SummingMergeTree only sums. AggregatingMergeTree can maintain any aggregate function, including ones that are not simply additive — averages, uniques, quantiles, min/max, argMax.

It does this with aggregate states: a partial, mergeable representation of an aggregation in progress. Chapter 9 covers states properly; here is the shape.

CREATE TABLE user_stats (
    user_id      UInt64,
    visits       AggregateFunction(count, UInt64),
    unique_pages AggregateFunction(uniq, String),
    avg_duration AggregateFunction(avg, UInt32),
    last_seen    AggregateFunction(max, DateTime)
) ENGINE = AggregatingMergeTree
ORDER BY user_id;

Write states with the -State suffix:

INSERT INTO user_stats
SELECT user_id,
       countState(),
       uniqState(url),
       avgState(duration),
       maxState(ts)
FROM page_views
GROUP BY user_id;

Read them with the -Merge suffix:

SELECT user_id,
       countMerge(visits)        AS visits,
       uniqMerge(unique_pages)   AS pages,
       avgMerge(avg_duration)    AS avg_dur,
       maxMerge(last_seen)       AS last_seen
FROM user_stats
GROUP BY user_id;

Why this works: an average cannot be averaged, but a (sum, count) pair can be added. A uniq state is a HyperLogLog sketch, and two sketches merge into one. The state carries exactly what is needed to combine partial results correctly.

AggregatingMergeTree plus a materialised view is the core ClickHouse pattern for real-time rollups, and chapter 11 builds it end to end. It is the thing that lets a dashboard over 40 billion rows respond instantly.

CollapsingMergeTree — a sign column

For mutable state where you want to cancel previous rows. Every row carries a sign of 1 (state) or -1 (cancel):

CREATE TABLE sessions (
    session_id UInt64,
    duration   UInt32,
    sign       Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY session_id;

To update, insert a cancelling row and then the new state:

INSERT INTO sessions VALUES (1, 100, -1);   -- cancel the old
INSERT INTO sessions VALUES (1, 250,  1);   -- the new state

During a merge, a matching +1 and -1 pair annihilate. The trick for querying without FINAL is to multiply by the sign:

SELECT session_id, sum(duration * sign) AS duration
FROM sessions GROUP BY session_id HAVING sum(sign) > 0;

This is correct regardless of merge state, which is the appeal.

Its weakness is that it depends on insertion order: if the -1 arrives before the +1 it is cancelling, they may not collapse. VersionedCollapsingMergeTree(sign, version) adds a version column so ordering does not matter, and it is what you should use if you need this at all.

In practice, ReplacingMergeTree covers most cases more simply. Reach for collapsing engines when you specifically need additive metrics to net out correctly.

Choosing

   What do you want when two rows share a key?

   keep both                      → MergeTree
   keep the newest                → ReplacingMergeTree(version)
   add the numbers                → SummingMergeTree
   maintain any aggregate         → AggregatingMergeTree     ← most powerful
   net out with +1 / -1 rows      → VersionedCollapsingMergeTree

And the practical guidance:

• Raw events, append-only — plain MergeTree. Most tables.
• Latest state per entity, CDC sink, dimension tables — ReplacingMergeTree.
• Simple counters — SummingMergeTree.
• Dashboards and rollups — AggregatingMergeTree behind a materialised view.
• Everything else — plain MergeTree plus a materialised view.

The FINAL trap

Because it is the mistake people make:

SELECT count() FROM events FINAL;         -- 40 seconds
SELECT count() FROM events;               --  0.02 seconds

FINAL forces a merge-on-read across parts. It has improved substantially — do_not_merge_across_partitions_select_final and parallel FINAL help a lot — and it is still the difference between a fast query and a slow one on a large table.

Design so you do not need it. Every engine above has a query pattern (argMax, sum, -Merge, sum(x*sign)) that gives the correct answer regardless of merge state. Learn those patterns and reserve FINAL for small point lookups and ad-hoc investigation.

Deduplication of identical inserts

Separate from all of the above, and worth knowing because it surprises people.

Replicated tables deduplicate identical insert blocks. If you retry an INSERT after a network failure and the block is byte-identical, ClickHouse notices (it stores a hash of the last ~100 blocks in Keeper) and ignores the duplicate. This makes retries safe, which matters enormously for Kafka ingestion.

It also means that inserting the same data twice on purpose silently does nothing — which looks like data loss until you know about it. insert_deduplication_token lets you control the identity explicitly. Non-replicated tables have non_replicated_deduplication_window for the same behaviour.

What goes wrong

Assuming deduplication is immediate. It is not. Query with the right pattern.

FINAL on every query. Slow, and usually unnecessary.

Different partitions never merge. A ReplacingMergeTree partitioned by month will never deduplicate a row inserted in January against the same key in February. If your key can appear across partitions, the engine cannot help you — this catches people out badly.

SummingMergeTree with non-numeric non-key columns. They take an arbitrary value from one of the merged rows.

Using ReplacingMergeTree as an OLTP table. Every update is an insert; the table grows until merges catch up; heavy update rates mean merges never catch up. It works for reasonable change rates, not for a transactional workload.

Silent insert deduplication hiding an intentional re-insert.

What to take from this chapter

• The variants differ only in what a merge does with rows sharing the same ORDER BY key.
• Merges are eventual and sometimes never happen — parts above the size limit, and different partitions, never merge. Always query with a pattern that is correct regardless.
• ReplacingMergeTree(version) keeps the newest — the standard CDC sink. Read with argMax, not FINAL.
• SummingMergeTree adds numbers — read with sum() ... GROUP BY, always correct.
• AggregatingMergeTree maintains any aggregate via -State/-Merge, and with a materialised view is the ClickHouse rollup pattern.
• VersionedCollapsingMergeTree for netting out with +1/-1; prefer it over plain collapsing.
• FINAL is a design smell. Learn argMax, sum(x*sign) and -Merge instead.
• Replicated tables deduplicate identical insert blocks, which makes retries safe and intentional re-inserts silently vanish.

Next: the decision that matters more than every other in ClickHouse.