Monitor Logical Replication Slot Stats with pg_stat_replication_slots

Monitor Logical Replication Slot Stats with pg_stat_replication_slots

How full does a logical decoding worker's memory buffer get before PostgreSQL gives up holding a transaction in RAM and starts writing it to disk? pg_stat_replication_slots answers that question per slot, in concrete numbers — spilled-transaction counts, spilled bytes, and the streamed alternative — without grepping WAL sender logs for a spill message that may never get written. It's a narrow view with a narrow job: reporting on the internal memory accounting of logical decoding, not the replay lag a standby is carrying.

Purpose and Overview

PostgreSQL supports two kinds of replication slots: physical, used for streaming a byte-for-byte copy of WAL to a standby, and logical, used for decoding WAL into a row-level change stream a subscriber can apply selectively. pg_replication_slots lists both kinds side by side — it reports whether a slot exists, whether it's active, and how much WAL it's holding open via restart_lsn, regardless of slot type. pg_stat_replication_slots covers narrower ground on purpose: per pgpedia's reference entry, it is "a statistics view showing statistics about logical replication slot usage, specifically about transactions spilled to disk from the ReorderBuffer" — physical slots never produce a row here at all.

That's also a different question than the one pg_stat_replication answers. That view reports "one row per WAL sender process, showing statistics about replication to that sender's connected standby server" — the send/receive/replay lag side of the pipeline. pg_stat_replication_slots sits one layer earlier, at the point where the walsender is still decoding WAL into logical changes and deciding what to do with a transaction that's outgrowing its memory budget. A standby can be caught up on lag and a logical slot can still be spilling heavily underneath — the two views are diagnosing different resources.

The view was added in PostgreSQL 14, according to pgpedia's change history, and every row it produces exists because of one specific internal decision point: whether an in-progress transaction being decoded fits inside logical_decoding_work_mem or has to go somewhere else. Fujitsu's engineering write-up on the view frames the underlying mechanism directly — during decoding, the walsender expands each WAL change into an in-memory ReorderBufferChange structure, and PostgreSQL checks the accumulated size of that structure against logical_decoding_work_mem "for each decoding." When a transaction crosses that threshold, it either spills to local disk or streams to the subscriber, and which of those two paths gets used depends on the streaming option set on the subscription. pg_stat_replication_slots is the counter that tells you, after the fact, which path fired and how often.

Sample Code

 1SELECT
 2    slot_name,
 3    spill_txns,
 4    spill_count,
 5    spill_bytes,
 6    stream_txns,
 7    stream_count,
 8    stream_bytes,
 9    total_txns,
10    total_bytes,
11    stats_reset
12FROM
13    pg_stat_replication_slots
14ORDER BY
15    slot_name;

Filtered variant, for slots that have spilled at least one transaction to disk:

 1SELECT
 2    slot_name,
 3    spill_txns,
 4    spill_count,
 5    pg_size_pretty(spill_bytes)                             AS spilled,
 6    round(100.0 * spill_bytes / NULLIF(total_bytes, 0), 2)  AS pct_spilled,
 7    stats_reset
 8FROM
 9    pg_stat_replication_slots
10WHERE
11    spill_txns > 0
12ORDER BY
13    spill_bytes DESC;

Notes: pg_stat_replication_slots requires PostgreSQL 14 or later — it does not exist on earlier releases. Only logical slots produce a row; a cluster running physical replication only will return an empty result set. On PostgreSQL 19, additional columns for slot-sync tracking exist beyond the base set queried here (covered below).

Code Breakdown

The base query reads the view's full column set for every logical slot on the server; the filtered variant narrows to slots that have actually crossed the spill threshold at least once.

spill_txns, spill_count, and spill_bytes

spill_txns counts distinct transactions that have had any part of their decoded changes written to disk. spill_count counts individual spill events, which can run higher than spill_txns — a single large transaction can spill multiple times as it keeps growing past logical_decoding_work_mem, plus one additional spill Fujitsu's write-up notes occurs for a transaction's last remaining changes as its commit record is decoded, even if that remainder is under the threshold. spill_bytes is the cumulative decoded-data size, in bytes, that has gone to disk across all of those events.

stream_txns, stream_count, and stream_bytes

These three columns mirror the spill trio exactly, but for the streaming path instead of the disk path. A transaction goes here instead of to disk when the subscription behind the slot was created with streaming = on — in that mode, an in-progress transaction that outgrows logical_decoding_work_mem gets sent to the subscriber incrementally rather than written to local disk first. A slot can carry nonzero values in both trios at once if some of its transactions were small enough to stay under the threshold, some spilled, and the subscription's streaming setting only came into effect after slot creation.

total_txns and total_bytes

These two are the running totals for everything the slot has decoded to date, whether or not a given transaction ever needed the spill or stream path. Comparing them against the spill and stream trios above is what turns raw counters into a ratio worth alerting on — the pct_spilled expression in the filtered query does exactly that, guarding the division with NULLIF in case a freshly reset slot briefly reports zero.

Key Logical Decoding Concepts

logical_decoding_work_mem and the Spill/Stream Decision

logical_decoding_work_mem is the GUC that caps how much memory a single walsender can spend holding a transaction's decoded changes in memory before it acts. Fujitsu's worked example sets this parameter to a deliberately small 64kB, then inserts 3,000 rows in one transaction with the subscription's streaming option off. The result: spill_txns = 1, spill_count = 7, spill_bytes = 396000 — one transaction, spilled seven separate times as it kept growing past the 64kB ceiling (six threshold crossings plus the final commit-record spill), for a combined 396,000 bytes written to disk. That single example shows exactly how the counters in pg_stat_replication_slots map onto real decoding activity.

ReorderBuffer and Per-Transaction Memory Tracking

Internally, the walsender tracks each in-progress transaction's decoded size using a ReorderBufferChange structure, and per Fujitsu's description, sorts changes across concurrent transactions using a hash table keyed by transaction ID. Different WAL operations cost different amounts against the budget — decoding an INSERT costs the base structure size plus the new tuple's length, while a TRUNCATE costs the base size plus the OID size multiplied by the number of relations being truncated. That per-operation variability is why two transactions touching the same number of rows can spill at very different points.

Streaming as a Subscription-Level Choice

Whether an oversized transaction spills to disk or streams to the subscriber isn't a server-wide setting — it's controlled by the streaming option on the individual subscription. Fujitsu's example demonstrates the streaming path by running a large insert and a medium insert in two parallel sessions against a subscription created with streaming = on, showing the walsender pick up and stream the in-progress transaction rather than writing it to local disk. A fleet running a mix of subscriptions with streaming on and off will show that split directly in the spill versus stream columns, slot by slot.

Why Physical Slots Never Appear Here

Because pg_stat_replication_slots is scoped to logical decoding's internal memory accounting, a physical slot used for standby streaming has nothing to report into it — there's no ReorderBuffer, no decoding step, and no spill/stream decision happening on that slot at all. Confirming whether a given slot is physical or logical in the first place means checking pg_replication_slots.slot_type, not this view.

Practical Applications

Reading pg_stat_replication_slots on a schedule turns a decoding memory bottleneck from a mystery surfacing as replication lag into a counter you can watch climb in advance.

Sizing logical_decoding_work_mem Before Enabling CDC

Before turning on a new logical replication pipeline for a change-data-capture workload, running representative transaction volume through a slot with a conservative logical_decoding_work_mem and checking spill_count afterward shows whether the default is actually adequate for that table's write pattern, the same way Fujitsu's 64kB test case surfaces spilling on a modest 3,000-row insert.

Diagnosing a Sudden Spill Spike

When spill_bytes on a previously quiet slot starts climbing, the filtered query narrows the investigation immediately to which slot and how much — a much faster starting point than searching WAL sender logs or guessing which application deployed a larger-than-usual batch job.

Distinguishing a Memory Problem from a Configuration Choice

A slot showing heavy spill_* activity while a sibling slot on the same server shows the equivalent volume in stream_* isn't necessarily a sign anything is wrong — it may simply reflect that one subscription was created with streaming = on and the other wasn't. Checking both trios together before treating a spilling slot as an incident avoids chasing a non-problem.

Cross-Referencing with pg_replication_slots for Full Slot Health

pg_stat_replication_slots says nothing about whether a slot is still active or how much WAL it's retaining. Joining its output against pg_replication_slots on slot_name gives a single view combining decoding memory pressure with slot state — active, restart_lsn, wal_status — for a complete read of a logical slot's health rather than half of it.

Version Compatibility

pg_stat_replication_slots was added in PostgreSQL 14, per pgpedia's tracked change history, and its ten-column shape — slot_name through stats_reset, covering the spill, stream, and total counters used throughout this post — has held stable across PostgreSQL 14 through 18.

PostgreSQL 19 extends the view with three additional columns tied to logical slot synchronization to standbys: mem_exceeded_count, slotsync_skip_count, and slotsync_last_skip, added per pgpedia's commit-level history. As of this writing, PostgreSQL's own documentation site lists PostgreSQL 19 as Beta 3, alongside the current stable 18.6, 17.11, 16.15, 15.19, and 14.24 releases — so these newer columns should be treated as forthcoming rather than assumed present on a production cluster today. A monitoring query written against the ten-column base set queried above will keep working unchanged once a cluster does upgrade to 19; it simply won't surface the new counters until the SELECT list is extended to include them.

Best Practices

• Track pct_spilled as a trend, not a single reading — a slot that spills occasionally under a batch load is different from one whose spill ratio is climbing week over week.
• Reset per-slot, not cluster-wide, when isolating one subscription — pg_stat_reset_replication_slot(slot_name) clears one slot's counters; passing NULL resets every logical slot's statistics at once, which is rarely what a targeted investigation needs.
• Check the subscription's streaming setting before treating spill as a defect — a slot spilling instead of streaming may simply belong to a subscription where streaming was never enabled.
• Raise logical_decoding_work_mem only after confirming spill is the actual bottleneck — the same symptom of a slow-feeling pipeline can come from network throughput or apply-side contention on the subscriber, neither of which this view measures.
• Join with pg_replication_slots for the complete picture — spill and stream counters describe decoding memory pressure; slot activity and WAL retention live in the companion view.
• Re-check column coverage after upgrading to PostgreSQL 19 — mem_exceeded_count gives a more direct memory-pressure signal than inferring pressure from spill_bytes alone, once it's available.

References

• PostgreSQL Documentation — The Cumulative Statistics System — lists pg_stat_replication_slots among the collected statistics views and documents how the stats system flushes and caches data.
• PostgreSQL Documentation — pg_replication_slots — the companion view for slot existence and state, covering both physical and logical slots.
• pgpedia — pg_stat_replication_slots — per-version column definitions and the view's change history back to its PostgreSQL 14 introduction.
• Fujitsu PostgreSQL Insider — How to Gain Insight into the pg_stat_replication_slots View — worked examples of disk spilling and transaction streaming with matching column output.