How eventual is eventual? Measuring ReplacingMergeTree dedup

The question

The ClickHouse docs are honest about it. ReplacingMergeTree removes rows with the same sorting key, but "data deduplication occurs only during a merge. Merging occurs in the background at an unknown time, so you can't plan for it." And a few lines later: it "does not guarantee the absence of duplicates".

Most people read "eventual", nod, and use the engine as if it was a unique constraint. I did that too for some time. What nobody publishes is the shape of that "eventual": after one minute, one hour, one day, how many copies are still there? I got the chance to measure it on production tables with billions of rows, and the answer was slower than I expected.

A clean experiment, by accident

For a while, one consumer in our pipeline wrote every event twice. Two workers read the same Kafka partitions from the same offset (the code used assign() where it should use subscribe(), so they never joined a consumer group), and a cron line that the scheduler silently ignored made both start in the same second. The duplicate factor at insert was exactly 2.0.

Bad for the data. For measuring, it was the perfect setup: I knew the start (2.0), I knew the right answer (1.0), and everything in between was the merge doing its job. Each copy went in a separate insert, so in a separate part, which is the normal case for a retransmission too.

The curve

Duplicate factor is rows divided by distinct sorting keys, by age of the data at the moment of reading:

Production, August 2026, insert factor 2.0. The row counts I kept are only the ones for 10 minutes and 1 hour.

Read it like this: two minutes after the write nothing has merged yet. After ten minutes, 78% of the keys still have their copy. After one hour, about one row in ten is still a duplicate. After a full day it's 1%, and after a month 4 rows in 10,000 are still there.

Why so slow in the end of the curve? My reading, and I didn't measure this part: merges prefer parts of similar size, so small fresh parts merge fast and the big old parts merge rarely. A part that reaches the size limit for merges (max_bytes_to_merge_at_max_space_in_pool, 150 GiB by default) is not picked anymore, so two copies sitting in two big parts can stay together for a long time. Rows in different partitions never merge at all, it's the rule of the engine.

In a normal moment the same table shows the same thing, in small. One hour of data read about one day later: 2,300,003 rows and 2,295,138 distinct uid, which is 0.21% still duplicated, right where the curve says it should be.

Measure it on your table

This gives the factor by age bucket. Use your own sorting key inside uniqExact, and keep the window short, because uniqExact on billions of rows is a heavy query:

SELECT
    multiIf(age < 600,   '0-10 min',
            age < 3600,  '10-60 min',
            age < 86400, '1-24 h',
                         '1-3 days')                    AS bucket,
    count()                                             AS rows,
    uniqExact(started_at, order_id, device_id)          AS keys,
    round(rows / keys, 4)                               AS dup_factor
FROM
(
    SELECT started_at, order_id, device_id,
           dateDiff('second', imported_at, now())       AS age
    FROM playback_log
    WHERE imported_at >= now() - INTERVAL 3 DAY
)
GROUP BY bucket
ORDER BY min(age);

If your table has no import timestamp, add one (DEFAULT now('UTC') costs almost nothing with DoubleDelta). Without it you can't tell how old a duplicate is, and the curve is the whole point.

The cost of reading before the merge

In our case the rollup job ran every 10 minutes. At ten minutes the factor is 1.78. The job counted physical rows from the ReplacingMergeTree table and wrote the totals into a SummingMergeTree, and a sum doesn't un-sum when the source merges later, so the raw table healed itself and the rollup kept the error forever.

After we fixed the writer and recomputed the history, the two tables got close, and stayed apart by a small amount anyway:

Both tables are right about what they do with that 0.5% to 2.8%. One engine collapses by key, the other one sums what it saw at the moment of the read, and they will disagree on the same data. No setting closes that; you choose which semantics the number needs and you build the rollup for it.

For the rollup itself, this is the change I recommended:

INSERT INTO plays_daily
SELECT toDate(started_at) AS day, order_id, device_id, count() AS plays
FROM playback_log FINAL
WHERE imported_at >= {from:DateTime}
  AND imported_at <  {to:DateTime}
GROUP BY day, order_id, device_id;

FINAL collapses at read time, so the rollup sees one row per key even if the merge didn't happen. It costs CPU, so test it on your heaviest window. Two details on the same query: the window is >= and <, never > on both sides, because a row exactly on the boundary falls in no window at all (we had that one too). And FINAL only fixes copies that are in the table when the rollup runs. A retransmission that arrives three days later lands in a different window, and only the layers before the insert can stop it.

Five layers, and the engine is the last one

The MySQL we used before ClickHouse guaranteed that a log line was unique. It was slow and it guaranteed. When we moved the audit data to ClickHouse we lost that for free, and we rebuilt it outside the database, in layers. This was the design:

1. The device generates the id. A UUID v4 per event, created at the moment the event happens on the screen.
2. The device keeps the event until the server acks. If the upload fails and it resends, the same uid comes back, so the identity survives the retry.
3. Dedup before the insert. The importer drops uids it saw recently, inside the batch and against a small table of recent keys.
4. ReplacingMergeTree as the safety net for whatever passes the first three.
5. A column TTL on uid. The row lives one year, the uid lives one month, because after 30 days a retransmission doesn't arrive anymore.

Layer 3 has this shape, simplified:

-- recent keys only; small and short-lived
CREATE TABLE seen_uids
(
    uid      UUID,
    seen_at  DateTime DEFAULT now('UTC')
)
ENGINE = ReplacingMergeTree
ORDER BY uid
TTL seen_at + INTERVAL 7 DAY;

-- the batch goes in only with uids nobody saw
INSERT INTO playback_log
SELECT b.*
FROM import_batch AS b
LEFT ANTI JOIN seen_uids AS s USING (uid);

INSERT INTO seen_uids (uid) SELECT DISTINCT uid FROM import_batch;

And layer 5 is one line in the DDL. I measured it doing its job: before day 30 the column was 100% filled, on the boundary day 50.6%, after that 0%. So the window where you can still audit duplicates is exactly 30 days, it's the other side of saving that space.

uid UUID CODEC(ZSTD(1)) TTL started_at + toIntervalMonth(1),

Layers go away quietly

Here is the part I didn't like to find. In an audit this year I read the importer line by line and layer 3 was off. It was switched off in a change two years before, and the comment above the code still said it ran. In the same audit: old players that don't send a uid got one generated on the server, on every parse, so a resent file looked like new events and layer 4 had nothing to collapse.

Nobody noticed any of this for two years, because the net below caught most of it. That's what a safety net is for and it's also the risk: a net that works well hides the layer above it that died. Test each layer by itself. The query above with an alert on it does the job, for example a duplicate factor above 1.01 for data older than one day, which by the curve should not happen.

Checklist

• Generate the id where the event happens. An id created on the server at parse time is a new id for every retry.
• Send the ack only after the data is safe on your side, and keep the same id on resend.
• Dedup before the insert. Count on the engine only for what escapes.
• Read recent data with FINAL, LIMIT 1 BY key, or GROUP BY on the key. Raw count() on fresh data counts copies.
• Don't feed a SummingMergeTree from a ReplacingMergeTree read before the merge. Read with FINAL, or summarize only data older than the age your curve says is safe enough.
• Keep a timestamp of import and measure your own curve. Mine depends of our part sizes and merge load; yours will be different.

Filed under the problem it solves

• clickhouse
• replacingmergetree
• deduplication
• eventual consistency
• idempotency
• column ttl
• reliability