Skip to main content
View rawEdit

Wait event metrics

Active session history and wait event metrics from pg_stat_activity.

Data source​

Wait event data is collected from pg_stat_activity:

select
  pid,
  state,
  wait_event_type,
  wait_event,
  query
from pg_stat_activity
where backend_type = 'client backend';

Core metrics​

Series are exported with the pgwatch_ prefix. Wait-event sampling counts come from the wait_events metric group (column total), and session state/duration come from the pg_stat_activity group.

Session state metrics​

MetricTypeDescription
pgwatch_pg_stat_activity_countGaugeSessions, grouped by state
pgwatch_pg_stat_activity_max_tx_durationGaugeLongest running transaction (seconds)

Wait event metrics​

MetricTypeDescription
pgwatch_wait_events_totalGaugeSampled count of backends per wait event / type

There is no pg_wait_event_count or pg_wait_event_activity_count series.

Labels​

Session state labels (pgwatch_pg_stat_activity_count)​

LabelValuesDescription
stateactive, idle, idle in transaction, idle in transaction (aborted), ...Session state
datnameDatabase nameTarget database
application_nameApplication nameReported application_name

Wait event labels (pgwatch_wait_events_total)​

LabelValuesDescription
wait_event_typeLWLock, Lock, BufferPin, Activity, Client, Extension, IPC, Timeout, IO, plus the synthesized CPU* placeholder (see below)Wait category
wait_eventVariousSpecific wait event
datnameDatabase nameTarget database
query_idqueryid (PG14+)Associated query id, when available

Wait event categories​

CPU* (synthesized placeholder)​

CPU is not a real PostgreSQL wait_event_type. When a backend is running on CPU, its wait_event and wait_event_type are NULL. To make on-CPU activity visible, the wait_events metric coalesces those NULLs into a CPU* placeholder value — so you will see CPU* (not CPU) in the wait_event / wait_event_type labels. The categories below are the standard PostgreSQL wait-event types reported as-is.

IO​

Disk I/O operations:

EventDescription
DataFileReadReading data file blocks
DataFileWriteWriting data file blocks
DataFileExtendExtending data file
DataFileFlushFlushing data file
WALReadReading WAL
WALWriteWriting WAL
WALSyncSyncing WAL to disk

Lock​

Row and table-level locks:

EventDescription
relationTable lock wait
tupleRow lock wait
transactionidTransaction lock wait
virtualxidVirtual transaction lock

LWLock​

PostgreSQL internal lightweight locks:

EventDescription
buffer_contentBuffer content lock
buffer_mappingBuffer mapping lock
WALInsertWAL insertion lock
lock_managerLock manager lock
ProcArrayProcess array lock

BufferPin​

Buffer pinning waits:

EventDescription
BufferPinWaiting for buffer pin

Activity​

Background process waits:

EventDescription
LogicalLauncherMainLogical replication launcher
AutoVacuumMainAutovacuum launcher
BgWriterMainBackground writer
CheckpointerMainCheckpointer process
WalWriterMainWAL writer

Common queries​

Sessions by state​

sum by (state) (pgwatch_pg_stat_activity_count)

Wait events distribution​

sum by (wait_event_type) (pgwatch_wait_events_total)

Active (non-idle) sessions​

sum(pgwatch_pg_stat_activity_count{state!~"idle.*"})

Sessions waiting on locks​

sum(pgwatch_wait_events_total{wait_event_type="Lock"})

I/O wait ratio​

sum(pgwatch_wait_events_total{wait_event_type="IO"})
/
sum(pgwatch_pg_stat_activity_count{state="active"})

Long-running transactions​

pgwatch_pg_stat_activity_max_tx_duration > 300

Dashboard usage​

These metrics are used in:

Average Active Sessions (AAS) health matrix​

The PostgresAI console rolls this wait-event data up into its DB health matrix as Average Active Sessions (AAS) checks: an overall check plus one per wait-event category (IO, IPC, Lock, LWLock). Each check pairs a sustained avg value with a peak value. The peak is derived from pgwatch_wait_events_total — the worst single time bucket, plus a p99 across buckets — and normalized to the instance's vCPU count, so a cell reads the same regardless of machine size.

Peak granularity: 1-minute buckets​

The peak metrics are computed over 1-minute buckets:

  • The zone is driven by the worst 1-minute bucket (previously the worst 5-minute bucket).
  • The displayed p99 label is the p99 of 1-minute buckets (previously p99 of 5-minute buckets).

A 1-minute maximum catches short bursts that 5-minute averaging smooths away, surfacing sub-5-minute CPU-queuing spikes that the coarser bucket hid. The avg (sustained) checks are unaffected — only the peak metrics change.

Thresholds are absolute and resolution-invariant​

The peak zone cutoffs are unchanged, and intentionally so:

Peak checkGreenYellowRed
Overall (AASp)< 50% of vCPUs50–80%> 80%
Per type (AAS_IOp, AAS_IPCp, AAS_LOCKp, AAS_LWLOCKp)< 20% of vCPUs20–50%> 50%

These cutoffs are absolute and deliberately not raised for the finer granularity. The worst minute exceeding 80% of vCPUs means real CPU queuing regardless of bucket size, so the threshold is resolution-invariant. Raising the cutoffs to compensate for 1-minute granularity would either under-alert spiky workloads or re-smooth the very sub-5-minute bursts that 1-minute buckets exist to surface.

The switchover is a calibration boundary​

Because a 1-minute maximum catches bursts that 5-minute averaging smooths, worst/peak values computed over 1-minute buckets run greater than or equal to the old 5-minute values for the same workload. At the switchover:

  • Peak cells may shift redder even though the underlying workload has not changed.
  • Peak history from before the switch is not directly comparable to peak history after it.

Treat the switchover as a calibration boundary: compare peaks within a single granularity regime, not across it. The avg/sustained values are continuous across the boundary.

Troubleshooting​

No wait event data​

  1. Verify pgwatch is collecting from pg_stat_activity:

    docker compose logs pgwatch-postgres pgwatch-prometheus | grep -i "stat_activity"

  2. Check monitoring user permissions (the product grants pg_monitor, not pg_read_all_stats):

    grant pg_monitor to postgres_ai_mon;

Wait events show "unknown"​

Some wait events may not be captured if the session state changes between sampling intervals. This is normal for very short waits.

High "idle in transaction" count​

Indicates connection leaks or application issues:

select pid, usename, state, query_start, query
from pg_stat_activity
where state = 'idle in transaction'
order by query_start;