pg_stat_replication_slots and decoding spill

Decoding has a memory budget, and it was invisible

Logical replication does not ship the write-ahead log. A walsender reads the log, reassembles it into transactions, and hands the reassembled transactions to an output plugin, which is what the subscriber actually receives. Reassembly means holding a transaction’s changes until it commits, because a transaction that aborts must never be sent, and holding them means memory.

There is a budget for that memory, per decoding session, and when a transaction exceeds it the changes are written to files on the primary’s disk and read back at commit time. This is not an error and nothing logs it at default settings. The subscriber simply receives that transaction later and the primary does more I/O than anyone expected. Before PostgreSQL 14 the only evidence was files appearing under the replication slot directory, which nobody watches.

PostgreSQL 14 added a view with one row per logical slot, counting transactions spilled, individual spill operations, bytes spilled, and the same three for streaming, which is the alternative behaviour where a large transaction is sent to the subscriber before it commits. It also counts the transactions and bytes the slot has decoded in total, so the spill share can be expressed against something. The System Views section of the release notes lists it beside the write-ahead log view, and mentions in passing the reset function that comes with it.

One large transaction against a small budget

The fixture is a slot, a table, and a single transaction of thirty thousand rows against a decoding budget of sixty-four kilobytes, which is small enough that a transaction of any size will exceed it.

SELECT slot_name FROM pg_create_logical_replication_slot('audit_feed', 'test_decoding');
CREATE TABLE dispatch (id integer PRIMARY KEY, note text);
BEGIN;
INSERT INTO dispatch SELECT g, repeat('o', 200) FROM generate_series(1, 30000) AS g;
COMMIT;
SELECT count(*) AS changes_decoded FROM pg_logical_slot_get_changes('audit_feed', NULL, NULL);
SELECT pg_sleep(1);
SELECT slot_name, spill_txns, spill_count, pg_size_pretty(spill_bytes) AS spilled,
       total_txns, pg_size_pretty(total_bytes) AS decoded
FROM pg_stat_replication_slots;

 slot_name  
------------
 audit_feed
(1 row)

CREATE TABLE
BEGIN
INSERT 0 30000
COMMIT
 changes_decoded 
-----------------
           30004
(1 row)

 pg_sleep 
----------
 
(1 row)

 slot_name  | spill_txns | spill_count | spilled | total_txns | decoded 
------------+------------+-------------+---------+------------+---------
 audit_feed |          1 |         154 | 9844 kB |          2 | 9849 kB
(1 row)

One transaction, and it was spilled to disk and read back well over a hundred times. Nearly everything the slot decoded went out through the filesystem on its way to the plugin.

The ratio to watch is the spilled bytes against the decoded bytes, and in this fixture it is almost one. That is the number that says the budget is wrong for the workload rather than that the workload is large. Raising logical_decoding_work_mem is the direct fix and it is per walsender, so the memory is multiplied by the number of active logical connections, which is the reason it defaults low.

The counters are per slot and they are cumulative, so the useful form is a rate. There is a reset function taking a slot name, which is the right tool before a measured experiment, and a trap while you are still on 14: the reset is a message to the statistics collector, so a read in the same session immediately afterwards can still return the old values. On 15 and later that indirection is gone, which is one of the quieter reasons to leave this version.

The join that silently returns nothing

This is the mistake worth naming, because it produces an empty result rather than an error and empty results get read as “no problem here”. The statistics view has a row only for logical slots. Physical slots, the ones a streaming standby uses, never appear in it at all.

SELECT pg_create_physical_replication_slot('standby_west');
SELECT r.slot_name, r.slot_type, r.active,
       s.slot_name IS NOT NULL AS has_stats_row
FROM pg_replication_slots r
LEFT JOIN pg_stat_replication_slots s ON s.slot_name = r.slot_name
ORDER BY r.slot_name;

 pg_create_physical_replication_slot 
-------------------------------------
 (standby_west,)
(1 row)

  slot_name   | slot_type | active | has_stats_row 
--------------+-----------+--------+---------------
 audit_feed   | logical   | f      | t
 standby_west | physical  | f      | f
(2 rows)

Write that as an inner join and your physical slots vanish from the dashboard. Write it as a filter on the statistics view and the alert that was supposed to catch a stuck standby is watching a set that never contains one. The outer join above is the shape to use, and the has_stats_row column is worth keeping in the output rather than in your head.

The other half of slot health is not in this view either. Whether a slot is retaining log, how much, and whether it has been invalidated are questions for pg_replication_slots, and that is the catalog which actually changed after 14.

SELECT count(*) FILTER (WHERE attname = 'invalidation_reason') AS has_invalidation_reason,
       count(*) FILTER (WHERE attname = 'inactive_since')      AS has_inactive_since,
       count(*) FILTER (WHERE attname = 'conflicting')         AS has_conflicting
FROM pg_attribute
WHERE attrelid = 'pg_replication_slots'::regclass AND attnum > 0;

On 14 the answer is that none of them exists:

 has_invalidation_reason | has_inactive_since | has_conflicting 
-------------------------+--------------------+-----------------
                       0 |                  0 |               0
(1 row)

On 18 all three do:

 has_invalidation_reason | has_inactive_since | has_conflicting 
-------------------------+--------------------+-----------------
                       1 |                  1 |               1
(1 row)

A 14 cluster can tell you a slot was invalidated only by inference, from the write-ahead log status column going to lost, and it cannot tell you why or when the slot last did anything. From 16 a logical slot says whether it conflicts with recovery, and from 17 the catalog names the invalidation reason and records when the slot last had a consumer. If you are writing slot alerting on 14 that has to survive an upgrade, write it against the status column and treat the three newer columns as an enrichment rather than a rewrite.

The counters are the slot’s, not the subscription’s

Two properties of these rows decide how much you can ask of them, and both follow from the same thing: the statistics are keyed by slot name and belong to the slot rather than to whatever is consuming it.

The first is that recreating a slot resets them. Drop a slot and create another with the same name, which is a normal step when repairing a subscription, and the row that comes back is a fresh one at zero. Nothing warns you and nothing marks the discontinuity, so a rate computed across that moment reads as a sudden calm. If your process for fixing a broken subscription involves recreating the slot, the accompanying step is to note the time, because the counters will not.

The second is that the name is all the identity there is. A slot named after the subscriber it feeds gives you a row you can read; a slot named sub_1 gives you a row that means nothing on a dashboard six months later. This is the cheapest thing on this page to get right and the most annoying to fix afterwards, because renaming a slot means dropping it, which is the case above.

There is also a limit on what the decoded byte count can tell you. It counts what the walsender handed to the output plugin, which is not what went over the network to the subscriber and is not what was read from the log. A plugin that filters heavily reads a great deal and emits very little, and both of those are invisible here. Use the count for spill ratios and for noticing that a slot suddenly became busy, not as a measure of replication traffic.

An empty view has two meanings

Before reading anything into a view with no rows, check which of the two reasons you are looking at. A cluster whose write-ahead log level is replica cannot have a logical slot at all, so the view exists, returns nothing, and is telling you about the server’s configuration rather than about its replication. A cluster at logical with no rows genuinely has no logical slots.

The distinction matters most on a standby, and on this version the answer is blunt. Slots are not replicated to a standby, and decoding from a standby is not possible at all before PostgreSQL 16. So a failover on a 14 cluster loses every logical slot on the old primary, and the subscriptions behind them have to be rebuilt from a fresh slot and a fresh snapshot, which for a large database is hours rather than minutes. Any runbook that treats logical replication as something a failover preserves is wrong on this version, and the correct step is to plan the rebuild in advance.

That is also the strongest single reason a fleet doing logical replication has to move off 14 rather than merely wanting to. The counters on this page tell you how a slot is behaving; what they cannot tell you is that the slot will not be there after the next failover.

What to watch on a slot you own

• Spilled bytes as a share of decoded bytes, per slot, as a rate. Persistently high says the decoding budget is wrong; a spike that matches a batch job says the batch job is the thing to split.
• Streamed transactions, which on this version means a large transaction was sent before committing. That is usually the better outcome, and it is worth knowing which slots do it so a subscriber that cannot handle it is not surprised.
• Retained log, from the slot catalog rather than from here. A slot nobody consumes is the failure that fills a disk, and no counter in this view moves while it happens.

The last of those is the one to page on. The spill counters are a tuning signal and they do not belong on an alert rule with a fixed threshold, because the number that matters is the share rather than the count.

What it costs to observe

Nothing gates the view. The counters are maintained by the walsender whether or not anyone reads them, and the view is one row per slot assembled on demand.

What does cost something is the thing the view measures. Every byte of spill is a write and a read on the primary’s data directory, competing with the workload, and it is charged to the same storage budget. The reason to watch these counters is not that the counters are expensive, it is that a fleet can carry a substantial and entirely invisible I/O load in them, discovered only when somebody asks why the primary’s write volume does not match what the workload actually generated.