clickhouse ReplacingMergeTree duplicates handling

TL;DR

ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

The query

clickhouse ReplacingMergeTree duplicates handling

Use this when

• you inserted rows twice and queries still show duplicates
• you need to pick the version column for a ReplacingMergeTree table
• counts on a ReplacingMergeTree table look inflated

Not for

• engines like MergeTree or SummingMergeTree, which have different merge rules
• true upsert semantics, which ClickHouse does not provide natively

Steps

1. Define a version column (e.g. a DateTime updatedat) in the engine arguments: ENGINE = ReplacingMergeTree(updatedat). On a merge, the row with the max version survives. Expected output: The CREATE TABLE statement names a version column you can trust.

2. Expect duplicates right after insert. Merges happen in the background on the server's schedule, not yours. Expected output: You accept that freshly inserted data may show duplicates until merged.

3. For exact reads now, query with FINAL or aggregate: SELECT ... FINAL, or group by the key and take argMax(row, version). Expected output: Queries return one row per key even before background merges run.

4. Run OPTIMIZE TABLE ... FINAL only sparingly. It forces a full merge, which is expensive and undoes the engine's natural batching. Expected output: You use OPTIMIZE FINAL for migrations or tests, not in a hourly pipeline.

5. Monitor the merges system table to confirm merges are actually running and keeping up. Expected output: system.merges shows active merges, so you know dedupe is progressing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Thread query: clickhouse ReplacingMergeTree duplicates handling

TL;DR: ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.