Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix xid_advance_interval when max_retention_duration is 0.

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

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-27T08:41:18Z

    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

  2. Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

    shveta malik <shveta.malik@gmail.com> — 2026-04-27T09:48:23Z

    On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> 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)
>

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

  3. Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

    shveta malik <shveta.malik@gmail.com> — 2026-04-27T10:15:51Z

    On Mon, Apr 27, 2026 at 3:18 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > 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.
> >

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

> > 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.

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

> > 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)
> >

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

thanks
Shveta

  4. Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-27T17:02:41Z

    Hi,

On Mon, Apr 27, 2026 at 2:48 AM shveta malik <shveta.malik@gmail.com> wrote:

> On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > 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)
> >
>
> 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.

  5. Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

    shveta malik <shveta.malik@gmail.com> — 2026-04-28T04:08:29Z

    On Mon, Apr 27, 2026 at 10:32 PM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> Hi,
>
> On Mon, Apr 27, 2026 at 2:48 AM shveta malik <shveta.malik@gmail.com> wrote:
>>
>> On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
>> <satyanarlapuram@gmail.com> wrote:
>> >
>> > 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)
>> >
>>
>> 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.

Thanks. The patch looks good.

thanks
Shveta

  6. Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

    Nisha Moond <nisha.moond412@gmail.com> — 2026-04-28T08:33:48Z

    On Mon, Apr 27, 2026 at 10:32 PM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> Hi,
>
> On Mon, Apr 27, 2026 at 2:48 AM shveta malik <shveta.malik@gmail.com> wrote:
>>
>> On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
>> <satyanarlapuram@gmail.com> wrote:
>> >
>> > 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)
>> >
>>
>> 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.

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

  7. Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0

    Amit Kapila <amit.kapila16@gmail.com> — 2026-04-28T10:58:29Z

    On Tue, Apr 28, 2026 at 9:38 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Mon, Apr 27, 2026 at 10:32 PM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > Hi,
> >
> > On Mon, Apr 27, 2026 at 2:48 AM shveta malik <shveta.malik@gmail.com> wrote:
> >>
> >> On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
> >> <satyanarlapuram@gmail.com> wrote:
> >> >
> >> > 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)
> >> >
> >>
> >> 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.
>
> Thanks. The patch looks good.
>

LGTM as well, so pushed.

-- 
With Regards,
Amit Kapila.