Engineering Atlas › System Design Fundamentals

Streaming database changes with CDC

15 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Three ways to capture changes

Query-based CDC polls SELECT * FROM t WHERE updated_at > :last. It needs no privileges, but it misses deletes, collapses several updates between polls into one, and skips rows whose transaction committed after the poll with an older timestamp. Shopify ran exactly this for years; to protect the source databases it polled each table at most hourly, so data was always at least an hour old and hard deletes never appeared (Shopify).

Trigger-based CDC copies each change into a shadow table from a trigger. It catches deletes and intermediate states, but doubles the write work inside every transaction.

Log-based CDC reads the log the database already writes for crash recovery and replication. The write path is untouched and the order is the database's own commit order. This is what Debezium, AWS DMS, Google Datastream and Netflix's DBLog do (DBLog paper), and what this lesson focuses on.

Anatomy of a change event

A typical log-based event carries an operation (c create, u update, d delete, r read from a snapshot), the row's before and after images, and source metadata: table, transaction id, commit timestamp and log position (an LSN in Postgres, a binlog file and offset or GTID in MySQL). The event key is the row's primary key. After a delete, Debezium also emits a tombstone by default (tombstones.on.delete), a record with the key and a null value, so Kafka log compaction can eventually drop every record for that key (Debezium docs).

Snapshot, then stream

A new consumer needs the current state, not just future changes, but the log only goes back as far as retention allows. So CDC starts with a snapshot and switches to the stream. The switch is where data is lost or duplicated if done carelessly: rows changed during the snapshot must not be missed, and stale snapshot values must not overwrite newer streamed ones.

The classic method records a log position, takes a consistent snapshot as of that position, emits every row as r, then streams from the position. On a 2 TB table that means one transaction open for hours while the log piles up; Shopify found MySQL snapshots held read locks for their whole duration and took hours on large tables (Shopify). The watermark method from Netflix's DBLog, also used by Debezium's incremental snapshots, instead reads the table in primary-key chunks interleaved with the stream, so it can pause, resume, and re-snapshot one table while the stream keeps flowing (Debezium blog).

Ordering and partitioning

The log is totally ordered, but nobody consumes it as one sequence at scale. Events are partitioned, usually by primary key, so that all changes to one row land in one partition and stay in order, while different rows are processed in parallel. Order across rows, or across tables, is lost unless you deliberately keep it, for example by routing a whole aggregate (an order and its line items) by the aggregate's id.

This is the ordering and duplicates problem shape, which recurs wherever one ordered source feeds parallel workers, such as an agent's tool-call log replayed into several evaluators: partition by the entity whose order matters, and make workers tolerate redelivery.

Down the stack

Layer 1The contract

A log-based CDC pipeline promises that every committed change appears in the stream, that aborted changes never do, and that changes to one primary key appear in commit order. Delivery is at least once: after a crash, the connector resumes from its last acknowledged position and re-emits what follows. It does not promise that a consumer sees a transaction atomically, nor order across keys once events are partitioned. Latency is usually well under a second, with no upper bound when something stalls.

Layer 2The mechanism

In PostgreSQL, CDC requires wal_level = logical. A replication slot records how far the consumer has confirmed (confirmed_flush_lsn) and how much WAL must be kept to resume (restart_lsn). An output plugin (pgoutput, built in and used with publications, or wal2json) turns WAL into logical changes. WAL records of concurrent transactions are interleaved, so the decoder keeps a reorder buffer per transaction and emits a transaction's changes only when its commit record arrives, in commit order; transactions larger than logical_decoding_work_mem (64 MB by default) spill to disk under pg_replslot (Postgres docs). Since Postgres 14 an output plugin that supports streaming can instead send the largest in-progress transaction in blocks before it commits, followed later by a stream commit or a stream abort, so the consumer must buffer per transaction and be ready to discard (Postgres docs). REPLICA IDENTITY controls the before image: DEFAULT logs only the old primary key for updates and deletes, FULL logs the whole old row at the cost of larger WAL.

MySQL needs binlog_format = ROW and usually binlog_row_image = FULL, with the position tracked as a GTID set; MongoDB change streams use a resume token. Debezium commits its source position to Kafka Connect's offsets topic after writing the events, which is exactly why delivery is at least once.

DBLog's watermark algorithm: write a low watermark row to a small table, which puts a marker in the log; select the next chunk by primary key; write a high watermark row. While consuming the log, collect keys changed between the markers. When the high watermark arrives, drop chunk rows whose key changed in that window (the log event carries a newer state) and emit the rest at that point. No locks, no long transaction, and every row arrives either from the chunk or in a newer log event.

Layer 3The machine

A slot holds back WAL removal, and WAL is generated in 16 MB segments regardless of whether anyone reads it. A primary writing 30 MB of WAL per second retains about 2.6 TB per day if the slot's consumer stops, which fills the disk and stops the database. Since Postgres 13, max_slot_wal_keep_size caps this by invalidating the slot, which forces a re-snapshot but protects the primary; its default is still -1, unlimited (Postgres docs). Postgres 18 adds idle_replication_slot_timeout to invalidate slots that no connection has used for a set time, also off by default (Postgres 18 release).

Failover used to be the other silent killer: logical slots lived only on the primary, so after promoting a standby the connector had no slot and had to re-snapshot. Postgres 17 can synchronise failover-enabled logical slots to a standby (failover on the slot, sync_replication_slots on the standby, plus a physical slot and hot_standby_feedback) (Postgres docs), and Debezium exposes this as slot.failover for Postgres 17 and later (Debezium docs). Every slot decodes the full WAL stream on its own, so five slots cost five times the decoding CPU on the primary, even if each publication covers one small table.

Decoding costs a few microseconds per change, but without streaming a 10 million row UPDATE produces nothing until its commit, then spills gigabytes to pg_replslot and arrives downstream as one burst. In a healthy pipeline end-to-end latency is tens to hundreds of milliseconds, dominated by Kafka producer batching (linger.ms) and consumer polling rather than by the database.

Why it's built this way

  1. Applications that write to a database and a second system cannot make both writes atomic, so the database's own log becomes the single source of truth for what changed.
  2. The log exists for crash recovery and replication anyway, so reading it adds no work to the write path and captures every writer, including ones the application team does not control.
  3. Logs are interleaved by time, not grouped by transaction, so decoders buffer per transaction and emit at commit to hide aborted and in-flight work.
  4. Logs cannot be kept forever, so new consumers start from a snapshot; long snapshots hold resources, so watermark-based chunking replaces one huge read with many small ones.
  5. One ordered log cannot be consumed by one worker at scale, so it is partitioned by key, keeping the only order most consumers need.

Diagram

flowchart LR
    App["App writes"] --> DB["Postgres primary"]
    DB --> WAL["WAL segments"]
    WAL --> Slot["Replication slot (restart_lsn)"]
    Slot --> Dec["Decoder: reorder buffer, emit at commit"]
    Snap["Chunk select between watermarks"] --> Merge["Drop chunk keys changed in window"]
    Dec --> Merge
    Merge --> K["Kafka topic, partitioned by primary key"]
    K --> S["Search indexer"]
    K --> C["Cache invalidator"]
    K --> W["Warehouse loader"]
    S -.->|"ack offset"| K

Worked example

An orders database receives 20,000 row changes per second at peak and generates about 25 MB of WAL per second. Three consumers need the data: an Elasticsearch index, a Redis cache invalidator and a warehouse loader.

Event volume: with JSON before and after images, events average about 900 bytes, so the topic receives 18 MB/s. Twelve partitions keyed by order_id let each consumer run twelve workers, each handling about 1,700 events per second, comfortably within what a single indexer thread can bulk-load.

Bootstrapping the index: the orders table has 400 million rows. A watermark snapshot with 10,000-row chunks reads about 40,000 rows per second without disturbing the primary, so the snapshot takes roughly 2.8 hours. During that time the stream keeps flowing. If a chunk covering orders 1,200,000 to 1,210,000 is selected while order 1,204,511 is updated, the update appears in the log between the watermarks, the chunk's copy of that row is dropped, and the indexer gets the newer version from the log event instead.

The failure: the connector's Kafka credentials expire on a Friday night and nobody notices for 6 hours. The slot holds 25 MB/s times 21,600 seconds, about 540 GB of WAL. The primary's volume has 400 GB free. Without a cap, the database stops accepting writes around hour 4.5. With max_slot_wal_keep_size = 200GB the slot is invalidated after about 2.2 hours, the primary stays up, and the team re-snapshots on Monday. Alerting on slot lag at 20 GB would have paged someone after about 13 minutes.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Log-based CDCEvery change and delete, commit order, no write-path costNeeds log access, slot and retention operations, row-level eventsFeeding indexes, caches and warehouses from an existing database
Trigger-basedWorks without log access, captures deletesExtra write in every transaction, triggers to maintainSmall tables on databases without logical decoding
Query pollingNo privileges, trivial to startMisses deletes and intermediate states, timestamp racesLow-stakes batch sync, append-only tables
Outbox plus CDCBusiness-level events with atomic writeApplication must write outbox rowsOther services need intent, not row diffs
Dual write in appNo extra infrastructureDiverges on any partial failureNever for data that must stay consistent

Pitfalls in production

Leadership lens

Check yourself

  1. Why can't SELECT ... WHERE updated_at > :last reliably capture all changes even if every writer sets updated_at?

    Show answer

    Deletes leave no row to find, several updates between polls collapse into one, and a long transaction can commit after the poll with an updated_at older than the high-water mark, so it is skipped forever.

  2. In the watermark snapshot, why is it safe to drop a chunk row whose key changed between the low and high watermarks?

    Show answer

    That change appears as a log event, which carries a state at least as new as anything the chunk could have read, so the consumer still ends with the latest value.

  3. A consumer applies events from 12 partitions in parallel. What ordering does it still have?

    Show answer

    Order per partition, so per primary key if events are keyed by it. It has no order across keys or tables, and it may see part of a multi-row transaction before the rest.

  4. Why does a stopped consumer threaten the source database in Postgres?

    Show answer

    Its replication slot prevents WAL removal past restart_lsn, so WAL accumulates on the primary's disk until the consumer resumes, the slot is dropped, or max_slot_wal_keep_size invalidates it.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →