MIME-Version: 1.0
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Mon, 27 Apr 2026 01:41:18 -0700
Message-ID: 
 <CAHg+QDdKVnCLHot=AcoPpEiSyDzGz7wGYjAFHVOw57oDtmUDWQ@mail.gmail.com>
Subject: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription
 max_retention_duration=0
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Amit Kapila <amit.kapila16@gmail.com>
Content-Type: multipart/mixed; boundary="000000000000c93c9306506d145e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDdKVnCLHot%3DAcoPpEiSyDzGz7wGYjAFHVOw57oDtmUDWQ%40mail.gmail.com>
Precedence: bulk

--000000000000c93c9306506d145e
Content-Type: multipart/alternative; boundary="000000000000c93c9206506d145c"

--000000000000c93c9206506d145c
Content-Type: text/plain; charset="UTF-8"

Hi Hackers,

When a subscription has retain_dead_tuples enabled with maxretention set
to zero (unlimited retention), adjust_xid_advance_interval() caps
xid_advance_interval to Min(interval, maxretention).  Since maxretention
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 results 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)

Thank
Satya

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

<div dir=3D"ltr">Hi Hackers,<div><br></div><div>When a subscription has ret=
ain_dead_tuples enabled with maxretention set<br>to zero (unlimited retenti=
on), adjust_xid_advance_interval() caps<br>xid_advance_interval to Min(inte=
rval, maxretention).=C2=A0 Since maxretention<br>is zero, this always colla=
pses the interval to zero milliseconds.<br><br>A zero makes=C2=A0

TimestampDifferenceExceeds(last_time, now, 0)=C2=A0 always</div><div>true i=
n get_candidate_xid(). This causes the apply worker to call=C2=A0</div><div=
>GetOldestActiveTransactionId() on every single WAL message. This results i=
n <br>a huge number of ProcArrayLock acquisitions under moderate write load=
.<br><br>Fix by adding a maxretention &gt; 0 guard to the cap.=C2=A0When ma=
xretention is zero ,</div><div>the exponential back-off in adjust_xid_advan=
ce_interval()<br>now works correctly, growing the interval from 100 ms towa=
rd the 180 s<br>ceiling.<br><br>Measured with perf uprobe counting GetOldes=
tActiveTransactionId calls<br>at ~39K TPS (pgbench, 5 clients):<br><br>=C2=
=A0 Before fix: 25,104 calls / 5 s =C2=A0(~5,021/s)<br>=C2=A0 After fix: =
=C2=A0 =C2=A0 31 calls / 5 s =C2=A0(~6/s)=C2=A0</div><div><br></div><div>Th=
ank</div><div>Satya</div></div>

--000000000000c93c9206506d145c--

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

RnJvbSBkZmE4OWFiYTA2NzQyNDI4ZmU1NmEyNjNhOTIzNmI4NjJiMTRlMzRiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTYXR5YSBOYXJsYXB1cmFtIDxzYXR5YW5hcmxhcHVyYW1AZ21h
aWwuY29tPgpEYXRlOiBNb24sIDI3IEFwciAyMDI2IDA4OjE3OjQyICswMDAwClN1YmplY3Q6IFtQ
QVRDSF0gRml4IGFwcGx5IHdvcmtlciBidXN5IGxvb3Agd2hlbiBzdWJzY3JpcHRpb24KIG1heF9y
ZXRlbnRpb25fZHVyYXRpb24gaXMgemVybwoKV2hlbiBhIHN1YnNjcmlwdGlvbiBoYXMgcmV0YWlu
X2RlYWRfdHVwbGVzIGVuYWJsZWQgd2l0aCBtYXhyZXRlbnRpb24gc2V0CnRvIHplcm8gKHVubGlt
aXRlZCByZXRlbnRpb24pLCBhZGp1c3RfeGlkX2FkdmFuY2VfaW50ZXJ2YWwoKSBjYXBzCnhpZF9h
ZHZhbmNlX2ludGVydmFsIHRvIE1pbihpbnRlcnZhbCwgbWF4cmV0ZW50aW9uKS4gIFNpbmNlIG1h
eHJldGVudGlvbgppcyB6ZXJvLCB0aGlzIGFsd2F5cyBjb2xsYXBzZXMgdGhlIGludGVydmFsIHRv
IHplcm8gbWlsbGlzZWNvbmRzLgoKQSB6ZXJvIGludGVybmFsIG1ha2VzIFRpbWVzdGFtcERpZmZl
cmVuY2VFeGNlZWRzKGxhc3RfdGltZSwgbm93LCAwKQphbHdheXMgdHJ1ZSBpbiBnZXRfY2FuZGlk
YXRlX3hpZCgpLiBUaGlzIGNhdXNlcyB0aGUgYXBwbHkgd29ya2VyIHRvIGNhbGwKR2V0T2xkZXN0
QWN0aXZlVHJhbnNhY3Rpb25JZCgpIG9uIGV2ZXJ5IHNpbmdsZSBXQUwgbWVzc2FnZS4gVGhpcyBy
ZXN1bHRzCmluIGEgaHVnZSBudW1iZXIgb2YgUHJvY0FycmF5TG9jayBhY3F1aXNpdGlvbnMgdW5k
ZXIgbW9kZXJhdGUgd3JpdGUgbG9hZC4KCkZpeCBieSBhZGRpbmcgYSBtYXhyZXRlbnRpb24gPiAw
IGd1YXJkIHRvIHRoZSBjYXAuIFdoZW4gbWF4cmV0ZW50aW9uIGlzIHplcm8sCnRoZSBleHBvbmVu
dGlhbCBiYWNrLW9mZiBpbiBhZGp1c3RfeGlkX2FkdmFuY2VfaW50ZXJ2YWwoKSBub3cgd29ya3MK
Y29ycmVjdGx5LCBncm93aW5nIHRoZSBpbnRlcnZhbCBmcm9tIDEwMCBtcyB0b3dhcmQgdGhlIDE4
MCBzIGNlaWxpbmcuCi0tLQogc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC93b3JrZXIu
YyB8IDUgKysrLS0KIDEgZmlsZSBjaGFuZ2VkLCAzIGluc2VydGlvbnMoKyksIDIgZGVsZXRpb25z
KC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC93b3JrZXIu
YyBiL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvd29ya2VyLmMKaW5kZXggOWYyYTE2
YjEuLmRkNmZjMzhhIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9sb2dpY2Fs
L3dvcmtlci5jCisrKyBiL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvd29ya2VyLmMK
QEAgLTQ5OTcsOSArNDk5NywxMCBAQCBhZGp1c3RfeGlkX2FkdmFuY2VfaW50ZXJ2YWwoUmV0YWlu
RGVhZFR1cGxlc0RhdGEgKnJkdF9kYXRhLCBib29sIG5ld194aWRfZm91bmQpCiAKIAkvKgogCSAq
IEVuc3VyZSB0aGUgd2FpdCB0aW1lIHJlbWFpbnMgd2l0aGluIHRoZSBtYXhpbXVtIHJldGVudGlv
biB0aW1lIGxpbWl0Ci0JICogd2hlbiByZXRlbnRpb24gaXMgYWN0aXZlLgorCSAqIHdoZW4gcmV0
ZW50aW9uIGlzIGFjdGl2ZS4gIFNraXAgdGhpcyBjYXAgd2hlbiBtYXhyZXRlbnRpb24gaXMgemVy
by4KIAkgKi8KLQlpZiAoTXlTdWJzY3JpcHRpb24tPnJldGVudGlvbmFjdGl2ZSkKKwlpZiAoTXlT
dWJzY3JpcHRpb24tPnJldGVudGlvbmFjdGl2ZSAmJiBNeVN1YnNjcmlwdGlvbi0+bWF4cmV0ZW50
aW9uID4gMCkKIAkJcmR0X2RhdGEtPnhpZF9hZHZhbmNlX2ludGVydmFsID0gTWluKHJkdF9kYXRh
LT54aWRfYWR2YW5jZV9pbnRlcnZhbCwKIAkJCQkJCQkJCQkJIE15U3Vic2NyaXB0aW9uLT5tYXhy
ZXRlbnRpb24pOwogfQotLSAKMi40My4wCgo=
--000000000000c93c9306506d145e--