PostgreSQL error: out of shared memory

SQLSTATE 53200 (out_of_memory) · Updated July 9, 2026

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

Despite the alarming wording, this is almost never about the server running out of memory in general. When it comes with the max_locks_per_transaction hint, it means one specific thing: the shared lock table — the fixed-size structure where PostgreSQL tracks locks on tables, indexes and other objects — is full.

What this error means

The lock table is sized at server start, with room for roughly max_locks_per_transaction × (max_connections + max_prepared_transactions) object locks. Two properties explain everything you'll observe:

• Despite the name, max_locks_per_transaction is not a per-transaction cap — it just sizes the shared pool. A single transaction can happily hold thousands of locks as long as the pool has room.
• Which also means one greedy transaction can drain the pool for everyone: the error may hit an innocent bystander, not the transaction that consumed the locks.

Every table, partition and index touched by a query needs its own lock entry, held until the end of the transaction. That's the arithmetic that gets you: a query over a table with 2,000 partitions and a few indexes each can need many thousands of entries — while the default sizing (64 × ~100 connections) gives you roughly 6,400 for the whole server.

Common causes

• Partitioned tables with many partitions: a query that can't prune touches every partition and every one of its indexes. By far the most common trigger.
• pg_dump / pg_restore on databases with a very large number of objects (it locks every table it dumps in one transaction).
• Migrations that alter or drop many tables in a single transaction.
• Many concurrent transactions each touching a moderate number of relations — the pool is shared, so it's the sum that matters.

How to diagnose it

While the failing workload runs (or right before it fails):

-- Quanto è pieno il lock table:
SELECT count(*) FROM pg_locks;

-- Chi sta consumando le entry:
SELECT pid, count(*)
FROM pg_locks
GROUP BY pid
ORDER BY count(*) DESC
LIMIT 10;

Compare the total against the formula above with your settings. If one pid holds thousands of locks, look at what it's running — and if partitions are involved, count them: SELECT count(*) FROM pg_inherits WHERE inhparent = 'events'::regclass;

How to fix it

• Raise max_locks_per_transaction — e.g. from 64 to 256 or 512 on servers with heavily partitioned schemas. Requires a restart; the extra shared memory is modest.

  ALTER SYSTEM SET max_locks_per_transaction = 256;
  -- poi riavvio del server

• Help partition pruning: make sure queries filter on the partition key with values the planner can see, so only the relevant partitions get locked at all.
• Batch wide operations: split migrations and maintenance scripts into multiple transactions instead of one giant one.
• Reconsider partition counts: schemas with many thousands of tiny partitions pay this tax everywhere, not just here.