PostgreSQL 'out of shared memory' with 'increase max_locks_per_transaction': find what is taking the locks and size the lock table

#Symptom

A query, migration, pg_dump or maintenance job fails with:

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

Despite the wording, the server has not run out of RAM. PostgreSQL keeps heavyweight locks in a fixed-size shared lock table that is allocated at startup. When one transaction, or all sessions together, need more lock slots than that table holds, the next lock request fails. Other sessions that need new locks can fail at the same moment, so one bad job can break unrelated application queries.

The lock table holds roughly:

max_locks_per_transaction * (max_connections + max_prepared_transactions)

This is a shared pool, not a hard per-transaction limit. A single transaction can take far more than max_locks_per_transaction locks as long as the pool has room. With the defaults (64 and 100 connections), the pool is a few thousand slots.

#Common causes

#Check 1: confirm current settings

SELECT name, setting, context
FROM   pg_settings
WHERE  name IN ('max_locks_per_transaction',
                'max_pred_locks_per_transaction',
                'max_connections',
                'max_prepared_transactions');

context = postmaster means a change needs a server restart. That holds for all of these.

#Check 2: find who holds the locks

While the problem is happening, or while you reproduce it, count locks per backend:

SELECT  l.pid,
        a.usename,
        a.application_name,
        a.state,
        now() - a.xact_start AS xact_age,
        count(*)             AS locks,
        left(a.query, 80)    AS query
FROM    pg_locks l
LEFT JOIN pg_stat_activity a ON a.pid = l.pid
GROUP BY l.pid, a.usename, a.application_name, a.state, a.xact_start, a.query
ORDER BY locks DESC
LIMIT 10;

One pid with thousands of locks is your culprit. Then see which relations it is locking:

SELECT  l.locktype, l.mode, l.fastpath, count(*) AS n
FROM    pg_locks l
WHERE   l.pid = 12345            -- pid from the previous query
GROUP BY l.locktype, l.mode, l.fastpath
ORDER BY n DESC;

SELECT  c.relkind, count(*) AS n
FROM    pg_locks l
JOIN    pg_class c ON c.oid = l.relation
WHERE   l.pid = 12345
GROUP BY c.relkind;             -- r = table, i = index, p = partitioned table

Many i and r rows with names like orders_2023_01, orders_2023_02 point to a query that is not pruning partitions. fastpath = true locks are held per backend and don't use the shared table until they overflow. Large counts with fastpath = false are what fill it.

If the session is a runaway job and the error is spreading to other workloads, end its transaction:

SELECT pg_cancel_backend(12345);      -- cancel the current query
-- if it stays idle in transaction:
SELECT pg_terminate_backend(12345);

Locks are released when the transaction ends.

#Check 3: count partitions and their indexes

SELECT  parent.relname       AS partitioned_table,
        count(*)             AS partitions
FROM    pg_inherits i
JOIN    pg_class parent ON parent.oid = i.inhparent
WHERE   parent.relkind = 'p'
GROUP BY parent.relname
ORDER BY partitions DESC;

A query that scans a partitioned table without pruning locks the parent, every partition and every index it might use. 1,000 partitions with 4 indexes each is about 5,000 locks for one SELECT. Check the plan:

EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE created_at >= now() - interval '1 day';

If the plan lists every partition, or shows Subplans Removed only at run time, look at the predicate. Pruning needs a condition on the partition key that the planner or executor can evaluate. Wrapping the key in a function or a cast often defeats it.

#Fix: raise the setting, standbys first

Raising max_locks_per_transaction is safe and the memory cost is small compared with shared_buffers, but it needs a restart. Values of 128–512 are common on databases with many partitions or tables. Size it from what you measured in Check 2, not by guessing very large.

Warning

On streaming replication, a hot standby must have max_locks_per_transaction (and max_connections, max_prepared_transactions) at least as high as the primary. If you raise the primary first, the standby will refuse to start or pause recovery when it replays the change, depending on the version. Raise and restart standbys first, then the primary. Do the reverse order when lowering.

#Self-managed

ALTER SYSTEM SET max_locks_per_transaction = 256;

# on each standby first, then the primary (or switch over, then restart the old primary)
sudo systemctl restart postgresql        # service name varies by distribution and version
psql -c "SHOW max_locks_per_transaction;"

With Patroni, change the value in the cluster's dynamic configuration (patronictl edit-config). Patroni marks members as pending restart. Restart the replicas one at a time by name (patronictl restart <cluster> <replica-member>), then the leader.

#Amazon RDS / Aurora PostgreSQL

The parameter is static, so it applies at reboot. Use a custom parameter group:

aws rds modify-db-parameter-group \
  --db-parameter-group-name pg16-custom \
  --parameters "ParameterName=max_locks_per_transaction,ParameterValue=256,ApplyMethod=pending-reboot"

aws rds reboot-db-instance --db-instance-identifier mydb

For Aurora, the parameter can be in the DB cluster or DB instance parameter group, depending on the engine version. Check where it appears in your parameter group listing. Reboot read replicas before or together with the writer.

#Azure Database for PostgreSQL Flexible Server

az postgres flexible-server parameter set \
  --resource-group rg-db --server-name pg-prod \
  --name max_locks_per_transaction --value 256

az postgres flexible-server restart --resource-group rg-db --name pg-prod

With high availability enabled, the restart includes the standby. Read replicas need the same or a higher value. Set them first.

Tip

In PostgreSQL 18, the number of fast-path lock slots per backend is derived from max_locks_per_transaction. Raising it also reduces lock-manager contention on partition-heavy workloads. Check the release notes for your exact version before relying on this.

#Fix the workload, not just the setting

• Partitioned tables: make queries filter on the partition key. Detach and drop old partitions. Avoid tens of thousands of tiny partitions.
• Migrations: run per-table DDL in separate transactions. Drop large schemas in batches instead of one DROP SCHEMA ... CASCADE.
• Temp tables in functions: create the temp table once per session (CREATE TEMP TABLE IF NOT EXISTS) and TRUNCATE it, rather than create and drop in a loop.
• pg_dump of huge catalogs: schedule dumps when other lock-heavy jobs aren't running, and size the setting for the number of tables plus indexes being dumped.

#Prevent it next time

• Add an alert on total rows in pg_locks relative to max_locks_per_transaction * (max_connections + max_prepared_transactions), warning at around 60%.
• Track partition count per table in your capacity review, and set an upper bound in your design standards.
• Keep primary and standby parameter values in the same config management code, with standby-first ordering built into the change procedure.
• Test schema migrations against a production-sized catalog, not an empty dev database. Lock counts scale with object count, not data volume.