Start here: everyone downstream needs to know when a row changes#
TL;DRthe 30-second version
- One database holds the source of truth, but search indexes, caches, warehouses, and other services all keep derived copies. They need to learn about every committed change, in order, without the database having to call each of them.
- The naive fixes are all leaky. Dual writes (update the database, then publish an event yourself) lose events whenever the second write fails after the first succeeds. Polling a table for rows that changed since a timestamp misses deletes entirely and hammers the database.
- Change data capture reads the write-ahead log the database already keeps for durability. Every committed change is there, in commit order, tagged with a log sequence number (LSN). A CDC reader tails that log and emits one event per change.
- The reader keeps a read cursor (how far it has read) and a separately saved checkpoint (how far it has confirmed). Because the checkpoint lags, a crash-and-restart re-reads a few already-sent changes. So delivery is at-least-once: nothing is lost, but a change can arrive twice.
Picture a single orders table in Postgres. It is the truth about who bought what. But the search box on your site reads from Elasticsearch, the product page reads from a Redis cache, the finance team reads from a Snowflake warehouse, and the shipping service keeps its own local copy so it does not have to call the orders database on every request. Each of those is a derived view of the same data. The moment an order is placed, cancelled, or its address is edited, every one of those copies is now wrong until it hears about the change.
So the real problem is propagation: how does a change committed in one place reliably reach every other place that cares, in the right order, without losing any? The tempting answer is to make the application do it. When your code writes the order, have it also publish an "order changed" event to a message queue. This is called a dual write, and it is broken in a subtle way. The database commit and the event publish are two separate operations against two separate systems. If the process crashes after the commit succeeds but before the publish, the change is now permanent in the database and invisible to everyone downstream. There is no transaction spanning both, so there is always a window where they disagree.
The other tempting answer is to poll. Add an updated_at column, and every few seconds ask the database for every row where updated_at is newer than the last time you looked. This works until it does not. It cannot see deletes, because a deleted row is simply gone and never shows up in a query. It misses changes that happen and are overwritten between two polls, so you only ever see the latest value, never the intermediate ones. And it puts a constant scanning load on the very database you are trying to protect. Polling trades correctness and freshness for the comfort of a plain SQL query.
The fix: tail the log the database already keeps#
Every durable database keeps a write-ahead log. Before a transaction is allowed to commit, its changes are appended to this log on disk, so that a crash cannot lose an acknowledged write. The topic on the write-ahead log covers why that ordering rule buys durability. Change data capture takes advantage of a happy side effect: that same log is a complete, ordered history of every committed change, and it is sitting right there. CDC reads it and streams it out.
Each record in the log carries a log sequence number, or LSN. It is a monotonically increasing position: LSN 5 committed after LSN 4 and before LSN 6, always. The LSN is the spine of the whole design. It gives every change a stable name, it defines the one true order, and it is the thing a reader remembers so it can resume. Postgres exposes exactly this through logical decoding; MySQL exposes it as a position in the binary log; the shape is the same everywhere.
A CDC reader is then almost embarrassingly simple. It holds a cursor, which is the LSN of the last record it has read. To make progress it reads the next record, turns that committed change into an event, and puts the event on the output stream. Then it advances the cursor by one and repeats. Because it walks the log in order and the log is in commit order, the events come out in commit order too. A consumer downstream sees inserts, updates, and deletes in exactly the sequence they happened in the source.
The one piece that makes this robust is the checkpoint. The cursor lives in the reader's memory, and memory does not survive a crash. So every so often the reader saves its cursor position to durable storage. That saved position is the checkpoint: a promise that everything up to this LSN has been delivered and will never need to be re-read. When the reader restarts after a crash, it does not start from the beginning of the log and it does not start from wherever its lost in-memory cursor happened to be. It starts from the checkpoint.
That gap is the entire personality of the system. Call it the lag: the number of records read but not yet checkpointed. Right after a checkpoint the lag is zero. It grows with every read until the next checkpoint resets it. And the lag is exactly the number of changes a crash will re-deliver. Frequent checkpoints keep the lag small and duplicates rare, at the cost of more checkpoint writes. Rare checkpoints go faster but re-deliver more on a restart. There is no setting that gives you both.
Watch one change flow through, then survive a crash#
- A user updates order 42's address. The transaction commits, and the database appends a record to the write-ahead log at LSN 7: an update on orders:42.
- The CDC reader, tailing the log, advances its cursor to LSN 7 and reads the record. Its cursor is now 7; its checkpoint is still 5 from earlier.
- The reader emits an event onto the output stream describing the update, and the search index consumer applies it. The lag is now 2: LSNs 6 and 7 are read but not checkpointed.
- Before the reader checkpoints, its process crashes. The in-memory cursor (7) is gone. The log on disk and the checkpoint on disk (5) both survive.
- The reader restarts and resumes from the checkpoint: the cursor snaps back to LSN 5. It reads LSN 6 and LSN 7 again and emits them again. The consumer sees the address update a second time.
- Because the consumer is idempotent (it applies each change keyed by its LSN, or upserts the row), seeing LSN 7 twice lands the same final state as seeing it once. No harm done, and nothing was lost.
PredictThe reader has read up to LSN 12 and last checkpointed at LSN 9 when it crashes. On restart, how many changes get re-delivered, and does the consumer ever miss LSN 10?
Hint: The re-delivered set is exactly the lag: read cursor minus checkpoint.
Three changes are re-delivered: LSNs 10, 11, and 12, which were read but not checkpointed. The consumer never misses LSN 10 — quite the opposite, it sees it twice. That is the guarantee: the restart resumes from the checkpoint (9) and re-reads everything after it, so the risk is duplicates, never loss.
The cost: cheap to read, bounded to re-deliver#
Reading the log is sequential, which is the fastest thing a disk does. The reader does a bounded amount of work per change: read one record, emit one event, occasionally save one checkpoint. There is no scanning of tables, no joins, no query planning. The load CDC adds to the source database is roughly the cost of streaming the log it was already writing, which is why CDC scales to high write rates where polling would fall over.
- Work per change: constant. One log read and one emit, independent of how large the table is or how many rows it holds.
- Duplicates after a crash: at most the lag, which is the read cursor minus the checkpoint. Bounded, and tunable by how often you checkpoint.
- End-to-end latency: how long a change waits between committing and reaching a consumer. Usually milliseconds to seconds, set by how aggressively the reader tails and how far consumers are behind.
- Log retention: the source must keep log records until the reader has checkpointed past them. A reader that falls too far behind can force the database to retain a large, growing log.
The trade: at-least-once, and what it forces on consumers
The defining trade of log-based CDC is its delivery guarantee. Because the checkpoint lags the cursor, a crash re-delivers the gap, so every consumer must accept that a change may arrive more than once. This is at-least-once delivery. It is a strong and honest guarantee: no committed change is ever silently dropped. But it pushes a requirement downstream — consumers must be idempotent, meaning that applying the same change twice lands the same result as applying it once.
Idempotency is usually easy to get here, because each event carries its LSN and its primary key. A consumer can record the highest LSN it has applied per key and ignore anything at or below it, or it can simply upsert the row by primary key so that re-applying an update just writes the same value again. A delete becomes an idempotent "ensure this key is absent." The LSN turns a stream that might repeat itself into one a consumer can safely de-duplicate.
People often ask for exactly-once instead. In the general case, exactly-once delivery across process boundaries is impossible, because the sender can never be sure whether a lost acknowledgement means the message was received or not, and so must either risk a duplicate or risk a loss. What real systems build is effectively-once processing: at-least-once delivery plus idempotent consumers, which produces the same observable result as if every change had been handled exactly once. CDC gives you the first half; the consumer supplies the second.
There is a second trade around the initial load. The log only goes back so far, so a brand-new consumer cannot rebuild years of history from it. The standard answer is a snapshot: take a consistent read of the current table, stream it as a batch of synthetic inserts, then switch to tailing the log from the LSN captured at the start of the snapshot. Stitching the snapshot and the live stream together without gaps or double-counting is the fiddly part of every real CDC deployment.
Compared to the alternatives
Every way of noticing a change makes a different bargain. Reading the log wins on completeness and load, at the cost of tighter coupling to the database's internals.
| Approach | Sees deletes? | Load on DB | Loses events? | Ordering |
|---|---|---|---|---|
| Dual write (app publishes) | Yes, if coded | Low | Yes — commit and publish are not atomic | App-defined, easy to get wrong |
| Polling updated_at | No | High — constant scans | Yes — misses deletes and in-between values | By timestamp, ties ambiguous |
| Trigger writes to outbox table | Yes | Medium — extra write per change | No, if in the same transaction | By insertion into the outbox |
| Log-based CDC | Yes | Low — reads the existing log | No — at-least-once | Exact commit order, by LSN |
The transactional outbox is the honorable middle ground and worth knowing: the application, in the same transaction as its data change, inserts a row into an outbox table, and a separate process ships those rows out. It fixes the dual-write gap because the outbox insert and the data change commit together. Log-based CDC can even read the outbox table's changes from the log, combining both patterns. The pure log approach is preferred when you want every change to every table without asking the application to write anything extra.
Where this runs in production
- Debezium is the most widely used open-source CDC platform. It runs as Kafka Connect connectors that read Postgres logical decoding, the MySQL binlog, MongoDB oplogs, and more, and publishes change events to Kafka topics with at-least-once semantics.
- PostgreSQL logical decoding turns the write-ahead log into a stream of row-level changes. A replication slot is the durable checkpoint: it records the confirmed LSN and stops the database from recycling log the consumer still needs.
- MySQL's binary log (binlog) records every change as an ordered event with a file-and-position or GTID (global transaction ID) coordinate. Read-replicas and CDC tools alike tail it; the coordinate is the resumable cursor.
- Netflix's DBLog and similar frameworks solve the snapshot-plus-stream stitching at scale, interleaving a consistent backfill with the live log so a new consumer starts complete and stays current.
- The pattern feeds search indexes, cache invalidation, data-warehouse and lakehouse loads, microservice data sharing, and audit trails — anywhere a derived copy must track a source of truth.
Where CDC bites you
- Non-idempotent consumers. If applying a change twice is not safe, at-least-once delivery will eventually corrupt state. Key every apply by LSN or upsert by primary key before you go live, not after the first duplicate.
- An abandoned checkpoint filling the disk. When a consumer stops advancing its checkpoint (a crashed reader, a paused replication slot), the source must retain all log after it. Left unwatched, this grows until the database runs out of disk.
- Botching the initial snapshot. Switching from the backfill snapshot to the live log at the wrong LSN either drops changes made during the snapshot or double-applies them. Capture the start LSN before the snapshot and resume the stream from exactly there.
- Schema changes. A column added or dropped in the source changes the shape of later events. Consumers and the serialization format must tolerate evolving schemas, or a deploy on the source breaks the pipeline.
- Assuming global order across tables. The log gives a total order by LSN, but once events are partitioned across topics or shards by key, order is only preserved within a partition. Cross-entity ordering must be reasoned about, not assumed.
- Confusing CDC with the WAL's own recovery. Replay during crash recovery rebuilds the database's own state and then the log can be truncated. CDC instead streams the log outward and must keep it until every external consumer has checkpointed — a different lifetime and a different owner.
In an interview
When a design question needs one database's changes to reach a search index, a cache, or a warehouse, reach for CDC and say why in one line: read the write-ahead log the database already keeps, so no change is missed and the application does not have to publish anything. Then name the alternatives you rejected, since dual writes lose events and polling cannot see deletes, to show you know the landscape.
Show you understand the guarantee. State that delivery is at-least-once because the saved checkpoint lags the read cursor, so a crash re-reads the gap, and that consumers must therefore be idempotent, keyed by the log sequence number or upserting by primary key. If asked for exactly-once, explain that the achievable goal is effectively-once: at-least-once plus idempotency. Mention the initial snapshot as the way a new consumer catches up, and replication lag as the metric you would alert on.
Why is CDC at-least-once rather than exactly-once?
The reader saves its position (the checkpoint) less often than it reads, so records exist that were delivered but not yet checkpointed. A crash resumes from the checkpoint and re-delivers those. Exactly-once across process boundaries is not achievable in general, so the design chooses to never lose a change and pushes de-duplication to idempotent consumers.
How does a brand-new consumer get the data that predates the log?
It takes a consistent snapshot of the current table, streams those rows as synthetic inserts, and then switches to tailing the log from the LSN it recorded at the start of the snapshot. That stitch is what makes the consumer both complete and current.
What happens if a consumer goes down for an hour?
Its checkpoint stops advancing, so the source retains all log after that point. When the consumer returns it resumes from its checkpoint and catches up. The risk is that a long outage lets the retained log grow large enough to threaten the source's disk, which is why replication lag is monitored.
Does CDC preserve the exact order of changes?
Within the log, yes — events come out in commit order by LSN. Once the stream is partitioned by key across topics or shards, ordering is guaranteed only within a partition, so changes to a single row stay ordered but changes across different rows may interleave.
References
- Debezium — reference documentation — The de facto open-source CDC platform; connectors, snapshots, and at-least-once semantics.
- PostgreSQL — Logical Decoding — How Postgres turns the WAL into a row-change stream; replication slots as the durable checkpoint.
- MySQL — The Binary Log — The ordered change log CDC readers tail, with file/position and GTID coordinates.
- Martin Kleppmann — Using logs to build a solid data infrastructure — The case for log-based change propagation; the intuition behind CDC.
- Andreakis & Papapanagiotou — DBLog: A Watermark Based Change-Data-Capture Framework — Netflix's approach to interleaving a snapshot with the live log without gaps.
Ready to try it?
The simulator is a real, deterministic implementation — pick a scenario and step through it, scrubbing the timeline forward and backward through every change.