Choosing Between ReplacingMergeTree and CollapsingMergeTree for ClickHouse Deduplication

Decision and constraints

When you need to deduplicate high‑volume streaming inserts in ClickHouse, the choice of table engine determines how duplicates are removed during background merges. The decision hinges on what information your ingest pipeline can reliably provide.

Supported options

Engine	Key requirement	Typical use case	Merge behavior	Storage overhead
ReplacingMergeTree	A monotonically increasing version column (e.g., event_time)	Keep the latest version of a record identified by a key	During merge, keeps the row with the maximum version value for each key	Low – only the extra version column
CollapsingMergeTree	A sign column containing +1 for insert and -1 for delete	Model update/delete as insert/cancel pairs	During merge, rows with same key and opposite signs cancel each other	Low – only the extra sign column

Trade‑offs

• ReplacingMergeTree works when you can attach a timestamp or version that always grows; it does not require application‑generated delete events, but stale duplicates may linger until a merge runs.
• CollapsingMergeTree gives immediate storage reclamation for update/delete patterns because matching +1/-1 pairs are removed during merges, yet it depends on the pipeline emitting correct sign values and cannot handle cases where only inserts arrive.

Concrete implementation

ReplacingMergeTree example

CREATE TABLE events_replacing (
    event_id   UInt64,
    event_time DateTime,
    payload    String
) ENGINE = ReplacingMergeTree(event_time)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_id);

Insert duplicate keys with different event_time values; after a background merge the row with the greatest event_time remains.

CollapsingMergeTree example

CREATE TABLE events_collapsing (
    event_id UInt64,
    sign     Int8,          -- +1 insert, -1 delete
    event_time DateTime,
    payload  String
) ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_id);

To delete a row, insert a new row with the same event_id and sign = -1. Matching pairs disappear during merges.

Validation steps

1. Insert test data: for ReplacingMergeTree, two rows with same event_id and increasing event_time; for CollapsingMergeTree, insert a +1 row then a -1 row with identical key.
2. Wait a few seconds or force a merge with OPTIMIZE TABLE … FINAL (note: this rewrites data and may be heavy on large tables).
3. Query the table: SELECT event_id, count() FROM events_replacing GROUP BY event_id HAVING count() > 1; should return zero rows. Same for events_collapsing.
4. Check merge activity: SELECT count() FROM system.merges WHERE is_running = 1; – a non‑zero value indicates merges are progressing.

Limitations and practical checks

Both engines rely on background merge throughput. If ingest outpaces merges, you may see temporary duplicates. Monitor system.merges and the parts_to_merge metric; sustained growth signals a merge bottleneck.

Changing the version or sign column after creation requires recreating the table or migrating data, because the engine semantics are bound to those columns.