max_locks_per_transaction is neither per transaction nor a maximum. It is the per-process share of one cluster-wide lock table, and any single transaction can take far more than its share as long as nobody else is using theirs. The name has been misleading people since 7.2, and the docs’ description of it as “the average number of object locks used by each transaction” is closer, if not exactly something you’d put on a dial.
The default is 64 through PostgreSQL 18 and 128 in 19, the minimum is 10, the maximum is INT_MAX, and the context is postmaster: changing it means a restart. What it sizes is the shared hash table of heavyweight locks. The table gets max_locks_per_transaction entries for every process slot the postmaster could conceivably hand out, which on 18 means max_connections plus autovacuum_worker_slots plus max_worker_processes plus max_wal_senders plus max_prepared_transactions plus two for the autovacuum launcher and the slot-sync worker: 136 slots at the defaults, so 64 × 136 = 8,704 lock entries. A second table, for the per-holder records, is sized at twice that on the assumption of two holders per lock. The entries are for objects (relations, pages, transaction IDs, advisory lock keys), not rows; row locks live in the tuples themselves and this parameter has nothing to say about them. Weak relation locks that fit in a backend’s fast-path array never touch the table either, and since 18 the size of that array is also derived from this parameter, which I covered in Sixteen Locks Ought to Be Enough for Anybody and won’t repeat.
Who actually runs out
When the table is full, the next lock request fails with out of shared memory and the hint You might need to increase "max_locks_per_transaction". The hint is right and the message is wrong; you are not out of shared memory in any sense that shared_buffers would recognize, you are out of lock entries. Three things get you there.
The first is one transaction that touches a very large number of relations. pg_dump takes AccessShareLock on every table it dumps, in one transaction, so a schema with more tables than the lock table has entries cannot be dumped at the default without help. Anything that creates or drops thousands of relations in one transaction is the same problem in AccessExclusiveLock: a DO block I wrote to create 24,000 empty tables died on 18 at the default after about 7,400 of them, with the hint above and nothing else to show for it. DROP SCHEMA ... CASCADE and DROP OWNED BY are the usual ways to meet this one in production.
The second is partitioning. The planner locks every partition it considers and every index on every partition, so a query over a 3,000-partition table with one index each takes 6,002 relation locks (the partitions, their indexes, the parent, and the parent’s index), whether or not pruning later throws most of them away. (A cached generic plan locks every partition in the plan at execution time too; that’s in The Sixth Execution.) One such query fits in the default table, 64 of its locks on the fast path and the other 5,938 in the shared table. Two running at once do not, nominally, and that is the part people miss: the table is shared, so the transaction that gets the error is whichever one asked last, not the one that took 6,000 entries.
The third is a lot of backends each holding a moderate number of locks, which is just the first two spread thin, and is what the “average per transaction” wording is trying to tell you.
Nineteen halves it
Through 18 the two hash tables draw their entries from the general pool of shared memory, which holds the space reserved for them plus whatever every other structure in the segment didn’t use, so the real capacity has always been the nominal number plus an undocumented bonus that depends on the rest of your configuration. In 19 (in beta as I write this), Heikki Linnakangas removed the safety margins, settled the split between the two tables at startup, and doubled the default to compensate. The numbers, measured with AccessExclusiveLock on one table at a time until the error:
| nominal | 18.6 | 19 beta 3 | |
|---|---|---|---|
max_locks_per_transaction = 64 |
8,704 | 14,872 | 8,701 |
max_locks_per_transaction = 128 |
17,408 | 24,000 (all I had) | 17,405 |
So 19’s default holds a little more than 18’s did, and the number is now the number. The release notes say to double any explicit setting when you upgrade, and they mean it: 64 carried forward from an 18 config was a 40% cut in lock capacity in my test, on a server that presumably had a reason to set it. The memory is also visible now; pg_shmem_allocations on 19 shows LOCK hash and PROCLOCK hash at their real sizes, about 370 bytes per entry between them, where 18 buries the same space in <anonymous>. At max_locks_per_transaction = 1024 with max_connections = 300 that is 120MB, which is cheap insurance against a dump that fails at 3 a.m.
Standbys first
A standby’s lock table has to hold every AccessExclusiveLock the primary’s did, because replay takes them, so a hot standby refuses to run with a lower value than the primary. If the standby starts up that way, it doesn’t: FATAL: recovery aborted because of insufficient parameter settings. The nastier case is the running standby whose primary was just restarted with a higher value. It logs hot standby is not possible because of insufficient parameter settings and recovery has paused, and then it keeps serving queries against a snapshot that stops moving. pg_get_wal_replay_pause_state() says paused, replay_lsn in the primary’s pg_stat_replication freezes, and if anyone helpfully unpauses it, it shuts down. Raise the standbys and restart them, then the primary, same as max_connections.
There is no view that reports lock-table occupancy, but select count(*) from pg_locks where not fastpath against max_locks_per_transaction times your process slots is the ratio to watch, at your worst moment, which is the nightly dump or the partition-maintenance job. On 19, pg_stat_lock.fastpath_exceeded counts every lock that spilled out of the fast path into the shared table, and if it climbs on a partitioned workload it’s telling you to raise this. Two constraints, and you take the larger. For the shared table, the setting times your process slots has to clear the lock count of your worst concurrent moment, the dump plus whatever runs beside it, with headroom. For the fast path on 18 and later, the setting itself has to clear the number of relations, indexes included, that your hottest query touches. For anything seriously partitioned both land at 1,024 or more, and the memory is a rounding error. Do the standbys first. And if you already set it on 18, double it before 19.