ClickHouse ReplacingMergeTree for CDC Workloads

ReplacingMergeTree exists because CDC and columnar storage want two different things at the same time, and something has to give. A source database sends a steady drip of inserts, updates, and deletes, and ClickHouse's MergeTree family was built to append data fast, not to mutate rows in place at high volume. Rather than fighting that architecture, ReplacingMergeTree turns the whole problem into something simpler: every incoming change, whatever kind it is, just becomes a new row, and duplicates on the same sorting key get collapsed away later, in the background, during a merge. The table doesn't update. It accumulates, then quietly tidies itself up.

Plain MergeTree won't do this. It's append-only with no deduplication logic at all, so a CDC stream aimed at a MergeTree table just piles up every version of every row forever. ReplacingMergeTree adds exactly one capability on top of that: when rows share a sorting key, the merge process throws away all but one. That's the entire mechanism, and it's also the entire reason the engine gets picked for CDC over almost anything else in the ClickHouse catalog.

ClickHouse has shipped a real UPDATE statement in recent releases, so mutation-based updates are no longer theoretically off the table. But for CDC specifically, where the write volume is continuous and often bursty, ReplacingMergeTree still wins on throughput, and it's the pattern ClickHouse's own managed connectors are built around. ClickPipes uses it across every CDC source it supports: Postgres, MySQL, and MongoDB all land in ReplacingMergeTree tables. That's not a niche trick someone found in a forum post; it's the documented, supported path.

The cost of that throughput is eventual consistency, meaning what follows should be stated bluntly. A relational database UPDATE overwrites a row on the spot. ReplacingMergeTree doesn't; the old and new versions of a row sit side by side until a merge decides which one wins, and until that happens, both exist. Anyone querying the table has to know this going in. The rest of this piece is largely about that one fact and its consequences.

The canonical schema: sorting key, version column, and soft-delete flag

Three decisions in the table definition make this whole scheme actually work: the sorting key, the version column, and a flag for deletes⟑c6. Get any one of them wrong, and the table doesn't just misbehave a little. It quietly diverges from the source database and nobody notices until a report looks off.

Start with the sorting key. ORDER BY has to match the primary key of the source table, because rows only become deduplication candidates when they share that key. Widening the sorting key, even slightly, say by adding a column that changes across updates, stops duplicates from landing in the same bucket. They just sit there as separate rows forever, and the merge has nothing to collapse. This is one of the easier mistakes to make and one of the hardest to spot, because the table still runs; it's just wrong.

The version column is what tells ClickHouse which of two duplicate rows to keep. Skipping it means ClickHouse falls back to keeping whichever row was inserted last, which sounds fine until delivery isn't strictly ordered, or a message gets redelivered; at that point "last inserted" and "most recent change" stop being the same thing. Version values usually come straight from the source system, such as a monotonically increasing updated_at timestamp cast to epoch milliseconds, a Postgres LSN, or, for Delta Lake sources, the _commit_version integer that is used as the version key in its reference implementation. A working example seen in practice derives _version as a UInt64 from Debezium's metadata fields, __source_ts_ms for MySQL sources or __source_lsn for PostgreSQL ones.

Deletes need their own signal, because ClickHouse has no way to physically remove a row just because a delete event arrived. A _deleted UInt8 column, defaulting to 0, carries that information instead. Passing both arguments, ENGINE = ReplacingMergeTree(_version, _deleted), lets the merge process treat the highest-version row as a tombstone once _deleted flips to 1, and a FINAL query then filters it out. The row itself doesn't vanish at merge time, it just gets marked and hidden. Leaving that flag out means a delete followed later by a re-insert at a lower version can bring a record back from the dead, which is about as bad a failure mode as CDC has to offer.

Put together, a representative table might look like this: user_id UInt64, email String, name String, status LowCardinality(String), updated_at DateTime64(3), _version UInt64, _deleted UInt8 DEFAULT 0, with ENGINE = ReplacingMergeTree(_version, _deleted) ORDER BY user_id. Treat that as a reference shape rather than something to copy wholesale; partitioning choices, like PARTITION BY toYYYYMM(created_at), are a separate design question with their own guidance and don't change any of the logic above. Example DDL (composite):.

How source database change streams map onto this schema

The schema above stays the same no matter what feeds it. What changes, quite a lot, is the plumbing that gets a change event from a source database's log into that ReplacingMergeTree table, and each source has its own quirks and its own ways of breaking.

Postgres reads changes off its Write-Ahead Log through logical decoding, and that requires a replication slot dedicated to the consumer. The traditional open-source path carries two production hazards: silently cached schemas on the sink, since a schema change in Postgres is not automatically propagated through Debezium without configuration, and a replication slot that can fill the disk and freeze every database on the instance if the consumer falls behind. First, schema changes on the Postgres side don't automatically propagate through the pipeline; without explicit configuration, the sink just keeps using its cached schema, and new columns disappear into the void. Second, that replication slot doesn't forgive a slow consumer. If the pipeline falls behind, the replication slot can fill the disk and freeze every database on the instance, not just the one being replicated.

There's a managed alternative to assembling that stack by hand. PeerDB, acquired by ClickHouse in 2024, now powers ClickPipes' Postgres CDC connector, and it uses the same WAL logical-replication foundation while removing the separate message-broker layer entirely. Hundreds of companies move meaningful volumes of Postgres data through it every month. What doesn't change is the destination: a ReplacingMergeTree table configured exactly as described above.

MySQL's binary log (binlog) is the foundation of its replication architecture. ClickPipes implements CDC for MySQL databases as its native data integration solution in ClickHouse Cloud, and the same ReplacingMergeTree pattern used for Postgres CDC is used internally for MySQL CDC in ClickPipes.

MongoDB works differently again, using Change Streams to emit CDC events in something closer to real time. Syncing those into ClickHouse means analytical queries stop competing with the operational database for resources. Nested documents are replicated as ClickHouse's native JSON type by default, preserving the nested structure, and flattening is optional and can be done via views or materialized views if columnar access is preferred. The query pattern at the end is unchanged, though: FINAL, for the same deduplication reasons as everywhere else. ClickPipes has a dedicated MongoDB connector built on this.

Delta Lake is the newest entrant here, and it's fair to call it emerging rather than settled. Delta Lake's Change Data Feed (CDF) provides row-level change events, and ClickHouse's ClickPipes team investigated this and open-sourced a reference implementation in Python (MIT licensed), with production-grade support planned in ClickPipes in coming months. CDF's version key, _commit_version, maps onto ReplacingMergeTree's version argument about as cleanly as anything in this piece. Schema evolution poses a challenge: DDL events do not exist at the CDF protocol level, so CDF cannot represent a column rename, drop, or type change, and consuming it anyway can break the pipeline outright, forcing a full resync rather than a graceful catch-up.

One operational note applies across all of these sources: ClickHouse merges work better with larger batches, since too many small parts trigger "too many parts" errors, though at low throughput this matters a lot less because merges simply keep pace with the trickle of inserts. The Debezium Kafka Connect config in S5 sets plugin.name: pgoutput, transforms.unwrap.type: io.debezium.transforms.ExtractNewRecordState, and delete.tombstone.handling.mode: rewrite, and these translate delete tombstones into rows the materialized view can route into the _deleted flag.

The merge timing gap and query correctness

Most of the actual production incidents come from here. Merges run on ClickHouse's own schedule, not on the application's. At any given moment, a key can have several versions of itself sitting on disk as separate, unmerged parts. Query the table directly, without doing anything about it, and the result includes every one of those versions. Row counts look inflated. A metric that should be a single number comes back double- or triple-counted. Even after a delete event is ingested, the soft-deleted row continues to exist in the ClickHouse destination with its last values until a merge runs and the query filters it.

None of this is a defect. ClickHouse documents this outright as an eventually consistent table state, and the mistake isn't the engine's behavior, it's assuming the table is immediately consistent when it was never designed to be. The gap widens exactly when it matters most: under heavy CDC load, when insert rates outrun the merge scheduler, which is precisely the condition a busy production system creates. The timing gap is the routine condition to design around continuously. It's the default state of the table, and every query against it needs to assume duplicates are present until proven otherwise. The most common production correctness bug with ReplacingMergeTree is querying the table without accounting for unmerged duplicates and treating the result as ground truth.

Query-time deduplication: FINAL, argMax, and LIMIT BY compared

FINAL is the blunt instrument, and it works: SELECT * FROM users FINAL WHERE _deleted = 0 forces ClickHouse to do merge-equivalent deduplication right there at query time, so the result is correct no matter what state the background merges happen to be in. The tradeoff is that FINAL reads every relevant part and deduplicates in memory, which gets expensive on large tables carrying a lot of unmerged history. It's the right default for dashboards, point-in-time lookups, and anything where correctness isn't negotiable and the table isn't enormous, and it's the pattern recommended for MongoDB and Delta Lake CDC queries specifically.

argMax() takes a more surgical approach. Instead of deduplicating the whole row, argMax(email, _version) grouped by the sorting key pulls just the value that corresponds to the highest version, which is useful when only a few columns matter or when the query is already doing aggregation work. It's really the same idea as FINAL, expressed one column at a time rather than one row at a time. Either pattern can be wrapped in a view so downstream consumers don't have to remember the filter logic themselves, something like CREATE VIEW users_view AS SELECT... FROM users FINAL WHERE _deleted = 0.

The shape looks like: SELECT FROM (SELECT FROM users ORDER BY user_id, _version DESC LIMIT 1 BY user_id) WHERE _deleted = 0. It's also the pattern to reach for when reconstructing table state as of some past moment; swap the ORDER BY from _version to updated_at, and the same structure answers "what did this look like at time T" instead of "what does this look like now".

Whichever pattern gets used, the delete filter has to come after deduplication, never before. Filter on _deleted = 0 too early, and a delete event can get excluded from consideration before it's had the chance to win the deduplication in the first place, leaving a deleted record visible in the output. The correct sequence, for a soft delete, is: insert a row with _deleted = 1 at a version higher than anything before it, deduplicate, and only then apply the _deleted = 0 filter.

A fourth use case inverts the usual goal. Querying without FINAL and without any delete filter returns the entire change log for a key, every version that ever existed, and that's genuinely useful for audit trails. Compare each row's _version against min(_version) OVER (PARTITION BY user_id), check the _deleted flag, and each version can be labeled CREATED, UPDATED, or DELETED. The same duplication that causes correctness bugs everywhere else turns into a feature here. The subquery approach can perform better than FINAL when the outer query is highly selective, avoiding full-table deduplication by limiting rows per key before filtering. Handling deletes correctly in all three patterns:.

Schema evolution: the silent failure mode that breaks CDC pipelines in production

None of the table design or query patterns above protect against the source database's schema changing while the pipeline keeps running on the old assumptions, and that change is the failure mode that catches teams off guard most often. ReplacingMergeTree has nothing to say about it either way; whatever protection exists has to be built into the ingestion layer, upstream of the table entirely.

The scenario is familiar enough to be almost a cliché at this point, and it's still the way this fails in practice. A product team adds a column to a Postgres table on a Friday afternoon, doesn't tell anyone on the data side, and by Monday morning the ingestion job has either quietly dropped the new field on the floor or stopped running altogether. Nobody notices Friday, because nothing crashes Friday. The gap caused by the dropped field or dead pipeline becomes visible days later, as a report that's missing a field it should have, or a pipeline that's been dead over a weekend nobody was watching. Schema evolution is a routine event CDC pipelines regularly encounter. It's a routine event in any database under active development, and a pipeline that isn't built to expect it is a pipeline that's already broken, just not yet caught.