Subject: Add logical_decoding_spill_limit to cap spill file disk usage per slot

From: shawn wang <[email-masked]>
Date: Mon, 23 Mar 2026 21:19:52 +0800

Hi hackers,

 == Motivation ==

We operate a fleet of PostgreSQL instances with logical replication. On
several occasions, we have experienced production incidents where logical
decoding spill files (pg_replslot/<slot>/xid-*.spill) grew uncontrollably —
consuming tens of gigabytes and eventually filling up the data disk. This
caused the entire instance to go read-only, impacting not just replication
but all write workloads.

The typical scenario is a large transaction (e.g. bulk data load or a
long-running DDL) combined with a subscriber that is either slow or
temporarily disconnected. The reorder buffer exceeds
logical_decoding_work_mem and starts spilling, but there is no upper bound
on how much can be spilled. The only backstop today is the OS returning
ENOSPC, at which point the damage is already done.

We looked for existing protections:

   - max_slot_wal_keep_size: limits WAL retention, but does not affect
   spill files at all.
   - logical_decoding_work_mem: controls *when* spilling starts, but not
   *how much* can be spilled.
   - There is no existing GUC, patch, or commitfest entry that addresses
   spill file disk quota.

The "Report reorder buffer size" patch (CF #6053, by Ashutosh Bapat)
improves observability of reorder buffer state, which is complementary —
but observability alone cannot prevent disk-full incidents.

== Proposed solution ==

The attached patch adds a new GUC:
logical_decoding_spill_limit (integer, unit kB, default 0)

When set to a positive value, it limits the total size of on-disk spill
files per replication slot. Key design points:

   1. Tracking: We add two new fields: - ReorderBuffer.spillBytesOnDisk —
   current total on-disk spill size for this slot (unlike spillBytes which is
   a cumulative statistic counter, this is a live gauge). -
   ReorderBufferTXN.serialized_size — per-transaction on-disk size, so we can
   accurately decrement the global counter during cleanup.
   2. Increment: In ReorderBufferSerializeChange(), after a successful
   write(), both counters are incremented by the size written.
   3. Decrement: In ReorderBufferRestoreCleanup(), when spill files are
   unlinked, the global counter is decremented by the transaction's
   serialized_size.
   4. Enforcement: In ReorderBufferCheckMemoryLimit(), before calling
   ReorderBufferSerializeTXN(), we check: if (spillBytesOnDisk + txn->size >
   spill_limit) ereport(ERROR, ...) This is only checked on the spill-to-disk
   path — not on the streaming path (which involves no disk I/O).
   5. Behavior on limit exceeded: An ERROR is raised with
   ERRCODE_CONFIGURATION_LIMIT_EXCEEDED. The walsender exits, but the slot's
   restart_lsn and confirmed_flush are preserved. The subscriber can reconnect
   after the DBA:
      1. increases logical_decoding_spill_limit, or
      2. increases logical_decoding_work_mem (to reduce spilling), or
      3. switches to a streaming-capable output plugin (which avoids
      spilling entirely).
   6. Default 0 means unlimited — fully backward compatible.

== Why per-slot, not global? ==

Each ReorderBuffer instance lives in a single walsender process and
corresponds to exactly one replication slot. A per-slot limit is:

   - Lock-free (no shared memory coordination needed)
   - Simple to reason about (each slot has its own budget)
   - Sufficient to protect against disk-full (the DBA sets the limit based
   on available disk / number of slots)

A global (cross-slot) limit could be layered on top later if needed, but
would require shared-memory counters with spinlock/atomic protection.

== Performance impact ==

   - Hot path (in-memory change queuing): zero overhead.
   - Spill path: one integer comparison before serialization, one integer
   addition after write() — negligible compared to the I/O cost.
   - Cleanup path: one integer subtraction after unlink() — negligible.

Looking forward to feedback.
Thanks,
Shawn.

---

From: shawn wang <[email-masked]>
Date: Mon, 23 Mar 2026 21:23:29 +0800

This is the patch.

shawn wang <[email-masked]> 于2026年3月23日周一 21:19写道：

>    - Sufficient to protect against disk-full (the DBA sets the limit
>    based on available disk / number of slots)
>

---

From: Bharath Rupireddy <[email-masked]>
Date: Mon, 23 Mar 2026 07:24:23 -0700

Hi,

> Hi hackers,

Thank you for proposing this new feature.

>  == Motivation ==
>
> We operate a fleet of PostgreSQL instances with logical replication. On several occasions, we have experienced production incidents where logical decoding spill files (pg_replslot/<slot>/xid-*.spill) grew uncontrollably — consuming tens of gigabytes and eventually filling up the data disk. This caused the entire instance to go read-only, impacting not just replication but all write workloads.
>
> The typical scenario is a large transaction (e.g. bulk data load or a long-running DDL) combined with a subscriber that is either slow or temporarily disconnected. The reorder buffer exceeds logical_decoding_work_mem and starts spilling, but there is no upper bound on how much can be spilled. The only backstop today is the OS returning ENOSPC, at which point the damage is already done.

Having a lot of spill files also increases crash/recovery times.
However, files spilling to disk causing no-space-left-on-disk issues
leading to downtime applies to WAL files, historical catalog snapshot
files, subtransaction overflow files, CLOG (and all the subsystems
backed by SLRU data structure), etc. - basically any Postgres
subsystem writing files to disk. I'm a bit worried that we may end up
solving disk space issues, which IMHO are outside of the database
scope, in the database. Others may have different opinions though.

How common is this issue? Could you please add a test case to the
proposed patch that without this feature would otherwise hit the issue
described?

Having said that, were alternatives like disabling subscriptions when
seen occupying the disk space considered?

> max_slot_wal_keep_size: limits WAL retention, but does not affect spill files at all.
> logical_decoding_work_mem: controls *when* spilling starts, but not *how much* can be spilled.
> There is no existing GUC, patch, or commitfest entry that addresses spill file disk quota.

Interesting!

> The "Report reorder buffer size" patch (CF #6053, by Ashutosh Bapat) improves observability of reorder buffer state, which is complementary — but observability alone cannot prevent disk-full incidents.

With the proposed reorder buffer stats above, would it be possible to
have a monitoring solution (an extension or a tool) to disable
subscriptions and notify the admin? Would something like this work?

> When set to a positive value, it limits the total size of on-disk spill files per replication slot. Key design points:
>
> Tracking: We add two new fields: - ReorderBuffer.spillBytesOnDisk — current total on-disk spill size for this slot (unlike spillBytes which is a cumulative statistic counter, this is a live gauge). - ReorderBufferTXN.serialized_size — per-transaction on-disk size, so we can accurately decrement the global counter during cleanup.
> Increment: In ReorderBufferSerializeChange(), after a successful write(), both counters are incremented by the size written.
> Decrement: In ReorderBufferRestoreCleanup(), when spill files are unlinked, the global counter is decremented by the transaction's serialized_size.
> Enforcement: In ReorderBufferCheckMemoryLimit(), before calling ReorderBufferSerializeTXN(), we check: if (spillBytesOnDisk + txn->size > spill_limit) ereport(ERROR, ...) This is only checked on the spill-to-disk path — not on the streaming path (which involves no disk I/O).
> Behavior on limit exceeded: An ERROR is raised with ERRCODE_CONFIGURATION_LIMIT_EXCEEDED. The walsender exits, but the slot's restart_lsn and confirmed_flush are preserved. The subscriber can reconnect after the DBA:
>
> increases logical_decoding_spill_limit, or
> increases logical_decoding_work_mem (to reduce spilling), or
> switches to a streaming-capable output plugin (which avoids spilling entirely).

When the logical_decoding_spill_limit is exceeded, ERRORing out in the
walsender is even more problematic, right? The replication slot would
be inactive, causing bloat and preventing tuple freezing, WAL files
growth and eventually the system may hit disk-space issues - it is
like "we avoided disk space issues for one subsystem, but introduced
it for another". This looks a bit problematic IMHO. Others may have
different opinions though.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Thu, 26 Mar 2026 05:33:20 +0000

Dear Shawn,

> We operate a fleet of PostgreSQL instances with logical replication. On several
> occasions, we have experienced production incidents where logical decoding spill
> files (pg_replslot/<slot>/xid-*.spill) grew uncontrollably — consuming tens of
> gigabytes and eventually filling up the data disk. This caused the entire instance
> to go read-only, impacting not just replication but all write workloads.

We have provided the subscription option streaming=parallel since PG16. It
replicates on-going transactions and applies immediately. Does it avoid the
issue?

> 5. Behavior on limit exceeded: An ERROR is raised with ERRCODE_CONFIGURATION_LIMIT_EXCEEDED.
> The walsender exits, but the slot's restart_lsn and confirmed_flush are preserved.
> The subscriber can reconnect after the DBA:

Not sure, but doesn't it mean the error is repeating till the GUC is increased?
Also, is there any difference for the slots's behavior, with the normal walsender's
exit case?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: shawn wang <[email-masked]>
Date: Sat, 4 Apr 2026 00:11:52 +0800

Hi Kuroda,

Thank you for the review and the great questions!

> replicates on-going transactions and applies immediately. Does it avoid
the

streaming=parallel does significantly reduce publisher-side spill files
in the common case — when enabled, the reorder buffer streams changes
directly instead of spilling to disk.

However, it cannot guarantee 100% avoidance of spilling.  There are
several fallback scenarios in the code where streaming is not possible
and the reorder buffer falls back to spill-to-disk even when
streaming=parallel is configured:

  1. Snapshot not yet consistent (snapbuild.c — SnapBuildCurrentState()
     < SNAPBUILD_CONSISTENT), e.g. right after slot creation.

  2. Transaction is being re-decoded after a restart
     (SnapBuildXactNeedsSkip() returns true).

  3. Transaction contains TOAST partial changes
     (rbtxn_has_partial_change), which cannot be streamed.

  4. Transaction contains speculative inserts (INSERT ... ON CONFLICT),
     also flagged as partial changes.

  5. Transaction has no streamable changes yet
     (!rbtxn_has_streamable_change).

  6. Output plugin does not support streaming callbacks
     (e.g. test_decoding without the streaming option).

  7. Parallel apply worker is busy for >10 seconds — the leader falls
     back to serializing changes to disk
     (applyparallelworker.c, SHM_SEND_TIMEOUT_MS).

  8. No parallel worker available — the leader serializes the entire
     streamed transaction to disk (worker.c,
     get_transaction_apply_action → TRANS_LEADER_SERIALIZE).

Additionally, streaming is a *subscription-level* parameter that only
applies to built-in logical replication.  Users of pg_recvlogical or
third-party CDC tools (Debezium, etc.) consume changes directly from
the publisher's walsender and have no subscription to configure.

So streaming=parallel and logical_decoding_spill_limit are
complementary: streaming reduces spilling in the common case, while
the spill limit provides a hard safety net for the cases where
spilling is unavoidable.

> Not sure, but doesn't it mean the error is repeating till the GUC is
increased?

Good question.  Yes, if the same large transaction is re-decoded
without any configuration change, the same ERROR will occur again.
This is intentional — the behavior is analogous to temp_file_limit:
once the limit is hit, the operation fails, and it will keep failing
until the DBA takes action.

The DBA has several options to resolve it:

  - Increase logical_decoding_spill_limit.
  - Increase logical_decoding_work_mem (so less data is spilled).
  - Enable streaming on the subscriber (streaming=on or
    streaming=parallel), which avoids spilling in most cases.
  - Investigate and address the root cause (e.g. break up the
    large transaction).

The ERROR message includes the current spill size and the configured
limit, making it straightforward to diagnose.

> Also, is there any difference for the slots's behavior, with the normal
walsender's

No, the slot behavior is the same as a normal walsender exit.
Specifically:

  - The slot remains valid (it is NOT invalidated).
  - restart_lsn and confirmed_flush are preserved.
  - The subscriber can reconnect and resume from where it left off.
  - In v2, spill files are properly cleaned up in the error path
    (via WalSndErrorCleanup), so no orphaned files are left behind.

The only difference is that the walsender's exit reason is logged as
an ERROR with ERRCODE_CONFIGURATION_LIMIT_EXCEEDED, rather than a
normal shutdown.  The slot itself is in exactly the same state as if
the walsender had exited normally or the connection was dropped.

Best regards,
Shawn

---

From: shawn wang <[email-masked]>
Date: Sat, 4 Apr 2026 00:14:34 +0800

Hi Bharath,

Thank you for the thorough and thoughtful review!

> scope, in the database. Others may have different opinions though.

That's a fair philosophical point.  However, I'd argue that spill files
are uniquely unprotected compared to the other subsystems you mentioned:

  - WAL files → max_slot_wal_keep_size
  - Temp files → temp_file_limit
  - SLRU/CLOG → bounded by design (sized proportionally to active XIDs)

Spill files are the gap: they can grow proportionally to the *data
volume* of in-progress transactions, not to the number of transactions,
and there is no existing upper bound.  A single bulk-load transaction
replicated via logical decoding can generate tens of GBs of spill files
with no way for the DBA to cap it.

So this is not about solving a general OS-level disk problem in the
database; it is about providing the same kind of safety net that already
exists for WAL (max_slot_wal_keep_size) and temp files (temp_file_limit)
— a per-subsystem guardrail that the DBA can configure.

> proposed patch that without this feature would otherwise hit the issue

Typically triggered by bulk data migrations or long-running DDL on large
tables,
combined with a subscriber that fell behind.  Each incident caused the
instance
to go read-only, affecting all workloads — not just replication.

We have also seen reports of this in pgsql-general and on community
forums, though the root cause is not always identified as spill files.

Absolutely.  I will add a TAP test that:
  1. Creates 10 logical replication slots with test_decoding.
  2. Starts a large transaction that generates spill files exceeding
     the configured logical_decoding_spill_limit.
  3. Verifies that the walsender ERRORs out with the expected
     ERRCODE_CONFIGURATION_LIMIT_EXCEEDED.
  4. Verifies that spill files are cleaned up after the error.

I will include the 4 in v2.

Test case:
1. build a table:

psql -p 5432 -d postgres -c "
CREATE TABLE spill_load (
    id      bigint,
    payload text
);
"

2. build some slots:
#base

for i in $(seq 1 10); do
    psql -p 5432 -d postgres -c \
        "SELECT pg_create_logical_replication_slot('spill_slot_${i}',
'test_decoding');"
done

psql -p 5432 -d postgres -c \
    "SELECT slot_name, plugin, active FROM pg_replication_slots ORDER BY
slot_name;"

3. start pg_recvlogical:
#bash
mkdir -p /tmp/spill_test/pids

for i in $(seq 1 10); do
    pg_recvlogical \
        --dbname="port=5432 dbname=postgres" \
        --slot="spill_slot_${i}" \
        --start \
        --no-loop \
        --status-interval=0 \
        --file=/dev/null &
    echo $! > /tmp/spill_test/pids/recv_${i}.pid
    echo "Started pg_recvlogical for spill_slot_${i}, PID=$!"
done

sleep 2

psql -p 5432 -d postgres -c \
    "SELECT slot_name, active, active_pid
     FROM pg_replication_slots
     WHERE slot_name LIKE 'spill_slot_%'
     ORDER BY slot_name;"

4. make a big transaction:

psql -p 5432 -d postgres -c "
BEGIN;
INSERT INTO spill_load
    SELECT i, repeat('X', 100000)
    FROM generate_series(1, 10000000) i;
COMMIT;

"

> seen occupying the disk space considered?

Yes, we considered monitoring-based approaches, but they have inherent
limitations:

  1. Reaction latency: Even with 1-second polling, a high-throughput
     bulk load can spill gigabytes between two polls.  The spill rate
     can easily exceed 100 MB/s, so a 5-second monitoring gap means
     500 MB of uncontrolled growth.

  2. External dependency: Relying on an external tool means the
     protection is not available by default.  Every deployment must
     set up and maintain the monitoring — and if it fails or is
     misconfigured, the disk-full scenario still occurs.

  3. No subscriber-side control: pg_recvlogical users and third-party
     CDC tools (Debezium, etc.) that consume directly from the
     publisher have no "subscription" to disable.

  4. Disabling a subscription does not immediately stop spilling:
     the walsender continues processing WAL records already in flight.

The proposed GUC acts as a synchronous, in-process safety valve — it
checks the limit at the exact point where the next spill write would
occur, with zero latency.  Monitoring and the GUC are complementary:
monitoring for alerting, the GUC as a hard backstop.

> different opinions though.

This is a great point and deserves a careful answer.

First, the "inactive slot causes WAL growth" problem already has a
well-established solution: max_slot_wal_keep_size.  When this is
configured (which is recommended practice), an inactive slot will be
invalidated once WAL retention exceeds the limit, releasing both WAL
files and catalog bloat.  So the combination of:

logical_decoding_spill_limit + max_slot_wal_keep_size

provides defense in depth: the spill limit prevents the data disk from
filling up with spill files, while max_slot_wal_keep_size prevents WAL
disk from growing unboundedly due to an inactive slot.

Second, compare the two failure modes:

  (a) WITHOUT spill limit: disk fills up → the ENTIRE instance goes
      read-only or shutdown.  All workloads stop.  Recovery requires manual
      intervention (rm spill files, restart).  Data loss is possible
      if WAL writes also fail.

  (b) WITH spill limit: walsender ERRORs → only replication is
      interrupted.  The primary continues serving all read/write
      workloads normally.  The DBA receives a clear error message
      and can act: increase the limit, increase work_mem, enable
      streaming, or investigate the large transaction.

cenario (b) is strictly better than (a): it converts an
instance-wide outage into a replication-only interruption with a
clear, actionable error message.

Additionally, in the updated patch (v2), I have ensured that spill

files are properly cleaned up in the error path (via
WalSndErrorCleanup), so exceeding the limit does not leave orphaned
files on disk.

Best regards,
Shawn