Log-Based Capture and the Write-Ahead Log as a Change Feed

A database that survives a crash must, before it acknowledges a write, record that write somewhere durable and replayable. Every engine with crash recovery therefore maintains an append-only log of the mutations it intends to make, and applies them to the data pages later. The log is written in commit order, it contains the before-and-after state of the rows it touches, and it is retained for a window that the operator controls.

That log is exactly what a change feed needs, and it exists before anyone asks for one. Log-based capture reads the log instead of querying tables, and in doing so it acquires four properties a query cannot offer: commit ordering, deletes, no dependence on an application-maintained timestamp column, and a consistent position that can be resumed after a restart. The cost is a decoding layer, a privilege, and a retention obligation that fails loudly.

What the log records, and what it does not

A redo log is written for recovery, not for consumers. It records physical or logical operations sufficient to redo a change on the same page layout, and its contents are an implementation detail that the engine is free to change between minor versions. Nothing in it is a stable public interface.

That distinction determines the architecture of every capture system. The log itself is not readable by outsiders in a portable way; instead the engine exposes a decoding interface that translates log records into a stream of changes described in terms of tables and rows. In PostgreSQL that interface is logical decoding, which reads WAL through a plugin and emits changes in a plugin-defined format. In MySQL the binary log is already a row-oriented or statement-oriented stream of changes at the server level, and the capture problem reduces to connecting as a replica and reading it.

The intermediate layer matters for correctness. Logical decoding must reconstruct a row image from records that were written to be replayed in page context, which means it needs catalog information: the table’s column layout at the time the record was written, and the identity of the row. Both are stored in the catalog, and both can change while decoding is in progress. A table that gains a column after a log record was written must still decode correctly, which is why decoders keep a catalog snapshot history and why decoding a very old position costs more than decoding a recent one.

Row identity is a separate decision. To emit an update or delete as a row-level change, the decoder must be able to name the row, and a table with no primary key has no stable name for a row. PostgreSQL requires either a primary key or an explicitly configured replica identity for a table to be captured; a table with neither produces errors on update and delete rather than a best-effort record. That is a constraint on the source schema, and it is the first thing to check when a capture stream reports failures on a particular table.

Physical versus logical replication

The two modes of replication differ in what they transmit and therefore in what a consumer can do with the stream.

Physical replication ships page-level changes and requires the replica to have an identical on-disk layout, identical major version, and identical platform assumptions. It is efficient — the standby applies bytes — and useless as a change feed, because a page write does not say which logical rows it affected.

Logical replication ships row-level changes and requires the consumer to understand the schema. It is less efficient per byte, because a single page write may decode into many row changes, and it carries more metadata. In exchange, the consumer can filter by table, transform values, and write to a different system with a different schema.

The efficiency difference is worth stating concretely. A batch insert of 1000 rows into a table with a narrow clustered key touches a modest number of pages, so physical replication transmits those pages once. Logical replication transmits 1000 row images, each carrying a table identifier and the column values. For wide rows the logical stream is larger than the physical one; for updates that modify one column of a wide row, the logical stream can be smaller if the engine emits only changed columns, and larger if it emits full row images. Which direction the ratio goes depends on row width and update pattern, and no general rule covers both.

The decoding pipeline

A capture process reading a log runs a loop with four stages.

Reserve. The consumer registers interest in the log at a position and asks the server to retain log from that position onward. In PostgreSQL this is a replication slot, which holds back log removal until the consumer confirms progress. Without it, the server is free to discard log the consumer has not read, and the consumer discovers this when its position no longer exists.

Read. The consumer pulls log records in order. Reading is sequential and cheap; the cost is bandwidth and, on a busy server, the volume of log generated by unrelated activity.

Decode. Each record is translated into zero or more row changes. Records for tables the consumer does not care about are decoded and discarded, which is why filtering at the server rather than the client matters: a publication that lists only the tables of interest avoids decoding work for the rest.

Emit and acknowledge. Changes are handed to the sink, and only after the sink has durably accepted them does the consumer advance its confirmed position. Advancing early creates the loss window described in committing offsets in the right order; advancing late costs log retention.

The acknowledge step is where a capture process differs most from a polling query. A query is stateless: forgetting the last result costs a re-read of a bounded table. A log consumer holds a server-side resource whose position determines how much log the server must keep, and a consumer that stops acknowledging will eventually force the server to retain log until the disk fills.

Retention is a hard limit, not a suggestion

A replication slot that no consumer advances prevents the server from recycling log. On a write-heavy primary, log accumulates at the rate of write volume, and the disk fills. The failure is not graceful: the primary stops accepting writes, so an abandoned capture process can take down the source database. Every engine with slot-style retention documents this, and operators are expected to monitor the gap between the slot’s confirmed position and the current log end.

The gap is measurable in bytes and in time. Bytes are the honest unit: a slot lagging by 40 GiB on a disk with 60 GiB free is an imminent outage regardless of how recently it was read. Time is a proxy that misleads when write volume changes, because the same 40 GiB represents minutes during a bulk load and hours during a quiet period.

The retention window also bounds how far back a consumer may resume. A consumer that has been offline longer than the retained log cannot resume from its stored position; the position has been recycled. Recovery from that state requires a fresh snapshot and a re-established position, which is the snapshot-to-stream handoff performed again, with the attendant duplicate or gap risk.

Heartbeats and the idle log

A quiet database generates little log, which creates a different problem: the consumer’s position is far behind the log end but there is nothing to read, so the consumer cannot tell whether it is caught up or stalled. Engines address this by emitting periodic keepalive records carrying the current log position, which logical decoding exposes to the plugin. A consumer that receives a keepalive learns the server’s current position without receiving any row changes, and can record that it has caught up to a known point.

This matters for reconciliation, because “caught up to position P at time T” is the only sound basis for a consistency check. Comparing a sink against a source without a known position is comparing two moving targets; comparing at a recorded position is a well-defined question.

What decoding cannot recover

Three limits apply to every log-based feed.

The log records changes, not intent. A transaction that updates a row twice within one transaction may decode as two changes or as one, depending on the decoder’s handling of intermediate states, and a consumer that needs the final state must apply both in order rather than assume the last one wins.

The log does not record reads. A capture stream is a record of mutations; it says nothing about queries, and nothing about rows that were never modified.

The log’s retention is finite, so a consumer that falls behind by more than the retention window cannot be repaired from the log. This is a hard boundary, unlike the polling cursor where falling behind merely costs a slow catch-up.

For sources without a log — most SaaS APIs, many older engines, files on object storage — the polling design in cursor columns and incremental boundaries remains the only option, with its tie and delete problems intact.