Postgres Logical Replication Does Not Send You the Old Row

Change data capture is usually sold as a stream of before-and-after rows. Build an audit log, diff the values, react to what changed. Postgres logical replication does not send you the before row.

By default it sends the new tuple and nothing else. The old values are not withheld to save space, and they were never captured.

Key takeaways#

• An UPDATE carries the new tuple. The old one appears only with REPLICA IDENTITY FULL.
• Even the key is omitted unless the update changed the key.
• FULL writes every column of the old row to WAL on every UPDATE.
• A slot with no consumer retains WAL forever, and both guards ship disabled.
• Schema changes, sequences and large objects are never replicated at all.

What does logical replication actually send?#

It sends row-level changes for tables you publish, as typed tuples rather than SQL. An INSERT carries the new row. An UPDATE carries the new row plus, conditionally, some identification of the old one. What counts as identification depends on one table setting, and the default setting is narrower than most pipelines assume.

That setting is REPLICA IDENTITY. The docs are brief about the default.

By default, this is the primary key, if there is one.

The anatomy of an UPDATE#

The replication protocol spells out the message parts, and the conditions matter more than the parts.

On an UPDATE, the key submessage is optional:

Byte1('K') — Identifies the following TupleData submessage as a key. This field is optional and is only present if the update changed data in any of the column(s) that are part of the REPLICA IDENTITY index.

So the key arrives only when the key itself changed. Update a status column and the key field is absent, because nothing about the key moved.

The old tuple is stricter still:

Byte1('O') — Identifies the following TupleData submessage as an old tuple. This field is optional and is only present if table in which the update happened has REPLICA IDENTITY set to FULL.

Read those together. With the default identity, an ordinary UPDATE produces a message with one tuple in it. There is no previous state anywhere in the stream.

A worked example#

Run UPDATE orders SET status = 'shipped' WHERE id = 42 on a table whose primary key is id.

The message reaching your consumer contains the new tuple. That means id 42, status shipped, and every other column at its current value. It contains no 'K', because the key did not change, and no 'O', because the identity is not FULL.

Now try to write an audit row saying "status changed from packed to shipped". You cannot. Nothing in the message says packed, and your consumer has no memory of the row.

Most pipelines paper over this by reading the previous value from their own sink. That works until two updates arrive close together. It also fails when a backfill replays the stream out of order. Then the audit log records a diff that never happened.

Turning on FULL, and what it costs#

Setting REPLICA IDENTITY FULL fixes the gap directly. Every UPDATE and DELETE then carries the complete old row.

The cost is write amplification on the primary. Postgres must place the entire old row into WAL for every update, not just the changed columns and the key.

Put numbers on it. Take a table with roughly 2 KB rows taking a million updates a day. FULL adds about 2 GB of WAL per day for that one table. WAL volume then drives replication lag, backup size and archive cost together.

There is a second cost on the subscriber side. Without a unique index to match on, the subscriber finds the row by comparing every column. That turns each apply into a sequential scan. It is fine on a small dimension table and painful on a large one, for the reasons covered in SQL query optimisation.

A table for the decision#

The right identity depends on what your consumer does, so here is how I would choose.

Row four is not a preference. The docs are explicit here. A table without a usable identity cannot take these writes at all.

Tables with a replica identity defined as NOTHING, DEFAULT without a primary key, or USING INDEX with a dropped index, cannot support UPDATE or DELETE operations when included in a publication replicating these actions. Attempting such operations will result in an error on the publisher.

An error on the publisher. Misconfigure the identity and your CDC setup starts rejecting production writes.

The slot that can shut down your primary#

This is the operational failure worth knowing before you enable anything. Postgres flags it as a Caution.

Replication slots persist across crashes and know nothing about the state of their consumer(s). They will prevent removal of required resources even when there is no connection using them. This consumes storage because neither required WAL nor required rows from the system catalogs can be removed by VACUUM as long as they are required by a replication slot. In extreme cases this could cause the database to shut down to prevent transaction ID wraparound.

Note the last clause. The risk is not only a full disk, it is the primary refusing writes to protect itself.

Two settings guard against it. Postgres ships both switched off.

max_slot_wal_keep_size defaults to -1, where "replication slots may retain an unlimited amount of WAL files". idle_replication_slot_timeout defaults to zero, which "disables the idle timeout invalidation mechanism".

So a consumer that dies quietly on a Friday holds WAL all weekend, by default, with no ceiling. Set both before you need them.

Redelivery is normal, not an incident#

One more property to design around. It looks like a bug the first time.

The current position of each slot is persisted only at checkpoint, so in the case of a crash the slot might return to an earlier LSN, which will then cause recent changes to be sent again when the server restarts.

That is at-least-once delivery, stated plainly. After a crash your consumer will see changes it has already applied.

Therefore make the apply idempotent rather than hoping it never happens. Upserts keyed on the primary key absorb a replay, and blind inserts into an audit table do not. It is the same boundary problem as exactly-once semantics in Kafka, and it has the same answer.

What it never replicates#

Three omissions catch people, and all three are documented rather than surprising.

Schema changes are the big one.

The database schema and DDL commands are not replicated.

So an ALTER TABLE ADD COLUMN on the publisher never reaches the subscriber. Replication of that table then breaks when a row arrives carrying a column the subscriber lacks.

Sequences are excluded too. The docs note that "the sequence itself would still show the start value on the subscriber", which matters the day you promote it. Large objects are excluded too, and the docs note "there is no workaround for that".

A fourth omission is not in that list, because it is not a restriction. It is just how slots work. A slot begins capturing at the moment you create it, so nothing before that point exists in the stream.

The initial sync copies current rows, and current rows are a snapshot rather than a history. So a CDC-backed audit log starts on the day you switched it on, and it can never reconstruct what changed the week before. Say that out loud to whoever asked for the audit trail, because they usually assumed otherwise.

The failure mode, and how to detect it#

Both main failures are quiet, and they are quiet in different ways.

The missing before-image never errors. Your audit table fills with rows whose old values are null or copied from the sink, and it looks populated. Nobody notices until an auditor asks what a field used to be.

Catch it in the consumer rather than the database. Assert that an old tuple is present before writing a diff, and fail loudly when it is not. A null before-value and an absent before-image should never look alike in your code.

The slot problem gives you more warning if you watch for it. Query the lag directly.

SELECT slot_name, active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained
FROM pg_replication_slots
ORDER BY 3 DESC;

Alert on active = false and on retained bytes crossing a threshold you pick from your disk headroom. An inactive slot is the leading indicator, and retained WAL is the thing that actually fills the volume.

What trigger-based CDC gets right#

Triggers were the old way to do this. They beat logical replication on exactly the point this article is about.

A row trigger receives OLD and NEW as ordinary records. The before-image is free, complete and available without changing any table-level setting, so audit use cases are trivial to write.

Triggers also run inside the transaction. That means no slot, no WAL retention risk, and no consumer to die over a weekend. The failure modes stay inside the database where you can see them.

Their cost is why we moved on. Every write pays for the trigger synchronously. The logic lives in the database rather than your codebase, and the audit table competes with production traffic for the same disk. Logical replication moved that work off the write path, and the missing old row is part of what it traded away.

Where this advice is wrong#

FULL is often cheap. My 2 GB per day assumed wide rows and a heavy update rate. A narrow table with thousands of updates a day adds megabytes, and worrying about that is a waste. Measure your row width and update volume before optimising.

Your connector may already handle this. Managed CDC tools often set the replica identity for you, or reconstruct a before-image from their own state. Check what yours does rather than assuming either way, because the behaviour differs between them.

The slot risk should not scare you off. It is a known failure with a two-line mitigation and a one-query alert. Trigger-based CDC has no slots and a worse steady-state cost. So avoiding logical replication over slot management trades a manageable risk for a permanent tax.

The short version#

Postgres logical replication sends the new tuple, sends the key only when the key changed, and sends the old row only under REPLICA IDENTITY FULL. So a CDC pipeline that assumes before-and-after values is building on something the default configuration never produced. So set FULL on the tables that genuinely need diffs, and measure the WAL it adds. Cap max_slot_wal_keep_size and idle_replication_slot_timeout, because both ship disabled. Make your apply idempotent, since redelivery after a crash is documented behaviour. And keep a separate plan for the DDL that never replicates.

Sources: PostgreSQL: logical replication publications, logical replication message formats, logical decoding explanation, replication configuration, logical replication restrictions