ReplacingMergeTree Deduplication Guarantees and Limitations

ReplacingMergeTree deduplicates data at merge time, not at write time, a fact that determines almost every design and query decision that follows.

Every insert creates its own data part. Rows don't get compared against what's already in the table the moment they land. Instead, duplicates sit side by side in separate parts until a background merge sweeps across them and collapses the ones that share an ORDER BY key. When a version column is part of the schema, the engine keeps the row with the highest version value. Without one, it falls back to keeping whatever was inserted last, a guarantee that's only as strong as insert order, which in a distributed system is weak.

This design suits upsert-style workloads well: change-data-capture pipelines, mutable dimension tables, state snapshots where a value gets overwritten repeatedly over its lifetime. A short window where duplicates exist trades instant consistency for higher throughput in these cases. What ReplacingMergeTree does not do is stand in for transactional UPSERT semantics. There's no ACID guarantee here, and no real-time enforcement of uniqueness. The next section needs to pin down the two things it cannot promise: timing and partition scope.

The two hard limits built into the deduplication contract

ReplacingMergeTree offers two guarantees, and both have a hard ceiling that no setting or workaround raises.

The first limit is timing. Merges happen eventually, but never on a fixed schedule. A duplicate pair might collapse within seconds on a cluster with light load, or it might sit unresolved for well over an hour if the system is under merge pressure. There's no dial to turn that forces a predictable SLA on this process. Anyone who needs a guaranteed window for deduplication to complete is asking for something the engine was never built to offer.

The second limit is scope. Deduplication happens only within a single partition. If a partitioning scheme lets the same logical key land in two different partitions, those duplicate rows will never be reconciled by any mechanism inside the engine. Running OPTIMIZE TABLE FINAL doesn't help here either: it forces a merge within each partition, but it has no way to reach across partition boundaries and merge rows that live in different ones.

These aren't bugs waiting on a patch. They follow directly from how column-oriented storage partitions and merges data, and both will still be true in any future version of the engine. The two limits by themselves are abstract until they appear in a query result that looks correct and isn't.

What a SELECT sees before a merge has run

A plain SELECT against a ReplacingMergeTree table, run without FINAL, returns every unmerged part exactly as it was written. Counts come back inflated. Aggregations double-count rows that haven't been collapsed yet. Nothing in the output flags that the numbers are off.

That silence is the real danger. The query doesn't error. It returns a result that's structurally valid and numerically plausible, the kind of number a dashboard happily displays without complaint. A modest inflation in row counts rarely trips an alert on its own, so the gap often becomes visible weeks later, when someone compares the dashboard against the original source system and finds the two disagree. By then, tracing the discrepancy back to an unmerged duplicate set takes real investigation.

The stakes rise in observability and security analytics workloads specifically, where duplicate event rows inflate alert counts and can distort an incident timeline if the newest version of a row hasn't merged yet. An analyst reconstructing what happened during an incident is working from a count that may not reflect the true event volume, and there's no asterisk on the number telling them so.

The problem compounds further once an AI agent is the one running the query. An agent invoking a query tool without explicit FINAL gets back inflated counts with no error signal, and then reasons on top of that number as if it were ground truth. The input looks structurally sound. The inference built on it is wrong. The instinctive fix at this point is to reach for FINAL on every query, and that fix comes with its own cost.

What FINAL costs and when cheaper alternatives hold up

FINAL does what it promises. A SELECT with FINAL returns a properly deduplicated result, because the engine merges the relevant parts in memory at query time before handing back rows. On a table carrying many unmerged parts, that in-memory merge can make the query dramatically slower than a regular scan. FINAL is the right call when the backlog of unmerged parts is small, and a much heavier one once that backlog grows.

The database engine's own history reflects how seriously this cost is treated. Engineering work on FINAL's performance has appeared repeatedly in the codebase's pull requests, aimed specifically at closing the gap between a FINAL query and a plain scan. The repeated engineering work on this path confirms the overhead is real and worth working around.

Two query-time patterns handle most production cases without paying that cost. The first is argMax(col, version) ... GROUP BY key, which retrieves the value of any column at its highest version and works correctly even if no merge has run yet. The second is LIMIT 1 BY key ORDER BY version DESC, semantically equivalent to the first and sometimes faster depending on how the rest of the query is shaped. Either pattern gets the latest version of each key without forcing an in-memory merge across every unmerged part.

For workloads where read latency has to stay predictable and the latest-state view has to be correct at all times, a Materialized View backed by AggregatingMergeTree moves deduplication earlier, into the ingestion path itself. Every insert updates the view, and AggregatingMergeTree's own merge logic combines aggregate states correctly, so accurate results are available even before the base table's parts have merged. The price is write amplification: two copies of the data now exist, and every insert does extra work to keep the view current. That trade pays off for stable, high-read workloads, and pays off less for tables whose schema changes often, since every schema change now has to be carried through the view as well.

Running OPTIMIZE TABLE FINAL on a schedule, as a blanket correctness fix, is its own operational hazard on large tables. It can run for hours, and it blocks concurrent merges while it works. None of this is a query-tuning problem in isolation. Whether FINAL, argMax, or a Materialized View is the right tool depends heavily on choices made back at schema design time.

Schema decisions that determine whether deduplication works correctly

The ORDER BY clause is the deduplication key. Only rows that share identical ORDER BY values within a partition are even candidates for merging, so that key needs to include every column that defines a row's unique identity, not just the columns that happen to be convenient for filtering.

A common mistake is ordering by low-cardinality filter columns first and tacking the unique identifier on at the end as an afterthought. The reasoning should run the other way: the unique identifier is there to guarantee correctness, and its position in the key should reflect that priority, not get treated as a detail to bolt on later. Making this worse is a structural fact about the engine itself: ORDER BY columns in ReplacingMergeTree can't be removed or reordered once the table is created. The key can only be extended by appending new columns. A deduplication key that turns out to be wrong in a meaningful way can't be patched in place. It requires standing up a new table and migrating the data over, a cost that falls hardest on teams who find the mistake only after it's already in production.

The version column deserves its own scrutiny. It needs to be a value that always increases monotonically with each new version of a row, something like a timestamp sourced from the originating system or a genuine sequence number. A database-assigned insertion timestamp is a weaker substitute, and it can collide when inserts happen in parallel, which undermines the one mechanism the engine uses to decide which row wins.

Partitioning strategy has to match the shape of the key space. If the partitioning expression can place the same logical key into two different partitions, say, by partitioning on ingestion date when a single entity's records can arrive on different days, the resulting cross-partition duplicates become permanently unresolvable. No later cleanup step undoes that.

One more limitation belongs in any schema decision: ReplacingMergeTree discards every version except the winner at merge time. Any requirement to recover prior versions of a record, for audit purposes, for compliance, for change tracking, can't be met by this engine alone. That needs a separate history table or a different engine entirely, decided on up front rather than discovered after the old versions are already gone.

Pre-ingestion deduplication as an alternative to relying on merge-time collapse

Every duplicate event that reaches a ReplacingMergeTree table becomes a data part that eventually has to be merged. In a workload where a high proportion of incoming events are duplicates, that adds up to a continuous background load, consuming CPU, memory, and I/O, without ever advancing the actual state of the data.

Pre-ingestion deduplication sidesteps that cost by removing or collapsing duplicates in the pipeline before the INSERT happens. The table then receives only the latest version of each key, and merges run against genuinely new data instead of spending cycles resolving noise that never needed to exist in the table.

The trade-off sits in pipeline complexity. The deduplication logic now has to be stateful. It needs to know the current version of each key, and it needs to handle events that arrive out of order. That requires either a lookup store or a buffered window somewhere in the ingestion layer. That's real engineering work, and it's fair to ask whether the operational savings on the database side are worth the added complexity upstream. For CDC pipelines, the answer often leans toward yes, because the source system's change log frequently already carries a monotonic sequence number, one that can serve directly as the version column if the pipeline preserves it through to the point of insert.

Deciding between pre-ingestion deduplication and merge-time deduplication is a question of where the operational cost and resource profile sit, not a question of correctness. Both approaches can produce a correct final result. What differs is which part of the system absorbs the work, and which failure modes the team has to design around. Whichever side of that line a system lands on, the query layer and anything reading from the table still has to account for the window in which duplicates might be present, and that's an operating discipline, not a one-time schema choice.

Operating a ReplacingMergeTree table reliably in production

Understanding the engine's guarantees is the easy part. Building operational practice around the gap between insert and merge is where reliability actually gets decided.

Backups deserve particular care. Restoring from a backup brings back raw parts in whatever merge state they were in at the moment the backup was taken. If background merges had already collapsed certain versions before that point, those versions are gone for good, and the restore won't reconstruct them. A restore only reflects a guaranteed-deduplicated state if an OPTIMIZE was run before the backup was taken, so backup procedures need to account for merge state explicitly rather than assuming the restored table behaves like a clean snapshot.

Monitoring should treat part count and merge lag as primary signals, not secondary ones. A part count that keeps growing relative to the insert rate means merge pressure is building, and it means queries run under FINAL will keep getting more expensive over time. A part count that stays low and stable means the engine is keeping pace with inserts. Either number tells an operator something concrete about whether the system is in a healthy state.

Every downstream consumer of a ReplacingMergeTree table, whether that's a dashboard, a scheduled batch job, or an AI agent, needs to be built with explicit knowledge of whether it's reading potentially pre-merge data or guaranteed post-merge data. That knowledge should drive which query pattern gets used: FINAL, argMax, or a Materialized View, matched to what the consumer actually needs.

Agentic access raises a specific, nameable risk. An agent that queries without FINAL gets inflated counts back with no error signal, and it reasons on top of data that's structurally valid but factually wrong. The mitigation is concrete: bake explicit deduplication into the query template the agent uses, or point the agent at a pre-deduplicated Materialized View instead of the raw table, so correctness isn't something the agent has to remember to ask for. Agent queries against a table in this kind of eventual-consistency state should also be logged and auditable, because non-deterministic merge timing means the identical query can return different counts depending on when it runs. Reconstructing why an agent reached a given conclusion later requires a record of both the query and the table's merge state at that moment.

None of this makes ReplacingMergeTree an unreliable engine. It makes it an engine whose guarantees are precise and limited, and the teams that get consistent results from it are the ones who treat eventual deduplication as a design constraint from the first schema decision onward, not a detail to patch in after something breaks.