Fix PostgreSQL 'ERROR: out of shared memory' When Too Many Locks or Temp Tables

The Error

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

Hit this during a big migration? You're not alone. The error surfaces when a single transaction tries to acquire more locks than PostgreSQL has slots to track — migrations touching hundreds of tables, bulk ETL jobs spinning up temp tables, or partitioned tables with 200+ partitions are all common culprits. The hint PostgreSQL gives you is accurate: max_locks_per_transaction is almost always the problem.

Root Cause

At startup, PostgreSQL pre-allocates a fixed lock table in shared memory. Its size is determined by:

max_locks_per_transaction × (max_connections + max_prepared_transactions)

The default max_locks_per_transaction is 64. That means if a single transaction locks more than 64 objects — tables, indexes, sequences, each counted separately — PostgreSQL exhausts the lock table and throws this error.

What tends to trigger it:

• Django or Rails migrations adding indexes and constraints across many tables in one transaction
• Liquibase or Flyway changesets running against large schemas (50+ tables)
• Bulk ETL jobs that create hundreds of temp tables without explicitly dropping them
• Partitioned tables — every partition is its own lock object, so a table with 200 monthly partitions burns 200 slots per query
• Explicit LOCK TABLE calls stacked inside a long transaction

Fix 1: Increase max_locks_per_transaction (Main Fix)

Start by checking the current value:

SHOW max_locks_per_transaction;

Then edit postgresql.conf. Not sure where yours lives? Ask PostgreSQL directly:

SELECT current_setting('config_file');

-- Typical paths:
-- Ubuntu/Debian: /etc/postgresql/16/main/postgresql.conf
-- Docker: /var/lib/postgresql/data/postgresql.conf

Bump the value:

# postgresql.conf
max_locks_per_transaction = 256   # up from default 64

This setting requires a full restart — a reload won't cut it:

# systemd
sudo systemctl restart postgresql

# Docker
docker restart your_postgres_container

# pg_ctl
pg_ctl restart -D /var/lib/postgresql/data

Confirm the change landed:

SHOW max_locks_per_transaction;
-- should return 256

Fix 2: Reduce Lock Usage in Your Code

On managed databases — RDS, Cloud SQL, Supabase — you often can't touch postgresql.conf. In those cases, reduce how many locks your code acquires in the first place.

Break large migrations into smaller transactions:

-- Bad: one transaction touching 200 tables
-- Good: batches of 20–30 tables per transaction

BEGIN;
ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ;
ALTER TABLE order_items ADD COLUMN unit_cost NUMERIC(10,2);
-- keep it under ~50 operations per transaction
COMMIT;

Drop temp tables explicitly before creating new ones:

DROP TABLE IF EXISTS tmp_processing;
CREATE TEMP TABLE tmp_processing AS
  SELECT id FROM orders WHERE status = 'pending';

Use ON COMMIT DROP so temp tables clean themselves up:

BEGIN;
CREATE TEMP TABLE batch_data (
  id BIGINT
) ON COMMIT DROP;  -- auto-dropped at COMMIT, lock freed immediately

INSERT INTO batch_data SELECT ...;
-- do your work
COMMIT;  -- temp table is gone

Fix 3: For Partitioned Tables

Partitioned tables are a sneaky source of this error. Each partition holds its own lock, so a query touching a 365-partition event log locks 365+ objects before doing any real work.

-- Check how many partitions your table has
SELECT count(*)
FROM pg_inherits
WHERE inhparent = 'events'::regclass;

A rough rule of thumb: set max_locks_per_transaction to at least 2 × number_of_partitions.

# Table with 365 daily partitions:
max_locks_per_transaction = 1024

Also confirm partition pruning is enabled — it's on by default in PG 12+ and tells the planner to skip partitions that don't match your query's WHERE clause:

SHOW enable_partition_pruning;
-- should be 'on'

Fix 4: ALTER SYSTEM (No File Edit Needed)

Have superuser access but don't want to SSH in and edit config files? Use ALTER SYSTEM:

ALTER SYSTEM SET max_locks_per_transaction = 256;

-- pg_reload_conf() is NOT enough for this setting
SELECT pg_reload_conf();  -- skip this
-- You still need a full restart

Verification

After restarting, confirm the setting and observe real lock usage:

-- Confirm new value
SELECT name, setting, unit
FROM pg_settings
WHERE name = 'max_locks_per_transaction';

-- Snapshot of current locks (run while the problem operation is in progress)
SELECT relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY pid, relation;

-- How many locks is each backend holding?
SELECT pid, count(*) AS lock_count
FROM pg_locks
WHERE relation IS NOT NULL
GROUP BY pid
ORDER BY lock_count DESC;

Reproduce the original error, run that last query during the operation, and you'll see the exact lock count — which tells you precisely what value to set rather than guessing.

Prevention

• Watch lock table usage proactively: A lock_count per backend that regularly creeps above 50 is a warning sign. Raise the limit before hitting the wall.
• Partition conservatively: Monthly partitions cover most use cases. Daily partitions for a 3-year dataset means 1,000+ lock objects — that's a lot to carry in every transaction.
• Run heavy migrations outside transactions: Flyway's --no-transaction flag and Django's atomic = False both let you skip the wrapping transaction on migrations that touch many tables. Or split into smaller batches — 30 tables per transaction is a safe ceiling.
• Account for max_connections in your math: The lock table scales with connection count. Double max_connections from 100 to 200 and each transaction effectively gets half the lock slots unless you raise max_locks_per_transaction in proportion.

Managing PostgreSQL config across multiple environments? The YAML ↔ JSON Converter at ToolCraft is handy for validating config files in YAML format — common in Docker Compose setups. Runs entirely in the browser, nothing uploaded.