ClickHouse ReplacingMergeTree Duplicates: Why They Stay and How to Read Correctly

ClickHouse ReplacingMergeTree duplicates are one of the most common surprises for teams coming from PostgreSQL or MySQL. You pick the engine because you want upserts, you insert a new version of a row, and the old version is still there the next day. Your dashboard shows double revenue. Nothing is broken: this is how the engine is designed to work.

The documentation puts it plainly: "Data deduplication occurs only during a merge", merges run "in the background at an unknown time", and the engine "does not guarantee the absence of duplicates". Once you accept that, the job splits into two parts: query in a way that is correct before merges happen, and design the table so that merges can actually remove the duplicates.

Why duplicates stay

A row is replaced only when a background merge reads both versions and writes one. Anything that stops the two versions from meeting in one merge keeps the duplicate alive:

• The merge has not happened yet. New inserts create new parts. ClickHouse merges them when it decides to, not when you insert. Recent data almost always contains duplicates.
• The key is not what you think. "Uniqueness of rows is determined by the ORDER BY table section, not PRIMARY KEY." If ORDER BY is (customer_id, order_id) and a new version changes customer_id, those are two different rows forever.
• The versions are in different partitions. In the MergeTree docs: "Parts belonging to different partitions are not merged." If the partition expression depends on a column that changes between versions, for example PARTITION BY toYYYYMM(updated_at), every update that crosses a month boundary leaves a permanent duplicate. The ReplacingMergeTree guide asks you to make sure the partition key does not change for a row.
• The versions are on different shards. A merge only sees the parts of the local table on one server. If your Distributed table shards by something random, two versions of the same key can land on different shards and will never be merged. Shard by an expression of the deduplication key.
• The parts are too big to merge. Background merges are capped by max_bytes_to_merge_at_max_space_in_pool, which is 150 GiB by default in current source. Once old parts reach that size, they stop being merged with each other, and duplicates between them can stay for a long time.

Check how many duplicates you really have

-- how many duplicates are still physically there?
SELECT count() AS rows, uniqExact(order_id) AS keys, rows - keys AS extra_versions
FROM orders;

-- where are they? (same key in several parts or partitions)
SELECT order_id, count() AS versions, groupUniqArray(_partition_id) AS partitions
FROM orders
GROUP BY order_id
HAVING versions > 1
ORDER BY versions DESC
LIMIT 20;

extra_versions tells you how much the table would shrink after a full merge, and how wrong a plain count() is right now. The second query shows whether duplicates sit in the same partition (they will probably be merged later) or across partitions (they never will). On a large table, run it on one partition at a time.

Choosing ver and is_deleted

The engine takes two optional parameters. ver is a version column of type UInt*, Date, DateTime or DateTime64. With it, the row with the highest version wins; without it, the last inserted row wins. Use a real version, such as an update timestamp from the source or a log sequence number from CDC. "Last inserted" depends on the order in which batches arrive, and replays or backfills will happily overwrite new data with old.

is_deleted is a UInt8 column where 1 means the row is deleted. The docs state that it "can only be enabled when ver is used". To delete a row, you insert a new version of it with a higher ver and is_deleted = 1. By default the delete marker itself is kept after a merge, so that an older version arriving later cannot resurrect the row. Queries with FINAL do not return rows whose latest version is a delete.

A table that looks right but has the partition problem:

CREATE TABLE orders
(
    order_id   UInt64,
    status     LowCardinality(String),
    amount     Decimal(18, 2),
    updated_at DateTime64(3),
    is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(updated_at, is_deleted)
PARTITION BY toYYYYMM(updated_at)   -- changes with every new version
ORDER BY order_id;

An order created in October and updated in November now has one version in each monthly partition, and they will never be merged. A safer version partitions by an immutable column and puts that same expression at the start of the sorting key:

CREATE TABLE orders
(
    order_id   UInt64,
    created_at DateTime,
    status     LowCardinality(String),
    amount     Decimal(18, 2),
    updated_at DateTime64(3),
    is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(updated_at, is_deleted)
PARTITION BY toYYYYMM(created_at)
ORDER BY (toYYYYMM(created_at), order_id);

Now a row cannot move between partitions, and since version 23.12 ClickHouse avoids merging across partitions at query time when the partition key is a prefix of the sorting key, according to the guide. That makes FINAL cheaper as well. The guide also recommends keeping ORDER BY columns immutable and adding the source primary key at the end.

Three ways to read correct results

-- 1. FINAL: merge at query time
SELECT status, sum(amount)
FROM orders FINAL
WHERE created_at >= '2026-11-01'
GROUP BY status;

-- 2. argMax: keep the latest version yourself
SELECT order_id,
       argMax(status, updated_at) AS status,
       argMax(amount, updated_at) AS amount
FROM orders
GROUP BY order_id
HAVING argMax(is_deleted, updated_at) = 0;

-- 3. LIMIT BY: one row per key
SELECT *
FROM orders
ORDER BY order_id, updated_at DESC
LIMIT 1 BY order_id;

• FINAL merges the data in memory at query time. It costs extra CPU and memory and runs slower than the same query without it, but it is the simplest correct option and much cheaper than forcing merges with OPTIMIZE. Filter on key columns in WHERE to reduce the data that has to be deduplicated. The guide notes that PREWHERE is not used with FINAL.
• argMax with GROUP BY gives you explicit control and works well in aggregations and materialized views. You must list every column, and you must filter delete markers yourself.
• LIMIT 1 BY is handy for small result sets and debugging. On a big table it sorts a lot of data, so do not build dashboards on it.

If every query against a table needs FINAL, the final setting applies it automatically for a session or user profile. For partitioned tables, do_not_merge_across_partitions_select_final = 1 makes FINAL process partitions independently. Use it only when versions of a row can never be in different partitions, otherwise you get duplicates back.

OPTIMIZE FINAL and CLEANUP: use sparingly

OPTIMIZE TABLE ... FINAL forces a merge. The docs warn that it "will read and write a large amount of data", and the guide adds that it can be memory-intensive and may need repeating as new data arrives. It also ignores the normal merge size limit, so a single command can rewrite a whole partition of hundreds of gigabytes. Run it per partition, on old partitions that no longer receive writes, at quiet hours, and only when you need the disk space back.

Delete markers are never removed by normal merges. To drop them, the docs require the experimental setting allow_experimental_replacing_merge_with_cleanup and either a manual OPTIMIZE ... FINAL CLEANUP or automatic forced merges of old partitions through min_age_to_force_merge_seconds and related settings:

ALTER TABLE orders MODIFY SETTING allow_experimental_replacing_merge_with_cleanup = 1;

OPTIMIZE TABLE orders PARTITION 202610 FINAL CLEANUP;

Only clean up partitions where late, out-of-order versions can no longer arrive; otherwise an old version can come back to life. If you need real deletes rather than markers, compare the options in our guide to deleting rows in ClickHouse.

What changes on terabyte-scale tables

• Duplicates in old, large parts can last for months. Once parts reach the merge size limit, the engine stops combining them. On a multi-terabyte table, measure extra_versions per partition and assume that only FINAL or your own deduplication in queries makes results correct.
• FINAL cost grows with the data it touches. On a small table it is barely visible. On billions of rows a query without a filter on the key or partition may read far more than you expect. Partition by an immutable time column, keep the partition expression as a prefix of ORDER BY, and always filter by it.
• Forced merges compete with inserts. An OPTIMIZE FINAL on a large partition uses disk bandwidth, CPU and temporary space that background merges of new data also need. If those fall behind, you end up with "too many parts" errors on inserts.
• Sharding mistakes are expensive to fix. Moving to a sharding key that includes the deduplication key means re-distributing the data. Get it right before the table grows.
• Materialized views see raw inserts. A materialized view on top of a ReplacingMergeTree source gets every version, not the deduplicated result. Aggregates built from it count each version unless you design for that.