PostgreSQL: out of shared memory (max_locks_per_transaction)

What it means

Despite the words, this is rarely about the server’s memory in general. Every table, index or partition a transaction touches gets a lock, kept until the transaction ends. Locks live in a table in shared memory whose size is fixed when the server starts. When it fills, the next lock request fails:

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

The hint names the setting that sizes the table. The PostgreSQL documentation describes the room as max_locks_per_transaction objects per server process (and prepared transaction). The default is 64, and the limit is shared: one session can use far more than 64 as long as the total fits, which is why the error can hit a session that isn’t doing anything unusual, because another one is.

Common causes

Queries over a table with many partitions: a query that can’t skip partitions locks every one of them, plus their indexes. Parallel workers each take their own locks, multiplying the count.
One transaction touching thousands of tables: a migration that creates or alters many tables at once, DROP SCHEMA … CASCADE on a big schema, many temporary tables in one transaction.
pg_dump of a database with many thousands of tables, which locks every table it dumps in one transaction.
Several such transactions at the same time, each within bounds alone.

How to fix it

Raise max_locks_per_transaction

In postgresql.conf (or your provider’s parameter settings):

max_locks_per_transaction = 128

It only takes effect after a restart. The cost is shared memory reserved at start, in proportion to the number of connections: with the default max_connections of 100, PostgreSQL 18.6 reserved 150 MB in total at 64, 154 MB at 128 and 202 MB at 1024 (postgres -C shared_memory_size). A hosted service usually exposes it as a parameter that needs a reboot.

Lock fewer objects per transaction

Make sure queries on partitioned tables filter on the partition key, so the planner skips partitions and doesn’t lock them. Check with EXPLAIN.
Split big migrations into several transactions.
Turn off parallelism for the query that fails (SET max_parallel_workers_per_gather = 0;), which stops each worker locking the same partitions again.
Keep the number of partitions reasonable: the PostgreSQL documentation warns that very many partitions increase planning time and memory use.

See who holds the locks

SELECT pid, count(*) AS locks, bool_or(fastpath) AS any_fastpath
FROM pg_locks
GROUP BY pid
ORDER BY locks DESC;

A session with thousands of locks is the one filling the table.

For pg_dump

Dump with a higher max_locks_per_transaction on the server, or dump fewer tables per run (--schema, --table).

Reproduce it

We didn’t do this on the shared test servers, since filling the lock table affects every session. In a throwaway PostgreSQL 18.6 container with default settings (max_locks_per_transaction 64, max_connections 100), we made a table with 10,000 list partitions and counted its rows:

CREATE TABLE events (id bigint, day int) PARTITION BY LIST (day);
-- 10,000 times, one per transaction:
CREATE TABLE events_1 PARTITION OF events FOR VALUES IN (1);
…
SELECT count(*) FROM events;

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".
CONTEXT:  parallel worker

With \set VERBOSITY verbose the code was 53200. The plan used parallel workers, which each lock every partition. With SET max_parallel_workers_per_gather = 0 the same count worked, and a transaction counting 3,000 partitions held 3,003 locks in pg_locks, 65 of them “fast path” locks that don’t use the shared table. There’s spare room beyond the documented size: two sessions holding 10,000 partition locks each at the same time both succeeded, and pg_dump of the database (10,001 tables) finished without error.

After adding max_locks_per_transaction = 128 to postgresql.conf and restarting the container, the original parallel SELECT count(*) FROM events returned 0 without error.

In Inlet

Inlet’s Activity monitor shows sessions and the locks they hold, so you can see which session holds thousands of them. EXPLAIN is drawn as a tree, which shows whether a query on a partitioned table reads every partition. Inlet’s backups use the bundled pg_dump, which locks every table it dumps, like any pg_dump.

out of shared memory (max_locks_per_transaction)