Change Data Capture (CDC)

How change data capture turns every committed write in a database into an ordered stream of events, so search indexes, caches and warehouses stay in step without dual writes.

Overview

The problem: one write, many copies

Most applications keep the same facts in several places. The rows live in a relational database, a search index holds a copy for full-text search, a cache holds hot rows, and a warehouse holds everything for reports. Each copy is derived data: it can be rebuilt from the database, which is the system of record (the one place whose answer wins).

The hard part is keeping the copies current. There are three common ways, and only one of them is reliable.

Nightly dump — Copy whole tables on a schedule — Copies are hours old; large tables take long to scan
Dual write — The app writes the database, then the index or broker — One write can fail after the other succeeds; two writers can race
Change data capture — Read the database's own change log and publish each change — Needs log access and care with the log's retention

Why dual writes break

In a dual write, the application updates two systems with no transaction across them. Two things go wrong. First, a crash between the two writes leaves one system updated and the other not. Second, two clients writing the same row can reach the two systems in opposite orders, so the database ends with one value and the index with the other, and nothing ever repairs it.

How CDC works

Every serious database already writes each change to a log before it changes its data files, so it can recover after a crash. PostgreSQL calls this the write-ahead log (WAL); MySQL has the binary log (binlog). CDC reads that log, turns each committed change into an event, and publishes the events in commit order. The application writes to one place only: its database.

Because the log is ordered, every consumer applies changes in the same order the database committed them. That is what fixes the race above: the index ends with whatever value the database ended with.

What PostgreSQL needs

PostgreSQL exposes its WAL to outside readers through logical decoding. Three settings and one object matter.

wal_level = logical — Adds the extra data needed to decode row changes — Default is replica; changing it needs a server restart
Replication slot — Remembers how far a consumer has read — Keeps WAL until the consumer confirms it
max_replication_slots, max_wal_senders — Limits on slots and sender processes — Both default to 10
Replica identity — Chooses which old column values are logged for UPDATE and DELETE — Default logs only old primary key values

The replica identity setting surprises people. By default, a DELETE event carries only the old primary key, and an UPDATE event carries no old values at all unless the key itself changed. A table with no primary key gets no UPDATE or DELETE events from Debezium. If a consumer needs the full before image, set it on the table.

products — f

The value f means full; d is the default (primary key only).

What a change event looks like

Debezium, a widely used open-source CDC tool, emits one event per changed row. Each event says what happened and carries the row after the change and, when the database logged it, the row before.

op — c create, u update, d delete, r read during a snapshot, t truncate
before — The old row; under the default replica identity only the old key on a delete, and usually empty on an update
after — The new row; empty for a delete
source — Where it came from: database, table, log position, commit time

By default, a delete event is followed by a tombstone: a record with the same key and an empty value. On a compacted Kafka topic, the tombstone lets Kafka drop every earlier record for that key.

Starting from existing data

The log does not hold the whole history of the table, so a new consumer cannot build its copy from the log alone. Debezium's default snapshot mode, initial, first reads the current contents of each table inside one transaction and emits a r event per row, then switches to streaming from the log position it recorded at the start. No change is lost in the handover.

Delivery guarantees

Debezium states that it delivers each change once in normal operation, but after a fault it may send some changes again: it is at-least-once. PostgreSQL behaves the same way: a slot's position is saved only at checkpoints, so after a crash it can resend recent changes. Consumers must therefore be idempotent, meaning applying the same event twice has the same effect as once. Upserting by primary key and deleting by key are idempotent; incrementing a counter is not.

The outbox pattern

Raw row changes expose your table layout to every consumer. When other services need business events (OrderPlaced, not a row diff), use an outbox: in the same transaction as the business write, insert the event into an outbox table. CDC reads only the outbox table and publishes each row. The event exists if and only if the business write committed.

order — 42 — OrderPlaced

Debezium's outbox router sends each row to a topic named from aggregatetype (here outbox.event.order) and uses aggregateid as the message key, so all events for one order land in one partition, in order. The id lets consumers drop duplicates.

The cost: a slot that nobody reads

A replication slot keeps every WAL file its consumer has not confirmed, even when no consumer is connected. If the connector stops, WAL piles up on the database's disk. max_slot_wal_keep_size defaults to -1, which means no limit. Monitor slot lag, set a limit, and drop slots you no longer use. The limit has a price: a slot that falls further behind than the limit loses WAL it needs, so its consumer can no longer continue and must start over with a fresh snapshot. That is better than a full disk on the primary.

What you will be able to do

Explain why dual writes lose data or diverge, and how CDC avoids both.
Describe what PostgreSQL needs for CDC: logical WAL, a replication slot and the right replica identity.
Design a CDC pipeline into a search index with a snapshot, idempotent consumers and delete handling.
Choose between raw table CDC and the outbox pattern.
Name the main operational risk (WAL retained by a stalled slot) and how to guard against it.

Key concepts

Change data capture: Reading the committed changes from a database's own log and publishing them, in commit order, as a stream of events.
Dual write: An application writing to two systems with no shared transaction. A crash or a race can leave them permanently different.
Write-ahead log (WAL): PostgreSQL's log of every change, written before the data files. Logical decoding turns it into row-level changes.
Replication slot: A PostgreSQL object that records how far one consumer has read, and keeps the WAL that consumer still needs.
Replica identity: The per-table setting that decides which old column values UPDATE and DELETE events carry. The default logs only old primary key values, and for an UPDATE only when the key changed.
Initial snapshot: A consistent read of existing rows, emitted as read events, before the connector switches to streaming from the log.
Tombstone: A record with a key and an empty value. On a compacted Kafka topic it tells Kafka to drop all earlier records for that key.
Outbox pattern: Writing a business event into an outbox table in the same transaction as the data change, and publishing that table with CDC.

When to use it

Keeping a search index, cache or warehouse in step with a relational database.
Explaining how to publish events from a service without a dual write.
Replacing nightly table dumps with a continuous feed of changes.
Letting other services react to data changes without calling each other directly.

Interview questions

How would you keep Elasticsearch in sync with a PostgreSQL database?
What is the dual-write problem, and how do CDC and the outbox pattern solve it?
What can go wrong operationally with PostgreSQL CDC?
How is CDC different from event sourcing?