🚀 New: chi (χ) — an open-source autoresearch harness for fleets of LLM coding agents. Read the announcement.

PostgreSQL 19 Shrinks What Your max_locks_per_transaction Setting Buys

PostgreSQL 19 fixed the size of the lock hash tables and raised the max_locks_per_transaction default from 64 to 128 to compensate. Servers that set the value explicitly in postgresql.conf get fewer locks for the same number. Parallel vacuum and JIT defaults changed meaning too.

Contents

PostgreSQL 19 came out on November 19, and its migration notes include this:

Change default of max_locks_per_transaction from 64 to 128. Lock size allocation has changed, so effectively settings must now be doubled to match their capacity in previous releases.

Read that from the point of view of a server that already sets the value. If your postgresql.conf, Ansible role, Helm chart or managed-database parameter group says max_locks_per_transaction = 256 because a partitioned schema or a large pg_dump once ran out of locks, the new default never reaches you. You carry 256 forward, the upgrade succeeds, and the shared lock table holds fewer locks than it did on 18. Nothing warns you. You find out when a query that touches many partitions fails with out of shared memory and a hint to raise max_locks_per_transaction.

The servers that never touched the setting come out ahead. The ones that tuned it are the ones that lose headroom.

Why the same number holds fewer locks

The change comes from a pgsql-hackers thread on shared hash table allocations by Heikki Linnakangas. The regular lock manager keeps two shared hash tables: LOCK (one entry per locked object) and PROCLOCK (one entry per backend holding a lock on that object). Before 19, the memory estimate for them carried slack:

  • lock.c and predicate.c each added a 10% “safety margin” to their shared memory estimate. The predicate lock tables were already fixed size and could never use theirs, so that space went to whoever grabbed it, in practice the regular lock tables.
  • PROCLOCK was sized at about twice LOCK, but neither table was fixed size. Whichever one ran short could take unused space from the other or from the leftover pool.

The patch series removed both margins and marked LOCK and PROCLOCK as fixed size, with their initial size equal to their maximum. Allocation became predictable, and the borrowing stopped. The final patch doubled the default to compensate.

Heikki measured each step with a test script that takes locks until it hits out of shared memory. These are his numbers from the posted patch set, on his test configuration, not a benchmark of any particular production setup:

Patch appliedShared memory (kB)Locks acquired
master before the series153,56014,927
remove 10% margin in predicate.c153,36014,159
remove 10% margin in lock.c153,04012,815
make LOCK/PROCLOCK fixed size153,0408,767
default max_locks_per_transaction = 128156,25617,535

At an unchanged setting, the last structural step leaves about 59% of the previous lock capacity, while total shared memory barely moves. The default bump then costs about 3 MB and ends above where master started. The committed change itself only edits the default, in the GUC table, the sample config, pg_resetwal and the docs. Its commit message says why that matters: servers with an explicit value get fewer locks than before for the same number, and doubling the value should restore the old capacity.

How to size it before the upgrade

The documentation for 19 still describes the setting the same way. The shared lock table has room for max_locks_per_transaction objects “per server process or prepared transaction,” and that is an average across all sessions, not a per-transaction cap. So what you need to know is peak concurrent lock usage across the whole server, not what one query takes.

On your current 18 server, sample it during the heaviest window you have, such as a nightly pg_dump, a partition maintenance job or a migration:

SELECT count(*) FROM pg_locks WHERE NOT fastpath;

Fast-path locks live in per-backend memory and do not occupy the shared table, which is why they are excluded. Compare that peak with what your current setting allows, then apply the same ratio to the doubled value on 19. If you never measured and only raised the setting after an incident, doubling it is the documented rule of thumb.

Two practical details. The setting needs a restart, so get it right in the config that the new cluster boots with, not after. And on a physical standby the docs require a value equal to or higher than the primary’s, or the standby will not allow queries. If you raise it on both, raise it on the standby first.

Two more settings that changed meaning, not name

The same migration section has two other changes where an old config file keeps parsing and starts doing something different.

Parallel vacuum now shares the cost limit

Since PostgreSQL 13, each process in a parallel VACUUM slept based only on the cost it had incurred itself, so a leader with three workers could use roughly four times the vacuum_cost_limit budget. The fix scales each process’s sleep by the number of active processes. The v19 docs give the delay as vacuum_cost_delay * accumulated_balance * active_processes / vacuum_cost_limit, capped at vacuum_cost_delay * 4 * active_processes and never more than one second at a time. The commit was deliberately not backpatched to 18, because it would slow parallel vacuum after a minor update.

This only affects vacuums that are throttled at all. Manual VACUUM has vacuum_cost_delay = 0 by default, so a maintenance script that runs VACUUM (PARALLEL 4) unthrottled is unchanged. A script that sets a cost delay will run slower on 19, and so will autovacuum once you enable the new parallel autovacuum (autovacuum_max_parallel_workers defaults to 0). If your vacuum budget was tuned by watching how fast parallel runs finished on 18, the real I/O it allowed was a multiple of the number you wrote down. Re-tune against the I/O you actually want.

JIT is off by default

The commit explains that JIT cost-based activation was unreliable. A small unrelated change could push a short query’s estimated cost over the threshold and make it pay LLVM compilation time, a performance cliff. Most OLTP users gain from this and should do nothing. But if a reporting workload got faster on 12 through 18 because JIT kicked in, and you never set jit explicitly, those queries lose it after the upgrade. The fix is jit = on for that role or database, not for the whole server.

This one runs the other way from the lock change. With locks, the people hurt are the ones who set the value. With JIT, the people hurt are the ones who didn’t.

Treat the config diff as part of the upgrade

pg_upgrade moves data, not intent. Whatever produces your new cluster’s configuration copies the numbers forward, and three of those numbers now mean something different. Before the cutover, diff your effective settings (SELECT name, setting, source FROM pg_settings WHERE source <> 'default') against the 19 migration notes, and for each one ask whether you set it for a capacity reason. If you did, check the capacity, not the number.

The same notes include harder breaks, such as standard_conforming_strings being forced on (dumps made by a pre-19 pg_dump with it off will not load properly) and pg_upgrade refusing clusters with btree_gist inet/cidr indexes. Those fail loudly. The lock change is the one most likely to show up weeks later, during the first partition-heavy job that runs after the upgrade. If you are also planning to use the new REPACK command, I covered its interaction with pg_dump earlier.

Sources