How WAL decoding works in PostgreSQL 16

WAL decoding is the stage of WAL stream mechanics where PostgreSQL 16 reads committed Write-Ahead Log records and reassembles them into ordered, row-level change events a logical consumer can apply without ever touching the primary’s on-disk pages. Get its memory, retention, and acknowledgement semantics wrong and a single slow consumer either spills gigabytes into pg_replslot/ or pins pg_wal until the primary refuses writes — so the decoder’s exact behaviour, not merely the fact that it runs, is what you operate in production.

The decoder is a stateful, transaction-aware cursor rather than a passive log forwarder. When a client commits, PostgreSQL flushes physical WAL records — tuple images, catalog updates, and heap changes — to disk. The walsender attached to a replication slot then reads those segment files sequentially, buffers each transaction in a reorder buffer keyed by XID, and emits the transaction only once its COMMIT record is seen. Before emitting, it consults the system catalog snapshot to map physical tuple IDs to current column names, dereferences TOAST pointers, and applies MVCC visibility so that only committed rows leave the server. The output plugin — the built-in pgoutput used by native subscriptions and by the Debezium connector, or a custom plugin like wal2json — frames the result as BEGIN / RELATION / INSERT|UPDATE|DELETE / COMMIT messages. The column set that reaches this stage is fixed by the publication the slot is bound to.

Commit and behaviour semantics #

Every knob that governs decoding trades a durability or ordering guarantee against latency or resource use. The table below fixes the exact behaviour of the parameters and operations that decide how the PostgreSQL 16 decoder acts; treat the defaults as starting points, not production values.

A genuine PostgreSQL 16 change worth planning around: logical slots can now be created and consumed on a physical standby. That lets you offload decoding CPU and WAL reads from the primary, but a standby slot is invalidated if the primary removes rows the standby’s catalog snapshot still needs (hot_standby_feedback = on mitigates this), so the failure surface differs from a primary-hosted slot.

Diagnostic patterns #

Decoder health is observable entirely through system views, and the numbers below are the thresholds that separate a healthy stream from an incident. The core signal is the gap between what the slot still needs (restart_lsn) and the write head (pg_current_wal_lsn()).

Retained WAL per slot, the single most important gauge:

-- Bytes of WAL each slot is pinning, plus PG 16 invalidation state.
SELECT slot_name,
       active,
       wal_status,                                  -- reserved | extended | unreserved | lost
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS unconfirmed_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Alert when retained_wal climbs past 60% of max_slot_wal_keep_size (or of max_wal_size when the cap is unbounded), and page immediately on wal_status = 'lost' — that slot is dead and the downstream must be reseeded from a fresh snapshot. Any slot showing active = false for more than 300 s is a disconnected consumer silently accruing retention.

Spill and streaming volume, which reveal whether logical_decoding_work_mem is too small:

-- PG 14+: per-slot decoding work. High spill_bytes => raise work_mem or enable streaming.
SELECT slot_name, spill_txns, spill_count,
       pg_size_pretty(spill_bytes) AS spilled,
       stream_txns, total_txns
FROM pg_stat_replication_slots;

Sustained non-zero spill_count between resets means large transactions are hitting disk on every decode; raise logical_decoding_work_mem toward 256MB or turn on transaction streaming. Live send-side lag for connected consumers:

-- write/flush/replay lag as durations for each streaming consumer.
SELECT application_name, state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Treat flush_lag above 5 s under steady write load as a downstream-throughput problem, not a decoder problem — the decoder rarely falls behind; the consumer usually does.

Safe deployment sequence #

Turning on logical decoding is not hot-reloadable — wal_level = logical requires a restart — so the rollout is a planned change, not a config push. The sequence below minimises the write outage and keeps a clean revert.

1. Stage the parameters in postgresql.conf (or via ALTER SYSTEM) without restarting:

   ALTER SYSTEM SET wal_level = 'logical';
   ALTER SYSTEM SET max_wal_senders = '10';
   ALTER SYSTEM SET max_replication_slots = '10';   -- size per configuring-slots guidance
   ALTER SYSTEM SET max_slot_wal_keep_size = '10GB'; -- never leave this unbounded in prod
   

   Size max_replication_slots and max_wal_senders with the headroom math in configuring max_replication_slots safely.

2. Restart on the standby first. In an HA pair, apply the change and bounce the replica, then fail over so the newly-logical node becomes primary. This turns a full-cluster restart into a single controlled switchover instead of a write outage on the live primary.

3. Confirm the level took effect before creating anything:

   SHOW wal_level;   -- must return: logical
   

4. Create the slot and its publication as a single, reversible unit. Use the peek path first so you never advance the slot during validation — the full walkthrough is in pg_create_logical_replication_slot step by step:

   SELECT pg_create_logical_replication_slot('cdc_main', 'pgoutput');
   CREATE PUBLICATION cdc_pub FOR TABLE public.orders, public.line_items;
   -- Non-destructive read: inspect changes without moving confirmed_flush_lsn.
   SELECT * FROM pg_logical_slot_peek_binary_changes('cdc_main', NULL, NULL,
     'proto_version', '4', 'publication_names', 'cdc_pub');
   

5. Bring the consumer online and watch confirmed_flush_lsn advance in pg_replication_slots for one full write cycle before declaring success.

Revert procedure. Stop the consumer, then drop the slot before touching wal_level — an orphaned slot left behind will keep pinning WAL:

SELECT pg_drop_replication_slot('cdc_main');
DROP PUBLICATION cdc_pub;
ALTER SYSTEM SET wal_level = 'replica';   -- takes effect on the next restart

Never lower wal_level while any logical slot still exists; drop every slot first or the reversal will not be clean.

Pipeline integration #

A Python consumer reads the decoded stream over the replication protocol, and its one hard job is to advance the slot’s confirmed LSN only after the change is durably persisted downstream. psycopg2 exposes this through LogicalReplicationConnection; the message-parsing details for the pgoutput frames are covered in parsing pgoutput format with psycopg2.

import psycopg2
from psycopg2.extras import LogicalReplicationConnection, ReplicationCursor

conn = psycopg2.connect(
    "host=primary dbname=app replication=database sslmode=require",
    connection_factory=LogicalReplicationConnection,
)
cur: ReplicationCursor = conn.cursor()
cur.start_replication(
    slot_name="cdc_main",
    decode=False,
    options={"proto_version": "4", "publication_names": "cdc_pub"},
)

def on_message(msg):
    event = parse_pgoutput(msg.payload)          # RELATION / INSERT / UPDATE / DELETE
    if event:
        apply_idempotent(event)                  # upsert keyed on PK — safe to replay
    # Advance the slot ONLY after the sink commit succeeds.
    msg.cursor.send_feedback(flush_lsn=msg.data_start)

cur.consume_stream(on_message, keepalive_interval=10.0)

Three patterns keep this reliable at scale:

• Idempotent upserts. Delivery is at-least-once, so every apply must be replayable. Key writes on the primary key with INSERT ... ON CONFLICT ... DO UPDATE, and use track_commit_timestamp for last-writer-wins when two origins can touch the same row.
• Checkpoint the LSN transactionally. Persist the last-applied LSN in the same downstream transaction as the data. On restart, resume from that stored LSN — send_feedback must never race ahead of a durable sink commit, or a crash between the two loses records.
• Retry with backoff, never rewind blindly. On a broken connection, reconnect and resume from the last confirmed LSN with exponential backoff (e.g. 1s → 2s → 4s, capped at 30s). If the server answers with a slot-invalidated error, the WAL is gone: stop, trigger a full subscription sync or table snapshot, and reinitialise the slot from a fresh baseline rather than attempting to catch up.

Export restart_lsn drift and spill_bytes as gauges to your metrics backend so the diagnostic thresholds above become alerts; the dashboard and rule templates live in async monitoring integration. Consumers should authenticate with a least-privilege REPLICATION role over TLS, as set out in the security and privilege boundaries reference, and the broader consumer-build patterns are in Python CDC parser development.

Authoritative references #

• PostgreSQL 16 manual — Logical Decoding: output plugin API, slot lifecycle, and the SQL/streaming interfaces.
• PostgreSQL 16 manual — Logical Decoding on Standby / pg_replication_slots: wal_status, restart_lsn, and confirmed_flush_lsn column semantics.
• PostgreSQL 16 manual — pg_stat_replication_slots: spill and stream statistics used in the diagnostics above.
• psycopg2 — LogicalReplicationConnection: protocol-level consumption API.

Related #

• Configuring max_replication_slots safely — sizing the slot and walsender ceilings that decoding depends on.
• pg_create_logical_replication_slot step by step — creating and validating the slot this decoder reads from.
• Parsing pgoutput format with psycopg2 — decoding the BEGIN/RELATION/DML/COMMIT frames the pipeline emits.
• Tuning synchronous_commit for logical replication — the durability/latency trade-off that governs commit visibility.

← Back to WAL Stream Mechanics