60‑Second PostgreSQL Tip #2: How to Fix Lock Contention & Wait Events
Stop slow PostgreSQL queries in 60 seconds. Learn how to detect blocked queries, fix lock contention, and monitor wait events for high-concurrency performance tuning.
Why slow queries happen in PostgreSQL
When users complain about performance, the root cause is rarely CPU or disk.
In most cases, it’s lock contention your query is waiting behind another transaction that holds a lock. This is common in high-concurrency environments where multiple sessions compete for the same resources.
Lock contention can lead to:
Blocked queries that appear hung.
Deadlocks when two sessions wait on each other.
Idle-in-transaction sessions holding locks unnecessarily.
Understanding this is critical for PostgreSQL performance troubleshooting because tuning indexes or hardware won’t help if the real issue is lock waits.
Find Blocking Query in PostgreSQL in 60 Seconds
Stop guessing and run this diagnostic query.
It uses pg_stat_activity and pg_locks to identify lock contention with pg_locks, show blocked vs. blocking sessions, and PostgreSQL wait_event types explained (Lock, LWLock, IO):
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocking.wait_event_type -- Lock, LWLock, IO, etc.
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
- Reveals which query is blocked, which one is blocking, and why.
- Works for deadlocks, idle‑in‑transaction sessions, and long‑running explicit transactions.
Not subscribed yet? Subscribe now and don’t miss my next tip!
PostgreSQL Wait Event Types: Quick Performance Tuning Guide
Understanding PostgreSQL wait events for high-concurrency databases is key for fast troubleshooting.
Use pg_stat_activity and pg_locks to see the wait_event_type and fix performance issues quickly.
1. Lock Wait Event
A PostgreSQL lock wait event happens when a session waits for a table or row lock.
Lock contention in PostgreSQL is common when multiple transactions try to update the same rows simultaneously.
Fix: Reduce transaction scope, avoid long-running transactions, and identify PostgreSQL blocking queries using pg_locks and pg_blocking_pids().
2. LWLock (Lightweight Lock) Wait
PostgreSQL LWLock wait events occur when internal lightweight locks protect shared memory structures like buffers or WAL.
High concurrency can make optimizing PostgreSQL lightweight locks necessary.
Fix: Check storage I/O, tune checkpoints, and monitor buffer-heavy workloads. Usually, no application-level change is needed.
3. IO Wait
An IO wait in PostgreSQL happens when queries hit disk instead of cache or during heavy writes.
To reduce PostgreSQL disk IO wait, optimize queries, improve indexes, increase shared_buffers, and ensure fast storage.
Fix: Use caching, monitor disk throughput, and avoid long sequential scans.
4. Transaction Wait
PostgreSQL transaction wait events occur when a session is waiting for another transaction to complete.
Resolving PostgreSQL blocked transactions is critical to prevent idle-in-transaction issues.
Fix: Kill idle transactions, keep transactions short, and check blocking queries with pg_locks.
5. Extension / Background Wait
PostgreSQL extension wait events happen due to background processes like autovacuum or logical replication.
Optimizing PostgreSQL background workers ensures smooth operation of extensions.
Fix: Monitor extensions, adjust worker settings, and avoid unnecessary background load.
Why Monitoring PostgreSQL Wait Events Matters
Knowing PostgreSQL wait event monitoring helps you prioritize fixes between lock contention and disk IO bottlenecks, avoid wasting time on CPU or index tuning, and build smarter PostgreSQL performance tuning alerts based on wait types.
Advice from me?
Add this check to your PostgreSQL performance monitoring stack (sample every 10 seconds).
You’ll eliminate most sudden latency spikes by addressing:
Idle‑in‑transaction sessions holding locks
Long‑running transactions causing lock waits
Catch you next week with another tip!
If you found this helpful, go ahead and share it with your network.
If you’re interested in becoming a premium member, feel free to support my publication!
👉 Paid members get instant access to:
Premium content with Deep dive into full RCA analysis, get actionable scripts, and Ask Me Anything via Email for Mentorship.
👉 Master High-Severity Incidents: My Checklist Mindset Playbook
After handling over 950 Sev-1 incidents for Fortune 100 customers at Microsoft, I realized something: when everything’s on fire, it’s easy to freeze or panic.
That’s why I developed a Checklist Mindset Playbook something I rely on every single time, and I want to share it with you.
Here’s what you’ll get:
A mental model I use to handle incidents without losing my head
How I stay calm and think clearly under pressure
My method to avoid panic debugging and make quick, confident decisions
Get 25+ practical SQL queries.
If you manage production systems, this mindset can change the way you respond to Sev-1 incidents trust me, it makes the chaos manageable.
After hundreds of high-stakes incidents, this checklist mindset is my secret weapon. And now, you can have it too.