Deleted in March, still for sale in June
A customer finds a kettle in the shop's search results, clicks it and gets a 404. The product was deleted from the catalogue in March. The search index never heard, because the job that feeds it asks the database one question every five minutes: SELECT * FROM products WHERE updated_at > :last_run. A deleted row has no updated_at. It has no row at all.
Search indexes, caches, warehouses and other services need the same thing from a table they do not own: every change to it, in order.
Two easy answers, and how each drifts
Deletes are not polling's only blind spot. A committed row can be skipped too, because the timestamp is taken before the commit: in PostgreSQL now() is when the transaction started, so a row stamped 12:04:59 by a transaction that commits at 12:05:02 was invisible to the 12:05:00 poll and is too old for the next one. Polling more often does not close that gap; it only loads the primary harder.
The other easy answer is to have the application tell the index itself: commit the change, then call the index. A process that dies between the two leaves the index behind for good, the dual write problem that Transactional Outbox opens with. Two writers add a quieter failure:
- Writer A sets the kettle's price to 20 in the database.
- Writer B sets it to 25 in the database, which now says 25.
- Writer B sends 25 to the index.
- Writer A, delayed by a slow network, sends 20 to the index.
The index says 20 until the next change, and nothing reports an error. The database put the two writes in an order; nothing passed that order on.
The database already wrote it down
As part of every commit, PostgreSQL records the change in its write-ahead log, and MySQL writes the committed transaction to its binlog, the log its replicas read (Write-Ahead Log explains why an engine keeps one). Change data capture (CDC) reads that log from outside and turns it into a stream of row-level change events: insert, update or delete, the table, the primary key, the new values and, depending on configuration, the old ones.
That closes every hole above. A delete is a record in the log, so it arrives as a delete event. Every committed write is there, and the reader sees it at its commit, not when its transaction began, so a slow transaction cannot slip in behind the reader. A connector reading PostgreSQL's logical decoding or MySQL's row-based binlog receives each transaction after it commits, in commit order, so the two prices arrive as 20 then 25 whatever the writers' network did. Debezium, usually run on Kafka Connect, is a widely used open-source connector of this kind.
The application's write path does not change. The source pays in log volume (wal_level = logical in PostgreSQL, binlog_format = ROW in MySQL), a replication connection, and log it must keep, a bill that comes due later.
Ordered by key, not by everything
The events go into a durable, partitioned log such as Kafka; partitions and retention are explained in Pub/Sub & event-driven systems. One partition would keep the database's whole order but cap the stream at what one consumer can take, so the connector keys each event by primary key, and every change to the kettle, product 9134, lands in the same partition, in log order. For a copy, that is the order that matters: the last event for a key is that row's current state.
Across keys there is no order at all: an invoice and its line items, written in one transaction, can land in different partitions, and a warehouse query in between sees an invoice with half its lines. Most copies settle for being right per row and briefly inconsistent across rows; a consumer that needs whole transactions has to reassemble them.
A new copy with no past
Months later, the analytics team wants the products in the warehouse. The topic keeps a week of events; the table holds eight years. Read the table and then start the log at the current position, and changes committed during the read are lost for good. Start the log first, and changes from the overlap arrive twice, survivable only if the sink can tell an older change from a newer one.
The clean handover pins both halves to one log position, P. The connector reads the table through a snapshot that sees the database exactly as of P, every transaction committed before P and none after, emits each row as a snapshot event, then streams changes from P. A change committed before P is reflected in the snapshot and one committed after it arrives from the log, so no change falls between the two halves. On a large table the read can take hours, and the source keeps its log since P all that time; some tools read in chunks between log events to avoid that. A nightly full reload, the batch approach of ETL/ELT & data orchestration, avoids the handover and can be up to a day stale.
Diagram components: PostgreSQL, Debezium, Kafka, Schema Registry, Elasticsearch, Data Warehouse.
A snapshot at position P, then the log from P. Each sink applies a change only if it is newer than what it holds.
Every change, at least once
The connector crashes. It had published events up to position 5,000 but last recorded its progress at 4,800, so on restart it publishes 4,801 to 5,000 again. Each loader has the same gap between applying a batch and recording its offset. Most CDC pipelines therefore deliver each change at least once, and every sink must absorb repeats.
For a copy of a table, the defence is to apply by key and keep with each row the log position of the change that produced it (P for snapshot rows), plus its sequence number where several changes share a position, skipping any event that is not newer; the general recipe is Idempotent Consumer. The CDC-specific trap is the delete: if it removes the row outright, a replayed update from before it brings the kettle back. So a delete leaves a marker with its position, a tombstone row or a deleted flag, for as long as replays can reach back.
When the table changes shape
Someone adds a discontinued_at column, and the events change shape: a strict consumer fails on the first new one, a lenient one drops the column silently. A schema registry keeps every version of the event schema, each event carries the id of its version, and the registry refuses a new version that breaks the compatibility rule configured for it. An added optional field usually passes; a renamed column, to every consumer, is a field removed and another added, which is why a captured table is changed in the expand-and-contract steps of Schema migrations without downtime.
What the source pays, and the number to watch
On PostgreSQL the connector reads through a replication slot, and the database keeps every WAL file the slot has not confirmed. Stop the connector for a weekend and WAL piles up on the source's disk; by default nothing caps it, and a full disk stops writes. Since PostgreSQL 13, max_slot_wal_keep_size can cap it, at the price of invalidating the slot, after which the copy needs a new snapshot. MySQL makes the opposite trade: it purges binlogs after a retention period, and a connector away longer must snapshot again. A long stall costs either the source's disk or a full re-copy.
So alert on lag, the time from a commit on the source to its change landing in the sink, and watch each stage on its own: the WAL the slot still holds, each consumer's offset lag, the sink's last applied change. One trap: judged by the age of the last applied change, a quiet table looks exactly like a stalled pipeline. A tiny heartbeat table, written every few seconds and captured like any other, keeps the signal moving.
The last cost is not on any disk. Consumers now depend on columns the owning team may consider private, and an event says status went from paid to refunded, not why. Services that need events designed on purpose get them from a Transactional Outbox, often relayed by CDC reading only the outbox table. Keeping a whole second database for failover is a different job (Replication & read replicas), and so is computing windows and joins over the change stream (Stream processing: windows, watermarks & state).
Follow the kettle
The CDC Replication Pipeline challenge is this pipeline, PostgreSQL to a warehouse, and its brief lists every pressure above. The requirement easiest to get wrong without noticing is the bootstrap, because a snapshot arrow and a log arrow look the same on a Grid whether or not they meet at one position. The kettle settles it. A copy bootstrapped after March must not contain it; a connector restart that replays events from before its delete must not bring it back; and a connector that stopped on the day of the delete must set off an alert before the source's disk fills.