Locks & waits

pg_stat_statements tells you how long a query took, not what it spent that time on. The Locks & waits tab answers the second question: what sessions wait on, which session is at the root of a blocking chain, and which queries are affected.

Where the data comes from

Source	Interval	What it gives
pg_stat_activity	5 s	One sample per active session: state, wait event type and name, query start, transaction start, application_name and the query id.
pg_locks + pg_stat_activity	5 s	Blocked sessions, the session blocking each one, the lock type and relation, how long the waiting statement has run, and the blocking transaction’s age.

Sampling means the numbers are estimates: session time is inferred from samples, and a wait shorter than the interval can be missed entirely. The UI says so wherever it matters.

Sessions belonging to QueryPilot itself are excluded, as are idle sessions with no work in flight.

CPU versus waiting

A session that is active with no wait event is running on CPU. Every other active sample is waiting, grouped by PostgreSQL’s wait event types:

Type	Typical meaning
Lock	Waiting for a heavyweight lock held by another transaction, such as a row or table lock.
LWLock	Contention on an internal shared-memory lock, for example buffer mapping.
IO	Reading or writing data, WAL or temporary files.
IPC	Waiting for another process, such as a parallel worker.
Client	Waiting for the application to send or receive. Often idle time, not database slowness.
BufferPin, Timeout, Extension, Activity	Less common; Activity is background processes idling.

How waits are attributed to queries

Each sample carries the query id PostgreSQL records (pg_stat_activity.query_id, available from PostgreSQL 14). QueryPilot matches it to the same fingerprints the query explorer uses, so a wait profile links straight to the query. On older versions, or when the id is not set, it falls back to matching the normalized query text. Samples that cannot be resolved are still counted in the totals.

What the tab shows

• Wait timeline: blocked sessions and the longest wait over the window.
• Wait events: time by wait type and by individual event, with CPU separated from waiting.
• Top queries: the queries that wait the most, the queries that block others most often, and the queries most often blocked.
• Blocking tree: the most recent blocking chain, from the session at the root down to everything queued behind it, with each session’s state, transaction age and statement.

A session that is idle in transaction at the root of a chain is called out: it holds locks while doing nothing, and it is the usual cause of a pile-up during a migration.

Per query, the same view is available from a query’s own page: what it waits on, what blocks it and what it blocks.

Related findings

Analysis turns the same data into findings: lock-contention groups waiting sessions by the contended relation, long-transaction reports transactions old enough to hold back vacuum or block others, and connection-saturation warns before connections run out. See Findings.

API

Method	Path	Description
GET	/api/v1/databases/{id}/waits	Wait timeline, wait events, top waiting, blocking and blocked queries, and the latest blocking tree. Query: range or from/to, step.
GET	/api/v1/queries/{id}/waits	The same profile for one query.