cdc

How it is built

Architecture overview

A change data capture (CDC) pipeline turns a database's own record of what happened into a stream of events other systems can consume: row inserted, row updated (before and after), row deleted, in commit order. The reason to build one is the problem of keeping several stores in step: the primary database, a search index, a cache, a warehouse, another service's read model. The two obvious solutions both fail. Dual writes (the application writes to both stores) break whenever one write succeeds and the other does not; distributed transactions across heterogeneous systems are rarely available and slow. CDC avoids both by reading a single source of truth, the database's commit log, after the fact. The log already holds every committed change in order, so the pipeline needs no change to the application and sees every writer, including batch jobs and manual fixes. Debezium is the best-known open implementation; it typically runs inside Apache Kafka Connect and writes to Kafka topics (source: Debezium documentation, architecture).

Parts:

• Log reader — decodes the database log into row-change events with a resume position — Change data capture, built on Write-ahead log, Consumer offsets, Message sequence numbers and Incremental sync cursors
• Snapshot and backfill — copies the rows that existed before the log starts, and re-copies a table on demand — Chunked backfill
• Position tracking — remembers how far the pipeline has read, so a restart resumes exactly there — Consumer offsets
• Schema handling — keeps events readable when columns change — schema-evolution (no page yet), protobuf-wire-format (no page yet)
• Delivery guarantee — makes repeats harmless or invisible — exactly-once-processing (no page yet), built on Idempotency keys, Event deduplication and Two-phase commit
• Outbox — lets applications publish events atomically with their own writes — Transactional outbox
• Retention and compaction — keeps the stream replayable without keeping every version of every row forever — log-compaction (no page yet), built on Segmented log and Swiss table

 application ──writes──► database ──► write-ahead log / binlog
                          │ (outbox table rows are ordinary rows)        │
                          │                                              ▼
                          │                     log reader (replication slot, position = LSN / binlog offset)
   snapshot / backfill ◄──┘  select in chunks         │  decode: schema at the time of each change
        │  (interleaved with the log)                 ▼
        └───────────────────────────────► event stream: topic per table, key = primary key
                                                      │  at-least-once, ordered per key
                       ┌──────────────┬───────────────┼────────────────┐
                       ▼              ▼               ▼                ▼
                 search index      cache          warehouse     other services (idempotent consumers)

There are two products of one design: the capture side (one reader per source database, correctness of order and position matters most) and the consumer side (many independent subscribers, each tracks its own offsets and must tolerate repeats).

How the parts fit together

Log reader

Every serious database writes changes to a log before it touches data pages (the Write-ahead log), so crash recovery and replication can replay them. Change data capture reads that log. In PostgreSQL this is logical decoding: the server decodes the write-ahead log into an application-level form, and a replication slot represents a stream of changes that can be replayed to a client in the order they were made, persisting independently of the connection and surviving crashes (source: PostgreSQL documentation, logical decoding concepts). In MySQL the reader consumes the binary log. Each change has a position (an LSN in PostgreSQL, a file and offset in the binlog) and the reader stores the last position it has handed on; that is the Consumer offsets contract, and it is what makes restarts safe. Positions also act as Message sequence numbers: a consumer that sees a gap knows something was lost, and one that sees a repeat can skip it. A consumer that wants only the changes after a point it already holds uses an Incremental sync cursor, an opaque token that stands for a log position.

Two details of the source matter. PostgreSQL stores a slot's position only at checkpoint, so after a crash it may go back to an earlier LSN and send recent changes again, and the documentation says plainly that logical decoding clients are responsible for avoiding ill effects from handling the same message more than once (source: same page). And the log must contain enough to reconstruct a row: updates and deletes only carry the old row's key unless the table is configured to log the full old row, which is why Debezium's PostgreSQL documentation has a dedicated section on replica identity (source: Debezium PostgreSQL connector documentation).

Snapshot and backfill

A log does not hold history from the beginning of time, so a new consumer first needs the current state of every table. Debezium's initial snapshot starts a transaction, reads the existing rows and then switches to the log from the position recorded at the start (source: Debezium PostgreSQL documentation, snapshots). A long snapshot of a large table blocks streaming and, if it fails, restarts from scratch, so newer designs copy in pieces. Chunked backfill processes old data in bounded key ranges, one short transaction per chunk, with a saved checkpoint and idempotent writes, so a crash loses at most a chunk. Debezium's incremental snapshots do this while the log keeps flowing: each table is captured in configurable chunks, triggered by a signal, and based on the DBLog design (source: Debezium documentation, incremental snapshots). The DBLog paper describes the key trick: a watermark-based approach that interleaves transaction-log events with rows selected directly from tables, so the full state is captured without halting log processing (source: Andreakis and Papapanagiotou, DBLog). The rule that makes the interleave correct is that a chunk's rows are discarded if the log shows a newer change for the same key inside the chunk window.

Position tracking

Where the reader keeps its position decides the delivery semantics. In Kafka Connect the connector's offsets are stored in Kafka itself and committed periodically; a crash between the event being written and the offset being committed produces a duplicate on restart. Debezium says so directly: after a fault no events are lost, but the connector might emit some duplicates, and like Kafka it provides at-least-once delivery of change events (source: Debezium PostgreSQL documentation, behaviour when things go wrong). The reverse hazard is on the database side: the server keeps log files for as long as a slot has not confirmed them. In PostgreSQL the default for max_slot_wal_keep_size is -1, meaning slots may retain an unlimited amount of WAL (source: PostgreSQL documentation, replication settings), so a stalled reader fills the database disk. Debezium's documentation has a section on WAL disk space consumption for this reason.

Schema handling

Events must be interpretable years later, while tables change. For the MySQL connector this is concrete: the connector must know the schema at the time of each insert, update or delete, not today's schema, so it keeps a schema history topic with all DDL statements and rebuilds table structures from it on restart (source: Debezium MySQL connector documentation). For consumers, the contract is a compatibility rule (schema-evolution, no page yet): backward means new readers can read old data, forward means old readers can read new data, full means both; adding an optional field is the usual safe change, while renaming a column is a breaking change in disguise (source: Confluent Schema Registry documentation). A compact binary wire format with field numbers instead of names, such as protobuf-wire-format (no page yet), is one way to make additions safe by construction.

Delivery guarantee

At-least-once capture plus consumers that tolerate repeats is the practical default. There are three ways to deal with the repeats. Idempotent application: an upsert keyed by primary key is naturally repeatable, and an Idempotency key (the log position) makes non-naturally-idempotent side effects safe. Deduplication: a consumer keeps the identifiers of recently seen events for a bounded window and drops repeats (Event deduplication). Transactional commit: when the consumer writes to a transactional store, it writes the result and the offset in one transaction, so both happen or neither does; across systems this needs Two-phase commit and is the exactly-once-processing (no page yet) pattern. Choose the cheapest that works: for index and cache updates, idempotent upserts are enough.

Outbox

Applications that want to publish an event ("order placed") face the dual-write problem again. The Transactional outbox solves it: the event is written as a row in an outbox table in the same local transaction as the state change; a relay then publishes it later, at least once. CDC is the best relay, because the outbox row appears in the log like any other insert; the relay needs no polling and no extra locking. Debezium ships an Outbox Event Router transformation for this (source: Debezium documentation, outbox event router). Consumers still need an idempotency key or deduplication, because the relay delivers at least once.

Retention and compaction

A topic that records every change grows without bound, but a new consumer rebuilding a table needs only the latest value per key. log-compaction (no page yet) keeps the most recent record per key and removes older ones, with tombstones (a null value) marking deletions; a background cleaner works on a Segmented log, rewriting closed segments and never touching the active one, and it uses a key-to-latest-offset map for which a hash table such as a Swiss table is a good fit. A compacted topic is therefore a replayable copy of the table's current state, while a time-retained topic is a replayable history of changes; a pipeline often wants both.

Data and control flow

One committed transaction, normal operation:

1. The application commits a transaction; the database writes it to the log.
2. The log reader, positioned at LSN n, decodes the change into events with before and after images, the table's primary key and the commit position.
3. Events are published to the topic of their table, keyed by primary key so that all changes of one row stay in one partition and keep their order.
4. The connector commits its offset periodically.
5. Consumers apply events idempotently and commit their own offsets.

Adding a new table or consumer:

1. Start an incremental snapshot of the table through a signal.
2. The snapshot reads chunk k of primary keys; log events inside the chunk window win over the chunk's rows.
3. When the last chunk is done, the consumer has a full copy and a continuous stream.

After a failure:

1. The reader restarts, asks the database for changes from the last committed position.
2. Events between the last committed offset and the crash are re-emitted; consumers drop or overwrite them.
3. If the position is gone (log files removed), the pipeline must re-snapshot: protect against this with slot-based retention and alerts on lag.

Scaling

Limits, in the order they bite, and the lever for each:

1. One log, one reader. Changes are ordered in a single log per database, so one reader per source is the unit of capture. Levers: a fast decoder, filter tables early, read from a replica where the database allows it.
2. Retention on the source. A slow or stopped reader forces the database to keep log files. Levers: monitor slot lag, cap retained WAL with max_slot_wal_keep_size (accepting that a too-far-behind reader must re-snapshot), alerts on lag.
3. Snapshot size. Initial snapshots of terabytes take long and load the source. Levers: incremental snapshots in chunks, a read replica, throttling.
4. Topic throughput. One partition per table caps ordered throughput; more partitions keep per-key order because events are keyed by primary key.
5. Consumer lag. Each consumer scales independently by its own partitions; compaction shortens the rebuild path for new consumers.
6. Hot rows. A single row updated thousands of times a second produces that many events. Lever: coalesce downstream, or compact before consumption.

Failure modes

Real implementations

Sources

• Debezium documentation: Architecture, PostgreSQL connector (snapshots, replica identity, WAL disk space, at-least-once), MySQL connector (schema history topic, incremental snapshots), Outbox Event Router.
• PostgreSQL documentation: Logical decoding concepts, Replication settings (max_slot_wal_keep_size).
• A. Andreakis and I. Papapanagiotou, DBLog: A Watermark Based Change-Data-Capture Framework, 2020. paper
• Pattern: Transactional outbox.
• Confluent, Schema evolution and compatibility.