Fix: out of shared memory / increase max_locks_per_transaction

This error means PostgreSQL ran out of slots in its shared lock table, the fixed size structure where it tracks every object level lock held by every transaction. Despite the wording, the server has not run out of RAM. One or more transactions are taking far more locks than the lock table was sized for, and the classic trigger is a query, dump, or migration touching a heavily partitioned table.

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

What this error means

At startup PostgreSQL allocates a lock table sized roughly as max_locks_per_transaction * (max_connections + max_prepared_transactions). This is a shared pool, not a per transaction cap: any single transaction may use many more slots than max_locks_per_transaction as long as the pool has room. Every table, index, partition, and sequence a transaction touches consumes a slot until the transaction ends. When the pool is exhausted, the next lock request fails with this error and that transaction aborts. Other sessions may start failing too, because they are competing for the same pool.

Common causes

• Queries against partitioned tables that cannot prune partitions, locking every partition and every index on each one.
• pg_dump or pg_restore on a schema with thousands of tables, since the dump locks each object it reads.
• Schema migrations that alter or drop many objects in a single transaction.
• Long transactions that gradually accumulate locks across many tables and never commit.
• Many concurrent sessions each touching a moderate number of objects, exhausting the shared pool together.
• max_locks_per_transaction still at the default of 64 on a database whose schema has grown to thousands of objects.

How to fix it

STABILIZE first. Find and end the transaction that is holding the most locks, which frees slots immediately and lets other sessions proceed.

1. DIAGNOSE: see who holds locks and how many, right now.

SELECT pid, count(*) AS locks
FROM pg_locks
GROUP BY pid
ORDER BY locks DESC
LIMIT 10;

2. DIAGNOSE: match the heaviest lockers to their queries.

SELECT a.pid, a.state, a.query_start, left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.pid IN (SELECT pid FROM pg_locks GROUP BY pid ORDER BY count(*) DESC LIMIT 5);

3. STABILIZE: if one session is clearly the offender (a stuck migration, an ad hoc query over all partitions), terminate it.

SELECT pg_terminate_backend(12345);

4. FIX the capacity. Raise max_locks_per_transaction to match your schema. For partitioned workloads, 256 or 512 is a reasonable starting point. This requires a restart.

ALTER SYSTEM SET max_locks_per_transaction = 256;

sudo systemctl restart postgresql

5. FIX the workload. Make queries prune partitions by filtering on the partition key, and confirm with EXPLAIN that only the relevant partitions appear in the plan. Break giant migrations into smaller transactions. For dumps of very large schemas, dump in sections or per schema so no single transaction locks everything:

pg_dump --schema=app_core -Fc mydb > app_core.dump

6. Verify after the restart that the new setting is live.

SHOW max_locks_per_transaction;

How to prevent it

• Size max_locks_per_transaction for your object count, and revisit it whenever you add large numbers of partitions.
• Design queries on partitioned tables to include the partition key so pruning limits how many partitions get locked.
• Keep partition counts sane: hundreds are usually fine, tens of thousands demand deliberate lock budgeting.
• Split schema migrations into smaller transactions instead of one transaction that touches every table.
• Watch pg_locks counts during dumps and migrations so you see pressure before the pool is exhausted.
• Detach or drop old partitions on a schedule so the object count does not grow without bound.

When to call a senior engineer

Call for help when the error keeps returning after you raised the setting, when you cannot tell which workload is consuming the lock table, or when a partitioning scheme has grown to the point where every change risks an outage. Erzon engineers can profile your lock usage, restructure the partitioning and migration strategy, and size the server so the lock table stops being the thing that takes you down.