Sixteen Locks Ought to Be Enough for Anybody — The Build

The Build

search
rss
☀

2026-08-17
6 min

PostgreSQL

Sixteen Locks Ought to Be Enough for Anybody

Here is a fact about PostgreSQL that surprises even people who have been running it for years: every query against a table takes a lock on every index on that table, whether or not the query uses any of them.
This is usually harmless. The locks are AccessShareLock, the weakest lock there is; they conflict with almost nothing, and they’re cheap to take. But “cheap” is not “free,” and at a certain combination of index count and query rate, this behavior turns into a CPU-eating lock contention problem on queries as innocent as a single-row primary key lookup.
The planner locks everything it looks at
When PostgreSQL plans a query, it takes AccessShareLock on every relation the query might use. That means the table, and every index on the table, because the planner has to open each index to decide whether it’s useful. It doesn’t matter that the plan ultimately uses exactly one of them. Consideration requires a lock.
So a table with a primary key and 20 secondary indexes costs 22 relation locks per query: the table, the primary key index, and the 20 others the planner examined and discarded. Every single execution.
The fast path, and falling off of it
Taking a lock normally means an entry in the shared lock table, which lives in shared memory and is protected by lightweight locks (it’s split into 16 partitions, each with its own LWLock). At high query rates on a many-core machine, those 16 LWLocks become a point of contention all by themselves.
PostgreSQL has an optimization for this, added back in 9.2: fast-path locking. Each backend gets a small private array where it can record weak relation locks (AccessShareLock, RowShareLock, RowExclusiveLock) without touching the shared lock table at all. Through PostgreSQL 17, that array has exactly 16 slots.
Sixteen. Count your indexes.
If a query needs more than 16 relation locks, the overflow goes through the shared lock table, LWLocks and all. One backend doing this is fine. A few hundred backends doing it thousands of times per second is how you get a wall of LWLock:LockManager waits in pg_stat_activity (spelled lock_manager before PostgreSQL 13), CPU pinned, and throughput dropping while the queries themselves remain trivially simple. If you run on RDS or Aurora, this is the LWLock:LockManager wait event that Performance Insights loves to show you in alarming shades of brown.
The nasty property of this failure mode is that it concentrates on exactly the wrong tables. The tables with too many indexes are your core tables, the ones every query touches, so every query pays the toll, and the contention scales with your total query rate. Partitioned tables get there even faster, since the planner may need to lock each partition and each partition’s indexes.
Watching it happen
This is easy to demonstrate. Build a users table with a primary key and twenty single-column indexes (I have seen worse in production, and so have you), and run the most boring query imaginable:
Copy1BEGIN;
2SELECT * FROM users WHERE id = 42;
3
4SELECT fastpath, count(*)
5 FROM pg_locks
6 WHERE pid = pg_backend_pid()
7 AND locktype = 'relation'
8 AND relation <> 'pg_locks'::regclass
9 GROUP BY 1;
10
11 fastpath | count
12----------+-------
13 f | 6
14 t | 16
That’s PostgreSQL 16: a single-row primary key lookup taking 22 relation locks, filling all 16 fast-path slots and pushing 6 locks into the shared lock table. On every execution, forever.
Prepared statements, the accidental fix
Here’s the part that isn’t obvious: prepared statements make this problem disappear, and not for any reason having to do with locking rules.
A prepared statement is planned with a custom plan for its first five executions. On the sixth, PostgreSQL builds a generic plan, caches it, and (if the cost comparison works out, which for a primary key lookup it will) reuses it from then on. Reusing a cached plan skips the planner entirely, and executing a cached plan only locks the relations actually in the plan, not everything the planner would have considered.
Copy1PREPARE u(bigint) AS SELECT * FROM users WHERE id = $1;
2-- execute it six times to get past the custom-plan phase, then:
3
4BEGIN;
5EXECUTE u(42);
6
7SELECT relation::regclass AS relation, fastpath
8 FROM pg_locks
9 WHERE pid = pg_backend_pid()
10 AND locktype = 'relation'
11 AND relation <> 'pg_locks'::regclass;
12
13 relation | fastpath
14------------+----------
15 users_pkey | t
16 users | t
Twenty-two locks became two, both comfortably on the fast path, and the shared lock manager never hears from this query again. As a bonus, you also stopped paying to re-plan the query on every execution, which on an over-indexed table is not a small amount of CPU by itself.
Two caveats. First, generic plans have a well-known failure mode: parameters with skewed distributions can get plans that are catastrophically wrong for particular values. plan_cache_mode exists for when this bites you, but the honest answer is to test your hot queries.
Second, and more annoying: prepared statements and connection poolers have a complicated relationship. PgBouncer in transaction pooling mode only supports protocol-level prepared statements as of 1.21, via max_prepared_statements; if you’re on an older version, upgrade. RDS Proxy is worse: a prepared statement pins the client session to a backend connection, which defeats the multiplexing that is the entire reason RDS Proxy exists. If your architecture depends on RDS Proxy, this fix is effectively off the table, and you should read the next section with particular interest.
PostgreSQL 18 removes the magic number
In PostgreSQL 18, thanks to work by Tomas Vondra, the fast-path array is no longer fixed at 16 slots. It’s sized at server start from max_locks_per_transaction, in 16-slot groups, so the default of 64 gives every backend 64 fast-path slots. Same table, same query, no prepared statement:
Copy1SHOW max_locks_per_transaction;
2 max_locks_per_transaction
3---------------------------
4 64
5
6BEGIN;
7SELECT * FROM users WHERE id = 42;
8
9SELECT fastpath, count(*)
10 FROM pg_locks
11 WHERE pid = pg_backend_pid()
12 AND locktype = 'relation'
13 AND relation <> 'pg_locks'::regclass
14 GROUP BY 1;
15
16 fastpath | count
17----------+-------
18 t | 22
All 22 locks on the fast path, out of the box. (Set max_locks_per_transaction = 16 on 18 and you get the old 16/6 split back, if you enjoy reenacting historical disasters.)
Note that there is no dedicated knob for this; the fast-path capacity rides along on max_locks_per_transaction, whose primary job is sizing the shared lock table. If your schema is heavily partitioned or heavily indexed, raise it past your worst-case relation count per query. The fast-path arrays themselves cost almost nothing; the shared lock table entries are bigger, but on any machine where this problem exists, you have the memory.
Or, and hear me out: fewer indexes
Both fixes above treat the symptom. The disease is a table with 21 indexes.
You already know the standard costs of over-indexing: every INSERT and non-HOT UPDATE maintains every index, VACUUM has to process every index, and each one occupies storage and buffer cache. Add this one to the list: through PostgreSQL 17, every index past the fifteenth on a hot table pushes every query against it off the lock fast path, and the penalty is paid in shared-memory contention across the whole system, not just by the query that did it.
Look at idx_scan in pg_stat_all_indexes. If it’s zero over a representative period, and the index isn’t enforcing a constraint, drop it. Your lock manager will thank you, quietly, sixteen locks at a time.

Related

All Your GUCs in a Row: max_locks_per_transaction
OK, sometimes you can lock tables.
All Your GUCs in a Row: enable_self_join_elimination

← Older
All Your GUCs in a Row: jit_above_cost, jit_inline_above_cost, and jit_optimize_above_cost

Newer →
All Your GUCs in a Row: jit_debugging_support, jit_dump_bitcode, and jit_profiling_support

Christophe Pettus · PGX

PostgreSQL query execution involves a subtle mechanism concerning relation locking that can lead to significant lock contention under high load, particularly on systems with many indexes. Fundamentally, every query against a table acquires locks on every index associated with that table, even if the query does not utilize those indexes. This behavior stems from the query planner needing to examine every potential relation to determine the optimal execution plan, resulting in locks on the table itself and all relevant indexes. While the weakest lock, AccessShareLock, is generally harmless, the accumulation of these relation locks, especially when combined with frequent query execution, can transform into a CPU-eating lock contention problem affecting innocent operations.

This issue is exacerbated because acquiring standard locks involves entries in a shared lock table protected by lightweight locks, which, on systems running at high query rates on many-core machines, become a bottleneck. PostgreSQL has implemented a feature called fast-path locking to mitigate this overhead. This optimization allows backends to record weak relation locks privately in a small array, avoiding interaction with the shared lock table for most operations. The limitation of this mechanism is its capacity, which historically was fixed at sixteen slots. If a query requires more than sixteen relation locks, the excess must pass through the shared lock table, creating contention in the LockManager, which can manifest as high wait events in performance monitoring tools. This contention is concentrated on core tables, especially those with numerous indexes, and scales directly with the system's total query rate. Partitioned tables compound this issue as the planner must account for locks across multiple partitions and their associated indexes.

The complexity of this locking behavior is demonstrated when a simple single-row lookup incurs locks on the table and all associated indexes, which can amount to many locks. This pattern, however, can be circumvented by using prepared statements. Prepared statements allow PostgreSQL to cache execution plans, bypassing the planner on subsequent executions. This caching mechanism results in fewer locks being acquired, as only the relations involved in the cached plan are locked, thereby reducing shared memory contention. However, this fix is not universally applicable; architectural considerations, such as connections pooling via PgBouncer or the use of RDS Proxy, can interfere with the session-specific locking behavior of prepared statements.

In PostgreSQL 18, enhancements to the fast-path mechanism were introduced, making the fast-path array dynamically sized based on the server setting max_locks_per_transaction, allowing backends to handle a larger number of relation locks. Nevertheless, the fundamental solution to the contention issue is structural: reducing the number of indexes. The text argues that the true disease is the over-indexing itself, where each additional index forces queries off the fast path into shared-memory contention. Therefore, for heavily indexed tables, reducing the total number of indexes—specifically dropping any index that is not actively used, such as those indicated by zero index scans in pg_stat_all_indexes—is the most effective long-term strategy to alleviate lock contention and improve system throughput.