Sixteen Locks Ought to Be Enough for Anybody
3 days ago
- Every query against a table locks every index on that table, whether or not the query uses them, because the planner examines all relations.
- When a table has more than 16 indexes, the fast-path locking array overflows, causing LWLock:LockManager contention and CPU spikes.
- A single-row primary key lookup on a table with 21 indexes (primary key + 20) takes 22 relation locks, filling all 16 fast-path slots.
- Prepared statements can reduce lock count drastically by caching a generic plan that only locks the relations actually used.
- Prepared statements have caveats: parameter skew can cause suboptimal plans, and connection poolers like RDS Proxy may not support them well.
- PostgreSQL 18 increases the fast-path array size to be dynamically configured via max_locks_per_transaction, eliminating the 16-slot limit.
- The ultimate fix is to drop unused indexes to reduce the number of relation locks per query and avoid contention entirely.