Change data capture

A holiday rental site keeps 300,000 listings in PostgreSQL. Three other systems need copies: a search index that finds "Lisbon, under 160 a night", a cache for listing pages, and a warehouse for reports. The obvious design is a dual write: the application updates the database, then updates the index and the cache.

Two things break it. First, the second write can fail after the first succeeded: a crash, a timeout, a network drop. The database says one thing and the index another, and nothing goes back to fix it. Second, and harder to see, two writers can race:

The two copies now stay different until someone happens to edit listing 4812 again. Guests searching for "under 160" find a listing that costs 165 when they open it. A distributed transaction across the database and the index could prevent this, but most search engines and caches can't take part in one (Distributed transactions: 2PC & sagas).

Every change PostgreSQL commits is first written to its write-ahead log (WAL) (Storage engines: B-trees, LSM trees & WAL). Replicas already follow that log to stay in step (Database replication). Change data capture (CDC) follows it too, and turns each committed change into an event other systems can read. The application writes to the database only; everything else follows.

PostgreSQL exposes this through logical decoding: it decodes the WAL, which describes changes at the storage level, into rows that can be understood without knowing the database's internals. A replication slot streams those changes to one consumer in the order they were committed. MySQL's binary log plays the same role; row-based logging, the default, records how each row changed.

A common setup runs Debezium connectors on Kafka Connect: by default each table's changes go to their own topic, and each event's key is the row's primary key. Keying by listing id puts every change to one listing in one partition, so its changes stay in order for each consumer (Message queues & pub/sub).

There is a simpler-looking alternative: query-based CDC, which polls each table on a timer with a query such as WHERE updated_at > :last_seen. It misses more than it seems:

Polling has quieter failures too. Transactions can commit out of order, so a row with id 100 can appear after id 101 has already been read and skipped, and many rows sharing one timestamp can be cut off at a batch boundary. Polling is fine when hourly freshness and missed intermediate versions are acceptable; for a copy that must match, read the log.

When the host lowers listing 4812 from 180 to 150, the connector publishes an event like this, with the key {"id": 4812}:

The source block says exactly where the change sits in the database's history: its transaction and its log sequence number (LSN), a position in the WAL that only grows. Consumers can use it to drop anything they have already applied.

How much of the old row arrives depends on PostgreSQL's REPLICA IDENTITY setting for the table. By default, before holds only the primary key columns; set it to FULL and it holds every column. The index doesn't need the old price, but a consumer that reacts to "price dropped by 30" does.

Deletes need one more step. Many CDC topics use log compaction, which keeps the latest event per key so the topic always holds a full copy of the table. A delete event isn't enough for compaction to forget a key; that takes an event with the same key and a null value, a tombstone. So Debezium follows every delete with a tombstone, and a deleted listing eventually disappears from the topic entirely.

The rental site turns on CDC on a Tuesday. The 300,000 listings were created over eight years, and the WAL holds only recent changes; most PostgreSQL servers are set up so that it never holds the full history. A new search index fed only from the log would contain just the listings changed since Tuesday.

So a connector first takes a snapshot: it reads every existing row and emits each as a read event (op r), then switches to streaming changes from the log from the point where the snapshot was taken. Done naively, this is a long, consistent read of every table while the log waits, and for a big table that can take hours.

Incremental snapshots avoid that wait. The table is read in chunks ordered by primary key, 1,024 rows at a time by default in Debezium, while log events keep flowing. The tricky part is a row that changes while its chunk is being read: the snapshot's copy is older than the log's. A snapshot window resolves it:

The same mechanism rebuilds a consumer later. If the search index is corrupted, or a new table is added to the capture, an on-demand snapshot re-sends the current rows without stopping the stream. The result is a topic every consumer can rebuild from, as long as it is compacted or the snapshot is rerun.

A replication slot remembers how far its consumer has confirmed, and PostgreSQL keeps every WAL file from that point on, so the consumer can resume without gaps. Slots survive crashes and know nothing about whether anyone is still reading. If the connector stops, the WAL keeps piling up.

Suppose the rental database writes about 20 GB of WAL a day, and its WAL directory has 50 GB of free space. The connector's host fails on a Friday evening and nobody notices:

The defenses:

• Alert on slot lag: the gap between the current WAL position and the slot's confirmed_flush_lsn in pg_replication_slots.
• Cap it with max_slot_wal_keep_size. Past the cap, PostgreSQL removes the WAL anyway; the database is safe, but the slot can no longer continue and the consumer must start again with a snapshot.
• Drop slots that are no longer used. An abandoned slot keeps WAL forever.
• Send heartbeats when the captured tables are quiet but the database is busy, so the connector keeps confirming positions and the WAL can be reclaimed.

Duplicates are the other catch. Running normally, Debezium delivers each change event once; after a crash it can send some events again, so the guarantee is at least once. PostgreSQL itself persists a slot's position only at checkpoints, so after a server crash it can resend recent changes too. Every consumer must apply events idempotently (Idempotency). For a copy keyed by listing, that's natural: write the row by its key, and skip any event whose LSN is not newer than the one already applied.

A CDC event is a copy of a row, so its shape is the table's shape. For the search index, cache and warehouse, owned by the same team and meant to mirror the table, that's exactly right. For other services it isn't. If the payments team builds on raw listings events, the listings team can no longer rename price_per_night or split the table without breaking them: the table has become a public API.

For events other teams rely on, write them explicitly to an outbox table in the same transaction as the change, and capture only the outbox (Distributed transactions: 2PC & sagas). CDC is then the relay, and the event's shape is a deliberate contract (Event-driven architecture):

Debezium ships an outbox event router for this. Each outbox row names its aggregate type, such as Listing, which picks the topic, and its aggregate id, such as 4812, which becomes the message key, so one listing's events stay in order. The application chooses what goes in the payload, and can keep the table's internals out of it.

Some databases have CDC built in, with their own limits:

DynamoDB Streams can include the item before and after each change, like Debezium's before and after. Its 24-hour limit has the same lesson as a replication slot, from the other side: a consumer that falls more than a day behind loses changes, so it needs a way to start again from a full copy.

For the rental site, the checklist:

• Write only to the database. Every other copy follows its log.
• Key events by primary key, so one row's changes stay in order.
• Start with a snapshot, and keep the ability to rerun one for any table or consumer.
• Watch slot lag and cap retained WAL, so a stalled connector can't fill the disk.
• Make every consumer idempotent, using the LSN to skip what it has applied.
• Handle deletes and tombstones, and decide whether consumers need old values (REPLICA IDENTITY).
• Keep raw change events internal; publish events for other teams through an outbox.