Change data capture
Instead of asking a table what changed, you read the log the database already writes to stay durable. That gets you every change in commit order, including deletes, without a timestamp column and without the writing application knowing anyone is watching.
The till roll is already printing. Walking the shelves each evening to guess what moved is the slow way round; reading the roll gives you every sale in order, including the things a shelf count can never show you, like an item rung up and then refunded.
Every system eventually needs a second copy of its data somewhere else — a search index, a warehouse, a cache, another service. The obvious way to keep that copy current is to ask the database what has changed lately, and the obvious way is wrong in three specific ways. The alternative is to stop asking the tables and read the record the database is already keeping for its own reasons.
Reading the log instead of the table#
A relational database records every change before it applies it, because that is what makes a commit survive a crash — the subject of write-ahead logging. That log is a complete, ordered account of everything that has happened to the data, and it exists whether or not anyone reads it. Change data capture is the observation that this account is exactly what a downstream consumer wants.
The log is written for the database’s own recovery, though, in terms of pages and byte offsets rather than rows. PostgreSQL’s term for the translation is logical decoding, which its documentation describes as extracting all persistent changes into a coherent, easy to understand format which can be interpreted without detailed knowledge of the database’s internal state — decoding a log that describes changes at a storage level into an application-specific form such as a stream of tuples or SQL statements.
Compare that with polling a table for recently updated rows. Polling cannot see a delete, because the row it would have found is gone. It cannot see intermediate states, because it only ever observes the latest value at the moment it looks. And it depends on every writer maintaining a timestamp column, so one code path that forgets produces changes that are simply never captured — with nothing anywhere reporting a problem. None of those gaps exist in the log, because durability already obliged the database to record every change exactly once.
The slot is the contract#
A consumer that reads a log needs to know where it got to, and that bookkeeping cannot live only in the consumer — if it did, a consumer that fell over would have no way to learn what it missed while the database recycled the segments. PostgreSQL puts the bookmark on the server side, in a replication slot, described as a stream of changes that can be replayed to a client in the order they were made on the origin server.
Two guarantees come with it and both matter for what you can build. Changes arrive in the order they were made rather than the order they happened to be written, so a consumer never has to reason about a change belonging to a transaction that later rolled back. And a logical slot emits each change just once in normal operation, which is what makes it reasonable to treat the stream as a sequence of facts rather than as hints to be re-checked against the source.
Slots outlive their consumers#
The server-side bookmark is the strength and the hazard, and PostgreSQL’s documentation is unusually blunt about the second. Slots persist across crashes and know nothing about the state of their consumers. They prevent removal of required resources even when no connection is using them — so write-ahead log segments cannot be recycled and vacuum cannot clean up the rows the slot still needs. The warning ends where it has to: in extreme cases this could cause the database to shut down to prevent transaction ID wraparound, so a slot that is no longer required should be dropped.
Read that as an operational rule rather than a footnote. A capture pipeline switched off for an afternoon, a consumer pointed at a new environment, a proof of concept nobody cleaned up — each leaves a slot behind, and each of those slots quietly instructs the database to keep everything the slot might still want. The failure surfaces as a disk filling on the primary, which is a long way from where the change was made and looks nothing like a consumer problem.
The subtler issue is what the stream means to whoever receives it. A row-level change feed describes what the storage did, not what the business decided — an update to three columns is three column values, not the intention behind them. Consumers written against that shape end up reconstructing intent from diffs, and they break whenever the schema changes underneath. This is the point at which the honest question is whether these two systems should be exchanging events they both understand instead of one of them reading the other’s storage.
IF YOU REMEMBER ONE THING
The log was going to be written anyway, which is what makes reading it cheap and complete. The slot that lets you read it reliably is also a standing instruction to the database to keep things — and it obeys that instruction whether anyone is listening or not.
Questions people also ask
4 QUESTIONSWhy not just poll for rows with an updated_at column?
Because that approach cannot see three things. It misses deletes, since a deleted row has no timestamp left to find. It misses intermediate states, showing only the last value between polls. And it depends on every writer maintaining the column correctly, which one forgotten code path breaks silently. Reading the log has none of those gaps, because durability already required the database to record every change.
What does logical decoding actually produce?
PostgreSQL describes it as decoding the contents of the write-ahead log, which describe changes at a storage level, into an application-specific form such as a stream of tuples or SQL statements. The log itself is written in terms of pages and byte offsets; decoding turns that into changes to rows that a consumer can understand without knowing the internals.
In what order do changes arrive?
PostgreSQL's replication slot represents a stream of changes that can be replayed in the order they were made on the origin server, and a logical slot emits each change just once in normal operation. Commit order rather than the order writes happened to hit the log is what makes the stream usable — a consumer never sees a change belonging to a transaction that later rolled back.
Can a capture pipeline take down the database it reads from?
Yes, and the documentation warns about it directly. Slots persist across crashes and know nothing about the state of their consumers, so they prevent removal of the resources they still require even when nothing is connected. That retains write-ahead log segments and blocks vacuum, and PostgreSQL notes that in extreme cases this could shut the database down to prevent transaction ID wraparound.