WAL Stream Mechanics

The Write-Ahead Log stream is the transport layer that every logical change-data-capture pipeline rides on, and this guide — part of PostgreSQL Logical Replication Architecture & Fundamentals — covers how WAL segments are generated, decoded, retained, and acknowledged in production on PostgreSQL 14 through 17. Unlike physical streaming replication, which ships opaque block-level WAL and mirrors an entire cluster byte-for-byte, logical replication decodes committed WAL records into ordered, row-level change sets that external consumers can apply without schema parity. Getting the generation and retention parameters right is what separates a stream that holds sub-second lag for years from one that silently fills pg_wal and takes the primary offline.

The failure consequences are concrete and asymmetric. If retention is too aggressive, WAL segments are recycled before a stalled consumer acknowledges them, the segments the slot still needs are gone, and the slot is permanently broken — the only recovery is dropping the slot and reseeding downstream from a fresh snapshot. If retention is unbounded, a single disconnected consumer pins WAL forever, pg_wal grows until the volume is full, and the primary refuses all writes. Both outcomes are page-your-on-call events, and both are governed by a handful of parameters and two LSN pointers on each replication slot. This page walks the full lifecycle: the GUCs that must be set before decoding starts, the step-by-step wiring of a slot to a pgoutput stream, a parameter reference, copy-paste diagnostics with thresholds, the failure signatures you will actually see, and how this machinery connects to publications, subscriptions, and downstream parsers.

Prerequisites & Configuration Objects #

Logical decoding does not run on a default cluster. Three server-side GUCs on the publisher gate the entire mechanism, and two of them size the resources the stream consumes.

wal_level must be logical. At the default replica level, PostgreSQL logs enough WAL to rebuild the entire cluster block-for-block but omits the relation and old-tuple metadata that logical decoding needs to reconstruct row images. Raising it to logical increases WAL volume — budget roughly 10-30% growth on update-heavy workloads — and, critically, requires a full cluster restart. It cannot be hot-reloaded, so schedule a maintenance window with a controlled bounce.

max_wal_senders (default 10 since PG 10) is the hard ceiling on concurrent walsender processes — one per active replication connection, physical or logical. max_replication_slots (default 10) caps the number of slots that can exist at once. Size both to peak consumer concurrency plus failover headroom; the safe-sizing math is worked through in configuring max_replication_slots safely.

logical_decoding_work_mem (default 64MB, per walsender) bounds the reorder buffer that assembles each transaction before its COMMIT is decoded. Exceed it and the transaction spills to disk under pg_replslot/<slot>/, adding latency to large transactions.

The role that opens the stream needs the REPLICATION attribute (or pg_read_all_data plus replication in newer setups) and a pg_hba.conf line that permits the replication pseudo-database — the exact host-based rules are covered in setting up pg_hba.conf for replication users.

-- On the publisher: reload-safe changes first, then confirm state.
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET logical_decoding_work_mem = '128MB';
SELECT pg_reload_conf();

-- wal_level cannot be reloaded — this only stages it for the next restart.
ALTER SYSTEM SET wal_level = 'logical';

-- Verify. pending_restart = true flags settings that need a bounce.
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN ('wal_level', 'max_wal_senders',
               'max_replication_slots', 'logical_decoding_work_mem');

Do not build anything on top of the stream until SHOW wal_level; returns logical on the running server, not just in the staged configuration.

Step-by-Step Implementation #

The following sequence takes a freshly configured publisher and produces a decoded, acknowledged stream. Each step is idempotent so it can run inside a provisioning job without failing on re-run.

1. Create a logical slot bound to pgoutput. There is no IF NOT EXISTS form, so guard creation by checking the catalog first. A non-temporary slot survives consumer restarts.

   SELECT pg_create_logical_replication_slot('sales_cdc', 'pgoutput')
   WHERE NOT EXISTS (
     SELECT 1 FROM pg_replication_slots WHERE slot_name = 'sales_cdc'
   );
   

2. Anchor retention so the slot’s WAL cannot be recycled out from under it. max_slot_wal_keep_size (PG 13+) caps how much WAL a slot may pin; leaving it at -1 means unlimited retention and risks a full volume, while setting it too low invalidates a lagging slot. A finite cap plus archiving is the production-safe stance.

   ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';  -- PG 13+: bound per-slot WAL
   ALTER SYSTEM SET wal_keep_size = '1GB';            -- floor for all consumers
   ALTER SYSTEM SET archive_mode = 'on';              -- decouple from local disk
   SELECT pg_reload_conf();
   

3. Open the replication connection and stream. For inspection, pg_logical_slot_peek_binary_changes reads without advancing the slot; for production, a consumer opens a START_REPLICATION connection so acknowledgements flow back automatically. The peek/get functions are for debugging only — they do not scale to a live pipeline.

   -- Ad-hoc inspection only: peek does NOT advance confirmed_flush_lsn.
   SELECT lsn, xid, data
   FROM pg_logical_slot_peek_binary_changes('sales_cdc', NULL, 10,
        'proto_version', '4', 'publication_names', 'sales_cdc');
   

4. Consume the binary pgoutput stream from Python. psycopg2’s replication connection exposes the raw protocol; the consumer must frame BEGIN/RELATION/INSERT/UPDATE/DELETE/COMMIT messages and periodically send feedback. The full byte-layout walkthrough lives in parsing the pgoutput format with psycopg2.

   import psycopg2
   from psycopg2.extras import LogicalReplicationConnection
   
   conn = psycopg2.connect(
       "dbname=app host=primary sslmode=verify-full",
       connection_factory=LogicalReplicationConnection,
   )
   cur = conn.cursor()
   cur.start_replication(
       slot_name="sales_cdc",
       decode=False,                     # raw bytes; pgoutput is binary
       options={"proto_version": "4",    # PG 16+: parallel-apply capable
                "publication_names": "sales_cdc"},
   )
   
   def on_message(msg):
       process(msg.payload)              # frame + persist the change
       msg.cursor.send_feedback(flush_lsn=msg.data_start)  # advance the slot
   
   cur.consume_stream(on_message)        # blocks; sends standby status updates
   

5. Acknowledge only after durable persistence. send_feedback(flush_lsn=...) moves confirmed_flush_lsn forward and releases the WAL behind it. Call it after the change is committed to the target, never before, or a crash between acknowledgement and persistence loses data. This is the mechanism that ties WAL retention to real downstream progress.

Relation metadata is emitted as a RELATION message before the first tuple for that table and again whenever the schema changes, so a consumer that caches relation OIDs can adapt its type mappings mid-stream without a restart. The end-to-end decode path — catalog snapshots, tuple reconstruction, and message framing — is dissected in how WAL decoding works in PostgreSQL 16.

Parameter Reference Table #

The pgoutput protocol version passed by the consumer also matters: proto_version 1 is the baseline, 2 (PG 14+) adds streaming of in-progress transactions, 3 (PG 15+) adds two-phase commit, and 4 (PG 16+) enables parallel apply. Request the highest version both ends support to avoid head-of-line blocking on large transactions.

Diagnostic Queries #

When a stream stalls, work from slot state outward. Every query below runs against a live publisher.

-- 1. Slot health snapshot. wal_status: reserved | extended | unreserved | lost.
--    'lost' means retention was exceeded and the slot is unrecoverable.
SELECT slot_name, active, active_pid, wal_status,
       restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- 2. Byte-level retained WAL per slot. Alert at 8 GB, page at the
--    max_slot_wal_keep_size threshold (10 GB in the setup above).
SELECT slot_name,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- 3. Live consumer lag from the walsender side (only rows for active streams).
--    write/flush/replay lag are intervals; alert when flush_lag > 30s sustained.
SELECT application_name, state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS apply_backlog_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- 4. Oldest WAL segment a slot still pins — useful before manual archiving.
SELECT slot_name, pg_walfile_name(restart_lsn) AS oldest_segment
FROM pg_replication_slots
WHERE slot_type = 'logical';

Two thresholds are worth wiring into alerting: retained WAL crossing 80% of max_slot_wal_keep_size, and any logical slot showing active = false for longer than a defined window (24 hours is a common default). The async monitoring integration guide covers exporting these to a metrics backend.

Failure Modes & Gotchas #

Slot invalidated to wal_status = 'lost'. A consumer stayed offline long enough that retained WAL exceeded max_slot_wal_keep_size. PostgreSQL invalidated the slot to protect the volume; restart_lsn is now null and the segments are gone. There is no in-place fix — drop the slot, recreate it, and reseed downstream from a fresh snapshot. Prevent recurrence by alerting well before the cap and by keeping archive_mode = on so archived segments provide a fallback restore path.

Unbounded pg_wal growth with a healthy-looking primary. A slot exists, active = false, and pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) climbs steadily. A consumer disconnected without dropping its slot, so restart_lsn is frozen and every new segment is pinned. Left alone this fills the volume and the primary rejects writes. Fix by restarting the consumer, or drop the abandoned slot after confirming nothing downstream still depends on it.

wal_level change appears to do nothing. ALTER SYSTEM SET wal_level = 'logical' succeeded, but SHOW wal_level still returns replica and slot creation fails with “logical decoding requires wal_level >= logical”. The setting is staged but the server was never restarted — pending_restart in pg_settings is true. Only a full restart applies it.

xmin frozen despite the consumer keeping up on LSN. confirmed_flush_lsn is advancing but catalog_xmin on the slot is not, and autovacuum warnings appear about tables that cannot be vacuumed. A long-running transaction on the primary holds a snapshot the slot needs for catalog visibility, so xmin cannot advance even though streaming is current. Find and end the offending backend in pg_stat_activity (state = 'idle in transaction' with an old xact_start).

Large transaction stalls the whole stream. Because decoding is strictly in commit order, a transaction is never streamed until its COMMIT is decoded — a single multi-million-row transaction blocks everything behind it and can spill past logical_decoding_work_mem to disk. On PG 14+ request proto_version 2 to stream in-progress transactions; on PG 16+ combine proto_version 4 with parallel apply on the subscriber to keep a hot table from starving low-churn datasets.

FAQ #

Integration Touchpoints #

WAL stream mechanics sit directly beneath the declarative objects most operators touch first. A publication decides which tables and columns ever reach the stream, so column-level filtering there reduces both WAL decode cost and downstream exposure; the design trade-offs are laid out in the publication and subscription models reference, and the physical-versus-logical distinction that motivates all of this is covered in logical vs physical replication differences.

On the consumer side, the same byte stream feeds either a built-in subscription sync or a custom parser. Teams building the latter start from building a Python logical decoding plugin or wire the stream into a Debezium connector and on into Kafka event routing. Slot provisioning that must survive restarts and failover is handled in initializing replication slots, and the privilege and TLS boundaries the replication role operates under are enforced through the security boundaries and permissions controls.

Related guides #

• Replication slot types — the state anchor that pins WAL retention.
• Publication and subscription models — what the stream is allowed to carry.
• How WAL decoding works in PostgreSQL 16 — the decode internals behind this page.
• Parsing the pgoutput format with psycopg2 — framing the binary stream in Python.
• Back to PostgreSQL Logical Replication Architecture & Fundamentals