ReplacingMergeTree deduplication in ClickHouse

How ReplacingMergeTree deduplication works for updated rows in ClickHouse

Event and telemetry tables often receive the same logical record more than once: a retried agent batch, a corrected upstream event, or a status that changes after it was first written. The goal is one current row per entity, but ReplacingMergeTree does not remove duplicates when rows are inserted. It deduplicates rows that share the same sorting key during background merges, so duplicates can remain visible for a while. Teams usually need this engine when corrections arrive as new rows and the table is append-heavy. If updates are rare and targeted, a lightweight UPDATE or a mutation may be the better fit, and the sibling topic on updating and deleting data covers that trade-off.

The ORDER BY key is the deduplication identity. Two rows are candidates for replacement only when every column in the sorting key matches, so a key built from a timestamp or a random ID will never collapse updates to the same entity. When you declare a ver column, ClickHouse keeps the row with the highest version value among duplicates; without one, the last inserted row among the merged parts is kept, which depends on merge order. Confirm in the ClickHouse documentation for your version exactly how the engine chooses the surviving row, how is_deleted interacts with the version column, and whether your tables use the parameters you expect. Reproduce the behavior on a copy with representative duplicate patterns before relying on it.

Plan for the gap between insert and merge. Until merges complete, queries can see several versions of the same key, so dashboards need either FINAL at query time, with the read cost that adds, or queries written to tolerate duplicates. Run OPTIMIZE TABLE ... FINAL only as a deliberate, scheduled action on a specific partition, because it forces a full merge that is expensive on large tables. The ClickHouse documentation on ReplacingMergeTree is not the right page for this engine, so verify syntax and merge semantics against the official MergeTree reference for your release before you publish a design.

Which dedup approach fits your update pattern

Producers that retry after a timeout often send the same event twice. This is the pattern ReplacingMergeTree handles most directly, because rows that share the same sorting key collapse during background merges. The table's ORDER BY must therefore include every column that identifies one logical event, or distinct events can be merged together.

Fit: ReplacingMergeTree suits cases where a duplicate is a full copy of an earlier row and no version comparison is needed.
Key design: Include an event ID and any other identifying column in the sorting key. A key that is too coarse removes legitimate rows.
Visibility window: Duplicates can appear in query results until a merge runs, so use FINAL or a deduplicated view for counts that must be exact.
Upstream safeguards: Where the producer can assign a stable event ID, deduplicate at the broker or ingestion layer as well, so the table is not the only control.
Retry behavior: Confirm how your ClickHouse version handles deduplication of retried insert blocks on replicated tables before relying on it.

Check that the sorting key identifies one event by comparing the count of distinct keys with the count of logical events in a sample of raw input.

A correction arrives as a newer version of an existing record, such as a revised order status or a re-scored case. Here the version column carries the design. During a merge, ReplacingMergeTree keeps the row with the highest version value for each sorting key, so the newest correction wins regardless of arrival order. The version must increase monotonically for each record, which usually means a revision counter or an updated-at timestamp from the source system.

Version source: Use a revision number from the source where one exists. A timestamp works only if clocks are consistent across producers.
Ties: Rows with equal version values are resolved in ways you do not control, so define a tie-breaker in the source data.
Deleted markers: Some releases support a deleted-marker column with ReplacingMergeTree. Confirm support and its query behavior for your release before depending on it.
Read path: Queries that need the latest state use FINAL or an aggregation that selects the maximum version. Measure these on production-sized data, because their cost grows with the data scanned.
Stable sort key: The version column does not change the sorting key, so keep the key stable across corrections.

Check the design by replaying out-of-order corrections into a test table and confirming that the surviving row carries the highest version.

How the version column decides which row survives a merge

A ReplacingMergeTree table keeps one row per sorting key, and the choice of survivor happens only when background merges combine data parts. Without a version column, the row that was inserted last among the rows being merged is kept. With a version column, named in the table definition, the row with the highest value in that column wins, regardless of insert order. That matters when a correction arrives late, or when a retried batch lands after a newer one. Confirm the exact behavior, including how ties are resolved and which column types are accepted as the version, in the ReplacingMergeTree documentation for the release you run, because these details have changed across versions and should be tested rather than assumed.

The ORDER BY key is the deduplication identity. Two rows are duplicates only if every column in that key is equal, so a key that is too narrow merges distinct business events into one row, which is silent over-merging and shows up as missing data in reports. A key that is too wide, or that includes a timestamp or ingestion ID that differs between copies of the same event, leaves duplicates in place. Both failures pass every insert check, so the test is a query: compare row counts from a sample of source records against the table after forced merges, and check that the identity columns match the business definition of one record. The same key also has to serve the filters your dashboards use, which is covered in the ordering key topic. Choose the key with both goals in mind and test the change on a copy first.

Before you rely on is_deleted or a particular version column type, confirm both in the docs for your release. Record the chosen version column, the key, and the expected survivor rule in your schema change record, so an engineer reviewing a future migration can see why the table behaves as it does. Ask your team to name the owner of this decision, since changing the key later means rebuilding the table and rewriting the data pipeline that feeds it.

Configuration options and when each fits

This table compares the ReplacingMergeTree parameters and query-time settings that decide which rows survive and how fast reads run. Read each row as a decision: what the option controls, when it fits, and what it costs in merge timing or query work. The main takeaway is that the ORDER BY key defines identity, so the version and deletion columns only matter once that key is right.

Option	What it controls	When it fits	Trade-off on merge timing and query cost
Version column	Decides which row wins among rows sharing the same ORDER BY key when parts merge, keeping the highest value	Corrections or late-arriving updates where each row carries a monotonic version such as an update timestamp or revision number	Without it, the surviving row is not guaranteed to be the latest; a non-increasing version can keep stale data. Confirm the tie-break behavior for your version
Is_deleted column	Marks a row as a deletion marker so that, with FINAL or after merges, the key is removed from results when the marker is the winning row	Sources that emit delete events and need removed keys to disappear from reads	Deleted rows still occupy storage until merges run, and reads must filter or use FINAL to hide them. Confirm the supported setup for your version
ORDER BY as identity	Defines the deduplication key: rows with equal sort-key values are candidates to collapse into one	Entities with a stable natural or surrogate key, such as an order ID or device and metric pair	A key that is too wide or includes changing fields means duplicates never match. Changing it later requires rebuilding the table
PARTITION BY alignment	Limits which parts can merge together, since deduplication only happens within a partition	Partitions by time or tenant where all versions of a key land in the same partition	If an update for a key arrives in a different partition, both rows survive. Coarser partitions merge more but slow partition-level drops
FINAL modifier at query time	Forces merge-time deduplication logic during SELECT so results reflect the latest version before background merges finish	Reports or lookups that must be exact now, on tables with a bounded number of unmerged parts	Adds read cost that grows with unmerged data and is often slower on large scans. Test response time on representative queries before adopting it for dashboards
Background merge timing	Controls when duplicate rows are physically removed, which depends on merge scheduling rather than on each insert	Workloads where short-term duplicates are acceptable and reads can tolerate them or filter with a query pattern	Duplicates may be visible for an unpredictable window. Ask how to measure unmerged parts and merge lag for your ingest rate
OPTIMIZE FINAL	Triggers an immediate full merge of a partition or table on demand	One-off cleanup after a bulk correction, or a controlled maintenance window on a small table	Rewrites large volumes of data and competes with ingestion and queries. It should not be scheduled routinely, as the ClickHouse documentation advises against forcing merges as a habit
Settings for the ClickHouse version in use	Engine and session settings that alter FINAL behavior, merge selection and deduplication details	Any production table, after reviewing the release notes for the version you run	Defaults and behavior vary between releases. Confirm every default and setting name against the documentation for your version before you rely on it

Why FINAL is a query tool and OPTIMIZE FINAL should not be routine

ReplacingMergeTree removes duplicates during background merges, so the rows you read may not yet be deduplicated. The cards below cover the two ways teams force that deduplication, FINAL at query time and OPTIMIZE FINAL on a schedule, and the cheaper patterns that often serve better.

What FINAL does at read time

The FINAL modifier merges rows with the same sorting key while the query runs, so results are correct before background merges finish. The cost grows with the number of parts and rows the query touches, and it is paid on every query. Check the query plan and rows read on a copy of production data before putting FINAL under dashboards that run every few seconds.

Why scheduled OPTIMIZE FINAL hurts

OPTIMIZE FINAL forces a merge of parts within each partition, which rewrites large data parts and uses CPU, disk I/O and merge slots that ingestion and queries also need. Running it on a timer repeats that rewrite on data that has mostly settled already. On a large table, a single run can slow inserts and dashboards for its whole duration, so confirm the cost on a staging cluster first.

Treat OPTIMIZE FINAL as a repair step

Use OPTIMIZE FINAL when a specific partition needs a one-time cleanup, for example after a bulk correction or a backfill that wrote many duplicate versions. Scope it to that partition, run it off-peak, and measure the result with row counts before and after. It should be a documented runbook step, not a job in the ingestion pipeline.

Query-side alternatives with argMax

Many analytical reads only need the latest version of each key. A GROUP BY on the sorting key with argMax(column, version) returns the newest value per key without the merge work of FINAL. This works well for aggregates and dashboards, but it needs a version column that increases on each update and a query pattern that filters on the key.

Replicas and merges on the same table

On ReplicatedMergeTree tables, each replica performs its own merges, coordinated through ClickHouse Keeper, so a forced merge on one node adds work across the replica set. Deduplication results should match on every replica once merges complete, but compare row counts across replicas after a repair. Plan repair runs with the same capacity review you would use for a rolling upgrade.

Setting up a ReplacingMergeTree table safely

This sequence takes a ReplacingMergeTree table from an agreed identity key and version rule to a replicated production table with a tested backfill, so each duplicate and correction behaves the same way before real data depends on it. Each step ends with a check that the table is doing what the business expects.

Agree the identity key and version column
The data owner and the engineering lead agree which columns make a row unique (the ORDER BY key of the table) and which column decides the winning row when duplicates arrive, such as an updated_at timestamp or a monotonically increasing revision number. Write both down with the rule for ties, and confirm that the version column is never null for incoming rows. Done means a signed-off one-page definition that names every column used and the expected behavior for a late or repeated event. Ask the owner to confirm the rule with a few real examples from source systems.
Create the table on a pre-production copy
The platform team creates the ReplacingMergeTree table on a pre-production ClickHouse cluster that matches production's version and cluster layout, using the agreed ORDER BY key and version column. Review the ordering key against the queries you expect to run, since the deduplication key and the filter pattern both depend on it. Done means the DDL is stored in version control and reviewed, and the table reports the expected engine and sorting key in system.tables. Confirm the server version against the ClickHouse production FAQ before you start.
Load representative data with known duplicates
Load a sample drawn from production that includes repeated keys, out-of-order versions, and a few deliberate corrections with higher version values. Keep a separate expected-results file that lists, for each test key, the row that should win. Done means the table holds every test case, and the expected-results file is checked into the same repository as the DDL. Include a case where a lower version arrives after a higher one, because that is the case most often mishandled.
Compare row counts with and without FINAL
Run the same count and value queries once with FINAL and once without, then compare both against the expected-results file. Before merges run, the plain query will usually show more rows than the deduplicated answer, and FINAL should match the expected winners exactly. Done means FINAL returns one row per test key with the correct version, and you have recorded the plain-query difference so the team understands what consumers see before merges complete. Note the query cost of FINAL on the sample as a baseline.
Observe merge behavior under realistic load
Replay a realistic insert rate into the copy while background merges run, and watch the number of active parts and the time merges take in system.parts and system.merges. Confirm that duplicates disappear through normal background merges without a manual OPTIMIZE. Done means the part count stays within the range your monitoring allows, and a plain query without FINAL converges to the expected winners after merges finish. Do not treat OPTIMIZE FINAL as a fix for a slow merge; investigate the cause first.
Confirm replicated table creation on the cluster
If the production design uses replicas, create the table as ReplicatedReplacingMergeTree with a ZooKeeper or ClickHouse Keeper path and replica name, then check that every replica lists the table and that inserts on one replica appear on the others. ClickHouse Keeper is the coordination service for ReplicatedMergeTree tables, and the replication guide recommends at least three Keeper nodes in production. Done means a test insert on one replica is visible on all replicas, and a recovered replica catches up without manual steps. Confirm the Keeper topology with your platform team before this step.
Roll to production with a tested backfill plan
Agree the cutover order with consumers, creating the production table, pointing the ingest pipeline at it, and loading historical data in controlled batches, ideally by partition, so that each batch can be checked and repeated. Take a backup before the backfill starts and confirm that a restore test has been run on a spare cluster, since a backup that was never restored may not restore when needed. Done means row counts and FINAL-based spot checks match the expected results for sampled keys, the backup is restorable, and the rollback plan has been rehearsed.

Security and operational considerations for deduplicated tables

A ReplacingMergeTree table holds more than the current state of each record: superseded versions remain on disk until background merges remove them. That makes access, retention, backups, merge health and key changes operational decisions, not afterthoughts. Check each card against your own data classification and recovery targets before the table goes into production.

Restrict access to corrected records

Old versions of a corrected row stay readable until a merge removes them, so a user who can query the base table can often see values that were later superseded. Create dedicated roles for readers and writers, and apply row policies where some teams should see only part of the data. Restrict the default user, which starts with all permissions, and confirm that FINAL queries and direct reads of the table follow the same policies. Test this with a read-only account, not an administrator.

Plan retention for superseded rows

Until merges run, superseded versions are physically present, so erasure requests and retention limits must cover them as well as the latest version. A deletion of the current row does not remove older copies from parts that have not merged yet. Ask your team to define when a merge is guaranteed to have happened for a given partition, and to document how a TTL or erasure job confirms that old versions are gone. Illustrative: a customer record corrected three times has four physical rows until its parts merge.

Restore-test every backup

Replication keeps replicas in sync, but it copies corruption and accidental deletes along with good data, so it does not replace backups. Use BACKUP and RESTORE for full and incremental copies, and store them outside the cluster. The ClickHouse documentation warns that a backup never test-restored will probably not restore properly when needed. Schedule restores into a spare cluster, compare row counts and version selection against the source, and record how long the restore took.

Monitor merge backlog and part counts

Deduplication only happens when parts merge, so a growing backlog means duplicate rows stay visible to every query and FINAL costs more. Track the number of active parts per partition, the merge queue and the time since the oldest unmerged part. Set alerts before part counts reach levels that slow inserts. Use these signals to tune ingestion batch size rather than forcing OPTIMIZE FINAL on a schedule, which rewrites large parts and adds load during business hours.

Control changes to the ordering key

The ORDER BY key defines both how rows are sorted for deduplication and which columns identify a duplicate. Changing it on a populated table does not rewrite existing data, so old and new layouts can coexist and produce unexpected results. Treat any key change as a migration: test it on a copy with representative data, confirm FINAL results match the expected record count, and schedule the cutover with a rollback copy. Record who approves the change and why.

Questions teams ask before changing their dedup approach

What does it cost to move from ad hoc deduplication to ReplacingMergeTree?

The cost comes mostly from people time: reviewing the table schema, confirming how duplicates are identified, rewriting queries that assume unique rows, and testing merge behavior on representative data. Costs rise with the number of tables and dashboards that read them, the data volume you need to replay or backfill, and how many query paths must change. Ask any partner for a quote built from a written scope that lists tables, query inventory, the test environment and the number of ClickHouse releases to validate against. One-time work covers schema changes, migration and testing. Recurring work covers monitoring and re-validation after upgrades, which is easy to miss in a first estimate.

What drives the effort in a deduplication change?

Four factors shape the effort most. The first is the schema review: the ORDER BY key determines which rows ReplacingMergeTree treats as duplicates, so a wrong key silently keeps or drops data. The second is data volume, which affects how long a copy takes and how much merge work follows. The third is the query rewrite: each query that needs current rows must be checked for FINAL, argMax patterns or filters on the version column. The fourth is merge tuning and pre-production testing, where you confirm how quickly duplicates disappear under your insert pattern. Each factor grows with the number of tables and the variety of update patterns in your data.

How long does validation take before we can switch production traffic?

No fixed duration can be given without your data and query inventory, so treat any number from a vendor as a starting point to test. Validation runs in stages: a schema review, a load of representative data into a pre-production cluster, query comparison against your current results, and a window in which you observe merges and read latency. The ClickHouse production guidance recommends a pre-production environment with representative data and queries before production changes, so plan validation around that environment. Ask for a written timeline that names the exit criteria for each stage.

What must our team provide before validation starts?

Your team provides four inputs. Sample data should cover the full range of update patterns, including late-arriving corrections and repeated events. Identity rules must state which columns define a logical row, which is the ORDER BY key in a ReplacingMergeTree table. Version semantics must explain how the newest row is chosen, such as a timestamp, a sequence number or an ingestion counter, and what happens when two versions tie. The query inventory lists every dashboard, API and report that reads the table, with its expected result. Missing any of these inputs usually adds rework later.

Can our own engineers make this change without a partner?

Yes, many teams do, provided they have someone who understands MergeTree sorting, merge behavior and how their queries read the table. The work is well defined: pick the key, choose the version column, test on a copy and rewrite affected queries. The risks are in the details, such as a version column that is not monotonic or a key that does not match real identity. A partner is most useful when the change spans many tables, when production data cannot be used for testing, or when the team lacks capacity to run the comparison. Either way, the same written checks apply.

Will using FINAL slow our dashboards?

Yes, it can, and the cost depends on the query and the number of unmerged parts. FINAL forces deduplication at read time, so it adds work on every query that uses it, and dashboards that run often can feel the load. Measure it on your own data by comparing query duration and rows read with and without FINAL against a representative copy. Where the cost is too high, options include narrowing the time range, pre-aggregating with materialized views, or accepting that some reads are eventually consistent. Avoid running OPTIMIZE FINAL as a routine fix, because it forces heavy merges that compete with ingestion.

How do we confirm the behavior for our ClickHouse release?

Test the exact release you run in production, because merge and deduplication details can change between versions. The ClickHouse production guidance recommends reading the changelog before an upgrade, since minor backward-incompatible changes can occur. Check the supported versions in the security policy, then run your representative test set on that release and record the results. Repeat the test before each upgrade. Your evidence should show that row counts, latest-version selection and query results match the expected output for your data.

Limits to confirm in your environment

ReplacingMergeTree deduplicates only when background merges run, so the results you see depend on timing, version columns, replication and the ClickHouse release you run. The cards below list the limits to verify against the docs for your release and against a copy of your own data before production queries depend on them.

Duplicates visible before merges

Rows with the same sorting key stay in the table until a background merge combines their parts. Until then, a plain SELECT can return several versions of one logical row. Measure how long duplicates persist under your insert rate, and decide whether dashboards and exports can tolerate them or need FINAL or a rebuilt query. Test this on a copy with your real insert pattern before you set expectations.

Version column type and ordering

The version column decides which row survives a merge, so its type and ordering rules must be confirmed in the reference for your release. Check how ties are resolved when two rows share a version, and whether a missing or null version value is accepted. Also confirm that the column is part of the update stream you actually send, such as a monotonic timestamp or sequence number.

Replicated tables and Keeper issues

On ReplicatedMergeTree tables, merges and replica state are coordinated through ClickHouse Keeper. Confirm how a Keeper outage or a lagging replica affects when duplicates are removed, and whether replicas converge to the same visible rows after recovery. Plan a check that compares row counts from each replica after a failover test.

FINAL with other query features

FINAL applies deduplication at read time, but its interaction with sampling, joins, PREWHERE, projections and parallel reads can change results or cost. Test each query shape your dashboards use, and compare counts with and without FINAL. Confirm which settings your release applies to FINAL queries before you standardize on them.

Support status for your version

Deduplication behavior and bug fixes depend on the release line you run. Check the security policy for which versions receive security updates, and confirm that your chosen line still receives fixes when your production schedule runs. Read the changelog for backward-incompatible changes before any upgrade that touches merge or deduplication behavior.

Performance on very large parts

Merging very large parts and reading with FINAL both consume CPU, memory and disk I/O, and the cost grows with the size of the parts and the number of versions per key. Measure merge duration and query latency on production-sized partitions, and confirm that merges keep up with ingestion. Schedule any manual optimization carefully, because forcing merges is rarely a routine fix.

Keeping deduplication behavior stable across ClickHouse releases

Treat every ClickHouse upgrade as a possible change to your deduplication results and re-run your dedup checks before and after it. The ClickHouse production FAQ says stable releases ship roughly monthly, LTS releases ship twice a year and are supported for a year after release, and minor backward-incompatible changes can occur, so the release you run today is not guaranteed to merge, collapse or return FINAL results exactly as the next one does. The security policy lists versions 26.9, 26.8, 26.7 and 26.3 as currently supported with security updates, and all other versions as unsupported, so confirm your running version against that list before you plan the next step.

Build a short verification set for your ReplacingMergeTree tables and run it on a pre-production cluster loaded with representative data and queries. Record the row counts returned by FINAL queries for a known set of keys, the number of duplicate keys remaining after a fixed merge window, and the results of any reports that depend on the version column. Compare these outputs between the current and target releases. Read the changelog for each release in the path, paying attention to changes to merge behavior, FINAL handling and version column semantics, and confirm the exact release names on the ClickHouse releases page before you schedule the window.

Repeat the same checks on production after the upgrade, and keep the pre-upgrade counts so you have a baseline to compare against. Because replicated tables merge independently on each replica, also confirm that the replicas agree on the deduplicated results once the upgrade has finished. If a check fails, stop the rollout, treat the difference as a correctness incident rather than a performance issue, and keep the pre-upgrade backup available until the results are confirmed. Agree the ownership of these checks, their frequency and the rollback criteria with your data owners before the first upgrade, not during the incident.

How DeployOpen scopes and delivers ClickHouse schema work

DeployOpen approaches ReplacingMergeTree deduplication as a schema decision that needs to be tested against your real update patterns before production tables change. We scope the table design, version column, query pattern and operating rules in writing, then build and verify them in a non-production environment that holds representative data.

How we scope

The written scope lists each table that needs deduplication, its ordering key and the duplicate or update patterns it receives.
We agree which version column defines the winning row and how ties and deletes are handled.
Query inventory covers which dashboards and reports must use FINAL or an equivalent read pattern, with their expected latency.

How we build

We create ReplacingMergeTree tables in a pre-production ClickHouse cluster, loaded with representative data and replayed update events.
Test cases check duplicate counts before and after background merges, and compare FINAL results against deduplicated reference queries.
We measure the effect of FINAL on the agreed queries and document where a materialized view or a rewritten query is the better option.

How we hand over

Deliverables include table DDL, insert and version column conventions, query guidance and a runbook for merge and duplicate checks.
We document why OPTIMIZE FINAL is kept for exceptional cases and which monitoring signals would justify running it.
Your team receives a walkthrough and a test script that can be rerun after any schema change.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer, which can cover schema reviews as query patterns change.
Engagements are fixed-scope, so changes to tables or query patterns are agreed in writing before work starts.
Confirm with your team which ClickHouse release line you run, since supported versions are listed in the project security policy.