MergeTree Engine Variants

Picking the wrong specialized MergeTree engine locks a storage strategy into place that only surfaces its cost months later — duplicate rows that never collapse, aggregate targets that double-count, or a merge queue that never converges. The specialized engines — ReplacingMergeTree, AggregatingMergeTree, SummingMergeTree, and the CollapsingMergeTree/VersionedCollapsingMergeTree pair — all inherit the same part-and-merge machinery as plain MergeTree, but each rewrites what a background merge does when two rows share the same sort key. Choosing among them is the single highest-traffic decision a data engineer makes when a table needs to be anything other than an append-only log, and getting it wrong is expensive to unwind because it is baked into the physical layout and every query that reads the table.

The unifying idea is that in every one of these engines the ORDER BY clause is no longer just a primary index — it becomes the key that defines which rows are the same row. When a merge combines two parts, rows that collapse to an identical ORDER BY tuple are candidates for the engine’s collapse rule: keep the newest, sum the metrics, merge the aggregate states, or cancel a sign-flipped pair. This page is the working reference for reasoning about that behavior across the whole family — the semantics of each variant, the crucial gap between merge-time and query-time results, and the decision procedure for matching an engine to a workload — with each mechanic covered in depth on its own page.

How each variant collapses rows at merge time

A background merge reads several sorted parts and produces one larger sorted part. Plain MergeTree copies every row through unchanged. The specialized engines intercept runs of rows that share the ORDER BY key and apply a collapse rule before writing the output. The diagram below maps the decision from “what should happen to rows that share a key” to the engine that implements it, along with how you read the table afterward.

Two invariants hold across the whole family and drive every other decision. First, collapse is local to a part and deferred to merge time. An INSERT always writes a new part verbatim; the engine’s rule only fires when a background merge happens to combine rows that landed in the same output part. Two rows with the same key that live in different parts are not collapsed until a merge unites them, and merges across partitions never happen at all. Second, the query you write must assume the collapse has not happened yet. Because merges are asynchronous and eventually consistent, correct reads either force the collapse with FINAL or reproduce the collapse rule in the query itself (GROUP BY with SUM, argMax, a -Merge combinator, or a sign-weighted sum). Treating any of these engines as if it deduplicates on insert is the root cause of nearly every correctness bug filed against them.

Core DDL / configuration reference

The four DDL blocks below are copy-ready templates, one per variant. Read the ORDER BY in each as the collapse key, and note that partitioning is mandatory and interacts with FINAL (a merge — and do_not_merge_across_partitions_select_final — operates within a partition, never across). All four share the same shape as plain MergeTree; only the engine line and the columns that carry collapse metadata differ.

-- ReplacingMergeTree: keep the newest row per key.
-- The version argument breaks ties toward the highest value; is_deleted tombstones a key.
CREATE TABLE IF NOT EXISTS analytics.user_state
(
    `user_id`     UInt64,
    `email`       String,
    `plan`        LowCardinality(String),
    `updated_at`  DateTime64(3),
    `version`     UInt64,          -- monotonic per user_id; highest wins on collapse
    `is_deleted`  UInt8 DEFAULT 0  -- 1 = tombstone; dropped on FINAL merge
)
ENGINE = ReplacingMergeTree(version, is_deleted)
PARTITION BY toYYYYMM(updated_at)
ORDER BY user_id;               -- the deduplication identity key

-- SummingMergeTree: add numeric columns per key at merge time.
-- Optionally list which columns to sum; unlisted numerics also sum unless they are in ORDER BY.
CREATE TABLE IF NOT EXISTS analytics.traffic_daily
(
    `day`         Date,
    `host`        LowCardinality(String),
    `requests`    UInt64,
    `bytes_sent`  UInt64
)
ENGINE = SummingMergeTree((requests, bytes_sent))
PARTITION BY toYYYYMM(day)
ORDER BY (day, host);           -- grouping dimensions; everything else is summed

-- AggregatingMergeTree: combine partial aggregate states per key.
-- Columns are AggregateFunction(...) holding intermediate state, not final values.
CREATE TABLE IF NOT EXISTS analytics.sessions_agg
(
    `day`           Date,
    `country`       LowCardinality(String),
    `visitors`      AggregateFunction(uniq, UInt64),
    `p95_ms`        AggregateFunction(quantile(0.95), Float64),
    `revenue`       AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, country);        -- grouping dimensions; states merge within a key

-- VersionedCollapsingMergeTree: net out mutable rows via a sign column plus a version.
-- sign = +1 adds a state, sign = -1 cancels the matching prior state; version orders cancel pairs.
CREATE TABLE IF NOT EXISTS analytics.order_state
(
    `order_id`   UInt64,
    `status`     LowCardinality(String),
    `amount`     Decimal(18, 2),
    `changed_at` DateTime64(3),
    `sign`       Int8,            -- +1 = new state, -1 = cancel old state
    `version`    UInt64           -- disambiguates cancel pairs that arrive out of order
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PARTITION BY toYYYYMM(changed_at)
ORDER BY order_id;               -- the row identity being replaced

Each engine’s arguments are the only place its collapse rule is configured. ReplacingMergeTree(version, is_deleted) names the tie-break and tombstone columns; omit them and the engine keeps an arbitrary row from the last-inserted part, which is rarely what you want. SummingMergeTree((requests, bytes_sent)) restricts summing to an explicit list so you do not accidentally sum an id column. VersionedCollapsingMergeTree(sign, version) names the sign column and the version that makes cancellation order-independent — the plain CollapsingMergeTree(sign) variant omits the version and is correct only when a -1 is guaranteed to be merged after its +1. Because these are MergeTree-family engines, the per-column codec choices from the columnar storage and compression reference apply unchanged — a version counter compresses well under Delta, and a LowCardinality dimension in the sort key stays cheap.

Step-by-step implementation

1. Fix the collapse identity before writing any DDL

The ORDER BY key is the contract for what “the same row” means, and it cannot be changed later without rebuilding the table. Write down, for the workload, the tuple that uniquely identifies a logical entity: user_id for a state table, (day, host) for a traffic rollup, (day, country) for a sessions aggregate. Lead the tuple with the lowest-cardinality columns you also filter on, exactly as in a plain MergeTree, because the collapse key doubles as the sparse primary index. Verify the intended key is what the table actually stores:

SELECT sorting_key, partition_key, engine
FROM system.tables
WHERE database = 'analytics' AND name = 'user_state';
-- sorting_key should read 'user_id'; engine 'ReplacingMergeTree'.

2. Insert duplicates and confirm they persist pre-merge

Prove to yourself that collapse is deferred. Insert two versions of the same key in separate statements — separate statements guarantee separate parts — and count:

INSERT INTO analytics.user_state VALUES (42, 'a@x.com', 'free', now64(3), 1, 0);
INSERT INTO analytics.user_state VALUES (42, 'a@x.com', 'pro',  now64(3), 2, 0);

SELECT count() FROM analytics.user_state WHERE user_id = 42;      -- returns 2
SELECT count() FROM analytics.user_state FINAL WHERE user_id = 42; -- returns 1

The plain count sees both parts; FINAL applies the collapse at read time and returns the single newest row. This gap is the defining behavior of the whole family and is covered end to end in deduplicating rows with ReplacingMergeTree.

3. Feed aggregate tables from a materialized view, never by hand

AggregatingMergeTree and SummingMergeTree tables are almost always the TO target of a materialized view that emits -State values (for aggregating) or pre-summed rows (for summing) on the insert path. Building the state columns by hand is error-prone; let the view do it:

CREATE MATERIALIZED VIEW analytics.sessions_agg_mv
TO analytics.sessions_agg
AS
SELECT
    toDate(event_ts)          AS day,
    country,
    uniqState(user_id)        AS visitors,
    quantileState(0.95)(latency_ms) AS p95_ms,
    sumState(amount)          AS revenue
FROM analytics.events_raw
GROUP BY day, country;

The -State combinators write intermediate state, which the AggregatingMergeTree then merges per key. The full pattern — including how the view’s GROUP BY must align with the target’s ORDER BY — is the subject of pre-aggregating rollups with AggregatingMergeTree and the broader materialized view creation patterns reference. Verify state is landing:

SELECT day, country, uniqMerge(visitors) AS visitors, sumMerge(revenue) AS revenue
FROM analytics.sessions_agg
GROUP BY day, country
ORDER BY day, country;

4. Write reads that reproduce the collapse rule

Every consumer of these tables must either use FINAL or aggregate defensively. For ReplacingMergeTree, GROUP BY key with argMax(col, version) reproduces the newest-row rule without the merge overhead of FINAL. For SummingMergeTree, keep the SUM(...) GROUP BY key even though the engine also sums — parts that have not merged yet still hold multiple rows. For AggregatingMergeTree, always finalize with the matching -Merge combinator. Confirm a defensive read agrees with FINAL:

SELECT user_id, argMax(email, version) AS email, argMax(plan, version) AS plan
FROM analytics.user_state
GROUP BY user_id
HAVING argMax(is_deleted, version) = 0;   -- reproduces FINAL semantics at query time

Integration touchpoints

These engines sit at the seam between ingestion and the query layer, so their behavior is shaped by what writes to them and what reads from them.

• Upstream ingestion. Every one of these engines still creates one part per insert, so the same batching discipline that protects a plain MergeTree applies here — small, frequent inserts create parts faster than merges can collapse them, widening the window in which duplicates or un-summed rows are visible. Front these tables with the batching described in how MergeTree handles background merging so merges keep pace with the collapse workload.
• Materialized-view producers. Aggregating and summing targets are driven by views that must emit exactly the state the target expects; a mismatch between the view’s GROUP BY and the target’s ORDER BY silently produces rows that never collapse. The alignment rules live in materialized view creation patterns.
• Downstream query and BI. Any dashboard reading these tables must apply the collapse rule. A ReplacingMergeTree served to BI without FINAL or a GROUP BY will double-count during the merge window; a common fix is a small ... FINAL view or a projection that the BI layer reads instead of the base table.
• Engine family mechanics. All four variants share the part lifecycle, system.parts accounting, and merge scheduling detailed in the MergeTree engine deep dive — the variants change only the merge’s output rule, not when or how merges are scheduled.

Tuning parameters

The setting engineers reach for first is do_not_merge_across_partitions_select_final = 1, because it makes FINAL affordable enough to use in production reads — but it is only correct when the collapse key is fully contained within a partition, so that no two rows sharing a key ever land in different partitions. If your ORDER BY key can span partitions (for example a user_id state table partitioned by month, where the same user appears in several months), enabling it will return stale rows. The safe pattern is to include the partition expression’s source column in the sort key, or to accept full FINAL cost. Treat optimize_on_insert = 0 as the default for any table taking sustained ingest: leaving it on makes every insert pay a collapse cost that the background merges will redo anyway.

Troubleshooting

Duplicates never disappear even after waiting. The two rows live in different partitions, so no merge will ever unite them — merges never cross partition boundaries. Confirm the keys straddle partitions:

SELECT partition, count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE database = 'analytics' AND table = 'user_state' AND active
GROUP BY partition
ORDER BY partition;

Fix: choose a PARTITION BY expression that keeps every value of the collapse key inside one partition (or query with a full GROUP BY that ignores partitioning).

FINAL is unusably slow on a large table. FINAL merges matching parts on the read path. On a table with thousands of parts this dominates query time.

SELECT count() AS active_parts
FROM system.parts
WHERE database = 'analytics' AND table = 'user_state' AND active;

Fix: enable do_not_merge_across_partitions_select_final when the key is partition-local, replace FINAL with a GROUP BY … argMax read, and reduce part count by batching inserts and letting min_age_to_force_merge_seconds compact old parts.

A SummingMergeTree column is being summed that should not be. An id or code column with a numeric type gets summed because it was not in the ORDER BY and no explicit sum list was given.

SELECT engine_full
FROM system.tables
WHERE database = 'analytics' AND name = 'traffic_daily';
-- Confirm the engine lists exactly the columns that should be summed.

Fix: recreate the table with an explicit SummingMergeTree((requests, bytes_sent)) column list, or move the non-additive column into the ORDER BY key.

AggregatingMergeTree query returns garbage numbers. Reading a raw AggregateFunction column without a -Merge combinator returns the internal binary state, not a value.

SELECT toTypeName(visitors) FROM analytics.sessions_agg LIMIT 1;
-- AggregateFunction(uniq, UInt64) — must be finalized with uniqMerge(...)

Fix: always wrap reads in the matching -Merge function and GROUP BY the key, as shown in pre-aggregating rollups with AggregatingMergeTree.

Collapsing rows do not cancel. A -1 row does not exactly match the columns of its +1, or (in the non-versioned engine) it merged before its +1.

SELECT order_id, sum(sign) AS net
FROM analytics.order_state
GROUP BY order_id
HAVING net NOT IN (0, 1)
ORDER BY net DESC;
-- net values other than 0 or 1 reveal unmatched cancel pairs.

Fix: ensure the cancel row carries the identical prior column values, and prefer VersionedCollapsingMergeTree(sign, version) so out-of-order pairs still net to zero.

Related

• ReplacingMergeTree vs AggregatingMergeTree vs SummingMergeTree — the decision guide that separates the three most common variants by the problem each solves.
• Deduplicating rows with ReplacingMergeTree — version columns, tombstones, FINAL, and OPTIMIZE trade-offs.
• Pre-aggregating rollups with AggregatingMergeTree — AggregateFunction columns and the -State/-Merge combinator pipeline.
• MergeTree engine deep dive — the part lifecycle and merge scheduling these variants inherit.
• How MergeTree handles background merging — why collapse is deferred and how to keep merges converging.
• Columnar storage & compression — codec choices for version, sign, and dimension columns.

Up: ClickHouse Core Architecture & Analytics Fundamentals