LWLock:LockManager
This event occurs when a process is waiting to access the internal shared memory structure that tracks locks on tables and indexes
It is not waiting for a row lock (like a standard blocking update); it is waiting for the right to read/write the list of who holds what locks.
This is effectively a CPU scaling bottleneck. It happens when many processes are trying to acquire or release locks at the exact same microsecond,
causing a traffic jam at the "Lock Manager's Front Desk."
This is an area where PostgreSQL 18 has made significant improvements.
Why it is happening (Root Causes)
- The "Fast-Path" Limit (The Core Issue):
PostgreSQL tries to avoid touching the main shared memory lock table because it's slow/contested.
Instead, it tries to record locks in a small, private "Fast-Path" array associated with the backend process.
This array was hardcoded to hold only 16 locks in old verions, where the OIDs of the tables and indexes are "remembered"
If a query touched 17+ tables/indexes (or partitions), it "spilled over" into the main shared lock manager, forcing it to take the LockManager LWLock.
-
The Partition Trap:If you query a partitioned table with 100 partitions and don't prune effectively, you instantly grab 100 locks,
overflow the Fast-Path, and hammer the LockManager
-
The Connection Storms:
if hundreds of connections suddenly try to query data, even if they don't touch the same rows, they all need to register their presence in the Lock Manager.
This creates contention on the memory buckets (Lock Manager partitions).
-
Lock Holders:
Many sessions (especially "idle in transaction") holding locks for long periods.
- Complex Queries and Table Partitions:
Queries that involve multiple partitions or indexes can acquire many locks.
Further Diagnosis
Use SELECT * FROM pg_locks to check the lock status
Resolution
- Identify and terminate or commit long-running idle in transaction sessions. Configure idle_in_transaction_session_timeout
- Ensure partition pruning, reduce transaction scope, and minimize explicit locking commands.
- Use connection pooling and limit the number of concurrent active transactions.
- For workloads inherently requiring many relation locks (e.g., massive partitioning), PostgreSQL 18+ offers a significant architectural improvement.
- Use Prepared statements. Reported to give 5x improvement
Additional References