PostgreSQL "out of shared memory": Fixing max_locks_per_transaction

This is one of the more confusing PostgreSQL errors. The server has plenty of RAM, shared_buffers is fine, and still a query fails with "out of shared memory" and a hint to raise max_locks_per_transaction. The error is not about memory in general. It means the fixed-size table PostgreSQL uses to track locks is full. Once you know that, the fix is usually quick, and the real cause is usually one of four things.

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

What the lock table is

Every table, index, sequence or other object that a transaction touches gets a lock, and most of those locks are held until the end of the transaction. PostgreSQL keeps them in a shared lock table whose size is fixed at server start. The documentation describes it as having space for max_locks_per_transaction objects per server process or prepared transaction. The default is 64.

Three details matter in practice:

• It is a shared pool, not a per-session quota. The docs say the setting limits the average number of object locks per transaction; a single transaction can lock far more as long as everything fits. So one session with 20,000 locks can make a completely innocent query in another session fail with this error.
• It counts objects, not rows. Row locks are not stored here; their number is unlimited. Locking a million rows in one table will not cause this error. Touching ten thousand tables will.
• The size formula. In the source code the table holds max_locks_per_transaction × (MaxBackends + max_prepared_transactions) entries, where MaxBackends is max_connections plus autovacuum workers, background workers and WAL senders. With default settings that is on the order of ten thousand slots for the whole server, which is less than many people expect.

Some locks do not use the shared table at all. Weak relation locks (the kind ordinary SELECT, INSERT, UPDATE and DELETE take) can go into a small per-backend fast-path array instead. In PostgreSQL 17 and earlier that array had 16 slots. PostgreSQL 18 improved locking for queries that touch many relations: the number of fast-path slots now scales with max_locks_per_transaction, giving 64 per backend with the default setting. That makes the problem less likely on 18, but does not remove it: once the fast-path slots are used up, further locks go to the shared table.

Step 1: find who holds the locks

While the problem is happening, look at pg_locks, grouped by process:

SELECT l.pid,
       count(*)                               AS locks,
       count(*) FILTER (WHERE NOT l.fastpath) AS in_shared_table,
       a.state,
       now() - a.xact_start                   AS xact_age,
       left(a.query, 80)                      AS query
FROM pg_locks l
LEFT JOIN pg_stat_activity a USING (pid)
GROUP BY l.pid, a.state, a.xact_start, a.query
ORDER BY locks DESC
LIMIT 10;

The fastpath column shows whether a lock was taken via the fast path; the ones that count against the shared table are those where it is false. Normally the top of this list is unremarkable: dozens of locks per session. During an incident you typically see one or two sessions with thousands. Check what that session is and how old its transaction is.

To see what kind of objects it has locked:

SELECT l.locktype, c.relkind, l.mode, count(*)
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = 12345            -- the pid from the query above
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

A long list of relkind = 'r' and 'i' under one parent name points to partitions and their indexes. Locks in AccessShareLock mode on everything in the database point to a dump or a catalog-wide script.

Compare total usage with capacity:

-- rough size of the shared lock table (the real one is a bit larger,
-- because background processes also get slots)
SELECT current_setting('max_locks_per_transaction')::int
     * (current_setting('max_connections')::int
        + current_setting('max_prepared_transactions')::int) AS approx_slots,
       (SELECT count(*) FROM pg_locks WHERE NOT fastpath)    AS used_now;

Step 2: stop the bleeding

If one session is clearly responsible and it is safe to stop it, cancel it, and terminate it only if cancelling does not help:

SELECT pg_cancel_backend(12345);     -- cancel the current query
SELECT pg_terminate_backend(12345);  -- end the session if cancel is not enough

Its locks are released when its transaction ends, and the rest of the system recovers immediately. If that session is a migration or a backup you actually need, do not kill it blindly. Plan a restart with a higher limit (step 4) or split the job (step 3) instead.

The usual causes

1. Partitioned tables with many partitions. A query on the parent that cannot prune partitions locks every partition, and its indexes, for the rest of the transaction. The partitioning documentation adds a less obvious trap: partitions removed by pruning during initialization of the query plan are still locked at the beginning of execution. The planner handles hierarchies of "up to a few thousand partitions fairly well", the docs say, provided typical queries can prune all but a few. Daily partitions kept for five years, each with four indexes, is already over 9,000 objects, about the size of the whole default lock table.
2. pg_dump of a database with many tables. pg_dump acquires shared table locks on the tables it dumps at the beginning of the run (its --lock-wait-timeout option exists exactly for that step, per the pg_dump documentation) and keeps them while the dump runs; the section on parallel dumps explains that the leader holds these shared locks so nobody can drop the tables mid-dump. Thousands of tables or partitions mean thousands of locks for one session, held for the whole dump. If you rely on logical dumps, our article on backup and restore testing explains why it is worth checking that they still complete.
3. Big migrations in one transaction. A script that creates, alters or drops hundreds of tables or partitions inside a single BEGIN ... COMMIT holds every one of those locks until the commit. So does a restore that runs everything in a single transaction.
4. Long-running transactions that touch a lot. A reporting session that opened a transaction in the morning and has since queried hundreds of tables keeps all those locks. Our guide to idle in transaction sessions shows how to find and limit them.

Step 3: fix the cause

• Partitions: make sure frequent queries filter on the partition key so the planner can prune, and check with EXPLAIN. Detach or drop partitions you no longer query. If the design needs tens of thousands of partitions, revisit it; the docs explicitly warn that too many partitions increase planning time and memory use.
• Migrations: commit in batches, for example one transaction per partition or per group of tables, instead of one giant transaction.
• Dumps: for very large schemas, consider dumping schemas or table groups separately, or use physical backups, which do not take a lock on every table.
• Long transactions: set idle_in_transaction_session_timeout and keep reporting sessions in autocommit or short transactions.

Step 4: raise the limit, in the right order

Raising max_locks_per_transaction is a legitimate fix, not just a workaround, when you really do have many partitions or tables. It only takes effect at server start:

ALTER SYSTEM SET max_locks_per_transaction = 256;
-- takes effect only after a restart

The shared memory for the lock table grows in proportion, so you do not need to make the value huge. Doubling or quadrupling the default is a reasonable first step; then check pg_locks during your heaviest jobs.

Monitoring so you see it coming

Add SELECT count(*) FROM pg_locks WHERE NOT fastpath to your monitoring and alert at a fraction of the capacity calculated above. Also track the maximum number of locks held by a single process. A slow climb usually follows partition count; a sudden spike usually lines up with a nightly dump or a deploy.