A PostgreSQL backend can be active, idle, idle in transaction or waiting on a specific event. That distinction changes the investigation. CPU tuning will not resolve a session waiting on a row lock, and a database setting will not fix a client that has opened a transaction then stopped sending work.
Read wait evidence in context
Start with the affected query and its duration, then examine state, wait-event type, wait event, transaction age and blocking relationship. A wait event identifies what the backend is waiting for; it does not always identify the ultimate business cause. Pair it with SQL text, application name, user, client address and the preceding workload change.
Useful categories to separate
- Lock waits: find the blocker and the transaction holding the conflicting lock.
- I/O waits: compare read or write pressure, cache behaviour and query access paths.
- Client waits: identify application backends holding transactions open or consuming results slowly.
- LWLock and buffer contention: investigate concurrency, checkpointing and workload hotspots before changing low-level settings.
Use transaction age as a first-class signal
Long-running transactions can block DDL, delay cleanup, retain old row versions and turn a small lock conflict into an outage. A query may be finished while the session remains idle in transaction. Measure the age and ownership of the transaction, then correct the application boundary rather than relying solely on a timeout.
The fix should match the category: change transaction scope for lock waits, query or storage behaviour for I/O waits, and application lifecycle handling for client waits. Recheck the same time window after the change.
Take a focused activity snapshot
For PostgreSQL versions exposing these activity columns, the following read-only query identifies non-idle work and transaction age. It excludes the diagnostic connection itself. Visibility depends on monitoring privileges; blank query details for other users can indicate insufficient access.
SELECT pid, application_name, state,
wait_event_type, wait_event,
clock_timestamp() - xact_start AS transaction_age
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
AND state IS DISTINCT FROM 'idle'
ORDER BY xact_start NULLS LAST;
State and wait event answer different questions. An active backend can be waiting, and a NULL wait event does not prove it consumed CPU throughout the incident. Short operations can occur between samples. Capture several appropriately spaced observations before claiming that one snapshot represents the entire slowdown.
Example: the application cannot consume results quickly
Suppose an export query appears in ClientWrite while the CPU chart is quiet. The next check is the consumer and network path, not an automatic index rebuild. A slow client, a large result set or congestion may prevent the backend from sending data promptly. Compare the export size, client processing rate and normal response before changing database configuration.
A separate example is ClientRead on an idle-in-transaction connection. The database may be waiting for the application to send its next command while the transaction retains an old snapshot. This is why simply excluding idle-looking waits from every operational report can miss a cleanup or blocking risk. State, transaction age and business ownership supply the missing meaning.
Use monitoring intervals deliberately
Run successive diagnostic samples in short, separate transactions. Statistics can be cached within a transaction, which can make repeated queries look unchanged. Record the sample time and avoid treating a counter delta across a reset as valid activity. Compare observations from the same server role if failover occurs.
When a particular wait persists, write down a specific next action: inspect the blocking chain, check storage latency, examine the client, or compare a changed plan. A wait-event label alone is not a recommendation. Verification should demonstrate that the original request completes faster and the previous waiting pattern falls under a comparable workload, without creating errors elsewhere.
Keep the evidence for the next incident
Mini DBA PostgreSQL monitoring provides query and metric history alongside session, lock, vacuum and WAL diagnostics, helping teams compare an incident with normal operation. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading