04. Wait event analysis (Active Session History)
Understand what your database is waiting on. This dashboard provides functionality similar to AWS RDS Performance Insights and Google CloudSQL Query Insights.
When to use
- Active sessions are high and you need to identify the bottleneck
- You suspect IO or lock pressure
- The Node overview ASH panel shows unusual wait patterns
- You need to correlate waits with specific queries
Key panels
- Wait class distribution over time — stacked area chart showing CPU, IO, Lock, Client, and other wait types
- Top waits by time — ranked list of specific wait events consuming the most time
- Active sessions by wait type — current session distribution across wait categories
Wait event types
| Wait type | Meaning | Common causes |
|---|---|---|
| CPU | Processing queries | Expected for compute-heavy workloads |
| IO | Reading/writing to disk | Undersized shared_buffers, missing indexes, large sequential scans |
| Lock | Waiting for locks | Concurrent updates to same rows, long transactions holding locks |
| Client | Waiting for client | Idle-in-transaction sessions, slow application, network latency |
| LWLock | Lightweight locks | Contention on internal Postgres structures, heavy WAL generation |
| BufferPin | Buffer pin waits | Concurrent access to same buffers, can indicate hot spots |
What good looks like
- CPU is the dominant wait type (means the database is doing useful work)
- Lock waits are rare and short-lived
- IO waits are low — most data is served from the buffer cache
- Client waits are minimal (no sessions stuck idle-in-transaction)
What to investigate
| Signal | Next step |
|---|---|
| Lock waits dominate | Go to Lock contention to find blocking queries |
| IO waits dominate | Check storage performance, shared_buffers size, and query plans (missing indexes causing seq scans) |
| LWLock (WALWriteLock) | Check WAL generation rate in Node overview; consider synchronous_commit = off for non-critical data |
| Client waits high | Look for idle-in-transaction sessions; set idle_in_transaction_session_timeout |
Related Checkup checks
- G001 — memory-related settings (CLI)
- A003 — Postgres settings (CLI)
- N001 — wait events grouped by type and query (monitoring stack)