ClickHouse as a Real-Time Analytics Destination

ClickHouse has become a default choice for real-time analytics because of how it stores data: in immutable, sorted parts, written once and compacted later through background merge operations. That columnar layout is the entire reason queries run fast against billions of rows. It also means the engine was built around a specific assumption: that data arrives in bulk and stays put once it lands. Change Data Capture breaks that assumption at its foundation, because CDC produces a continuous stream of inserts, updates, and deletes, one row at a time, in the order a source database happened to commit them.

Writes in ClickHouse are append-only by design. ALTER TABLE ... UPDATE exists, but it rewrites entire data parts to apply a change, an operation suited to infrequent bulk corrections, not a steady drip of individual row mutations. Lightweight deletes, via DELETE FROM, work similarly: they mark rows with a deletion mask and leave the actual removal to background merges. Neither mechanism was built to absorb thousands of change events a second. Treating ClickHouse like a Postgres replica, issuing an UPDATE for every change event coming off a source database, makes the engine buckle under the write pattern long before it runs out of query speed.

The real engineering problem sits between what CDC produces and what ClickHouse prefers to receive. CDC hands you a row-by-row record of mutation. ClickHouse wants append-only, bulk-shaped writes. Closing that gap, not avoiding it, is what the rest of this piece works through: getting CDC data into ClickHouse correctly requires working with its merge architecture, not against it.

Why batch ETL fails this use case

Before any engine-level decision matters, the ingestion pattern itself has to be right, and batch ETL was never built for this. A batch job that polls a source database every hour, or even every fifteen minutes, hands an analytics layer a picture of the world that has already moved on by the time it arrives. Worse, any row that gets inserted and then deleted between two polling windows never appears in the record. It simply doesn't exist downstream, with no error, no gap flagged, nothing to signal that a transaction happened and vanished before anyone looked.

CDC closes that hole by reading the source database's own transaction log. Postgres writes to its Write-Ahead Log, MySQL to its binlog, MongoDB exposes change streams. Each commits a record of every change in the order it happened, so a CDC pipeline reading that log captures every committed transaction with no polling gap to fall through. A CDC pipeline feeding ClickHouse can bring the delay between a committed transaction on the source and a queryable row in ClickHouse to under five seconds.

That speed makes a class of use cases possible that batch ETL structurally cannot serve: attribution in A/B testing, dashboards customers look at directly, and inputs feeding AI agents that need current state. Each depends on analytical data reflecting what actually happened a few seconds ago, not what a batch window last managed to capture. Getting the ingestion pattern right is the precondition. Landing that stream inside ClickHouse without breaking the engine that makes it fast is the next question.

Choosing the right MergeTree engine for a CDC workload

Once CDC is the agreed foundation, the table engine choice becomes the single most consequential decision in the whole pipeline. Most teams reach for the merge-time deduplication engine, and for good reason, but the different engine options for this kind of workload each encode a different trade-off, and the choice has to be made deliberately.

ReplacingMergeTree deduplicates rows that share the same sorting key during background merges. A _version column, populated with a monotonically increasing LSN, a Kafka offset, or a millisecond timestamp, determines which of several versions of a row wins. The deduplication isn't instant: it happens when merges run, so between merges a table can legitimately hold several versions of the same row at once. Query design has to account for that fact.

Two query patterns handle this in practice. SELECT... FINAL forces an in-memory merge at query time and guarantees the latest state of every row, but the overhead grows with table size and can slow queries badly once a table reaches billions of rows. The argMax() pattern, by contrast, runs materially faster than the equivalent FINAL query on wide tables with many columns, and it's the pattern most production dashboards under load should rely on. The most common failure mode here is using FINAL everywhere without understanding that performance cliff: queries that feel fast at launch degrade steadily as the table grows, and by the time it's noticeable, it's already a production problem. A second common mistake is inserting one row at a time, which creates a new data part per row and overwhelms the merge scheduler that's supposed to be consolidating those parts in the background. For most teams, ReplacingMergeTree remains the practical default for CDC, simpler to operate than either collapsing variant.

CollapsingMergeTree takes a different approach: every CDC update inserts two rows, one with sign = -1 that cancels the prior state, and one with sign = 1 carrying the new state. It suits aggregation-heavy workloads well, but it requires the pipeline itself to carry knowledge of the previous row's state, which is a real operational burden, and it depends on the cancel row arriving after the state row in the same part order. Out-of-order or concurrent inserts, which are normal for any real streaming source, can leave rows that never collapse.

VersionedCollapsingMergeTree fixes the ordering dependency by adding a version column, making the collapse order-independent, the right choice when a pipeline cannot guarantee in-order delivery, which describes most real streaming sources. It's more operationally complex than ReplacingMergeTree, and that complexity is worth taking on only when the workload is heavily aggregation-oriented and correctness under concurrent writes genuinely matters.

| Engine | Dedup mechanism | Best query pattern | Operational cost | When to use | |---|---|---|---|---| | ReplacingMergeTree | Version column, resolved at merge time | argMax() over FINAL for large tables | Low | Default choice for most CDC workloads | | CollapsingMergeTree | Sign column (+1/-1 row pairs) | Aggregation queries | Medium, order-dependent | Aggregation-heavy, reliably ordered inserts | | VersionedCollapsingMergeTree | Sign + version column | Aggregation queries | High | Aggregation-heavy, out-of-order delivery |

For the large majority of CDC analytics workloads, ReplacingMergeTree paired with argMax() queries is the right starting point. VersionedCollapsingMergeTree is worth the added complexity only when aggregation semantics demand it and the team has the operational capacity to run it correctly.

Source-specific replication mechanics: Postgres, MySQL, and MongoDB

The engine decision only pays off if the data arriving at ClickHouse is correct and complete to begin with, and that depends on how each source database exposes its changes. Production CDC pipelines break more often in this extraction layer than in the ClickHouse landing layer, because each source has its own log format, its own failure modes, and its own blind spots.

Postgres writes every change to its Write-Ahead Log for crash recovery purposes, and CDC reads that log through a logical replication slot, replaying an ordered stream of changes as they commit. Three paths carry that stream into ClickHouse. The built-in MaterializedPostgreSQL engine breaks silently on DDL: a schema change on the source, such as adding or dropping a column or changing a type, causes replication to stop for the affected table, and ClickHouse detects this internally but surfaces no error and no metric. Other tables keep replicating, the slot keeps advancing, and the only visible symptom is that the numbers coming out of the affected table are wrong. A Kafka Connect path, using ClickHouse's Kafka engine, defaults to a single consumer thread per table (kafka_num_consumers = 1), though parallelism can be configured through kafka_num_consumers and kafka_thread_per_consumer; error handling in this path is opaque, and schema evolution requires manual DDL changes applied by hand. A managed option, ClickPipes, entered public beta in March 2025 and propagates DDL automatically: added columns just show up, and while some type changes require a resync, the pipeline keeps working.

The more dangerous risk in Postgres CDC is at the instance level, not the table level. A logical replication slot pins the WAL stream for the entire Postgres instance, not just the table being replicated. If the consumer on the other end stalls, whether the sink process dies or a task fails, Postgres keeps retaining WAL for the whole instance until disk fills up, at which point every database on that instance loses the ability to write. Postgres 17, released in 2024, addressed one major cause of this with failover slots, which let logical decoding resume cleanly after a standby gets promoted; before that fix, an inactive or lagging consumer causing WAL retention to exceed max_slot_wal_keep_size was the most common trigger for slot invalidation. ClickPipes supports Postgres 17 failover slots through a toggle, applicable only on Postgres 17 and above. A newer approach, WalShadow, consumes the physical WAL directly and removes the need for logical replication slots entirely, eliminating the WAL retention risk and reducing resource load on the source instance. It's a direction worth watching, not yet a production-standard recommendation today.

MySQL's native CDC connector for ClickPipes reached private preview in April 2025. It supports continuous replication and one-time migration from MySQL running on RDS, Aurora, CloudSQL, and on-premises deployments, though Azure Flexible Server is limited to one-time migration only. The connector builds in automatic schema change replication.

MongoDB CDC relies on the database's native Change Streams, which capture every document change with latencies as low as a few seconds and keep analytical data synchronized with the operational database in near real time. The harder problem with MongoDB is structural rather than one of latency: its documents tolerate type inconsistency within the same collection, so dates might appear as numbers in some records and ISO strings in others, and a nested field might be a plain string in one document and a complex struct in the next. A pipeline has to reconcile these inconsistencies before it can insert the data into ClickHouse's typed columns. Native JSON support in ClickHouse is currently in private preview for the MongoDB CDC connector, which points toward a more direct path for handling this kind of variability.

Schema evolution: the silent pipeline killer

Schema drift on the source database is the most common reason a working CDC pipeline eventually breaks, and whether a given approach survives that drift gracefully comes down to whether schema evolution was treated as a design requirement from the start. A source table changes shape constantly over the life of an application: columns get added, removed, retyped. The destination table in ClickHouse has to track those changes, or the pipeline breaks.

The failure modes differ by path. On a Kafka Connect or self-built pipeline, schema evolution requires manual DDL changes, so every schema change on the source becomes a manual intervention event somewhere in production, with a person required to notice it and apply the matching change downstream. On ClickPipes for Postgres, column additions propagate automatically. Removed columns are detected but not propagated: rows replicated after the drop get populated with NULL in that column. Changing a column's type isn't automatically propagated either and has to be applied by hand.

MongoDB adds a further layer to this problem. Because type inconsistency can exist within a single collection, document by document, schema evolution there is an ongoing structural negotiation that the pipeline has to normalize continuously, before any of it reaches ClickHouse's typed columns.

A schema change that depends on manual intervention is a production incident waiting for its moment, and automatic schema propagation belongs in the list of requirements for a CDC pipeline, not the list of conveniences. A managed replication service handles four things here that a self-built pipeline has to implement on its own: detecting DDL events inside the replication stream, translating those events into the equivalent ClickHouse DDL, executing that DDL without stalling the rest of the pipeline, and logging each change so it can be audited later.

Pipeline reliability: exactly-once semantics and deduplication

Exactly-once delivery at the transport layer gets treated as a non-negotiable requirement in most streaming system discussions, but inside a ClickHouse CDC pipeline it carries less weight than that reputation suggests. ReplacingMergeTree's deduplication logic already merges duplicate CDC events for the same record into the correct final state, which changes what delivery guarantee the pipeline actually needs to enforce upstream, a design assumption worth making on purpose rather than arriving at it by accident.

In upsert ingestion mode, deduplication runs against the source record's key. Given that, forcing exactly-once delivery at the transport layer adds a real performance cost without buying a matching correctness benefit, since duplicate events for the same record resolve to the same final state once ReplacingMergeTree merges them. At-least-once delivery, paired with ReplacingMergeTree's own deduplication, produces the same correct end state that exactly-once delivery would, at a lower operational cost.

That conclusion depends entirely on one condition holding: that the pipeline is in fact built on ReplacingMergeTree, or on an engine with equivalent merge-time deduplication, and that the version column driving that deduplication is populated correctly and consistently for every event. Swap in a table engine without merge-time deduplication, or get the version column wrong: the same at-least-once delivery that was harmless under the deduplicating engine turns into duplicate rows sitting permanently in the data. The reliability guarantee a CDC pipeline needs comes from the transport layer and destination engine working together, with the engine choice made earlier in the pipeline being what makes the lighter delivery guarantee safe to use.