GoKawiilHere 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.
... continue reading