ClickHouse ReplacingMergeTree: append-only deduplication with FINAL and version columns

What ReplacingMergeTree actually guarantees

If you need one current row per business key in ClickHouse, ReplacingMergeTree is often the right engine, but only if you understand its boundary: it deduplicates during merges, not during INSERT. A query without FINAL can still see superseded rows because background merges are asynchronous.

The useful takeaway: use the ORDER BY key as the record identity, use a monotonic version column to choose the survivor, and use FINAL only when a query must immediately return one current row per key. Where aggregation is acceptable, argMax(payload, version) is usually cheaper than FINAL.

Worked example: one business key, two versions

Run the following in clickhouse-client or any SQL interface with CREATE and INSERT privileges on the target database. Replace events with your own table name.

CREATE TABLE events (
  account_id UInt64,
  event_id UInt64,
  version UInt64,
  payload String
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (account_id, event_id);

INSERT INTO events VALUES (1, 10, 1, 'old'), (1, 10, 2, 'new');

SELECT payload FROM events FINAL WHERE account_id = 1 AND event_id = 10;

The logical result is the payload from the higher version: 'new'. The same query without FINAL may return 'old', 'new', or both rows until a merge compacts the parts.

How the sorting key and version column interact

The ORDER BY expression defines the deduplication identity. Rows with the same sorting key are replacement candidates. The optional version parameter tells the engine which candidate should survive: higher versions win. If two rows have the same version, the survivor is unspecified, so exact correctness requires unique or deterministic versions.

This model fits append-only event streams and immutable facts. Ingestion stays simple: you insert a new version instead of updating in place. Merges later compact superseded versions. It is not a general-purpose mutable key-value store, and it does not enforce uniqueness at write time.

Check the behavior in a disposable environment

Use a throwaway database or table before relying on this pattern in production.

SELECT count() FROM events;
SELECT count() FROM events FINAL;

OPTIMIZE TABLE events FINAL;

SELECT count() FROM events;
SELECT count() FROM events FINAL;

Expected check: before the forced merge, the raw count may be 2 while the FINAL count is 1. After OPTIMIZE, the raw count may fall to 1, but the logical result should stay unchanged. Do not assume exact merge timing; background merges are not a correctness boundary.

OPTIMIZE changes on-disk part layout and requires the OPTIMIZE privilege. On large tables it can add I/O and CPU load. It has no row-level rollback; if you need to undo data changes, restore from backup or insert corrected rows with a higher version.

FINAL versus argMax versus raw queries

Approach	What it gives you	Main cost or risk
Raw query	Fastest read path, append-only ingestion	Can return superseded rows until merges happen
FINAL	Immediate logical row selection per sorting key	Higher CPU, memory, and I/O; benchmark with production-like part counts
GROUP BY with argMax(payload, version)	Newest value without FINAL	High-cardinality groups can consume substantial memory; equal versions need a tie policy

If your access pattern is aggregation, prefer the argMax form. If a query must return one exact current row per key and you cannot tolerate stale duplicates, FINAL is the direct tool, but measure it under realistic concurrency.

Limits and common mistakes

• Uniqueness is not enforced on insert. Duplicate rows can be visible immediately. Deduplication happens at merge time or query time with FINAL.
• Do not treat merge timing as correctness. Background merges are asynchronous. A query that must be correct now needs FINAL or an aggregation that explicitly selects the newest version.
• Equal versions are an unspecified tie. Increase version whenever the logical record changes, and make the value unique or deterministic when survivor selection matters.
• Keep volatile fields out of the sorting key. Only include columns that genuinely define record identity. Frequently changing fields in ORDER BY can fragment the deduplication identity.
• Verify cross-partition behavior. If one logical key can appear in multiple partitions, test the exact ORDER BY and PARTITION BY expressions in your deployed ClickHouse version before relying on cross-partition deduplication.
• Benchmark FINAL realistically. Tiny tables hide its cost. Inspect query duration and memory consumption with production-like cardinality, part counts, and concurrency.

Version assumptions and practical verification

This explanation assumes a recent stable ClickHouse release where ReplacingMergeTree(version) and FINAL are available. Merge scheduling and FINAL optimizations have changed across releases, so verify against your deployed version. The safest check is the disposable test above: create the table, insert both versions, compare raw and FINAL counts, force compaction with OPTIMIZE, and confirm the logical result does not change.