🚨 PostgreSQL Error Guide: 'out of shared memory' Lock Table Exhaustion

Exact Error Message

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

The SQLSTATE is 53200 (out_of_memory).

What the Error Means

PostgreSQL reserves a fixed-size region of shared memory at startup for the lock table, sized from max_locks_per_transaction * (max_connections + max_prepared_transactions). This region holds every object-level lock across all backends. When a single transaction (or the whole cluster) needs more lock slots than the shared lock table can hold, PostgreSQL cannot allocate another entry and raises out of shared memory — and the HINT points straight at the cause: max_locks_per_transaction.

Despite the generic name, this is almost never about running out of system RAM. It is specifically the lock table running out of slots. The classic trigger is a single statement that touches a huge number of relations at once — for example a query across thousands of partitions, or a transaction that locks many tables/indexes. Each locked object consumes a slot, and max_locks_per_transaction is an average budget per transaction, not a hard per-transaction cap (any backend can use more as long as the cluster-wide pool has room).

Common Causes

• Queries spanning many partitions. A SELECT or UPDATE across a partitioned table with thousands of partitions locks each touched partition (and its indexes), exhausting the lock table.
• Transactions touching many tables/indexes. Bulk DDL, pg_dump against a schema with tens of thousands of objects, or a migration locking many relations in one transaction.
• A low max_locks_per_transaction. The default (64) is fine for typical OLTP but far too small for heavily partitioned schemas.
• Many advisory locks. Applications that acquire large numbers of advisory locks within one transaction consume slots too.
• High concurrency multiplying lock demand. Many backends each holding moderate numbers of locks can collectively fill the shared pool.

How to Reproduce the Error

On a cluster with the default max_locks_per_transaction = 64, create a partitioned table with many partitions and query all of them in one transaction:

-- (setup) a range-partitioned table with, say, 5000 daily partitions
BEGIN;
SELECT count(*) FROM events;  -- plans across all partitions
-- ERROR:  out of shared memory
-- HINT:  You might need to increase max_locks_per_transaction.
COMMIT;

Because each partition and its indexes require lock slots, scanning thousands of them in one statement overflows the lock table.

Diagnostic Commands

Check the current lock-table sizing parameters:

psql -c "SELECT name, setting FROM pg_settings \
WHERE name IN ('max_locks_per_transaction','max_connections','max_prepared_transactions');"

Count how many locks each backend currently holds (read-only):

psql -c "SELECT pid, count(*) AS locks FROM pg_locks \
GROUP BY pid ORDER BY locks DESC LIMIT 10;"

See the total number of lock entries in use across the cluster:

psql -c "SELECT count(*) AS total_locks FROM pg_locks;"

Break locks down by type to spot partition/relation pressure:

psql -c "SELECT locktype, count(*) FROM pg_locks GROUP BY locktype ORDER BY count DESC;"

Identify how many partitions a table has (read-only):

psql -c "SELECT count(*) FROM pg_inherits WHERE inhparent = 'events'::regclass;"

Find the error in the server log:

sudo journalctl -u postgresql --no-pager | grep -A1 "out of shared memory" | tail -20

Step-by-Step Resolution

1. Confirm it is the lock table. The HINT about max_locks_per_transaction is the giveaway. Check pg_locks counts and current settings with the commands above to see how close you are to the limit.

2. Identify the offending workload. Use the per-PID lock count query to find which backend accumulates the most locks, and match it to a query touching many partitions or tables.

3. Raise max_locks_per_transaction. This is the standard fix. Increase it (for example to 256, 512, or higher for very partitioned schemas) in postgresql.conf. Note this requires a server restart because the lock table is allocated at startup.

   max_locks_per_transaction = 512

4. Size it for the workload. The total lock-table capacity is max_locks_per_transaction * (max_connections + max_prepared_transactions). If a single query needs to lock ~5000 partitions, ensure the budget comfortably exceeds that across expected concurrency.

5. Reduce the number of locked objects. Use partition pruning so queries touch only the relevant partitions, add WHERE clauses on the partition key, or consolidate excessively granular partitions. Fewer locked relations means fewer slots needed.

6. Restart and verify. After increasing the parameter and restarting, re-run the workload and confirm pg_locks stays well below the new ceiling.

Prevention and Best Practices

• Size max_locks_per_transaction for your worst-case query: estimate the maximum partitions/relations a single transaction touches and add headroom.
• Design partitioning to enable pruning so queries rarely span all partitions; filter on the partition key.
• Avoid unbounded growth in partition counts; prune or merge old partitions on a schedule.
• Monitor pg_locks cardinality and alert when the lock table approaches capacity.
• Remember that the parameter change needs a restart; plan it into a maintenance window.
• For triage, the free incident assistant can turn an out-of-shared-memory log block into a partition-count or parameter-sizing recommendation.

Related Errors

• out of memory (process-level) — a backend exhausted OS memory, distinct from the lock table.
• could not resize shared memory segment — a different shared-memory allocation failure, often dynamic_shared_memory.
• too many locks situations surfacing as out of shared memory — same root, the lock table.
• out of shared memory with a predicate-lock hint — SERIALIZABLE SSI predicate locks exhausting their pool.

Frequently Asked Questions

Does this mean my server is out of RAM? Almost never. The name is misleading: it means PostgreSQL’s fixed-size shared lock table is full, not that the OS is out of memory. The fix is to enlarge the lock table via max_locks_per_transaction, not to add system RAM (though more locks do use a little more shared memory).

Is max_locks_per_transaction a hard per-transaction cap? No. It is used to size a cluster-wide shared pool. Any single transaction may use more than that number of locks as long as the overall pool (across all backends) has room. The setting is an average budget multiplied by the connection count.

Why does the parameter change require a restart? The lock table is allocated in shared memory at server startup based on this parameter, so its size cannot change while the server is running. You must edit postgresql.conf and restart for the new value to take effect.

How do I stop a partitioned query from locking every partition? Enable partition pruning by filtering on the partition key in your WHERE clause so the planner only touches relevant partitions. Pruned partitions are not locked, dramatically reducing lock-table demand.

Could SERIALIZABLE isolation cause this too? Yes. SERIALIZABLE uses predicate locks (SIReadLocks) that also live in shared memory and can exhaust their pool under heavy read/write workloads, producing the same error. Tuning max_pred_locks_per_transaction addresses that variant.