ReplacingMergeTree Deduplication Guarantees and Production Limitations

ReplacingMergeTree removes duplicate rows during background merges, not when rows are inserted.

A merge takes a set of parts (the small, immutable chunks of data that accumulate as inserts land) and combines them into a single, larger part. If a version column is defined, the row with the highest version number survives. If no version column exists, the last-inserted row among the duplicates wins.

Two details complicate this: which row wins a merge depends on the version column when one is defined, and on insertion order when it isn't. And a table receiving updates without an explicit version column falls back to insertion order, which creates a quiet dependency on timing that becomes relevant later when replication and partial updates enter the picture.

Merges themselves are scheduled by the server, not by the application. Duplicates sitting in a table between merges are the normal, expected state of a ReplacingMergeTree table at almost any given moment, an assumption the engine was built around from the start.

The three scope constraints that bound every deduplication guarantee

Everything the engine promises about removing duplicates holds only within three boundaries: one partition, one shard, and the moment a specific merge completes. Step outside any of the three and the guarantee stops applying, permanently, not temporarily.

The first boundary is partition scope. Deduplication happens within a single partition. If a design allows the same row to land in two partitions, both copies live there indefinitely, surviving every maintenance operation the engine offers.

The second boundary is shard scope. In a sharded cluster, deduplication logic runs entirely local to the shard holding the data. There is no cross-shard reconciliation step, scheduled or otherwise. A row that gets routed to two different shards, whether from a routing bug, a retry that lands differently, or a resharding event, survives on both shards for as long as the table exists.

The third boundary is timing. Between the moment a row is inserted and the moment a merge finally removes its outdated predecessor, both versions exist in the table and both are fully queryable. Depending on load and part size, it can stretch to hours, and under certain configurations, days.

A fourth factor compounds all three: ORDER BY column immutability. If a column included in ORDER BY changes upstream, something common when replicating from a transactional source system, the updated row generates a different key. It never matches against the original row it was meant to replace, so both rows survive as if they were unrelated records.

There's a ceiling layered on top of the timing constraint too. Duplicates that accumulate inside a part after it crosses that ceiling have no automatic path to resolution. They simply stay.

How materialized views make out-of-partition duplicates a permanent corruption

Materialized views built on top of a ReplacingMergeTree source expose the sharpest version of this problem, because they break the one thing engineers often assume: that a merge will eventually clean things up.

A materialized view fires at insert time. The materialized view's target table does not get that correction. It already consumed both rows at insert time, and that aggregated result is now simply wrong, with no merge on the source table able to reach back and fix it.

It reflects how INSERTs and materialized views interact by design, and the failure stays invisible until someone checks the numbers against a source of truth and finds a mismatch they can't explain.

The sharpest version of this is visible in sumState(), countState(), and similar AggregateFunction columns. These store a binary aggregate state, not a plain number you can eyeball and correct. Once a double-counted value gets baked into that state, there's no SQL update that fixes it. The only way out is rebuilding the materialized view from scratch, reprocessing the source history to regenerate a clean aggregate state. For a table at any meaningful scale, fixing that requires a project, not a quick fix.

What querying a ReplacingMergeTree table without FINAL returns

A query against a ReplacingMergeTree table without FINAL runs against whatever parts currently exist, merged or not.

The consequences follow directly from the mechanism already described. A point lookup meant to return "the current status" of a single record can return two rows for that record: the stale version and the fresh one, sitting side by side in the same result set.

None of this throws an error. Catching that requires someone comparing output against an independent source of truth.

FINAL is the mechanism that forces merge-equivalent deduplication at query time, collapsing duplicate rows before results get returned to the caller, which is the engine working as designed rather than broken. Querying without it is the default path, not a mistake, and FINAL is the opt-in correction layered on top when a query needs guaranteed-unique rows. That framing matters, because it shifts the question from "did someone forget something" to "does this query need FINAL, and can the workload afford it."

The real cost of using FINAL

FINAL used to carry a reputation for being prohibitively slow, and for good reason: before version 20.5, it ran single-threaded no matter how large the table or how many CPU cores sat idle on the host. Every FINAL query funneled through one thread, regardless of cluster size.

That changed incrementally over several releases. Parallel execution arrived in stages, with more complete data-parallel execution landing in version 23.3, splitting parts into layers that run across threads simultaneously, with max_final_threads defaulting to all available CPU cores. That alone changed the practical calculus for FINAL on large tables, since the bottleneck of forcing everything through a single thread no longer applied by default.

A second improvement came with the do_not_merge_across_partitions_select_final setting. For partitioned tables, this setting plus the parallelization work together changes FINAL from a last resort into a genuinely viable default for many workloads.

None of that makes FINAL cost-free, and one interaction deserves particular attention. FINAL disables the PREWHERE optimization by default, the step that normally filters rows early to reduce how much data gets scanned. Adding an explicit PREWHERE clause to work around that disabling isn't a safe fix. PREWHERE filters rows before FINAL applies its deduplication logic, so a manually added PREWHERE can filter out the replacement row that was supposed to win, causing the stale version to appear in the result instead. That's a correctness bug disguised as a performance tweak, and it's easy to introduce without realizing it.

OPTIMIZE TABLE FINAL, the manual table-wide command, is a separate tool entirely and shouldn't be confused with query-time FINAL. FINAL as a query modifier has gotten dramatically better. OPTIMIZE TABLE FINAL as a maintenance command hasn't changed in the same way, and still deserves the caution it always has.

The argMax pattern as an alternative and when the trade-off favors it

The main alternative to FINAL leaves the merge process untouched. The argMax + GROUP BY pattern reconstructs the latest version of each logical row at query time, working directly from raw data regardless of whether any merge has run.

The pattern itself is straightforward: group by the columns that make up the ORDER BY key, then select argMax(column, version) for each value column in the row. That expression returns the value of column from whichever row has the highest version, for each group, which is functionally the same outcome FINAL aims for, just computed differently.

Because this pattern operates at query time over raw rows, it sidesteps every one of the scope constraints that make FINAL's guarantee conditional. Partition scope doesn't matter. Shard scope (within a single query against a single shard's data) doesn't matter. Merge timing doesn't matter. As long as the version column is populated correctly across every insert, duplicates anywhere in the data get resolved correctly by the GROUP BY, whether or not a merge has ever touched them.

That reliability comes at a cost in verbosity and planning overhead. On full-table scans, the GROUP BY step adds computational overhead compared to running FINAL against data that's already well-merged.

One documented production benchmark found the two approaches roughly competitive on full-table scans at scale, with FINAL coming out faster in that particular case. That result doesn't settle the question in FINAL's favor across the board. argMax tends to be the stronger choice when data is known to be poorly merged, or when the PREWHERE interaction described earlier makes FINAL unsafe for a given query's shape. Neither pattern wins across every case. Which one fits depends on how merged the data actually is, how the query needs to filter rows, and how much verbosity a team is willing to maintain in exchange for not depending on merge state.

Production failure patterns that emerge when scope constraints are ignored

The three scope constraints aren't theoretical. They produce specific, recurring failure patterns once production systems start assuming guarantees the engine never made.

Part count runaway is a related pattern. Cloudflare's billing pipeline hit a related failure mode in a documented production incident, where runaway part counts caused query-planner lock contention, though the root cause there was a partitioning-key migration rather than ingestion simply outrunning the merge process.

The large-part ceiling described earlier acts as a quiet dedup stopper in production. Once a part crosses the merge size threshold, automatic merges stop selecting it, and any duplicates that accumulate inside that part from that point forward have no path to automatic resolution. This failure is configuration-invisible: the table looks healthy, query latency looks normal, and nothing in standard monitoring flags that deduplication has silently stopped working for a subset of the data sitting inside that oversized part.

Schema design decisions that interact with the deduplication contract

Partition key, ORDER BY, and version column choices aren't just performance tuning levers on a ReplacingMergeTree table. Each one directly decides whether the deduplication contract actually holds for any given row, or quietly fails to.

The partition key functions as a deduplication boundary. Any partitioning scheme that can place the same logical row into two different partitions, time-based partitioning being the classic case when upstream systems send late-arriving or corrected events, turns cross-partition duplicates into a permanent condition. The fix is choosing a partition key that guarantees every version of a given logical row always lands in the same partition, no matter when it arrives.

ORDER BY defines row identity for deduplication purposes, so what goes into it has to be chosen with that in mind. Including a column that changes in the source system, an updatable status field or a timestamp reflecting processing time rather than event time, breaks deduplication for every row where that column later changes value. Only genuinely immutable identity columns belong in ORDER BY.

The version column carries more weight than it might first seem. A monotonically increasing version, whether that's a sequence number, an event timestamp in milliseconds, or an explicit update counter, gives the engine a reliable way to pick the correct survivor among duplicates. Without one, the engine falls back to insertion order within a part, and insertion order isn't guaranteed to be consistent across replicas in a replicated setup.

The version column and ORDER BY design also unlock the engine's one supported delete mechanism. Soft deletes work by inserting a new version of a row with the same ORDER BY key, a higher version number, and an is_deleted flag set to 1. Queries run with FINAL automatically suppress rows flagged this way. This pattern is the only supported way to delete data from a ReplacingMergeTree table without reaching for ALTER TABLE DELETE, a heavier, less frequent operation.

Where practitioners disagree on deduplication responsibility

The sharpest disagreement among practitioners working with ReplacingMergeTree is about where deduplication responsibility should live: upstream, before data ever reaches the table, or downstream, resolved at query time.

The upstream approach deduplicates at the stream processing layer, a Kafka consumer, a Flink job, or an application-level check, before rows ever get inserted. Done well, with short transformation latency, this removes duplicates before the ReplacingMergeTree engine's merge behavior ever becomes relevant to correctness. The table still benefits from the engine's version-based update semantics for things like handling corrections, but correctness no longer depends on merges happening on any particular schedule.

The downstream approach accepts duplicates as a normal, expected state of the table and manages them at read time, through FINAL or argMax. This is operationally simpler on the write side, since nothing upstream needs to guarantee exactly-once delivery. But it pushes correctness responsibility onto every query author, every materialized view definition, and every downstream consumer who touches that table, all of whom now need to know to apply the right pattern consistently.

One practitioner's documented approach offers a genuine counterpoint to the idea that FINAL is simply too expensive to use broadly: running FINAL on every single ReplacingMergeTree query, consistently, against a table at the scale of hundreds of billions of rows, using the parallelization improvements introduced around version 22.6 together with the do_not_merge_across_partitions_select_final setting.

That the disagreement persists, even among practitioners operating at serious scale, says something about the nature of the question itself. It isn't resolvable by benchmark alone, because the right answer depends on how much control a team has over its upstream pipeline, how tolerant a given workload is of query-time overhead, and how much operational complexity a team is willing to carry in exchange for simpler writes. Anyone building on ReplacingMergeTree has to answer that question for their own system, with the constraints laid out here as the terms of the decision rather than a verdict already reached for them.