What is ReplacingMergeTree and when do you use it vs regular MergeTree?

Think of a wiki where every edit creates a new page version rather than overwriting the old one. ReplacingMergeTree is like a background process that periodically goes through the wiki and removes old versions, keeping only the latest. But at any given moment between cleanups, you might see multiple versions of the same page coexisting. You need to explicitly ask for the 'latest version only' view.

ReplacingMergeTree solves the problem of mutable data in ClickHouse's append-only architecture. Since ClickHouse does not support traditional UPDATE statements (mutations exist but are async background operations), teams that need to maintain current state (user profiles, product catalog, device status) use ReplacingMergeTree. You INSERT the new version of a row with the same ORDER BY key values, and during background merges, ClickHouse keeps only the row with the highest version column value, discarding older duplicates.

Internally, deduplication only happens during part merges, not at INSERT time. When two parts are merged, if they contain rows with the same ORDER BY key, the row with the higher ver column (a UInt or DateTime column specified as the engine parameter) survives. Between merges, duplicate rows coexist in different parts. This means a simple SELECT may return multiple versions of the same entity. To get deduplicated results, use either SELECT...FINAL (forces a merge at read time, slower) or manually deduplicate with GROUP BY the key columns and argMax(column, ver) to pick the latest value.

At production scale, ReplacingMergeTree is the standard pattern for maintaining dimension tables (user profiles, product catalogs, configuration data) that receive updates. A CDC pipeline from PostgreSQL can stream INSERT/UPDATE events as new rows with incrementing version numbers. The table eventually converges to contain only the latest version of each entity. For large tables, the argMax pattern in queries is preferred over FINAL because it leverages ClickHouse's parallel execution rather than single-threaded merge-on-read.

The non-obvious gotcha is that ReplacingMergeTree only guarantees deduplication within a single partition. If the same ORDER BY key appears in two different partitions (due to different partition key values), both copies survive forever because ClickHouse never merges across partitions. This means your partition key must not divide rows that should be deduplicated. Also, FINAL performance degrades significantly on large tables because it forces single-threaded processing of the merge logic.