MIME-Version: 1.0
References: 
 <CAHg+QDdKVnCLHot=AcoPpEiSyDzGz7wGYjAFHVOw57oDtmUDWQ@mail.gmail.com>
 <CAJpy0uDGM8eVqMORHKw61mm24AjH7jPZAhUaN3YeJrZ1bdpQFQ@mail.gmail.com>
In-Reply-To: 
 <CAJpy0uDGM8eVqMORHKw61mm24AjH7jPZAhUaN3YeJrZ1bdpQFQ@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Mon, 27 Apr 2026 10:02:41 -0700
Message-ID: 
 <CAHg+QDfQEX5b=4MB=67WgPrF4t8gyUMWyFp7-hFe=EAFW-qALA@mail.gmail.com>
Subject: Re: [Patch]: Fix excessive ProcArrayLock acquisitions with
 subscription max_retention_duration=0
To: shveta malik <shveta.malik@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Amit Kapila <amit.kapila16@gmail.com>
Content-Type: multipart/mixed; boundary="000000000000db234406507415ce"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDfQEX5b%3D4MB%3D67WgPrF4t8gyUMWyFp7-hFe%3DEAFW-qALA%40mail.gmail.com>
Precedence: bulk

--000000000000db234406507415ce
Content-Type: multipart/alternative; boundary="000000000000db234306507415cc"

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

Hi,

On Mon, Apr 27, 2026 at 2:48=E2=80=AFAM shveta malik <shveta.malik@gmail.co=
m> wrote:

> On Mon, Apr 27, 2026 at 2:11=E2=80=AFPM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > Hi Hackers,
> >
> > When a subscription has retain_dead_tuples enabled with maxretention se=
t
> > to zero (unlimited retention), adjust_xid_advance_interval() caps
> > xid_advance_interval to Min(interval, maxretention).  Since maxretentio=
n
> > is zero, this always collapses the interval to zero milliseconds.
> >
> > A zero makes  TimestampDifferenceExceeds(last_time, now, 0)  always
> > true in get_candidate_xid(). This causes the apply worker to call
> > GetOldestActiveTransactionId() on every single WAL message. This result=
s
> in
> > a huge number of ProcArrayLock acquisitions under moderate write load.
> >
> > Fix by adding a maxretention > 0 guard to the cap. When maxretention is
> zero ,
> > the exponential back-off in adjust_xid_advance_interval()
> > now works correctly, growing the interval from 100 ms toward the 180 s
> > ceiling.
> >
> > Measured with perf uprobe counting GetOldestActiveTransactionId calls
> > at ~39K TPS (pgbench, 5 clients):
> >
> >   Before fix: 25,104 calls / 5 s  (~5,021/s)
> >   After fix:     31 calls / 5 s  (~6/s)
> >
>
> Thanks for reporting it. I am reveiwing the problem sattement.
> Meanwhile can you please look at it, I am getting the following error
> while applying the patch on my Ubuntu setup (git am):
>
> error: corrupt patch at line 22
>

Thanks! Please find the updated v2 patch.

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi,</div><br><div class=3D"gmail_quote gm=
ail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Apr 27, =
2026 at 2:48=E2=80=AFAM shveta malik &lt;<a href=3D"mailto:shveta.malik@gma=
il.com">shveta.malik@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">On Mon, Apr 27, 2026 at 2:11=E2=80=AFPM SATYA=
NARAYANA NARLAPURAM<br>
&lt;<a href=3D"mailto:satyanarlapuram@gmail.com" target=3D"_blank">satyanar=
lapuram@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; Hi Hackers,<br>
&gt;<br>
&gt; When a subscription has retain_dead_tuples enabled with maxretention s=
et<br>
&gt; to zero (unlimited retention), adjust_xid_advance_interval() caps<br>
&gt; xid_advance_interval to Min(interval, maxretention).=C2=A0 Since maxre=
tention<br>
&gt; is zero, this always collapses the interval to zero milliseconds.<br>
&gt;<br>
&gt; A zero makes=C2=A0 TimestampDifferenceExceeds(last_time, now, 0)=C2=A0=
 always<br>
&gt; true in get_candidate_xid(). This causes the apply worker to call<br>
&gt; GetOldestActiveTransactionId() on every single WAL message. This resul=
ts in<br>
&gt; a huge number of ProcArrayLock acquisitions under moderate write load.=
<br>
&gt;<br>
&gt; Fix by adding a maxretention &gt; 0 guard to the cap. When maxretentio=
n is zero ,<br>
&gt; the exponential back-off in adjust_xid_advance_interval()<br>
&gt; now works correctly, growing the interval from 100 ms toward the 180 s=
<br>
&gt; ceiling.<br>
&gt;<br>
&gt; Measured with perf uprobe counting GetOldestActiveTransactionId calls<=
br>
&gt; at ~39K TPS (pgbench, 5 clients):<br>
&gt;<br>
&gt;=C2=A0 =C2=A0Before fix: 25,104 calls / 5 s=C2=A0 (~5,021/s)<br>
&gt;=C2=A0 =C2=A0After fix:=C2=A0 =C2=A0 =C2=A031 calls / 5 s=C2=A0 (~6/s)<=
br>
&gt;<br>
<br>
Thanks for reporting it. I am reveiwing the problem sattement.<br>
Meanwhile can you please look at it, I am getting the following error<br>
while applying the patch on my Ubuntu setup (git am):<br>
<br>
error: corrupt patch at line 22<br></blockquote><div><br></div><div>Thanks!=
 Please find the updated v2 patch.=C2=A0</div></div></div>

--000000000000db234306507415cc--

--000000000000db234406507415ce
Content-Type: application/octet-stream;
	name="v2-0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch"
Content-Disposition: attachment;
	filename="v2-0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mohg3htq0>
X-Attachment-Id: f_mohg3htq0

RnJvbSAxOGM0NzM4YWIxMDBlYTI5OTY2Y2VkY2FiY2QxZGUxNjQ1ZmYzNTc4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTYXR5YSBOYXJsYXB1cmFtIDxzYXR5YW5hcmxhcHVyYW1AZ21h
aWwuY29tPgpEYXRlOiBNb24sIDI3IEFwciAyMDI2IDE2OjUzOjM3ICswMDAwClN1YmplY3Q6IFtQ
QVRDSF0gRml4IGFwcGx5IHdvcmtlciBidXN5IGxvb3Agd2hlbiBzdWJzY3JpcHRpb24KIG1heF9y
ZXRlbnRpb25fZHVyYXRpb24gaXMgemVybwoKV2hlbiBhIHN1YnNjcmlwdGlvbiBoYXMgcmV0YWlu
X2RlYWRfdHVwbGVzIGVuYWJsZWQgd2l0aCBtYXhyZXRlbnRpb24gc2V0CnRvIHplcm8gKHVubGlt
aXRlZCByZXRlbnRpb24pLCBhZGp1c3RfeGlkX2FkdmFuY2VfaW50ZXJ2YWwoKSBjYXBzCnhpZF9h
ZHZhbmNlX2ludGVydmFsIHRvIE1pbihpbnRlcnZhbCwgbWF4cmV0ZW50aW9uKS4gIFNpbmNlIG1h
eHJldGVudGlvbgppcyB6ZXJvLCB0aGlzIGFsd2F5cyBjb2xsYXBzZXMgdGhlIGludGVydmFsIHRv
IHplcm8gbWlsbGlzZWNvbmRzLgoKQSB6ZXJvIGludGVybmFsIG1ha2VzIFRpbWVzdGFtcERpZmZl
cmVuY2VFeGNlZWRzKGxhc3RfdGltZSwgbm93LCAwKQphbHdheXMgdHJ1ZSBpbiBnZXRfY2FuZGlk
YXRlX3hpZCgpLiBUaGlzIGNhdXNlcyB0aGUgYXBwbHkgd29ya2VyIHRvIGNhbGwKR2V0T2xkZXN0
QWN0aXZlVHJhbnNhY3Rpb25JZCgpIG9uIGV2ZXJ5IHNpbmdsZSBXQUwgbWVzc2FnZS4gVGhpcyBy
ZXN1bHRzCmluIGEgaHVnZSBudW1iZXIgb2YgUHJvY0FycmF5TG9jayBhY3F1aXNpdGlvbnMgdW5k
ZXIgbW9kZXJhdGUgd3JpdGUgbG9hZC4KCkZpeCBieSBhZGRpbmcgYSBtYXhyZXRlbnRpb24gPiAw
IGd1YXJkIHRvIHRoZSBjYXAuIFdoZW4gbWF4cmV0ZW50aW9uIGlzCnplcm8sIHRoZSBleHBvbmVu
dGlhbCBiYWNrLW9mZiBpbiBhZGp1c3RfeGlkX2FkdmFuY2VfaW50ZXJ2YWwoKSBub3cKd29ya3Mg
Y29ycmVjdGx5LCBncm93aW5nIHRoZSBpbnRlcnZhbCBmcm9tIDEwMCBtcyB0b3dhcmQgdGhlIDE4
MCBzCmNlaWxpbmcuCi0tLQogc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC93b3JrZXIu
YyB8IDUgKysrLS0KIDEgZmlsZSBjaGFuZ2VkLCAzIGluc2VydGlvbnMoKyksIDIgZGVsZXRpb25z
KC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC93b3JrZXIu
YyBiL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvd29ya2VyLmMKaW5kZXggOWYyYTE2
YjEuLmRkNmZjMzhhIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9sb2dpY2Fs
L3dvcmtlci5jCisrKyBiL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvd29ya2VyLmMK
QEAgLTQ5OTcsOSArNDk5NywxMCBAQCBhZGp1c3RfeGlkX2FkdmFuY2VfaW50ZXJ2YWwoUmV0YWlu
RGVhZFR1cGxlc0RhdGEgKnJkdF9kYXRhLCBib29sIG5ld194aWRfZm91bmQpCiAKIAkvKgogCSAq
IEVuc3VyZSB0aGUgd2FpdCB0aW1lIHJlbWFpbnMgd2l0aGluIHRoZSBtYXhpbXVtIHJldGVudGlv
biB0aW1lIGxpbWl0Ci0JICogd2hlbiByZXRlbnRpb24gaXMgYWN0aXZlLgorCSAqIHdoZW4gcmV0
ZW50aW9uIGlzIGFjdGl2ZS4gIFNraXAgdGhpcyBjYXAgd2hlbiBtYXhyZXRlbnRpb24gaXMgemVy
bywKKwkgKiB3aGljaCBtZWFucyB1bmxpbWl0ZWQgcmV0ZW50aW9uIChubyB0aW1lb3V0KS4KIAkg
Ki8KLQlpZiAoTXlTdWJzY3JpcHRpb24tPnJldGVudGlvbmFjdGl2ZSkKKwlpZiAoTXlTdWJzY3Jp
cHRpb24tPnJldGVudGlvbmFjdGl2ZSAmJiBNeVN1YnNjcmlwdGlvbi0+bWF4cmV0ZW50aW9uID4g
MCkKIAkJcmR0X2RhdGEtPnhpZF9hZHZhbmNlX2ludGVydmFsID0gTWluKHJkdF9kYXRhLT54aWRf
YWR2YW5jZV9pbnRlcnZhbCwKIAkJCQkJCQkJCQkJIE15U3Vic2NyaXB0aW9uLT5tYXhyZXRlbnRp
b24pOwogfQotLSAKMi40My4wCgo=
--000000000000db234406507415ce--