Re: Maximum amount of pg_locks - Mailing list pgsql-admin

Tim Herren <tim.herren@protonmail.ch> 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.