max_locks_per_transaction v18

On Mon, 18 Aug 2025 at 15:13, James Pang <jamespang886@gmail.com> wrote:

We are planning to database upgrade, and evaluate PGv18 as next new major version. Based on new release notes, one question about, "Improve the locking performance of queries that access many relations ".
new share_lock_table size is based on max_locks_per_transaction, our production databases have 8k-10k connections, and existing PGV14 stable running there long time. Is it possible to get a new GUC instead of reusing "max_locks_per_transaction", so we can more flexible control on our production environment, for example, we want to keep similar value as existing "shared_lock_table" size related, and separate control of "max_locks_per_transaction".

What do you have max_locks_per_transaction set to?

Can you demonstrate that having a separate GUC for the fast-path
locking slots is useful? Have you benchmarked this? If so, I suspect
the results of that will be more likely to convince us than an
evidence-less request.

One thing to note is that the change Tomas made will never result in
there before fewer fastpath locking slots than there were previously,
so I doubt you'll find any regressions here, which might mean there's
not much chance we'll adjust this at this hour for v18.

David

not tested and any regression found either, with 10k connections, and
"max_locks_per_transaction=128", it need about more than 1GB extra
memory,right? per my understanding, max_locks_per_transaction is the max
locked objects in a transaction (that's not an average locked objects at
the same time among all connections), but for past-path-lock slots, the
memory will be allocated based on this parameter after client connection
established, right? so, even no so many fast lock slots needed, for 10k
connections, extra memory got allocated there. We may test that in our
environment, and update then if anything found.

James

David Rowley <dgrowleyml@gmail.com> 於 2025年8月18日週一 下午2:09寫道：

On Mon, 18 Aug 2025 at 18:23, James Pang <jamespang886@gmail.com> wrote:

not tested and any regression found either, with 10k connections, and "max_locks_per_transaction=128", it need about more than 1GB extra memory,right? per my understanding, max_locks_per_transaction is the max locked objects in a transaction (that's not an average locked objects at the same time among all connections), but for past-path-lock slots, the memory will be allocated based on this parameter after client connection established, right? so, even no so many fast lock slots needed, for 10k connections, extra memory got allocated there. We may test that in our environment, and update then if anything found.

Can you share how you came to 1GB extra?

By my calculations, I believe it's an extra 5625 kB total for the
entire instance.

select pg_size_pretty((max_locks_per_xact / 16 * 8 +
max_locks_per_xact / 16 * 4 * 16) * connections::numeric) from
(values(128,10000)) v(max_locks_per_xact, connections);

David

thanks for you clarification about additional memory calculations: for
10k connections , 5625kB maybe fine. I justed looked through the
discussions about memory allocation "
/messages/by-id/cba2406a-66bb-41ac-b1cf-bb898596e0e1@vondra.me&quot;,
I feel confused about the statement "the memory 1000 connections 80MB
there"

Thanks,

James
David Rowley <dgrowleyml@gmail.com> 於 2025年8月18日週一 下午2:58寫道：