PostgreSQL — ERROR: out of shared memory During Heavy DDL

Resolve PostgreSQL out of shared memory errors from lock table overflow by understanding max_locks_per_transaction, sizing it for DDL fan-out, and batching schema changes.

advanceddebuggingpostgresqllocksconfiguration

Symptoms

ERROR: out of shared memory: this connection has run out of shared memory for locks — increase max_locks_per_transaction
The failure occurs during ALTER TABLE on a table with many partitions, or one transaction touching thousands of objects
HINT text mentions the number of lock slots held, far above the default budget

Possible causes

One transaction locks more objects than its share of lock slots: a DROP or ALTER cascading over hundreds of partitions or dependencies
max_locks_per_transaction (default 64) is a per-transaction average budget, and a single transaction can exceed it up to a hard cap
A migration tool batches many DDL statements into one transaction, accumulating locks beyond the budget
Combination with FK cascade drops multiplies the object count per statement

Diagnosis steps

Read the hint for the held count

psql -c "ALTER TABLE events DETACH PARTITION events_202610;" 2>&1 | tail -3

Interpretation

The HINT reports the lock slots currently held, telling you exactly how far the transaction exceeded the budget.

Count the objects a statement will touch

psql -c "SELECT count(*) FROM pg_class WHERE relispartition AND relname LIKE 'events_%';"

Interpretation

The partition or dependency count approximates the locks a single DDL will request; multiply by concurrent transactions for the sizing math.

Check current settings

psql -c "SHOW max_locks_per_transaction;"

Interpretation

Default 64 is tiny for partitioned fleets; the sizing formula is max_locks * (max_connections + prepared), bounded by shared memory.

Root cause

PostgreSQL preallocates a fixed lock table sized by max_locks_per_transaction times the number of connection slots. A single transaction that must lock more objects than its budget — typically DDL cascading across hundreds of partitions or foreign-key dependencies — exhausts the shared lock table and fails with out of shared memory.

Fix

Raise max_locks_per_transaction to cover the largest single DDL fan-out (requires restart), or restructure migrations to commit between batches, detaching or dropping partitions in separate transactions, and splitting migration tool mega-transactions into bounded units.

Risk note

Raising the setting grows the shared memory lock table permanently. Compute the budget from measured object counts, not from panic multiples.

Verify the fix

Run the previously failing DDL after the change; it completes, and a load test with concurrent transactions shows no lock table pressure.

Symptom walkthrough

The error is specific and self-diagnosing:

ERROR: out of shared memory:
HINT: Another command is in progress, or you have run out of
shared memory for locks: increase max_locks_per_transaction.
This command appears to be trying to lock 4128 objects.

Four thousand objects in one command — the partition fleet plus its indexes and constraints. The budget math: the lock table holds max_locks_per_transaction * (max_connections + max_prepared_transactions) slots total, and any single transaction may use up to the whole table when alone but collides when others hold theirs. Default 64 per transaction is designed for OLTP, not for a DETACH on a 500-partition table.

Fix in practice

Size from measured counts, then restructure what you can:

# postgresql.conf — requires restart
max_locks_per_transaction = 512     # covers 500-partition cascades

-- batch the DDL so each transaction stays small:
BEGIN;
  ALTER TABLE events DETACH PARTITION events_202610;
COMMIT;
BEGIN;
  DROP TABLE events_202610;
COMMIT;

# migration tooling: bounded transactions, not mega-transactions
for batch in chunked(partitions, size=50):
    with conn.transaction():
        for p in batch:
            conn.execute(f"ALTER TABLE events DETACH PARTITION {p}")

Prefer restructuring when the fan-out is accidental (a tool wrapping all DDL in one transaction); prefer the parameter when the fan-out is intrinsic (a single ATTACH across the whole partition fleet).