ClickHouse ReplacingMergeTree Duplicates: Resolving CDC Deduplication Lag in Production

A ClickHouse CDC pipeline can be completely caught up and still return the wrong answer.

That sounds contradictory until you separate two different facts: the change event may have arrived at the destination, while the old physical row is still present in the table. If the query does not apply the right replacement semantics, a count can include both versions. A deleted row can still appear. A dashboard can look stale even though Kafka offsets and connector checkpoints are healthy.

This is the production trap behind many reports of “ClickHouse duplicates.” The problem is not always a broken connector, and it is not always slow merging. Sometimes the source identity is wrong. Sometimes versions tie. Sometimes a delete event cannot reproduce the same key as the original row. Sometimes the data is correct for an append-only history table but incorrect for a current-state dashboard.

This guide builds a practical model for finding the difference. It focuses on PostgreSQL or MySQL CDC flowing through tools such as Debezium and Kafka into ClickHouse, but the reasoning applies to other CDC systems as well.

What does ReplacingMergeTree actually replace?

ReplacingMergeTree stores inserted rows in parts and uses background merges to consolidate those parts. During a merge, rows with the same values for every column in the table’s ORDER BY expression are treated as candidates for replacement. If a version column is configured, the row with the greatest version wins.

That definition contains the first important distinction:

The table can accept a newer version immediately without physically removing the older version immediately.

The ClickHouse ReplacingMergeTree engine reference explicitly describes replacement as a merge-time behavior and warns that background merges happen asynchronously. The detailed ReplacingMergeTree guide makes the operational consequence clear: a table may contain duplicate versions at any moment.

So there are two states to keep separate:

• Physical state: the parts currently stored on disk, which may contain several versions of a logical row.
• Logical state: the current row a query should return after applying version and delete rules.

A plain SELECT does not automatically promise the second state just because the table uses ReplacingMergeTree.

The identity is ORDER BY, not simply PRIMARY KEY

A common design mistake is to assume that the ClickHouse PRIMARY KEY defines the replacement identity. In MergeTree-family tables, the primary key controls the sparse index. It may be a prefix of ORDER BY. Replacement uses the complete sorting-key tuple.

Imagine a source table with a stable business key called customer_id. This design can deduplicate correctly for that identity:

SQL — illustrative schema; validate against your ClickHouse release:

CREATE TABLE analytics.customer_state
(
    tenant_id UInt64,
    customer_id UInt64,
    email String,
    status LowCardinality(String),
    version UInt64,
    is_deleted UInt8
)
ENGINE = ReplacingMergeTree(version, is_deleted)
ORDER BY (tenant_id, customer_id);

But if an update changes a value that was accidentally included in ORDER BY, ClickHouse sees a new identity. It does not understand that the application intended to update the old row. The result is two logical keys instead of one updated key.

This is why the ClickHouse CDC deduplication documentation emphasizes an immutable ordering key. Put stable identity columns in ORDER BY; keep mutable attributes out of the identity unless you deliberately want a new logical row.

Before tuning merges, write down the exact ORDER BY tuple. If the update and delete events cannot reproduce that tuple, no merge setting can repair the model.

Why CDC deduplication lag happens

“Deduplication lag” is not one metric. In production, it is usually a combination of four freshness dimensions:

A healthy connector proves only the first dimension. It does not prove that a plain query has one row per source key.

The event timeline

The following timeline is a useful mental model when investigating a duplicate report:

The contrarian point is worth stating plainly: a merge backlog is not automatically a data-integrity incident. If the application reads historical events, duplicates may be intentional. If it needs one current row per key, the same backlog becomes a semantic correctness problem. The right response depends on the serving contract.

Version columns: the difference between “latest” and “last inserted”

Without a version column, the winning row depends on insertion and part order. That can be acceptable for a tightly controlled append stream, but it is a weak foundation for a CDC pipeline that can retry, replay, reorder, or fan in events.

With ReplacingMergeTree(version), the greatest version wins among rows that share the same ORDER BY identity. The version must represent a meaningful order for every operation, including deletes. A timestamp is not automatically safe: two events can share the same timestamp, clocks can move, and connector retries can deliver an older event after a newer one.

A database log position, transaction sequence, or connector sequence can be a better candidate when its ordering semantics are known. In its PostgreSQL CDC material, ClickHouse uses PostgreSQL logical-decoding source.lsn as an example of a version. That example should not be treated as a universal contract for every connector.

Equal versions are another subtle failure mode. If two rows have the same maximum version, the physical insertion order becomes relevant. In a distributed or replay-heavy pipeline, that tie-breaker may not match business intent.

A practical version checklist

Before trusting a version column, answer these questions:

1. Is it ordered per logical source key, or only globally?
2. Can two events for the same key share the value?
3. Does the delete event receive a version greater than the row it deletes?
4. Can a retry deliver an older version after a newer one?
5. Is the value preserved across replay and backfill?
6. Do all replicas and consumers interpret it the same way?

If the answer to any of these is unclear, label the design as unresolved rather than calling it exactly-once deduplication.

Deletes are tombstones, not disappearing rows

CDC deletes are often represented by inserting a newer row with a delete flag. With the is_deleted form of ReplacingMergeTree, the highest-version tombstone wins and is retained by ordinary merges.

That retention is intentional. If the tombstone disappeared immediately, a delayed older version could arrive later and make the deleted entity visible again. The tombstone acts as memory: it tells future merges that older states are no longer current.

The usual read pattern therefore filters the winning deleted state:

SQL — current-state read pattern:

SELECT
    tenant_id,
    customer_id,
    email,
    status
FROM analytics.customer_state FINAL
WHERE is_deleted = 0;

The delete event must contain the same identity columns used in ORDER BY. This is easy to miss when the source connector emits a delete payload with only a subset of the old row. If the tombstone has a different identity, it cannot replace the original row.

For PostgreSQL, source-side replica identity matters when a delete needs key columns that are not the primary key. The exact setting and connector behavior must be checked against the source and connector versions; do not assume that every CDC tool reconstructs missing identity fields automatically.

Why cleanup is dangerous

OPTIMIZE ... FINAL CLEANUP is not an ordinary “remove old duplicates” command. The official cleanup guidance requires confidence that old versions will not arrive later and that replicas are synchronized. Otherwise, a late old version can be retained as if it were current because the tombstone that protected the row has already been removed.

The ClickHouse settings reference also makes cleanup availability version-sensitive. Treat cleanup as a controlled maintenance procedure with a documented gate, not as an automated reaction to every duplicate count.

Warning: Never turn tombstone cleanup into a blind cron job. First prove the source retention window, connector replay behavior, replica state, and absence of late events. If those conditions cannot be proved, keep tombstones and filter them at the serving boundary.

FINAL vs OPTIMIZE FINAL: similar words, different jobs

These two commands are often conflated because both contain “FINAL.” Their operational roles are different.

The ClickHouse best-practice guidance on avoiding OPTIMIZE FINAL warns that forced final merges can create substantial decompression, recompression, memory, and storage work. That does not make the command unusable. It means the command belongs in a maintenance plan, not in the default response to a dashboard showing duplicates.

A good production design often accepts query-time FINAL for a narrow current-state workload, while using a separately refreshed serving table or view for high-volume dashboards. The correct choice depends on scan width, update rate, freshness target, and capacity. Do not copy a benchmark from another workload and call it a guarantee.

A safe production diagnosis workflow

When someone reports duplicate rows, resist the urge to run a forced merge first. Use evidence to locate the layer that is failing.

Step 1: Prove that the event arrived

Compare the source checkpoint, connector offsets, delivery timestamps, and the maximum version visible in ClickHouse. If the new event never arrived, this is a delivery or connector problem, not a merge problem.

For Kafka-based ingestion, inspect the connector’s offset and retry metrics alongside the ClickHouse Kafka table or ingestion logs. The Kafka table engine documentation describes the relevant ingestion and offset concepts, but connector-specific retry and ordering behavior still needs to be checked in the connector documentation.

Step 2: Prove the identity and version

Pick one affected source key and retrieve every physical version. Look for:

• Missing identity columns.
• An update that changed an ORDER BY column.
• A delete with a different key shape.
• A version that moved backward.
• Equal maximum versions.
• A repeated event that is identical versus a repeated event with conflicting payloads.

A small, targeted query is more useful than a broad OPTIMIZE because it tells you whether the stored records can logically converge at all.

SQL — targeted inspection; replace columns and key values:

SELECT
    tenant_id,
    customer_id,
    version,
    is_deleted,
    _part,
    _part_uuid
FROM analytics.customer_state
WHERE tenant_id = 42
  AND customer_id = 781
ORDER BY version, _part;

The system columns available and the exact diagnostic fields should be checked against your ClickHouse version. The example is a documentation-guided investigation pattern, not a locally executed benchmark.

Read-only diagnostic query pack

The following examples are intentionally narrow. Replace the database, table, key columns, and version column, then validate the available system-table columns against your ClickHouse build before putting them into monitoring.

SQL — find logical keys with more than one physical row:

SELECT
    tenant_id,
    customer_id,
    count() AS physical_rows,
    min(version) AS oldest_version,
    max(version) AS newest_version,
    countIf(is_deleted = 1) AS tombstones
FROM analytics.customer_state
GROUP BY tenant_id, customer_id
HAVING physical_rows > 1
ORDER BY physical_rows DESC
LIMIT 100;

SQL — compare a plain read with a current-state read:

SELECT 'plain' AS read_mode, count()
FROM analytics.customer_state
WHERE tenant_id = 42
UNION ALL
SELECT 'final' AS read_mode, count()
FROM analytics.customer_state FINAL
WHERE tenant_id = 42
  AND is_deleted = 0;

SQL — inspect active merges:

SELECT
    database,
    table,
    elapsed,
    progress,
    num_parts,
    result_part_name,
    total_size_bytes_compressed
FROM system.merges
ORDER BY elapsed DESC;

SQL — inspect active parts by partition:

SELECT
    database,
    table,
    partition,
    count() AS active_parts,
    sum(rows) AS rows,
    sum(bytes_on_disk) AS bytes_on_disk,
    max(level) AS max_level
FROM system.parts
WHERE active
  AND database = 'analytics'
  AND table = 'customer_state'
GROUP BY database, table, partition
ORDER BY active_parts DESC;

These queries answer different questions. The duplicate-group query tests logical convergence. The plain-versus-FINAL comparison tests semantic freshness. system.merges and system.parts test storage pressure. Add system.replicas and system.replication_queue checks when the table is replicated. A single query cannot identify every layer.

Step 3: Inspect merges, parts, and replication queues

Use ClickHouse system tables to determine whether the problem is active work, accumulated parts, replication delay, or an error state. Useful areas include:

• system.merges for active merge work.
• system.parts for active parts, levels, sizes, and partition distribution.
• system.replicas for replica delay and queue state.
• system.replication_queue for failed or pending replicated operations.
• system.query_log for expensive or failing FINAL queries.

The exact columns differ by release and deployment mode, so validate diagnostic SQL against the system tables documentation. Do not build an alert on a column copied from an old blog without checking its current reference.

Step 4: Check the partition boundary

Background merging is partition-local. If versions of the same logical key can land in different partitions, ordinary merges cannot bring them together. A partition key that changes with mutable business data is therefore a correctness risk, not just a performance choice.

The partition key should normally be stable for a logical row. If the workload permits the same key to span partitions, query-time FINAL and partition-related settings require extra care. Current ClickHouse releases also have version-sensitive behavior around partition pruning during FINAL, so review the setting reference before changing defaults during an upgrade.

Step 5: Choose the correctness boundary

There are three defensible patterns:

1. Query-time correctness: use FINAL for current-state queries where the workload can afford it.
2. Refreshed serving layer: periodically materialize a current-state table or view designed for the dashboard workload.
3. Upstream stateful deduplication: use a stream processor when the product needs a strict, continuously maintained current state before ClickHouse.

None is universally best. The decision depends on whether the table is an event history, a current-state store, or both. A useful architecture often keeps the raw CDC history and exposes a separate current-state serving path rather than forcing every query to solve deduplication independently.

CDC schema design that survives retries and replays

A production CDC table should be designed around the source row’s identity, not around the shape of one sample event.

Stable identity

Include every column needed to identify the logical row. In a multi-tenant system that may mean (tenant_id, source_primary_key). The tuple must remain stable across updates, and delete messages must carry enough information to reproduce it.

A meaningful version

Use a source or connector sequence whose ordering semantics are understood. For PostgreSQL, an LSN may be appropriate in a specific architecture. For MySQL, binlog file and position, GTID, or a connector-provided sequence may be relevant, but the exact mapping must be verified with the connector documentation. Never assume that a wall-clock updated_at field is a total order.

Explicit deletes

Represent a delete with the same identity and a higher version. Keep the tombstone until the system can prove that old events cannot return. Filter it at the current-state read boundary.

Idempotent replay expectations

CDC systems often retry. A retry is harmless only if the repeated event has a stable identity and deterministic version semantics. If a retry receives a new version every time, the pipeline may create an endless stream of logically equivalent “new” rows. That is not the same as idempotent ingestion.

Materialized views are not merge observers

A materialized view reacts to inserted blocks. It does not automatically wake up later because a background merge replaced two old rows. If a design assumes that an MV can compare the final logical row after every merge, it is mixing insert-time behavior with merge-time behavior.

This distinction is one of the most useful production corrections to make early. The pipeline may need an explicit current-state refresh rather than an MV that assumes storage merges are application events.

Symptom-to-action decision matrix

Use this matrix to choose the first investigation, not as a substitute for checking the deployed version and connector.

Common mistakes that create duplicate-looking data

Mistake 1: Treating an acknowledged insert as an update

ClickHouse accepted a new version, but the old physical version can remain. Fix the read contract or wait for controlled merges; do not infer correctness from insert acknowledgement.

Mistake 2: Using mutable columns in ORDER BY

If a customer changes region and region is part of the sorting key, the event may become a new logical identity. Keep mutable attributes out of the identity.

Mistake 3: Using timestamps without testing ties and regressions

Timestamps are easy to produce and hard to reason about under concurrency. Test same-timestamp events, clock skew, replay, and delete ordering before relying on them.

Mistake 4: Sending incomplete delete payloads

A tombstone that lacks one identity column cannot replace the old state. Configure source-side identity exposure and connector mapping deliberately.

Mistake 5: Running OPTIMIZE FINAL as an alert handler

A forced merge may reduce visible duplicates while creating a larger resource incident. Investigate parts, queues, and workload pressure first.

Mistake 6: Cleaning tombstones before the replay window closes

This removes the protection that prevents an old update from resurrecting a deleted row. Cleanup needs an explicit source-retention and replica-synchronization policy.

Mistake 7: Applying FINAL after aggregation

The placement matters. For a current-state count, apply replacement to the table before aggregation:

SQL — current-state aggregation:

SELECT
    status,
    count()
FROM analytics.customer_state FINAL
WHERE is_deleted = 0
GROUP BY status;

The query still assumes that the identity, version, and tombstone model are correct. FINAL cannot repair a bad key or an ambiguous version stream.

Before and after: changing the operational model

Before

A team sees duplicate customers in a dashboard. It runs OPTIMIZE TABLE ... FINAL, watches the count drop, and closes the incident. A week later, the same query becomes slow, the source connector replays an older event, and a deleted customer reappears.

The team treated physical compaction as the correctness guarantee.

After

The team defines the serving contract first. It validates the source identity and version, checks whether the delete contains the full key, separates connector freshness from merge freshness, and uses FINAL or a refreshed current-state layer for queries that require one row per key. Cleanup is gated by replay and replica conditions.

The team no longer asks “How quickly does ClickHouse merge?” as its only question. It asks “Which freshness boundary does this product promise, and where is that promise enforced?”

That is the durable improvement. Faster merges can help, but they are not a substitute for a correctness boundary.

When not to use ReplacingMergeTree

ReplacingMergeTree is a strong fit for append-oriented change streams and current-state queries that can define their replacement semantics. It is not a universal substitute for a transactional uniqueness constraint or a fully managed state store.

Consider another design, or add a separate serving layer, when:

• The application needs immediate uniqueness enforcement at write time.
• Late, out-of-order events cannot be bounded or represented with a reliable version.
• The workload requires frequent broad scans that cannot afford query-time replacement.
• The data is a high-value current state and there is no capacity for reconciliation or refresh.
• Updates can change the logical identity and the source cannot emit a tombstone for the old identity.
• The table is being used simultaneously as an immutable event history and as a low-latency current-state API without an explicit contract for either view.

The alternative is not automatically “deduplicate upstream.” Stateful stream processing introduces its own state, checkpoint, replay, and recovery requirements. The useful decision is to choose where correctness is enforced and make that boundary observable.

Production observability checklist

Track the following as separate signals rather than collapsing them into one “CDC lag” number:

• Source commit-to-event time.
• Connector checkpoint and retry age.
• Maximum source version versus maximum ClickHouse version.
• Replication queue size and oldest queue item age.
• Active merge count and merge duration.
• Number, size, and level of active parts by partition.
• Query latency and memory for FINAL reads.
• Count of visible duplicate identities in a sampled audit query.
• Count and age of winning tombstones.
• Reconciliation results for a known set of source keys.

A useful audit does not need to scan the entire table on every interval. Sample keys, partition ranges, or business-critical entities, then run a deeper reconciliation during controlled windows.

The most meaningful alert is often not “parts are high.” It is “current-state reconciliation has exceeded the product’s freshness objective.” That connects infrastructure behavior to user-visible correctness.

Documented examples versus company case studies

Public ClickHouse documentation provides strong implementation examples, including PostgreSQL logical decoding, Debezium/Kafka transformations, versioned rows, and delete markers. These are useful documented patterns, but they are not independently measured customer case studies.

The public research reviewed for this article did not provide enough verified, comparable production metrics to claim that a named company reduced duplicate lag by a specific percentage or achieved a particular throughput. That evidence gap matters. A premium technical article should not manufacture a success story just to satisfy a case-study template.

The more honest production lesson is this: ClickHouse’s own CDC examples demonstrate the mechanics, while independent practitioner discussions repeatedly surface the operational questions, replay, ordering, FINAL cost, partition boundaries, and tombstone handling, that must be tested in the reader’s environment. Use the official PostgreSQL CDC series as a starting point, then validate connector-specific behavior with a replay test and a representative workload.

For broader architecture context, Vertex Frontier’s Postgres CDC in Production guide covers failover, retries, and duplicate-handling concerns before the data reaches ClickHouse. The Debezium vs. Estuary Flow vs. MaterializedPostgreSQL comparison is a natural companion when choosing the CDC path itself rather than diagnosing the ClickHouse table.

A practical test plan before production rollout

Run this as a repeatable validation exercise, not as a one-time happy-path demo.

1. Insert one source row and confirm its identity and version in ClickHouse.
2. Update a mutable attribute and confirm that ORDER BY remains unchanged.
3. Deliver the same event twice and compare physical rows with the current-state result.
4. Deliver an older event after a newer event and confirm the winner.
5. Delete the row and confirm the tombstone has the same identity and a greater version.
6. Replay an old pre-delete event and confirm it does not resurrect the row.
7. Move events across connector restarts and partitions if the architecture allows it.
8. Test a large partition under realistic write pressure and query concurrency.
9. Compare plain reads, FINAL reads, and the intended serving layer.
10. Document the ClickHouse and connector versions, schema, settings, workload, and observed results.

If the test has no measured workload, do not publish a performance number. Publish the test method and the conditions that readers need to reproduce.

Final perspective

The reliable way to solve ClickHouse ReplacingMergeTree duplicates is not to chase the fastest merge. It is to define what “correct” means for the reader, then trace that promise backward through the system.

First prove that the event arrived. Then prove that updates and deletes share one immutable identity. Prove that the version is ordered and that retries cannot create ambiguous winners. Inspect replication queues, parts, and merges. Finally, enforce current-state semantics with FINAL or a deliberately designed serving layer when the product cannot wait for background compaction.

For teams building the wider analytical platform, Vertex Frontier’s MySQL CDC and Apache Doris architecture article provides a useful adjacent comparison: CDC delivery and analytical serving are separate design decisions. Its Apache Iceberg guide is also relevant when deciding whether the system needs a table layer, a query engine, or both.

ReplacingMergeTree is powerful precisely because it separates ingestion from compaction. But that separation moves responsibility to the architecture. Once the team treats identity, ordering, delivery, merges, and query semantics as separate contracts, “duplicate lag” stops being a mysterious ClickHouse failure and becomes a diagnosable production state.

The deduplication, late-arrival, and tombstone reasoning in this article is also a common interview topic — our Data Engineering Interview Questions & Answers guide covers the same trade-offs under the CDC and streaming sections.

Frequently asked questions

About The Author

A Gadallh

Ahmed Gadallah is the Founder and Editor of Vertex Frontier, where he publishes research-driven articles on AI, data science, cloud computing, cybersecurity, software engineering, and emerging technologies, with a focus on technical accuracy, clarity, and practical insights.

View all articles by A Gadallh →