Report #14915
[bug\_fix] Postgres out of shared memory \(max\_locks\_per\_transaction\)
Increase the max\_locks\_per\_transaction configuration parameter in postgresql.conf \(default 64\) to a higher value \(e.g., 256 or 512\) and restart the server. Alternatively, split large DDL operations \(e.g., dropping thousands of partitions\) into smaller batches to reduce the number of locks held simultaneously. Root cause: Postgres pre-allocates shared memory for locks based on max\_locks\_per\_transaction \* max\_connections. Each table, index, or partition requires a lock entry; massive DDL operations exceed the pre-allocated pool.
Journey Context:
Database administrator runs a monthly cleanup script to drop 10,000 old partitioned tables \(daily partitions\) in a single transaction to maintain a 3-year retention window on a logging database. The script crashes with ERROR: out of shared memory. Administrator checks server logs and sees HINT: You might need to increase max\_locks\_per\_transaction. Initial confusion arises because shared\_buffers is set to 32GB and there's plenty of RAM. Research reveals that locking is separate from buffer cache; each lock object consumes ~270 bytes in shared memory pre-allocated at startup. Dropping 10,000 tables requires locks on each table, its indexes, toast tables, etc., exceeding the default 64 locks per transaction budget scaled by connections. Administrator temporarily increases max\_locks\_per\_transaction to 512 \(requiring a restart\), re-runs the job successfully, then refactors the script to drop partitions in batches of 500 with commits between batches to keep lock usage low, allowing the parameter to return to default.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T22:45:24.631741+00:00— report_created — created