public inbox for [email protected]help / color / mirror / Atom feed
Maximum amount of pg_locks 5+ messages / 4 participants [nested] [flat]
* Maximum amount of pg_locks @ 2026-01-28 20:12 Tim Herren <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Tim Herren @ 2026-01-28 20:12 UTC (permalink / raw) To: [email protected] <[email protected]> Hi I'm trying to wrap my head around the way the calculation for the maximum amount of locks works in postgres 16.11 I already came to the understanding that the maximum amount of locks are not on a transaction basis, but rather influenced by the setting "max_locks_per_transaction" and the "max_connections". I'm saying influenced rather than calculated because on my server a simple multiplication of those two values, set at 512 and 180 respectively gives 92160. Yet I regularly observe around 119k locks during my backup using "pg_dump -Fc". postgres=# SELECT locktype, mode, count(*) FROM pg_locks GROUP BY 1, 2 ORDER BY 3 DESC; locktype | mode | count ------------+-----------------+-------- relation | AccessShareLock | 119948 virtualxid | ExclusiveLock | 2 (2 rows) The database I'm backing up at that time contains 242 schemas and each has around 500 relations (a mix of tables and sequences). So that matches pretty well. I've verified that my configured setting is enough in the sense that I get a usable backup and my database remains operational during the backup period. Non the less I would like to understand where that difference comes from and how close to the "actual limit" I am. Please let me know if this needs more information. Thanks ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maximum amount of pg_locks @ 2026-01-28 20:21 Beniamin Hendre <[email protected]> parent: Tim Herren <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Beniamin Hendre @ 2026-01-28 20:21 UTC (permalink / raw) To: Tim Herren <[email protected]>; +Cc: [email protected] Actualisation script maximo plus?? mie., 28 ian. 2026, 22:12 Tim Herren <[email protected]> a scris: > Hi > > I'm trying to wrap my head around the way the calculation for the maximum > amount of locks works in postgres 16.11 > I already came to the understanding that the maximum amount of locks are > not on a transaction basis, but rather influenced by the setting > "max_locks_per_transaction" and the "max_connections". > I'm saying influenced rather than calculated because on my server a simple > multiplication of those two values, set at 512 and 180 respectively gives > 92160. > > Yet I regularly observe around 119k locks during my backup using "pg_dump > -Fc". > > postgres=# SELECT > locktype, > mode, > count(*) > FROM pg_locks > GROUP BY 1, 2 > ORDER BY 3 DESC; > locktype | mode | count > ------------+-----------------+-------- > relation | AccessShareLock | 119948 > virtualxid | ExclusiveLock | 2 > (2 rows) > > The database I'm backing up at that time contains 242 schemas and each has > around 500 relations (a mix of tables and sequences). > So that matches pretty well. > > I've verified that my configured setting is enough in the sense that I get > a usable backup and my database remains operational during the backup > period. Non the less I would like to understand where that difference comes > from and how close to the "actual limit" I am. > Please let me know if this needs more information. > > Thanks > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maximum amount of pg_locks @ 2026-01-28 20:57 Tom Lane <[email protected]> parent: Tim Herren <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Tom Lane @ 2026-01-28 20:57 UTC (permalink / raw) To: Tim Herren <[email protected]>; +Cc: [email protected] <[email protected]> Tim Herren <[email protected]> writes: > I'm trying to wrap my head around the way the calculation for the maximum amount of locks works in postgres 16.11 > I already came to the understanding that the maximum amount of locks are not on a transaction basis, but rather influenced by the setting "max_locks_per_transaction" and the "max_connections". > I'm saying influenced rather than calculated because on my server a simple multiplication of those two values, set at 512 and 180 respectively gives 92160. > Yet I regularly observe around 119k locks during my backup using "pg_dump -Fc". Well, the hash table size is indeed set by a calculation of that form, but it's max_locks_per_transaction times the number of potential locker processes --- not only regular sessions (max_connections), but also autovacuum processes and other background workers. And prepared transactions, too. lock.c has #define NLOCKENTS() \ mul_size(max_locks_per_xact, add_size(MaxBackends, max_prepared_xacts)) where postinit.c calculates MaxBackends as: /* Note that this does not include "auxiliary" processes */ MaxBackends = MaxConnections + autovacuum_worker_slots + max_worker_processes + max_wal_senders + NUM_SPECIAL_WORKER_PROCS; Then on top of that, there's a pretty considerable fudge factor: lock.c scales up its shared-memory size request by 10%, and there is (from memory) about 100K slop space added on top of the total of shared-memory size requests from all modules, and all of that space is potentially available for the lock table to overflow into. (Other shared data structures could claim it too, but I think the lock table is the only one that we don't have a hard upper bound for.) If you really want to know how many locks can be taken in your particular setup, I'd counsel experimenting by deliberately exhausting the lock space. (Maybe not during production hours.) The precise limit will vary across PG versions, and potentially depend on other factors like what extensions you have loaded. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maximum amount of pg_locks @ 2026-01-28 21:50 Ron Johnson <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Ron Johnson @ 2026-01-28 21:50 UTC (permalink / raw) To: [email protected] <[email protected]> On Wed, Jan 28, 2026 at 3:57 PM Tom Lane <[email protected]> wrote: > Tim Herren <[email protected]> writes: > > I'm trying to wrap my head around the way the calculation for the > maximum amount of locks works in postgres 16.11 > > I already came to the understanding that the maximum amount of locks are > not on a transaction basis, but rather influenced by the setting > "max_locks_per_transaction" and the "max_connections". > > I'm saying influenced rather than calculated because on my server a > simple multiplication of those two values, set at 512 and 180 respectively > gives 92160. > > Yet I regularly observe around 119k locks during my backup using > "pg_dump -Fc". > > Well, the hash table size is indeed set by a calculation of that form, > but it's max_locks_per_transaction times the number of potential > locker processes --- not only regular sessions (max_connections), > but also autovacuum processes and other background workers. And > prepared transactions, too. lock.c has > > #define NLOCKENTS() \ > mul_size(max_locks_per_xact, add_size(MaxBackends, > max_prepared_xacts)) > > where postinit.c calculates MaxBackends as: > > /* Note that this does not include "auxiliary" processes */ > MaxBackends = MaxConnections + autovacuum_worker_slots + > max_worker_processes + max_wal_senders + > NUM_SPECIAL_WORKER_PROCS; > > Then on top of that, there's a pretty considerable fudge factor: > lock.c scales up its shared-memory size request by 10%, and there > is (from memory) about 100K slop space added on top of the total of > shared-memory size requests from all modules, and all of that space > is potentially available for the lock table to overflow into. (Other > shared data structures could claim it too, but I think the lock table > is the only one that we don't have a hard upper bound for.) > > If you really want to know how many locks can be taken in your > particular setup, I'd counsel experimenting by deliberately exhausting > the lock space. (Maybe not during production hours.) The precise > limit will vary across PG versions, and potentially depend on other > factors like what extensions you have loaded. > I asked a similar question a few years ago, and you said that on modern systems it's no problem to bump max_locks_per_transaction pretty darned high (like 1 million). "The default, 64, has historically proven sufficient" had me slowly incrementing, but after your comment *I added a zero* to my existing max_locks_per_transaction value (1536, one and a half KiB) *and haven't thought about it since*. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maximum amount of pg_locks @ 2026-01-29 09:50 Tim Herren <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tim Herren @ 2026-01-29 09:50 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: [email protected] <[email protected]> On Wednesday, January 28th, 2026 at 10:51 PM, Ron Johnson <[email protected]> wrote: > On Wed, Jan 28, 2026 at 3:57 PM Tom Lane <[email protected]> wrote: > >> Tim Herren <[email protected]> writes: >>> I'm trying to wrap my head around the way the calculation for the maximum amount of locks works in postgres 16.11 >>> I already came to the understanding that the maximum amount of locks are not on a transaction basis, but rather influenced by the setting "max_locks_per_transaction" and the "max_connections". >>> I'm saying influenced rather than calculated because on my server a simple multiplication of those two values, set at 512 and 180 respectively gives 92160. >>> Yet I regularly observe around 119k locks during my backup using "pg_dump -Fc". >> >> Well, the hash table size is indeed set by a calculation of that form, >> but it's max_locks_per_transaction times the number of potential >> locker processes --- not only regular sessions (max_connections), >> but also autovacuum processes and other background workers. And >> prepared transactions, too. lock.c has >> >> #define NLOCKENTS() \ >> mul_size(max_locks_per_xact, add_size(MaxBackends, max_prepared_xacts)) >> >> where postinit.c calculates MaxBackends as: >> >> /* Note that this does not include "auxiliary" processes */ >> MaxBackends = MaxConnections + autovacuum_worker_slots + >> max_worker_processes + max_wal_senders + NUM_SPECIAL_WORKER_PROCS; >> >> Then on top of that, there's a pretty considerable fudge factor: >> lock.c scales up its shared-memory size request by 10%, and there >> is (from memory) about 100K slop space added on top of the total of >> shared-memory size requests from all modules, and all of that space >> is potentially available for the lock table to overflow into. (Other >> shared data structures could claim it too, but I think the lock table >> is the only one that we don't have a hard upper bound for.) >> >> If you really want to know how many locks can be taken in your >> particular setup, I'd counsel experimenting by deliberately exhausting >> the lock space. (Maybe not during production hours.) The precise >> limit will vary across PG versions, and potentially depend on other >> factors like what extensions you have loaded. > > I asked a similar question a few years ago, and you said that on modern systems it's no problem to bump max_locks_per_transaction pretty darned high (like 1 million). "The default, 64, has historically proven sufficient" had me slowly incrementing, but after your comment I added a zero to my existing max_locks_per_transaction value (1536, one and a half KiB) and haven't thought about it since. > -- > > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > > <Redacted> lobster! Thank your for your explanation and the information from an actual production server. I'll do some more reading and then adjust my monitoring. Thanks again for the valuable input, have a nice day. Tim ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-01-29 09:50 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-28 20:12 Maximum amount of pg_locks Tim Herren <[email protected]> 2026-01-28 20:21 ` Beniamin Hendre <[email protected]> 2026-01-28 20:57 ` Tom Lane <[email protected]> 2026-01-28 21:50 ` Ron Johnson <[email protected]> 2026-01-29 09:50 ` Tim Herren <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox