MIME-Version: 1.0
References: 
 <CA+T=_GU-vTxFqRwWJMR4Hz8YUXkpUv_q6Nm3CrTqHNbhCrS5BA@mail.gmail.com>
 <OS9PR01MB1214972CB037BE2C6F1DE7CB2F556A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
In-Reply-To: 
 <OS9PR01MB1214972CB037BE2C6F1DE7CB2F556A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
From: shawn wang <shawn.wang.pg@gmail.com>
Date: Sat, 4 Apr 2026 00:11:52 +0800
Message-ID: 
 <CA+T=_GUf17BqsLRUM36c_=h4hOcS9fYDMYWdRZL3ALL_M88GGA@mail.gmail.com>
Subject: Re: Add logical_decoding_spill_limit to cap spill file disk usage per
 slot
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008e900a064e909636"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BT%3D_GUf17BqsLRUM36c_%3Dh4hOcS9fYDMYWdRZL3ALL_M88GGA%40mail.gmail.com>
Precedence: bulk

--0000000000008e900a064e909636
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Kuroda,

Thank you for the review and the great questions!


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

streaming=3Dparallel does significantly reduce publisher-side spill files
in the common case =E2=80=94 when enabled, the reorder buffer streams chang=
es
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=3Dparallel is configured:

  1. Snapshot not yet consistent (snapbuild.c =E2=80=94 SnapBuildCurrentSta=
te()
     < 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 =E2=80=94 the leader fal=
ls
     back to serializing changes to disk
     (applyparallelworker.c, SHM_SEND_TIMEOUT_MS).

  8. No parallel worker available =E2=80=94 the leader serializes the entir=
e
     streamed transaction to disk (worker.c,
     get_transaction_apply_action =E2=86=92 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=3Dparallel 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 =E2=80=94 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=3Don or
    streaming=3Dparallel), 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
> exit case?

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

--0000000000008e900a064e909636
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div>Hi Kuroda,<br>
<br>
Thank you for the review and the great questions!</div><div><br>
<br>
&gt; We have provided the subscription option streaming=3Dparallel since PG=
16. It<br>
&gt; replicates on-going transactions and applies immediately. Does it avoi=
d the<br>
&gt; issue?<br>
<br></div><div>
streaming=3Dparallel does significantly reduce publisher-side spill files<b=
r>
in the common case =E2=80=94 when enabled, the reorder buffer streams chang=
es<br>
directly instead of spilling to disk.<br>
<br>
However, it cannot guarantee 100% avoidance of spilling.=C2=A0 There are<br=
>
several fallback scenarios in the code where streaming is not possible<br>
and the reorder buffer falls back to spill-to-disk even when<br>
streaming=3Dparallel is configured:<br>
<br>
=C2=A0 1. Snapshot not yet consistent (snapbuild.c =E2=80=94 SnapBuildCurre=
ntState()<br>
=C2=A0 =C2=A0 =C2=A0&lt; SNAPBUILD_CONSISTENT), e.g. right after slot creat=
ion.<br>
<br>
=C2=A0 2. Transaction is being re-decoded after a restart<br>
=C2=A0 =C2=A0 =C2=A0(SnapBuildXactNeedsSkip() returns true).<br>
<br>
=C2=A0 3. Transaction contains TOAST partial changes<br>
=C2=A0 =C2=A0 =C2=A0(rbtxn_has_partial_change), which cannot be streamed.<b=
r>
<br>
=C2=A0 4. Transaction contains speculative inserts (INSERT ... ON CONFLICT)=
,<br>
=C2=A0 =C2=A0 =C2=A0also flagged as partial changes.<br>
<br>
=C2=A0 5. Transaction has no streamable changes yet<br>
=C2=A0 =C2=A0 =C2=A0(!rbtxn_has_streamable_change).<br>
<br>
=C2=A0 6. Output plugin does not support streaming callbacks<br>
=C2=A0 =C2=A0 =C2=A0(e.g. test_decoding without the streaming option).<br>
<br>
=C2=A0 7. Parallel apply worker is busy for &gt;10 seconds =E2=80=94 the le=
ader falls<br>
=C2=A0 =C2=A0 =C2=A0back to serializing changes to disk<br>
=C2=A0 =C2=A0 =C2=A0(applyparallelworker.c, SHM_SEND_TIMEOUT_MS).<br>
<br>
=C2=A0 8. No parallel worker available =E2=80=94 the leader serializes the =
entire<br>
=C2=A0 =C2=A0 =C2=A0streamed transaction to disk (worker.c,<br>
=C2=A0 =C2=A0 =C2=A0get_transaction_apply_action =E2=86=92 TRANS_LEADER_SER=
IALIZE).<br>
<br>
Additionally, streaming is a *subscription-level* parameter that only<br>
applies to built-in logical replication.=C2=A0 Users of pg_recvlogical or<b=
r>
third-party CDC tools (Debezium, etc.) consume changes directly from<br>
the publisher&#39;s walsender and have no subscription to configure.<br>
<br>
So streaming=3Dparallel and logical_decoding_spill_limit are<br>
complementary: streaming reduces spilling in the common case, while<br>
the spill limit provides a hard safety net for the cases where<br>
spilling is unavoidable.</div><div><br>
<br>
&gt; Not sure, but doesn&#39;t it mean the error is repeating till the GUC =
is increased?<br>
<br></div><div>
Good question.=C2=A0 Yes, if the same large transaction is re-decoded<br>
without any configuration change, the same ERROR will occur again.<br>
This is intentional =E2=80=94 the behavior is analogous to temp_file_limit:=
<br>
once the limit is hit, the operation fails, and it will keep failing<br>
until the DBA takes action.<br>
<br>
The DBA has several options to resolve it:<br>
<br>
=C2=A0 - Increase logical_decoding_spill_limit.<br>
=C2=A0 - Increase logical_decoding_work_mem (so less data is spilled).<br>
=C2=A0 - Enable streaming on the subscriber (streaming=3Don or<br>
=C2=A0 =C2=A0 streaming=3Dparallel), which avoids spilling in most cases.<b=
r>
=C2=A0 - Investigate and address the root cause (e.g. break up the<br>
=C2=A0 =C2=A0 large transaction).<br>
<br>
The ERROR message includes the current spill size and the configured<br>
limit, making it straightforward to diagnose.</div><div><br>
<br>
&gt; Also, is there any difference for the slots&#39;s behavior, with the n=
ormal walsender&#39;s<br>
&gt; exit case?<br>
<br></div><div>
No, the slot behavior is the same as a normal walsender exit.<br>
Specifically:<br>
<br>
=C2=A0 - The slot remains valid (it is NOT invalidated).<br>
=C2=A0 - restart_lsn and confirmed_flush are preserved.<br>
=C2=A0 - The subscriber can reconnect and resume from where it left off.<br=
>
=C2=A0 - In v2, spill files are properly cleaned up in the error path<br>
=C2=A0 =C2=A0 (via WalSndErrorCleanup), so no orphaned files are left behin=
d.<br>
<br>
The only difference is that the walsender&#39;s exit reason is logged as<br=
>
an ERROR with ERRCODE_CONFIGURATION_LIMIT_EXCEEDED, rather than a<br>
normal shutdown.=C2=A0 The slot itself is in exactly the same state as if<b=
r>
the walsender had exited normally or the connection was dropped.<br>
<br>
Best regards,<br>
Shawn<br>
</div>

--0000000000008e900a064e909636--