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:
1 BEGIN; 2 SELECT * FROM users WHERE id = 42; 3 4 SELECT 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.
1 PREPARE u(bigint) AS SELECT * FROM users WHERE id = $1; 2 -- execute it six times to get past the custom-plan phase, then: 3 4 BEGIN; 5 EXECUTE u(42); 6 7 SELECT 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:
1 SHOW max_locks_per_transaction; 2 max_locks_per_transaction 3 --------------------------- 4 64 5 6 BEGIN; 7 SELECT * FROM users WHERE id = 42; 8 9 SELECT 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.