ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction.
ID: database/pg-out-of-shared-memory
Version Compatibility
| Version | Status | Introduced | Deprecated | Notes |
|---|---|---|---|---|
| 16 | active | — | — | — |
Root Cause
PostgreSQL has exhausted its shared memory allocation for lock tracking. This typically occurs when a single transaction acquires locks on a very large number of objects (e.g., ALTER TABLE on a partitioned table with thousands of partitions, or mass DDL operations). The max_locks_per_transaction setting controls the shared lock table size.
genericWorkarounds
-
90% success Increase max_locks_per_transaction to accommodate your workload
Edit postgresql.conf: max_locks_per_transaction = 256 (default is 64). Restart PostgreSQL. Calculate the required value: total lock slots = max_locks_per_transaction * (max_connections + max_prepared_transactions). For partitioned tables, each partition counts as a separate lock.
-
85% success Break large DDL operations into smaller batches to reduce concurrent lock count
Instead of a single ALTER TABLE on a parent partitioned table, alter each partition individually in separate transactions. For mass DROP/CREATE operations, process in batches of 50-100 objects per transaction with COMMIT between batches.
Dead Ends
Common approaches that don't work:
-
Increasing shared_buffers hoping it will increase lock table space
95% fail
shared_buffers controls the buffer cache for data pages, not the lock table. The lock table size is controlled by max_locks_per_transaction * max_connections. Changing shared_buffers has no effect on lock table capacity.
-
Setting max_locks_per_transaction to an extremely high value without understanding the memory implications
60% fail
Each lock slot consumes shared memory. Setting max_locks_per_transaction very high (e.g., 10000) with many connections can exhaust total shared memory, causing PostgreSQL to fail to start.