04. Wait event analysis (Active Session History)
Active Session History (ASH) style dashboard for deep wait event analysis.
ASH by wait type​
ASH by wait event​
ASH by query​
Purpose​
Similar to AWS RDS Performance Insights or Oracle ASH, this dashboard provides:
- Detailed breakdown of what sessions are waiting on
- Historical wait event patterns
- Correlation between waits and performance issues
When to use​
- Database is slow but CPU is not high
- Investigating lock contention
- Understanding I/O patterns
- Diagnosing intermittent slowdowns
Prerequisites​
This dashboard uses wait event data collected from pg_stat_activity by pgwatch. No additional extensions are required.
Key panels​
Wait event distribution​
What it shows:
- Stacked area chart of wait events over time
- Each color represents a wait event type
Wait event categories:
| Category | Description | Common events |
|---|---|---|
| CPU | On-CPU processing | CPU |
| IO | Disk I/O operations | DataFileRead, WALWrite |
| LWLock | Internal PostgreSQL locks | BufferContent, LockManager |
| Lock | Row/table locks | tuple, transactionid |
| BufferPin | Buffer pinning | BufferPin |
| Activity | Background processes | LogicalLauncherMain |
| IPC | Inter-process communication | BgWorkerStartup |
Top wait events​
What it shows:
- Ranked list of most common wait events
- Percentage of total wait time
Interpretation guide:
| Wait event | Meaning | Action |
|---|---|---|
CPU | Query processing | Normal if workload-appropriate |
DataFileRead | Reading from disk | Check shared_buffers, add memory |
DataFileWrite | Writing to disk | Normal for writes |
WALWrite | WAL I/O | Check storage speed |
LWLock:BufferContent | Buffer contention | Scale up, partition tables, reduce bloat |
Lock:tuple | Row lock wait | Check for lock conflicts |
Lock:transactionid | Transaction wait | Long transactions blocking |
Wait events by database​
Breakdown showing which databases contribute most to waits.
Wait events by query​
Links wait events to specific queries (when available).
Limitations:
- Only captures queries active during sampling
- Short queries may be missed
- Use with 02. Query analysis for complete picture
Understanding wait events​
CPU waits​
High CPU wait is normal for compute-bound workloads. Investigate if:
- CPU wait is disproportionate to actual CPU usage
- Query throughput is lower than expected
I/O waits​
Common I/O wait events:
| Event | Cause | Solution |
|---|---|---|
DataFileRead | Reading data not in cache | Increase shared_buffers, add indexes |
DataFileExtend | Growing data files | Pre-extend files, faster storage |
DataFileFlush | Flushing dirty pages | Tune checkpoint_* settings |
WALSync | Syncing WAL | Faster WAL storage, adjust commit_delay |
Lock waits​
Lock wait events indicate heavyweight lock contention:
| Event | Cause | Solution |
|---|---|---|
Lock:tuple | Row-level lock wait | Avoid multiple sessions modifying same rows; use FOR UPDATE SKIP LOCKED or NOWAIT |
Lock:transactionid | Waiting for transaction | Find and address long transactions |
Lock:relation | Table-level lock | Use low lock_timeout with retries for DDL. See Zero-downtime schema migrations |
LWLock waits​
Lightweight locks are internal to PostgreSQL:
| Event | Cause | Solution |
|---|---|---|
LWLock:BufferContent | Concurrent access to same buffer page | Scale up instance, partition tables, reduce foreign keys, address bloat with VACUUM FULL or pg_repack |
LWLock:LockManager | Fast-path lock limit exceeded (16 locks/backend) | Enable partition pruning, reduce indexes, use connection pooler. See Postgres Marathon series |
Variables​
| Variable | Purpose |
|---|---|
cluster_name | Cluster filter |
node_name | Node filter |
db_name | Database filter |
wait_event_type | Filter by event type |
Related dashboards​
- Overview — 01. Node overview
- Lock details — 13. Lock contention
- Query correlation — 02. Query analysis
Troubleshooting​
No wait event data​
-
Verify pgwatch is collecting metrics:
docker compose logs pgwatch | grep -i wait -
Check VictoriaMetrics has wait event data:
curl 'http://localhost:8428/api/v1/query?query=pg_stat_activity_count' -
Ensure
pg_stat_activityis accessible to the monitoring user
Wait events don't match RDS Performance Insights​
Different sampling rates and methodologies may cause variations. PostgresAI monitoring uses:
- Default: 10ms sampling interval
- Aggregation into time buckets for visualization
RDS Performance Insights may use different intervals.
"Other" category too large​
The "Other" category contains infrequent or miscellaneous waits. If large:
- Check for uncommon extensions
- Review background worker activity
- Consider if sampling rate is appropriate