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

From: SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Subject: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0
Date: 2026-04-27 08:41:18
Message-ID: CAHg+QDdKVnCLHot=AcoPpEiSyDzGz7wGYjAFHVOw57oDtmUDWQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch application/octet-stream 1.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2026-04-27 08:42:20 Re: Row pattern recognition
Previous Message Bharath Rupireddy 2026-04-27 08:36:00 Re: Fix race condition in pg_get_publication_tables with concurrent DROP TABLE