Subject: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

From: SATYANARAYANA NARLAPURAM <[email-masked]>
Date: Mon, 27 Apr 2026 01:41:18 -0700

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

---

From: shveta malik <[email-masked]>
Date: Mon, 27 Apr 2026 15:18:23 +0530

On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
<[email-masked]> wrote:
> Fix by adding a maxretention > 0 guard to the cap. When maxretention is zero ,

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
Shveta

---

From: shveta malik <[email-masked]>
Date: Mon, 27 Apr 2026 15:45:51 +0530

> > a huge number of ProcArrayLock acquisitions under moderate write load.

 I agree with the problem statement. I can see it in my debugging.

> > now works correctly, growing the interval from 100 ms toward the 180 s

Yes, this should work. Let's see what others have to say on this.

> >   After fix:     31 calls / 5 s  (~6/s)

Just curious, how did you catch this problem? Did it show up in any of
your profiling reports?

thanks
Shveta

---

From: SATYANARAYANA NARLAPURAM <[email-masked]>
Date: Mon, 27 Apr 2026 10:02:41 -0700

Hi,

> error: corrupt patch at line 22

Thanks! Please find the updated v2 patch.

---

From: shveta malik <[email-masked]>
Date: Tue, 28 Apr 2026 09:38:29 +0530

> Thanks! Please find the updated v2 patch.

Thanks. The patch looks good.

thanks
Shveta

---

From: Nisha Moond <[email-masked]>
Date: Tue, 28 Apr 2026 14:03:48 +0530

> Thanks! Please find the updated v2 patch.

Thanks for the patch. I am able to reproduce the reported issue in
debugging. The  xid_advance_interval stays 0 when retain_dead_tuples
is enabled but max_retention_duration is off which is unexpected
behavior.

Confirmed that the patch fixed it.

--
Thanks,
Nisha

---

From: Amit Kapila <[email-masked]>
Date: Tue, 28 Apr 2026 16:28:29 +0530

> Thanks. The patch looks good.

LGTM as well, so pushed.

-- 
With Regards,
Amit Kapila.