ReplacingMergeTree Deduplication Guarantees and Real-World Limits

ReplacingMergeTree solves exactly one problem: it removes duplicate rows sharing the same ORDER BY key, but only during background merges, and only on a schedule nobody can predict in advance. That last part trips people up. The engine's own documentation is blunt about it: deduplication is "eventual correctness only." It doesn't guarantee anything at insert time, and it says outright that you should not rely on it. Engineers who miss that line end up debugging phantom traffic spikes that turn out to be duplicate rows sitting in unmerged parts.

Two workloads make this engine worth reaching for. One is idempotent retry logic: at-least-once delivery systems that might insert the same record twice, and need the second copy to quietly disappear. The other is real-time sync from a mutable source, like change-data-capture off a MySQL or PostgreSQL table, where every update to a row arrives as a brand-new insert. Both patterns depend on the same background mechanism. Understanding where that mechanism ends is the actual job here.

How the background merge's timing works

Merges run on a heuristic. The system looks at part sizes, current load, and its own internal scheduling logic, then decides when to combine parts, not on any fixed interval and not against any service-level agreement. On a quiet cluster, that might mean two duplicate rows get resolved within two seconds of landing. On a busy day, the same table, the same engine, the same schema, and those duplicates can sit unresolved for over an hour. That range, two seconds to an hour-plus, is the actual planning window engineers work with. Anyone building against a tighter assumption is building on sand.

Inside a merge, the mechanics are straightforward. The engine looks at the parts selected for that merge, finds rows sharing an ORDER BY key, and keeps exactly one survivor. Which row survives depends on whether a version column exists, which the next section covers in detail.

The scope of comparison matters here. Deduplication is strictly intra-merge. Only rows inside the parts chosen for that specific merge operation get compared against each other. Rows sitting in parts that weren't selected this round? Not touched, not even considered.

And this is the constraint that catches people off guard: deduplication does not cross partition boundaries, full stop. Two duplicate rows that happen to land in different partitions cannot be merged away by the engine through its normal background merge process. If a pipeline partitions by day and the same event gets written on both sides of a midnight boundary, both copies live in the table permanently. That's not a bug. It's how the engine is built, and it means partition design isn't just a performance decision here, it's a correctness decision.

What queries return before the merge runs

Running a plain SELECT, with no FINAL, causes the query to read every active part on disk, duplicates included. COUNT(*) comes back inflated, sometimes by a wide margin. Cases have been observed where the count is inflated by roughly 1.8 times the true value. SUM() and AVG() get skewed the same way, because duplicate event values get added into the aggregate as if they were distinct events.

A dashboard showing a substantially inflated multiple of the real event count doesn't throw an error, and that is what makes this genuinely dangerous rather than just annoying. A dashboard showing a substantially inflated multiple of the real event count doesn't throw an error. It just looks like a busy day, a traffic spike, maybe a successful marketing push, without flagging itself. It just looks like a busy day, a traffic spike, maybe a successful marketing push. Nobody gets paged for a metric that's too high in a direction that looks like good news. The failure mode isn't a crash, it's a business decision made on a number that was never real to begin with.

Three symptoms appear at the query level, and they don't always appear together:

• COUNT(*) overcounts active rows, since every unmerged duplicate counts as its own row.
• SUM() and AVG() inflate whenever the duplicated rows carry numeric payload values.
• One manual approach is to use argMax() with a GROUP BY on the key columns, pulling the value tied to the highest version without invoking FINAL and without waiting on a merge that might not run for another hour.

That last option matters because it's free of the performance cost that comes with FINAL. It's not elegant, but for a one-off correctness check, DISTINCT on the key columns gets the job done.

Version columns: when insertion order is not a safe tiebreaker

Without a version column, the engine falls back on part creation order. It keeps whichever row belongs to the most recently created part, essentially last-insert-wins. That sounds fine until multiple threads are writing concurrently. At that point, "most recent" isn't a meaningful concept anymore, it's a race, and the outcome is undefined in any way an engineer could plan around.

A version column fixes that by giving the engine an explicit tiebreaker instead of an implicit one. Setting a column of type UInt*, Date, DateTime, or DateTime64 means the row carrying the highest version value survives the merge, regardless of which part it happened to land in or which thread wrote it first. Insertion order stops mattering. That's the whole point.

No version column means last-inserted-wins, and a version column means highest-value-wins. When two rows share the exact same version value, the engine falls back to last-inserted-wins, the same behavior as having no version column at all. That's an edge case to design around explicitly rather than rely on.

Version columns stop being optional in a few specific situations:

• Multi-threaded insert pipelines, where the same key can arrive through two different threads at nearly the same instant.
• CDC streams, where network conditions or upstream buffering can deliver events out of order.
• Retry logic, where a corrected record might reach ClickHouse before the original bad record has even been merged away.

In each case, the shared thread is the same: order of arrival stops being a reliable signal, so the system needs something more explicit to lean on.

ORDER BY design: the dual job of sort key and deduplication key

In a plain MergeTree table, ORDER BY does one job: it sets the physical sort order on disk, which drives how efficiently the engine can skip data during a scan and how well it compresses. In ReplacingMergeTree, that same clause takes on a second job. It also defines the uniqueness key that deduplication runs against. Both responsibilities live in the same column list, and they don't always want the same thing.

Getting the sort order right can move query performance by 100 times or more on the right workload. Get it wrong, tune it purely for deduplication correctness at the expense of scan efficiency, and that gain evaporates. The fix isn't to pick one goal over the other, it's to sequence the columns so both goals get served.

The pattern that tends to work: put the low-cardinality columns that queries filter on most often at the front of the ORDER BY, since that's what lets the sparse index skip large chunks of data during a scan. Then append the actual unique identifiers at the end, where their only job is correctness. One worked example puts analytics dimensions, region, category, and similar fields, first, followed by customer and supplier key columns, with the true uniqueness fields, order key and line number, placed last where they belong.

If that combined column list grows unwieldy, PRIMARY KEY can be declared as a subset of it. That trims the in-memory index footprint without touching how deduplication behaves, since the ORDER BY clause, not the PRIMARY KEY, is what governs uniqueness here.

FINAL semantics: the correctness lever and its performance cost

Appending FINAL to a query forces the merge logic to run right there, at query time, so the result looks exactly like every background merge already happened. It's the one option that guarantees correctness without needing to wait around for the scheduler. But what does that guarantee actually cost?

Before version 20.5, FINAL ran single-threaded, no matter how large the cluster or how many cores sat idle. Since 20.5, it can run in parallel, which meaningfully changes the math on modern clusters, but parallel execution isn't the same as free execution. The overhead is still real but smaller than it used to be.

One setting cuts that overhead sharply: do_not_merge_across_partitions_select_final, available since version 20.10. It restricts FINAL's merge work to within a single partition, and if a partition already consists of one part above level zero, it skips that partition's merge work. Why does this setting matter so much? Because cross-partition merging is where FINAL's cost concentrates. Confine the work to inside partition boundaries, and the heaviest part of the bill disappears.

FINAL earns its cost in a couple of specific settings:

• Low-frequency queries where correctness is non-negotiable, reporting jobs, data exports, anything feeding a downstream publish step.
• Tables that are small, or partitioned well enough that a per-partition merge stays cheap even at query time.

Outside those conditions, FINAL on every query is usually the wrong default. It works, but it's a lever to pull deliberately, not a habit.

argMax() as a FINAL alternative for aggregation queries

For queries that are already doing aggregation, there's a cheaper path than FINAL. GROUP BY the unique key, then call argMax(column, version) on each payload field to pull the value tied to the highest version. The result: one row per key, correctly deduplicated, without the engine ever running its full merge logic at query time.

A simple form looks like: GROUP BY user_id, argMax(name, version) AS latest_name. That returns exactly one row per user, with the name field pulled from whichever underlying row carried the largest version value. No FINAL, no cross-part merge, just an aggregate function doing the tiebreaking work directly.

This approach fits two situations especially well. First, queries that are aggregations by nature anyway, counts, sums, latest-state lookups, since the GROUP BY clause is already sitting there and argMax just rides along with it. Second, materialized views that pre-aggregate frequently queried, deduplicated data, where the query-time cost gets paid once during the view's refresh instead of on every read against it.

argMax() only works if you know exactly which column holds the authoritative version value. It's not a general substitute for FINAL on queries that need to return full, untouched row sets, it's a substitute specifically for the aggregation case.

Write amplification: the operational cost of letting duplicates reach the table

Every duplicate that slips through to storage isn't just a query-time correctness problem, it's a write-cost problem too. Merging parts means reading the source parts off disk, writing a new merged part, then deleting the originals. For a merge combining 100 GB of source parts, that's roughly 100 GB read, roughly 100 GB written, for a net data volume that hasn't grown. Call it 200 GB of I/O to process 100 GB of actual data.

Left unchecked, high duplicate rates create a spiral. New parts keep accumulating faster than the background merge process can consolidate them. Each merge cycle that does run generates more I/O, which eats into the write bandwidth available for new inserts, which slows ingestion, which lets even more unmerged parts pile up. It's a feedback loop, and it gets worse the longer it runs unaddressed.

There's a hard ceiling on how far this can go before the system pushes back. Once a partition's part count crosses the parts_to_throw_insert threshold, 3,000 parts by default, new inserts into that partition get blocked with a "Too many parts" exception. And even the automatic merge selector has a limit of its own: it caps source-part size at around 150 GB, so extremely large accumulations of parts may need manual intervention rather than resolving themselves through routine background activity.

Upstream deduplication as the first line of defense

None of this argues against the engine, it argues for not asking it to do all the work alone. The stronger pattern is two-stage: a stream processor catches and drops the large majority of duplicates in real time, before they ever reach storage, and ReplacingMergeTree is left to clean up whatever rare, late-arriving duplicate slips past that first layer. One does the volume work, the other does the safety net.

That division of labor pays off directly in merge pressure. When duplicates are caught upstream, background merge activity drops to a minimum, and clusters stop needing to be sized for the combined load of heavy ingestion plus heavy merge I/O at the same time. Eliminating duplicates before they land can remove merge pressure for something like 99.9% of the workload, leaving the engine to handle only the genuine edge cases it was actually designed for.

Batching compounds the effect. Every insert creates a new part and triggers background bookkeeping, so a stream of many small inserts generates far more merge overhead than the same data arriving as fewer, larger batches. Batching thousands of rows per insert, rather than sending them one or a handful at a time, meaningfully cuts the number of parts the system ever has to reconcile.

A couple of complementary controls round this out. Idempotent insert design at the application layer, assigning stable keys before a write goes out, prevents whole classes of duplicates from being generated. And for streaming pipelines, exactly-once or at-least-once delivery with deduplication handled at the Kafka consumer layer keeps the volume of duplicates hitting the table low enough that the background merge process is genuinely a backstop, not a load-bearing wall.