Databases / change-data-capture deep dive

PostgreSQL logical replication slots: the WAL-retention contract behind CDC

A logical replication slot is not a queue of copied rows. It is a durable promise from PostgreSQL: “do not recycle the WAL or catalog state needed to reproduce changes before this consumer has acknowledged them.” That promise makes CDC reliable—and makes an abandoned slot a storage incident waiting to happen.

Databases / change-data-capture deep dive · WAL retention, logical decoding, replication-slot state, vacuum interaction, monitoring, security, and a Docker lab

2026-09-20

The concrete problem

Suppose an application writes orders to PostgreSQL while a separate consumer feeds an analytics warehouse, search index, or audit system. The consumer may be slow, disconnected, or temporarily redeployed. When it reconnects, it must receive every committed change it missed, in the original transaction order, without the database sending the entire table again.

PostgreSQL already has the history: the write-ahead log (WAL). But WAL is normally recyclable after checkpoints and backups no longer need it. A CDC consumer needs a durable cursor that says how far it has safely processed. PostgreSQL's logical replication slot is that cursor plus a retention constraint.

This is why a replication slot is more consequential than a normal application offset. A Kafka offset can make a consumer reread messages; an inactive PostgreSQL slot can keep gigabytes or terabytes of database-side history alive. Reliability is purchased by moving the storage bill to the producer.

What “logical” means

WAL records physical changes: heap page updates, index modifications, full-page images, commit records, and other storage-level operations. A logical consumer does not want page bytes. It wants a stream such as “insert this row,” “update these columns,” and “transaction 42 committed.”

Logical decoding reconstructs that higher-level stream from WAL. PostgreSQL reads WAL from a slot's restart position, builds transaction state, reconstructs row changes, and passes committed changes to an output plugin. Built-in logical replication uses the pgoutput plugin; the small test_decoding plugin is useful for experiments. Extensions such as Debezium provide their own consumer and mapping around this protocol.

How the retention contract works

The most important slot fields are positions, not row counts:

The LSN is a log position, not a timestamp and not a byte offset from the beginning of the database. PostgreSQL provides functions such as pg_wal_lsn_diff to calculate the distance between LSNs. A useful first approximation of retained WAL for one slot is:

pg_current_wal_lsn() - restart_lsn

That quantity is a diagnostic estimate. It is not identical to the filesystem size of pg_wal, because other slots, archiving, checkpoints, replication, and segment boundaries may retain additional files.

The subtlety is that confirmed_flush_lsn and restart_lsn are related but not interchangeable. A decoder may still need earlier WAL to finish an in-progress transaction or to maintain decoding state. PostgreSQL advances the restart horizon only when it can prove that older records are no longer needed.

History: from physical standby to logical decoding

Physical streaming replication replays WAL onto another PostgreSQL instance. It preserves pages and database state, which is ideal for a hot standby, but it does not naturally produce a portable stream of row-level changes for a different system.

Logical decoding introduced the missing abstraction: retain WAL at a stable position, interpret it into transactions, and let an output plugin decide the external representation. Replication slots arrived in PostgreSQL 9.4 alongside logical decoding, making that position survive disconnects and restarts. Native logical replication was added later, in PostgreSQL 10, using the same decoding machinery and the pgoutput protocol.

The design deliberately separates three responsibilities:

• WAL is the durable source of truth.
• The slot is the server-side retention and progress state.
• The output plugin is a policy boundary for formatting decoded changes.

That separation is why a slot can serve a built-in subscription, a custom logical decoder, or a CDC connector without changing the storage engine's WAL format.

The important source structures and functions

In the PostgreSQL source tree, persistent slot metadata is represented by ReplicationSlotPersistentData in src/include/replication/slot.h. It includes the slot name, plugin, database identity, slot type, restart and confirmation LSNs, and transaction horizons. A shared-memory ReplicationSlot wraps that durable data with locking, dirty-state, active-owner, and in-memory bookkeeping.

A simplified control flow looks like this:

The ordering of the client-side acknowledgement matters. A safe connector writes the decoded event to its destination—or otherwise durably records an equivalent offset—before telling PostgreSQL that the LSN is flushed. If it acknowledges first and crashes before the destination commit, the change can be lost. If it commits first and acknowledges later, a crash causes a duplicate, which is normally handled with an idempotent destination key or transaction-aware sink.

Why catalog state is pinned too

WAL alone is not enough to decode every future change. The decoder may need relation metadata, tuple descriptions, replica identity information, and historic snapshots. A logical slot therefore carries a catalog horizon, commonly visible as catalog_xmin, that influences vacuum's ability to remove old catalog tuples.

This creates a second kind of storage pressure. An abandoned slot can keep ordinary table bloat growing indirectly when a long-running transaction or decoding horizon prevents cleanup, and it can make catalog changes harder to vacuum. The exact behavior depends on workload and PostgreSQL version, but the diagnostic principle is stable: inspect both WAL retention and transaction/catalog horizons rather than looking only at table size.

Backpressure, bounds, and failure modes

max_slot_wal_keep_size is a safety valve, not a substitute for monitoring. With an unlimited or very large allowance, the failure mode is disk exhaustion. With a finite allowance, the failure mode can become slot invalidation: the consumer must be re-seeded because the source history it needed is gone. Choose the failure explicitly and alert before either threshold.

For each production slot, alert on absolute retained bytes, rate of growth, age of the oldest required LSN, consumer activity, and time since the last acknowledged flush. Alerting only on replication lag in seconds is weak: a quiet database can have a very old slot with almost no retained bytes, while a busy database can fill storage quickly even when the consumer is only minutes behind.

Security and isolation

Logical replication is an extraction interface. A role that can connect to a replication endpoint and use a slot may receive row changes, including values that the application would not expose through its normal API. Treat replication credentials like database-read credentials with an unusually durable cursor.

• Use a dedicated least-privilege role and restrict it with pg_hba.conf; do not reuse an application superuser.
• Use TLS and certificate or SCRAM authentication for remote consumers.
• Review publication tables and replica identity: updates and deletes may require the old row identity, and a broad publication can export more than intended.
• Protect slot names and monitoring output. Names are not secrets, but they reveal topology and consumer intent.
• Do not assume dropping a slot erases already-retained WAL immediately from every backup or archive. Apply the retention and deletion policy of each copy separately.
• Make the destination idempotent. At-least-once delivery is the practical default; exactly-once behavior requires an explicit atomic boundary between the sink and the acknowledged LSN.

Compact reproducible lab: watch a slot pin WAL

This lab uses the official PostgreSQL image and the built-in test_decoding plugin. It creates a logical slot, generates changes without consuming them, measures the WAL distance held by the slot, then consumes and drops the slot. The image download may take a few minutes on a fresh machine.

Prerequisites: Docker with a running daemon and network access to Docker Hub. The commands use port 55432 so they do not require replacing a local PostgreSQL installation.

docker run --rm --name pg-slot-lab \
  -e POSTGRES_PASSWORD=postgres \
  -p 55432:5432 \
  -d postgres:16 \
  -c wal_level=logical \
  -c max_replication_slots=4 \
  -c max_wal_senders=4

until docker exec pg-slot-lab pg_isready -U postgres >/dev/null 2>&1; do sleep 1; done

docker exec pg-slot-lab psql -U postgres -c \
  "CREATE TABLE events(id bigint primary key, payload text);"

docker exec pg-slot-lab psql -U postgres -c \
  "SELECT * FROM pg_create_logical_replication_slot('lab_slot', 'test_decoding');"

docker exec pg-slot-lab psql -U postgres -c \
  "SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;"

docker exec pg-slot-lab psql -U postgres -c \
  "INSERT INTO events SELECT g, repeat('x', 200) FROM generate_series(1, 50000) AS g;"

docker exec pg-slot-lab psql -U postgres -c \
  "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;"

Expected observation: the slot is active = false, and the final query reports a nonzero retained_wal. The exact size depends on PostgreSQL version, compression, page layout, and the container's timing. The important relationship is that the current WAL position moves while the slot's restart position does not.

Now advance the logical slot by consuming its decoded changes, then inspect it again:

docker exec pg-slot-lab psql -U postgres -c \
  "SELECT count(*) FROM pg_logical_slot_get_changes('lab_slot', NULL, NULL);"

docker exec pg-slot-lab psql -U postgres -c \
  "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;"

docker exec pg-slot-lab psql -U postgres -c \
  "SELECT pg_drop_replication_slot('lab_slot');"

docker stop pg-slot-lab

Expected result: consuming the slot advances its confirmed position and allows the decoder to release history once no other constraint needs it. The retained-WAL number may stay rounded up to a whole segment immediately after consumption; segment recycling is not an instantaneous filesystem trim. Dropping the slot removes the retention fence, but a database may still have WAL files for unrelated reasons.

Variation 1: make the sink fail after decoding

Repeat the lab, but never run pg_logical_slot_get_changes. The producer continues to accept writes while the slot retains WAL. This is the shape of a disconnected CDC connector. Generate a second batch and compare the LSN distance; the number should grow roughly with write volume.

Variation 2: observe at-least-once replay

Repeat the lab and call pg_logical_slot_peek_changes('lab_slot', NULL, NULL) instead of pg_logical_slot_get_changes. “Peek” returns decoded changes without advancing the slot. Calling it twice returns the same changes again. This small experiment makes the acknowledgement boundary visible: delivery and advancement are separate operations, so a real sink needs idempotency or an atomic offset protocol.

On this run, postgres:16 did not complete its image pull in the environment, while postgres:16-alpine ran the lab successfully; replacing the image tag in the commands is a direct fallback. Otherwise use an existing PostgreSQL 16+ instance with wal_level=logical, max_replication_slots > 0, and a role allowed to create logical slots. If the server is PostgreSQL 15 or older, omit the newer diagnostic columns and use the same LSN-distance queries.

Debugging method in production

1. List every slot and its owner. Identify inactive slots first; they are often the easiest explanation for unbounded WAL.
2. Compute LSN distance from the current WAL position to each slot's restart_lsn. Sort by bytes, not by slot name.
3. Check whether the slot is active but not progressing. Correlate the consumer's last acknowledged LSN with its destination commit and network health.
4. Check long transactions and catalog horizons. A slot can be caught up on output while a transaction still delays cleanup.
5. Check server logs for slot invalidation, missing WAL, output-plugin errors, and replication authentication failures.
6. Decide whether to repair or drop. Reconnecting a healthy consumer preserves continuity; dropping a stale slot is often safer than filling the disk, but it requires a full downstream re-snapshot.

Before dropping anything, record the slot name, consumer identity, last acknowledged LSN, retention size, and downstream recovery plan. A slot is state, not configuration: deleting it changes what the source can prove about the consumer's history.

Primary sources

1. PostgreSQL documentation: Logical Decoding — architecture, slots, output plugins, and decoding behavior.
2. PostgreSQL documentation: Logical Replication — publications, subscriptions, protocol, and operational constraints.
3. PostgreSQL documentation: pg_replication_slots — current slot state and diagnostic columns.
4. PostgreSQL documentation: Replication and WAL configuration — slot limits, WAL retention bounds, and sender settings.
5. PostgreSQL source: src/backend/replication/slot.c — slot allocation, locking, persistence, and lifecycle.
6. PostgreSQL source: logical decoding README — snapshot building, reorder buffering, and output-plugin flow.
7. PostgreSQL 9.4 release notes — the release that introduced logical decoding and replication slots.