Change data capture (CDC)

Change data capture (CDC) turns every committed insert, update and delete in a database into an event other systems can follow. The application writes only to its database; a CDC pipeline, usually reading the database’s transaction log, carries each change to caches, search indexes, warehouses and other services. You gain one source of truth and no dual writes; you pay with some lag, a pipeline to run, and events shaped like your tables.

Picture it first: the branch day book

A bank branch keeps balances in its ledger, and head office wants the app’s passbook and the fraud desk kept current.

Phone every hour: “Which accounts changed since ten?” An account that went from ₹5,000 to zero and back to ₹3,000 reports ₹3,000, so the fraud desk never learns it touched zero; an account opened and closed within the hour never comes up at all.

Make clerks fill in a carbon slip with each entry. Nothing is missed, but every customer waits longer.

Copy the day book. The branch already writes every entry, in order, into a day book before touching the ledger. A runner copies new lines and keeps a bookmark. Nothing is missed and the counter is no slower, but pages he hasn’t copied can’t be thrown away: if he falls ill for a month, the cupboard overflows.

Map it back: the ledger is your tables, the day book is the write-ahead log or binlog, the runner is the CDC connector, his bookmark is a replication slot, and the desks are your cache, search index and warehouse.

Why change data capture

Most systems keep the same facts in several shapes: the catalogue in PostgreSQL, search in Elasticsearch, hot products in Redis, history in a warehouse. Each copy is derived data.

Having the application write every copy is a dual write, and it drifts: the index write fails after the commit, or two requests reach the database as A, B and the index as B, A. The transactional outbox article walks through those failures. CDC turns it around: the application writes only the database, and every copy follows the database’s record of committed changes, in commit order. A failed downstream write is retried from the stream, and a broken index can be rebuilt by replaying it.

You pay with eventual consistency, a pipeline with its own failure modes, and events shaped like tables rather than business facts.

Three ways to capture changes

Where do you tap into the write: the tables, each transaction, or the log?

Query-based polling

Polling needs only read access. Every table gets an updated_at column, and a poller asks for rows newer than its watermark:

SELECT id, title, price_paise, stock, updated_at
FROM   products
WHERE  updated_at > $1          -- watermark from the previous poll
ORDER  BY updated_at
LIMIT  1000;

Fine for an hourly warehouse load; as a sync mechanism it has four blind spots:

• Hard deletes are invisible. A deleted row matches no query, so you need soft deletes and a purge job.
• In-between states are lost. A price that moves ₹999 → ₹899 → ₹799 between polls arrives as one jump.
• Timestamps are not commit order. In PostgreSQL, now() is the transaction’s start time. A slow transaction stamps 10:00:05 but commits at 10:00:12, after a poll has moved the watermark past :09, so it is skipped for good.
• Load trades against lag. Average lag is half the poll interval, and 40 tables polled every 5 seconds is 40 ÷ 5 = 8 queries a second, each needing an updated_at index that every writer, including a 2 am manual fix, keeps correct.

Soft deletes fix the first hole, and re-reading an overlap window fixes the third at the price of duplicates; the second can’t be fixed. Polling suits consumers that only need the latest state, minutes late.

Triggers into a change table

An AFTER INSERT OR UPDATE OR DELETE trigger on each table inserts the operation, old row and new row into a changes table; a poller drains it in sequence order. Running inside the user’s transaction, it sees deletes, every intermediate state and old values, and a rollback leaves no trace. You pay on your hottest path:

• Every write is two writes: at 5,000 writes a second, 5,000 × 2 = 10,000 row inserts a second, plus WAL and indexes, while the user waits.
• Schema coupling: every new column means editing triggers, and a buggy trigger fails the business write itself.
• Still a poller: the changes table needs clean-up and has the same late-commit trap (see the outbox’s polling relay).

Use triggers when you can’t get replication access, or for a few quiet tables that need a full audit trail.

Log-based capture

Databases already log each change before acknowledging the commit, for recovery and replication: PostgreSQL’s WAL, MySQL’s binlog, MongoDB’s oplog (see write-ahead logging). A log-based connector reads that log as a replication client.

You gain every change after commit, in commit order, deletes and old values included, without touching tables or transactions. You pay with replication privileges, a log position the database must keep, and a stateful connector. This is what people usually mean by CDC.

Log-based capture, database by database

PostgreSQL decodes its WAL into row changes when wal_level = logical (since 9.4). The pgoutput plugin, built in since version 10, formats them; a publication lists the tables (Debezium creates one FOR ALL TABLES by default); a replication slot is your server-side bookmark. A slot is crash-safe, serves one reader at a time, and keeps every WAL segment its consumer hasn’t confirmed; after a server crash it can resend recent changes.

MySQL needs binlog_format = ROW and binlog_row_image = FULL; the position is a file and offset or a GTID set, which survives a failover.

MongoDB change streams report only majority-committed changes, so you never see a write that is later rolled back. Each event’s _id is a resume token, valid while the oplog still holds that point.

DynamoDB Streams record each item change exactly once, in order per item, and keep records for 24 hours.

Notice the second row: MySQL, MongoDB and DynamoDB forget on a schedule, so a slow consumer loses its place; PostgreSQL remembers, so a slow consumer can fill the primary’s disk.

The Debezium pipeline

Debezium is the common open-source set of log-based connectors, behind one event format. You run it on Kafka Connect (the usual choice: a cluster that runs source and sink connectors and restarts failed tasks on another worker), as Debezium Server (a standalone app that sends to Kinesis, Pub/Sub, Pulsar, Redis Streams and more, no Kafka needed), or as an embedded engine inside a Java application.

Each table gets its own topic, <topic.prefix>.<schema>.<table>, and each message’s key is the row’s primary key. Kafka hashes keys to partitions, so every change to product 4471 stays in one partition, in order (see Kafka).

1. The application commits; the change is in the WAL before the commit returns.
2. Debezium streams decoded changes through its replication slot.
3. It writes one event per row change to the table’s topic, stores the LSN it reached in Kafka Connect’s offsets topic, and confirms it to the slot so PostgreSQL can recycle older WAL.
4. Consumers, often sink connectors, read at their own pace.

Anatomy of a change event

A price update as Debezium emits it, trimmed. The key is {"id": 4471}; the value is an envelope:

{
  "before": { "id": 4471, "title": "Steel bottle 1 L", "price_paise": 49900, "stock": 120 },
  "after":  { "id": 4471, "title": "Steel bottle 1 L", "price_paise": 34900, "stock": 120 },
  "op": "u",
  "source": {
    "connector": "postgresql", "name": "shop", "db": "catalogue",
    "schema": "public", "table": "products",
    "txId": 90817, "lsn": 3820145672, "snapshot": false,
    "ts_ms": 1793178000120
  },
  "ts_ms": 1793178000415
}

• before / after are row images; an insert has no before, a delete no after.
• op is c create, u update, d delete or r read (copied by a snapshot), plus t truncate and m message.
• source gives the table, transaction id, log position and ts_ms, the commit time at the database. The top-level ts_ms is when Debezium processed it, so the gap is your lag: 415 − 120 = 295 ms.

What before holds is a setting. In PostgreSQL, REPLICA IDENTITY DEFAULT gives only the primary key, enough for a cache but not an audit; FULL logs every old column, at the cost of more WAL. Two traps: in a publication that replicates updates and deletes, a table with no primary key under the default makes PostgreSQL reject its UPDATEs and DELETEs; and unchanged large out-of-line (TOAST) values are missing from update events, replaced by a placeholder.

Deletes come in pairs. After the d event comes a tombstone: the same key with a null value. With log compaction, Kafka keeps only the latest message per key and a tombstone eventually removes the key, so a compacted CDC topic is a current copy of the table that a new consumer can load from offset 0. Tombstones survive only delete.retention.ms, 24 hours by default; a slower rebuild misses them, and deleted products come back to life.

Snapshots: the rows the log has forgotten

Old rows’ inserts left the log long ago, so a new connector copies existing rows first, then switches to streaming with no gap and no overlap.

The initial snapshot

Debezium’s PostgreSQL connector opens a transaction, reads the current log position, selects every row as an r event, commits, and streams from the position it read. Changes made during the copy sit after that position, so nothing is lost.

The cost is time. Copy a 400-million-row table at, say, 50,000 rows a second: 400,000,000 ÷ 50,000 = 8,000 seconds, over two hours, with live changes waiting and the slot holding WAL. If it dies at 90%, it starts again from zero. On hosted MySQL without global read locks, table locks can block writes for the whole copy. snapshot.mode picks the policy: initial (default, once), when_needed (also when the stored position is gone), no_data, initial_only or always.

Incremental snapshots

Netflix’s DBLog (Andreas Andreakis and Ioannis Papapanagiotou, 2020) snapshots at any time, pauses and resumes, and never stalls the log. Debezium adopted its watermark idea in 2021 as incremental snapshots, started by a signal:

1. Take the next chunk of the table in primary-key order, 1,024 rows by default.
2. Write a low watermark into the log, opening the snapshot window.
3. Run the chunk’s SELECT; hold the rows in memory.
4. Write a high watermark, closing the window.
5. Keep streaming; for each log event in the window whose key is buffered, drop the buffered row.
6. At the high watermark, emit the remaining rows as r events.

Nobody knows exactly when the SELECT ran, only that it ran inside the window. A row the log touched in the window may be staler in the buffer, so it goes; an untouched row hasn’t changed since the window opened, so its buffered copy is current.

You gain snapshots that run beside live streaming, resume after a crash, and can be re-run for one table at any time. A 30-million-row catalogue is 30,000,000 ÷ 1,024 ≈ 29,297 chunks, each an indexed range scan. You pay with a primary-key requirement and a weaker meaning for r: “the row at some moment”, not “the initial row”. A consumer can get r after u for the same key, or a d for a key it never saw. Treat r as an upsert.

Guarantees and gaps

Order is per row, not global. One key’s events stay in one partition, in commit order; rows in different partitions or topics have no relative order. See message ordering.

Delivery is at-least-once. After a crash the connector resumes from its last recorded offset and re-emits what followed. So consumers upsert by primary key, delete by key on d, and reject stale writes with a version that only grows, such as a row version column. See idempotent consumers and the inbox pattern.

Events are per row, not per transaction. An order with three items is four events on two topics; a consumer can briefly see one item. Turn on provide.transaction.metadata (off by default) and Debezium writes BEGIN and END events with event counts to a <prefix>.transaction topic and tags each change with its transaction id, so a consumer can buffer until all four arrive.

Schemas change under you. Use a schema registry (Debezium supports Avro with Apicurio or Confluent), add nullable columns before writing them, and treat renames and drops as breaking changes. MySQL’s binlog carries DDL, which Debezium records in a schema history topic; PostgreSQL’s logical decoding doesn’t. See serialization and schema evolution.

Going deeper: running CDC in production

A stalled slot fills the primary’s disk

A slot keeps every WAL segment its connector hasn’t confirmed, connected or not. A connector down for an upgrade or stuck on a poison event, or a forgotten test slot, and WAL piles up. At 20 MB of WAL a second, that is 20 MB × 3,600 s = 72 GB an hour; with 500 GB free, the disk fills in 500 ÷ 72 ≈ 6.9 hours, and PostgreSQL’s documentation warns that a full WAL disk can make the server panic and shut down.

Defences:

• Alert on WAL retained per slot, long before the disk is at risk:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM   pg_replication_slots;

• Cap it. max_slot_wal_keep_size (PostgreSQL 13+) defaults to -1, unlimited. With a cap, a slot that falls too far behind is invalidated instead: you trade an outage for a re-snapshot. PostgreSQL 18 adds idle_replication_slot_timeout (off by default) for slots left idle.
• Drop abandoned slots. They also block VACUUM on old catalog rows, in the extreme risking a wraparound shutdown.
• Send heartbeats from quiet databases. WAL is shared by the whole server, so a connector watching a quiet database beside a busy one confirms no progress while WAL piles up. Debezium’s heartbeat.interval.ms and heartbeat.action.query fix it with a tiny periodic write.

Short retention forces a re-snapshot

MySQL, MongoDB and DynamoDB fail the other way. A connector down longer than the binlog’s expiry, the oplog’s window or DynamoDB’s 24 hours has lost its position; Debezium’s MySQL connector stops and asks for a new snapshot (when_needed takes one automatically). Keep retention well above your longest plausible outage, long weekend included.

Failover and your bookmark

Up to PostgreSQL 15, logical slots exist only on the primary, so after failover you create a new slot and snapshot again; PostgreSQL 16 allows slots on standbys but doesn’t sync them. PostgreSQL 17 adds failover slots: create the slot with failover = true, enable sync_replication_slots on the standby, and list the standby in synchronized_standby_slots so the connector never gets ahead of it. Only slots showing synced = true before the failover survive it. Managed services that replicate disks carry slots anyway; on MySQL, GTIDs let the connector resume elsewhere.

With asynchronous replication the connector may already have published a commit the standby never got; after failover, repair the copies with an incremental snapshot.

Big transactions, churny tables and PII

• Big transactions arrive in one burst. Unless the client streams in-progress transactions, PostgreSQL passes a transaction on only at commit, spilling to disk past logical_decoding_work_mem (64 MB by default). A one-transaction backfill of 10 million rows lands as 10 million events at once; batch backfills at, say, 10,000 rows per transaction.
• Churny tables cost events. A view counter updated 5,000 times a second is 5,000 events a second; leave it out of table.include.list.
• PII flows everywhere. Phone numbers, addresses and PAN numbers land in Kafka, the lake and every consumer. Decide per column: column.exclude.list drops it, column.mask.hash… hashes it with a salt, column.mask.with.N.chars stars it out. A tombstone clears a compacted topic, but every downstream copy needs its own delete when a customer asks to be forgotten.

Use cases, end to end

Cache invalidation

An invalidator reads shop.public.products and, on every u or d, deletes product:<id> from Redis; the next read reloads from the database. Delete rather than overwrite: a lagging overwrite can restore an old price, while a delete costs at most one extra read. No service needs invalidation code on its write path, and manual SQL fixes invalidate too. See hot keys, cache stampede and invalidation.

Search index sync for a big sale

In a Flipkart-style Big Billion Days sale, 30 million products live in PostgreSQL and search runs on Elasticsearch. At midnight, sellers and pricing jobs push, say, 3,000 price and stock changes a second.

• Partitions. Twelve partitions carry 3,000 ÷ 12 = 250 events a second each, every product’s changes in one partition, in order.
• Bulk writes. Indexers group 500 events per bulk request: 3,000 ÷ 500 = 6 requests a second in total.
• Stale writes. The row version becomes Elasticsearch’s external version, which rejects any write not newer than the stored one, so a replayed event can’t roll a price back.
• Deletes and backfills. A d becomes a delete by id. When the index gains a field, an incremental snapshot re-sends all ~29,297 chunks while live updates keep flowing.

Documents joining several tables need a stream processor (Flink).

Warehouses, read models, migrations and audit

• Warehouse and lake loading. A loader buffers a few minutes of events, then MERGEs them: upsert on c, u and r, delete or flag on d. Data is minutes old, not a day, and no nightly export hammers the source. See OLTP vs OLAP and batch vs stream processing.
• Read models. A delivery-tracking service keeps a local copy of order addresses from the orders topic instead of calling the orders service.
• Strangler-fig migrations. CDC keeps the new system’s store in sync while reads, then writes, move across.
• Audit. With REPLICA IDENTITY FULL, each update carries old and new values, a transaction id and a commit time. The log knows what and when, rarely who: keep a modified_by column.

CDC, outbox or event sourcing?

Raw CDC publishes your tables: ideal when the consumer wants rows, risky when it is another team, whose code then breaks on your refactors and sees one action as four unrelated row events. For cross-team contracts, publish designed events through a transactional outbox, often relayed by a CDC connector watching only the outbox table.

Event sourcing inverts CDC: the event log is the truth and tables are projections of it.

Cheat sheet

How to say it in the interview

“Writing Postgres, Elasticsearch and Redis separately is a dual write, and the copies drift. So Postgres is the only write. Debezium tails its WAL through a replication slot, snapshots existing rows, then streams every committed change to a topic per table, keyed by product id. Delivery is at-least-once, so the indexer and invalidator upsert by key and reject stale versions. I’d cap max_slot_wal_keep_size and alert on slot lag so a stuck connector can’t fill the primary’s disk. Other teams get outbox events, not my tables.”

Why not just poll an updated_at column? It misses hard deletes, merges changes between polls, skips slow transactions stamped behind the watermark, and queries every table on every poll. The log has every committed change, in order.

What if the connector is down for a day? On PostgreSQL the slot keeps all that WAL: 72 GB an hour × 24 ≈ 1.7 TB. With a cap, the slot is invalidated instead and you re-snapshot. On MySQL or DynamoDB, if the position aged out of the binlog or the 24-hour stream, you snapshot again.

How does the search index stay correct with duplicates? Keying by primary key keeps each product’s changes in order; the indexer upserts by id, deletes on d, and sends a version so Elasticsearch rejects older writes.

How do you add a table or rebuild an index without downtime? Run an incremental snapshot alongside live streaming, or replay a compacted topic from offset 0 within delete.retention.ms.

Is CDC the same as the outbox pattern? No. CDC publishes row changes, coupling consumers to your schema; the outbox publishes designed events, often relayed by CDC.

Where to go next

• Transactional outbox: designed events, often relayed by CDC.
• Write-ahead logging: the log CDC reads.
• Data replication: the same log, keeping replicas in step.
• Kafka: partitions, consumer groups and compaction.
• Event sourcing and CQRS: when the log is the source of truth.
• Idempotent consumers and the inbox pattern and delivery guarantees: surviving at-least-once delivery.
• Hot keys, cache stampede and invalidation: after the invalidator deletes a hot key.

References and further reading

1. Debezium documentation: Architecturedebezium.ioThe three ways to run Debezium (Kafka Connect, Debezium Server, embedded engine), one topic per table by default, and sink connectors.
2. Debezium documentation: PostgreSQL connectordebezium.iopgoutput and publications, the initial snapshot steps and snapshot modes, the change-event envelope and op codes, REPLICA IDENTITY, tombstones, transaction metadata, WAL growth, failover slots and at-least-once recovery.
3. Debezium documentation: MySQL connectordebezium.ioRow-based binlog requirements, the 30-day binlog_expire_logs_seconds default, GTIDs, the schema history topic, and the forced re-snapshot when the binlog position is purged.
4. Incremental Snapshots in Debezium (Debezium blog, 2021)debezium.ioWhy Debezium adopted DBLog’s approach, the 1,024-row default chunk, the snapshot window and buffer, resumption from offsets, and the changed semantics of read events.
5. DBLog: A Watermark Based Change-Data-Capture Framework (Andreakis and Papapanagiotou, Netflix, 2020)arxiv.orgThe original watermark algorithm for interleaving chunked table selects with log events, without locks and without stalling the log.
6. PostgreSQL documentation: Logical decoding conceptspostgresql.orgReplication slots: crash-safe, one consumer at a time, may resend after a crash, retain WAL and catalog rows; failover slot synchronisation to standbys.
7. PostgreSQL documentation: Replication settingspostgresql.orgmax_slot_wal_keep_size (default -1, unlimited), idle_replication_slot_timeout, sync_replication_slots and synchronized_standby_slots.
8. MongoDB manual: Change streamsmongodb.comMajority-committed notifications, resume tokens, the oplog history requirement, update deltas and opt-in pre- and post-images.
9. Amazon DynamoDB Developer Guide: Change data capture for DynamoDB Streamsdocs.aws.amazon.comItem-level stream records kept for 24 hours, stream view types, exactly-once records per stream, per-item ordering and shard lineage.
10. Apache Kafka documentation: Log compactionkafka.apache.orgKeeping the latest value per key, tombstones as delete markers, and the 24-hour delete.retention.ms default after which a lagging reader can miss deletes.