Change data capture

Change data capture emits every insert, update, and delete from a database as an ordered event stream. The naive version - polling WHERE updated_at > ? - misses deletes entirely, misses rows updated twice between polls, and puts a read load on the primary proportional to how fresh you want the data. Log-based CDC reads the database’s own write-ahead log instead: the same record the database uses for replication and crash recovery, so it sees every committed change in commit order, deletes included, without querying the tables at all.

How it works

Every write in PostgreSQL is appended to the WAL before the transaction commits. Logical decoding parses that stream into row-level changes - “row 42 in table orders changed from this state to this state” - and hands it to an output plugin. A replication slot is the bookmark: the server retains WAL segments from the slot’s confirmed position forward, so a consumer that disconnects and returns picks up exactly where it left off.

Debezium is the standard connector implementation: it runs inside Kafka Connect, opens a replication slot (with pgoutput since PostgreSQL 10, or decoderbufs/wal2json on older setups), and publishes each change as an event to a Kafka topic per table.

The parts that bite

Replication slots retain WAL. The slot exists precisely so the server cannot discard WAL the consumer has not read. If the consumer stops - connector down, network partition, topic deleted - the disk fills. pg_replication_slots exposes retained lag; alert on it. An abandoned slot is the single most common way a CDC pipeline takes down the source database. Drop slots you are not consuming (pg_drop_replication_slot), and treat slot lag as a first-class metric.

The initial snapshot is not a log read. A new connector has no history to resume from, so it snapshots the current table contents while the WAL keeps moving, then switches to the log at the snapshot’s position. On a large table this takes hours and puts sustained read load on the primary. Debezium supports incremental (chunked) snapshots for exactly this reason; a snapshot blocking DDL on a hot table is a production incident, not a convenience problem.

Schema changes are events too. A column added upstream changes the shape of every event after it. Consumers built on a fixed schema need either a schema registry with compatibility rules or explicit handling for unknown fields - “the pipeline worked until the migration” is a schema-registry-shaped problem.

Slot failover is a version question. Before PostgreSQL 17, a replication slot lived on the primary only: promote a standby and the slot is gone, and the connector starts a fresh snapshot. PostgreSQL 17 adds failover slots that survive promotion, but they must be created with failover = true and the standby must be configured to sync them. On anything earlier, plan the promotion procedure for your CDC the same way you plan it for the application.

Delivery semantics

Debezium emits events in commit order per table, keyed by primary key. Kafka gives at-least-once delivery: a consumer that commits offsets after processing can see a change twice after a restart. Everything downstream of CDC should be idempotent - upsert on the event’s key, or deduplicate on the WAL position (lsn) carried in the event metadata. Exactly-once is achievable only by making the sink write and the offset commit part of one transaction, which most sinks cannot do; design for duplicates instead.

Failure modes and diagnostics

• Disk filling on the primary. Check pg_replication_slots for active = false slots and growing retained WAL. The fix is restarting or dropping the consumer - never just deleting WAL files.
• Consumer lag that never catches up. A burst of writes outpaces a single-threaded snapshot or a slow sink. Debezium is largely single-threaded per connector; scale by splitting connectors, not by adding workers to one.
• Events for deleted rows disappearing. REPLICA IDENTITY controls what the WAL records for a row - the default logs the old primary key only. REPLICA IDENTITY FULL records the entire old row, at the cost of larger WAL.
• Silent filtering. Publications (FOR TABLE, FOR TABLES IN SCHEMA) decide which tables the slot sees with pgoutput. A table missing from the publication produces no error - it produces nothing.

What this is not for

CDC is an integration mechanism, not a backup: the slot preserves changes, not restorable state, and it cannot rebuild a dropped table. It is also the wrong tool for synchronous requirements - the event arrives after commit, with network and consumer latency on top. If the caller needs the answer in the same transaction, that is an application write or a synchronous replica, not CDC.