PG Phriday: The Folder That Ate the Publisher

Logical replication has been part of Postgres since version 10, and the syntax page that governs it is almost comically brief. CREATE SUBSCRIPTION wants a name, a connection string, a list of publications, and then it offers one innocuous line:

[ WITH ( subscription_parameter [= value ] [, ... ] ) ]

That single line expands to more than a dozen options, and several of them change how logical replication uses storage resources. After all, subscriptions created with no WITH clause work perfectly fine. Most subscriptions in the wild don't need these tweaks, and nobody thinks about that line again, if they ever knew it existed at all.

Imagine a production system boasting several downstream logical replicas. Consider disk monitors lighting up and flagging the pg_replslot directory. It’s suddenly filling with thousands of anonymous artifacts, and nobody seems to know what is writing there or why. Well, it is Postgres writing in that directory, and the "why" is a longer story.

So what lives in that directory? What makes it balloon to terrifying proportions seemingly at random? How is logical replication involved? Is there any way to control or even stop this behavior?

The answers lie inside that very same innocuous and esoteric WITH clause. Let's see what's going on here.

No Man's Land

Let's start with the files themselves. The pg_replslot directory maintains one subdirectory per replication slot. Inside each sits a small state file recording where the slot stands, plus whatever the decoding process couldn't hold in memory. Clusters with active logical replication will have files there, others won't. Nothing too ground-breaking.

Any DBA worth their salt will perform a quick check on pg_replication_slots if logical replication is acting up. Let's start there:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       wal_status, safe_wal_size
  FROM pg_replication_slots
 ORDER BY slot_name;

 slot_name | active | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size 
-----------+--------+-------------+---------------------+------------+---------------
 sub_a     | t      | 0/1570278   | 0/852F340           | reserved   | 
 sub_b     | t      | 0/1570278   | 0/852F340           | reserved   | 
 sub_c     | t      | 0/1570278   | 0/852F340           | reserved   | 

Every slot is active and reserved rather than extended, unreserved, or lost, so the slot itself is fine. The safe_wal_size column is blank because max_slot_wal_keep_size defaults to -1, so there's technically no limit to the amount of WAL the slot might retain. There's no obvious culprit here.

The next step is to check pg_replslot for one of the slots:

$> ls -l /var/lib/postgresql/data/pg_replslot/sub_a/

total 116608
-rw------- 1 postgres postgres      200 Sep  3 14:01 state
-rw------- 1 postgres postgres 14179546 Sep  3 14:01 xid-741-lsn-0-1000000.spill
-rw------- 1 postgres postgres 20805450 Sep  3 14:01 xid-741-lsn-0-2000000.spill
-rw------- 1 postgres postgres 20805450 Sep  3 14:01 xid-741-lsn-0-3000000.spill
-rw------- 1 postgres postgres 20805450 Sep  3 14:01 xid-741-lsn-0-4000000.spill
-rw------- 1 postgres postgres 20805450 Sep  3 14:01 xid-741-lsn-0-5000000.spill
-rw------- 1 postgres postgres 20805450 Sep  3 14:01 xid-741-lsn-0-6000000.spill
-rw------- 1 postgres postgres  1183254 Sep  3 14:01 xid-741-lsn-0-7000000.spill

Now we see several spill files beside the state file, 114MB in all. The names include the transaction ID and the LSN boundary. This is the real crux of the matter. What is significant about these spill files? Why is the publisher writing them at all?

Note: this information is also available using the pg_ls_replslotdir() administrative function in Postgres 15 and up, available to superusers and members of the pg_monitor role.

Waiting for the Commit Record

Logical decoding runs on the publisher. Each replication slot gets its own walsender process, and the logical replication architecture description explains that this walsender starts logical decoding of the WAL and loads the standard output plugin. Along the way it needs somewhere to assemble what it decodes. That "somewhere" is a structure called the reorderbuffer: one per walsender to hold the changes of in-flight transactions.

Why hold them at all? Because subscribers receive changes in commit order, and WAL isn't written in commit order. Two concurrent transactions interleave their WAL records, and one may roll back after the other commits. So the walsender decodes a change, recognizes which transaction it belongs to, and sets it aside until the commit record for that transaction turns up. Only then can the whole transaction be handed to the output plugin and sent downstream.

Setting things aside costs memory. The budget comes from logical_decoding_work_mem, which defaults to 64MB. A transaction that fits stays in memory. A transaction that doesn't gets written out to pg_replslot/<slot>/, as we've already seen. Thus a long-running or otherwise enormous transaction is held until it commits, and anything the buffer can't hold remains on disk in the meantime.

But wait... Why must the walsender wait for the commit record? Technically, it doesn't. Remember that long WITH clause we mentioned earlier for setting subscription options? The streaming setting actually controls what happens during this decoding procedure. On Postgres 17 and earlier, it defaults to off, which tells the publisher to decode and store transactions locally before transmission.

Let's watch the trap spring. Here's the whole setup on a Postgres 17 publisher, one table and one publication:

-- On the publisher
CREATE TABLE spill_demo (id bigint, payload text);
CREATE PUBLICATION spill_pub FOR TABLE spill_demo;

And on each of three separate subscriber nodes, the matching table and a subscription created with nothing whatsoever in a WITH clause:

-- On subscriber node A
CREATE TABLE spill_demo (id bigint, payload text);

CREATE SUBSCRIPTION sub_a
  CONNECTION 'host=pub17 dbname=postgres user=postgres'
  PUBLICATION spill_pub;

Nodes B and C get identical treatment with sub_b and sub_c. Three subscriptions, three slots on the publisher. This is how that looks in the subscription catalog:

-- On subscriber node A
SELECT subname, substream, subtwophasestate, suborigin,
       subdisableonerr, subfailover
  FROM pg_subscription WHERE subname = 'sub_a';

-[ RECORD 1 ]----+------
subname          | sub_a
substream        | f
subtwophasestate | d
suborigin        | any
subdisableonerr  | f
subfailover      | f

The substream column is the streaming parameter as stored in the catalog, and f means off. Remember, that's the default up to Postgres 17.

Now for a workload that forces spill files. We can simulate a larger workload by dialing logical_decoding_work_mem down to 64kB on the publisher so the effect fits inside a container:

-- On the publisher
ALTER SYSTEM SET logical_decoding_work_mem = '64kB';
SELECT pg_reload_conf();
SELECT pg_stat_reset_replication_slot(NULL);

BEGIN;
INSERT INTO spill_demo
SELECT i, repeat('x', 250) FROM generate_series(1, 300000) i;

-- and now, we wait...

The wide rows ensure we have a visible payload in the pg_replslot directory, and we refrain from ending the transaction so the spill files remain visible. Here is the baseline before the insert:

$> du -sh /var/lib/postgresql/data/pg_replslot/*

8.0K    /var/lib/postgresql/data/pg_replslot/sub_a
8.0K    /var/lib/postgresql/data/pg_replslot/sub_b
8.0K    /var/lib/postgresql/data/pg_replslot/sub_c

Thirty seconds in:

$> du -sh /var/lib/postgresql/data/pg_replslot/*

114M    /var/lib/postgresql/data/pg_replslot/sub_a
114M    /var/lib/postgresql/data/pg_replslot/sub_b
114M    /var/lib/postgresql/data/pg_replslot/sub_c

$> du -sh /var/lib/postgresql/data/pg_replslot

342M    /var/lib/postgresql/data/pg_replslot

We can even see this in the pg_stat_replication_slots catalog view:

SELECT slot_name, spill_txns, spill_count,
       pg_size_pretty(spill_bytes) AS spill_bytes,
       stream_txns, stream_bytes
  FROM pg_stat_replication_slots
 ORDER BY slot_name;

 slot_name | spill_txns | spill_count | spill_bytes | stream_txns | stream_bytes 
-----------+------------+-------------+-------------+-------------+--------------
 sub_a     |          1 |        1775 | 112 MB      |           0 |            0
 sub_b     |          1 |        1775 | 112 MB      |           0 |            0
 sub_c     |          1 |        1775 | 112 MB      |           0 |            0

Our single transaction spilled 1,775 times for each node thanks to our artificially low ceiling, and stream_bytes stays at zero. You can't stream a transaction that isn't complete, after all. The contrast between spill_* and stream_* tells us explicitly what's going on during the decoding process.

Watch what happens after we commit the transaction:

$> du -sh /var/lib/postgresql/data/pg_replslot

28K     /var/lib/postgresql/data/pg_replslot

The directory is empty again. Whoever gets paged has a disk-full alert, a du that says 28K, and no forensic evidence beyond a handful of cumulative counters. Now consider that there are three of these decoding slots, one for each subscriber.

Death by a Thousand Decoders

That multiplication isn't an accident of this test. The subscription section of the docs says each subscription receives changes via one replication slot, and the configuration chapter requires max_replication_slots to cover the subscription count plus reserve, with max_wal_senders at least as large. In this case, "N subscriptions" means N slots, walsenders, and reorderbuffers. Nobody is sharing anything.

So that 300,000 row transaction, which exists exactly once in the WAL, was decoded three times and written to disk three times:

SELECT count(*) AS slots,
       pg_size_pretty(sum(spill_bytes)) AS total_spilled,
       pg_size_pretty(max(spill_bytes)) AS per_slot
  FROM pg_stat_replication_slots;

 slots | total_spilled | per_slot 
-------+---------------+----------
     3 | 335 MB        | 112 MB

We held it artificially low for illustrative purposes, but the documentation has this to say about logical_decoding_work_mem:

The "doesn't have many such connections" fragment is doing a lot of work there. The decoding must happen regardless, and we can choose to store it in memory or disk. Memory is at a premium, so past a certain subscription count, spilling to disk makes more sense.

For publications where we can generally expect slightly larger than normal transactions, there's a trick we can use. It's actually possible to set logical_decoding_work_mem on a per-user basis. Check this out:

-- On subscriber node C
CREATE ROLE spill_role LOGIN REPLICATION;
ALTER ROLE spill_role SET logical_decoding_work_mem = '64MB';
ALTER SUBSCRIPTION sub_c
  CONNECTION 'host=pub17 dbname=postgres user=spill_role';

With the lowered value of 64kB, a smaller 100,000 row transaction spilled 591 times on sub_a and sub_b at 37 MB each. Meanwhile, sub_c reported a spill_count of zero and stayed at 8.0K. It's a cheap way to insert a de-facto subscription parameter that doesn't actually exist.

Be wary: initial synchronization spawns dedicated table synchronization workers that each create a replication slot of their own. Each of these needs a decoder and allocates logical_decoding_work_mem. A lot of subscriptions and a lot of initial syncs could allocate more memory than expected.

In any case, holding all decoded transaction content in memory or disk on the publisher seems incredibly resource-intensive, doesn't it?

Like a River

That's what the streaming parameter is actually for. While off is the behavior we've been watching, on tells the publisher to forward transaction contents as they're decoded. The parallel setting, available since Postgres 16, does the same but hands those changes directly to a parallel apply worker on the subscriber.

The chapter on streaming of large transactions puts it this way:

That means spilled decoding either goes to disk or is transmitted directly to the subscriber depending on how we set the streaming parameter. The clearest way to observe this is to run both at once. We can repeat our previous open-transaction experiment with sub_a flipped to parallel rather than off.

Here's how the directory looks under that scenario:

$> du -sh /var/lib/postgresql/data/pg_replslot/*

8.0K    /var/lib/postgresql/data/pg_replslot/sub_a
114M    /var/lib/postgresql/data/pg_replslot/sub_b
114M    /var/lib/postgresql/data/pg_replslot/sub_c

This is also reflected in the replication slot statistics:

SELECT slot_name, spill_count,
       pg_size_pretty(spill_bytes) AS spill_bytes,
       stream_count, pg_size_pretty(stream_bytes) AS stream_bytes
  FROM pg_stat_replication_slots ORDER BY slot_name;

 slot_name | spill_count | spill_bytes | stream_count | stream_bytes 
-----------+-------------+-------------+--------------+--------------
 sub_a     |           0 | 0 bytes     |         1775 | 112 MB
 sub_b     |        1775 | 112 MB      |            0 | 0 bytes
 sub_c     |        1775 | 112 MB      |            0 | 0 bytes

Notice that sub_a still moved 112 MB. The publisher isn't doing less work, the evidence has merely been laundered to the subscriber. Now the subscriber is on the hook for the spilling rather than the publisher. This makes it much easier to scale logical subscriber count, because the publisher no longer needs to account for potential spilling for every subscription and initial sync event.

Where the River Ends

Here's a Postgres 18 subscriber with streaming = on, caught mid-transaction while the publisher's pg_replslot sits flat at 28K:

$> ls -l /var/lib/postgresql/18/docker/base/pgsql_tmp

total 4
drwx------ 2 postgres postgres 4096 Sep  3 14:08 pgsql_tmp125.0.fileset

With parallel, in-progress changes go straight to a free parallel apply worker and there's no pgsql_tmp directory at all. When no parallel apply worker is free, it falls back to those same temporary files. The pg_stat_subscription view reports the relationship between the two:

SELECT subname, worker_type, pid, leader_pid,
       received_lsn, latest_end_lsn
  FROM pg_stat_subscription;

 subname |  worker_type   | pid | leader_pid | received_lsn | latest_end_lsn 
---------+----------------+-----+------------+--------------+----------------
 sub_a   | parallel apply | 154 |        139 |              | 
 sub_a   | apply          | 139 |            | 0/F5A3C60    | 0/F5A3C60

The row with a leader_pid is visible proof that parallel apply is engaged. Notice what's blank there: received_lsn, latest_end_lsn, and the (unspecified here) message timestamps are all NULL for parallel apply workers. If aggregating based on this view, be sure to focus on apply workers only.

The documentation on streaming promises that changes are still applied in commit order regardless of transaction size, preserving the same guarantees as the non-streaming mode. Moving the spool downstream costs nothing but disk on the other machine, which is usually where we want it anyway.

A Return to Sanity

Thankfully, Postgres 18 flipped the default for the streaming setting from off to parallel, but I would have also been fine with on. Does that mean the problem is solved on 18? Mostly. The streaming chapter in the docs notes that Postgres still spills in some cases even with streaming enabled, such as in cases where tuples contain TOAST data.

Wide out-of-line payloads reproduce that on 18 with default settings. Here's twenty thousand rows of 6400-byte text, stored as EXTERNAL to disable compression:

-- On the publisher
CREATE TABLE toast_demo (id bigint, payload text);
ALTER TABLE toast_demo ALTER COLUMN payload SET STORAGE EXTERNAL;
ALTER PUBLICATION spill_pub ADD TABLE toast_demo;

BEGIN;
INSERT INTO toast_demo
SELECT i, repeat(md5(random()::text), 200)
  FROM generate_series(1, 20000) i;

SELECT slot_name, spill_count,
       pg_size_pretty(spill_bytes) AS spill_bytes,
       pg_size_pretty(stream_bytes) AS stream_bytes,
       pg_size_pretty(total_bytes) AS total_bytes
  FROM pg_stat_replication_slots WHERE slot_name = 'sub_a';

 slot_name | spill_count | spill_bytes | stream_bytes | total_bytes 
-----------+-------------+-------------+--------------+-------------
 sub_a     |        4000 | 136 MB      | 9723 kB      | 2852 kB

Now we can see that both spill and stream columns show some kind of non-zero value even though streaming is set to parallel. Exposure drops sharply, but it isn't zero. Instead, we have a scenario where we've streamed 2,852 kB of transaction information, but had to spill 136MB locally anyway.

Back From the Brink

There is an easy fix for those on Postgres 17 or earlier. Locating candidates is just a single quick query on the pg_subscription catalog table:

SELECT subname, subenabled,
       CASE substream WHEN 'f' THEN 'off'
                      WHEN 't' THEN 'on'
                      WHEN 'p' THEN 'parallel' END AS streaming
  FROM pg_subscription ORDER BY subname;

 subname | subenabled | streaming 
---------+------------+-----------
 sub_b   | t          | off

Then enabling streaming is one statement per subscription:

-- Works on Postgres 16 or higher
ALTER SUBSCRIPTION sub_b SET (streaming = parallel);

-- Version 14 and 15 only support on or off
ALTER SUBSCRIPTION sub_b SET (streaming = on);

No restart necessary. The apply worker notices, exits, and respawns on its own, which you can watch as a changed pid in pg_stat_subscription. Unfortunately for users of Postgres 13 and older, the streaming option doesn't exist at all. If you're in that position, we strongly recommend upgrading. Version 19 is coming soon, and when it does, even Postgres 14 will rotate out of community support.

Don't Cross the Streams

What shocks me most isn't necessarily the mystery of the unexplained disk usage. The logical replication monitoring section is rather terse and never names pg_stat_replication_slots, pg_replslot, logical_decoding_work_mem, the reorderbuffer, or spill risks. Its guidance merely compares logical replication to physical streaming replication and suggests that monitoring a publication node resembles monitoring a physical replication primary. Maybe for WAL sender lag, but the multiplicative risk here requires closer scrutiny.

The configuration settings chapter for logical replication---the first page a DBA might frantically consult---doesn't mention logical_decoding_work_mem at all. That got filed under resource consumption. A DBA following the trail of documentation basically has no way to know about pg_replslot at all. It's a riddle wrapped in a mystery inside an enigma.

So go read what your own subscriptions actually say regarding streaming. Even if you're on Postgres 18, you could be using the old defaults if any subscribers experienced an upgrade. It's one column in pg_subscription and a quick query away. Then consider that streaming is one of only two subscription options with an entire section of documentation devoted to it. It's a complicated piece of machinery that deserves respect.

There are, of course, other useful parameters lurking within the confines of logical subscriptions as well. But we'll save those for a later time. Until then, happy (logical) streaming!