Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

PostgreSQLFlexQueryStoreWaitStats — Query Store Wait Statistics

Wait-event counts aggregated per query per interval. Collected by a background sampler (~every 1 second) reading pg_stat_activity. Tells you why queries wait, not how many times they ran.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexQueryStoreWaitStats" and LogicalServerName_s == server
| project Queryid_str_s, Dbid_d, Userid_d,
          Event_type_s, Event_s, Calls_d, Start_time_t, End_time_t

Fields

Query identity

  • Queryid_d (number) — query fingerprint (prefer Queryid_str_s for joins).
  • Queryid_str_s (string) — string form of the fingerprint.
  • Dbid_d (number) — database OID.
  • Userid_d (number) — role OID.

Wait event

  • Event_type_s (string) — wait class (Client, Lock, IO, LWLock, Timeout, IPC…).
  • Event_s (string) — specific event (ClientRead, DataFileRead, WALWrite…).
  • Calls_d (number) — number of 1-second samples this (query, wait event) was observed.

Collection window

  • Start_time_t / End_time_t (datetime) — bounds of the aggregation interval (default 15 min).

Use cases — troubleshooting

  • Why is a query slow — see whether a Queryid_str_s waits mostly on Lock, IO, LWLock, Client, or IPC.
  • Lock contention — high Event_type_s == "Lock" (e.g. relation, transactionid, tuple) points to blocking/serialization.
  • I/O bottlenecks — IO waits like DataFileRead/WALWrite indicate storage/IOPS limits.
  • Client/network stalls — dominant Client / ClientRead waits mean the app, not the DB, is the bottleneck.
  • Contention on shared structures — LWLock / BufferPin waits (buffer mapping, WAL, etc.).
  • Wait-profile shift detection — compare a query's wait mix across windows to catch new contention.

Q&A

Q&A

Q: Query IDs are not unique here, unlike in QueryStoreRuntime — why?

In PostgreSQLFlexQueryStoreRuntime the grain is (Queryid, Dbid, Userid, window) — one row per query per window.

In PostgreSQLFlexQueryStoreWaitStats the grain is (Queryid, Dbid, Userid, Event_type, Event, window). The same query can be caught waiting on several different events during the same window, each getting its own row.

Window 16:10 → 16:25 for Queryid X:

  Queryid_str_s  | Event_type | Event           | Calls_d
  ───────────────┼────────────┼─────────────────┼────────
  X              | Lock       | transactionid   |  42
  X              | IO         | DataFileRead    |  11
  X              | Client     | ClientRead      |   3

To get the full wait profile of a query in a window, group all rows on (Queryid_str_s, Start_time_t, End_time_t).

Q: How are these logs sampled? What do Start_time_t / End_time_t represent? What does Calls_d mean here?

Wait stats are collected by a background sampler (every ~1 second), not by instrumenting every execution:

Every ~1 second:
  SELECT pid, query_id, wait_event_type, wait_event
  FROM pg_stat_activity
  WHERE wait_event IS NOT NULL
  → for each (query_id, event_type, event) found: counter += 1

At end of window:
  flush counters → one row per (query_id, event_type, event)

Start_time_t / End_time_t are the aggregation window boundaries (same 15-min windows as Runtime) — not the start/end of any individual query execution.

Calls_d here counts samples, not executions. It is how many times the 1-second sampler observed this (query, wait event) pair during the window.

Example. SELECT * FROM mytable is called 3 times during the window — once per incoming HTTP request. "3 times" means 3 separate application round-trips, each triggering its own parse → plan → execute cycle in Postgres. Each call takes 10 seconds and spends 7 of those blocked on a lock.

HTTP request 1 → backend executes SELECT * FROM mytable  (t=0s → t=10s)
  t=0   pg_stat_activity: backend → active (no wait)          not counted
  t=1   pg_stat_activity: backend → Lock/transactionid  ┐
  t=2   pg_stat_activity: backend → Lock/transactionid  │
  t=3   pg_stat_activity: backend → Lock/transactionid  │  7 samples
  t=4   pg_stat_activity: backend → Lock/transactionid  │
  t=5   pg_stat_activity: backend → Lock/transactionid  │
  t=6   pg_stat_activity: backend → Lock/transactionid  │
  t=7   pg_stat_activity: backend → Lock/transactionid  ┘
  t=8   pg_stat_activity: backend → active (lock released)    not counted
  t=9   pg_stat_activity: backend → active                    not counted
  t=10  query finishes, result sent to app

HTTP request 2 → same pattern → 7 more samples
HTTP request 3 → same pattern → 7 more samples

What gets written at the end of the window:

Category       = PostgreSQLFlexQueryStoreWaitStats
Queryid_str_s  = X
Event_type_s   = Lock
Event_s        = transactionid
Calls_d        = 21          ← 7 samples × 3 executions
Start_time_t   = 16:10
End_time_t     = 16:25

In PostgreSQLFlexQueryStoreRuntime for the same window: Calls_d = 3 (3 actual executions). The two Calls_d values mean completely different things.

Q: Does this category record waits only? What about queries running on CPU?

Yes — only waits. When a backend runs on CPU, pg_stat_activity shows wait_event = NULL and the sampler skips it. Consequences:

  • A query that runs in 200 ms pure CPU with no blocking produces zero rows here.
  • A query that runs in 500 ms but spends 480 ms on a lock will appear here.

Wait stats and runtime stats are complementary: Runtime finds expensive queries; Wait Stats explain what they were blocked on. High Total_time_d with no rows here → CPU-bound. Many wait samples → contention-bound.

Q: What is the difference between the Event_type_s categories and their events?

Event_type_s is the broad class of what a backend is waiting for. Event_s is the specific thing within that class.

Client — waiting on the application, not the database engine.

EventMeaning
ClientReadWaiting to receive the next command from the client. The DB is idle; the app is slow to send.
ClientWriteWaiting to send results to the client. The network or app is too slow to consume output.

Dominant Client waits mean the bottleneck is outside Postgres — slow application or network.

Lock — waiting to acquire a heavyweight lock (visible in pg_locks).

EventMeaning
relationWaiting for a table- or index-level lock. Caused by DDL, LOCK TABLE, or conflicting DML.
transactionidWaiting for another transaction to commit or rollback — the most common lock wait in OLTP.
tupleWaiting for a specific row (tuple) lock — multiple transactions targeting the same row.
extendWaiting to extend a relation file. High under heavy INSERT load.
pagePage-level lock (rare; GiST/GIN operations).
advisoryApplication-defined advisory lock (pg_advisory_lock).
objectLock on a non-relation database object (sequence, schema, etc.).

Lock/transactionid is the most common wait in contended OLTP. Cross-reference with PostgreSQLLogs deadlock entries or PostgreSQLFlexSessions to identify the blocking transaction.

LWLock — lightweight locks: Postgres's internal spinlock/mutex system for protecting shared memory structures. Not user-visible SQL locks. Seeing them means internal contention, usually under high concurrency.

EventMeaning
BufferMappingContention on the shared buffer map. Sign of very high concurrency hitting the same data.
BufferContentWaiting to read or write the content of a specific buffer.
WALWrite / WALBufMappingContention on WAL buffer structures. Heavy write workloads.
ProcArrayLockContention on the process array (snapshot generation). Many concurrent transactions.
CLogControlLockContention on the commit log. High transaction rates.
AutovacuumLockContention on autovacuum scheduling structures.
RelationMappingWaiting to read/update the relation-to-file mapping.

LWLock waits usually signal concurrency saturation. Tune max_connections, add connection pooling, or partition hot tables.

IO — waiting for actual I/O operations to complete.

EventMeaning
DataFileReadReading a data block from disk — a shared_buffers miss reaching storage.
DataFileWriteWriting a data block to disk (synchronous eviction write by the backend).
DataFileExtendExtending a data file (INSERT into a full table/index).
WALWriteWriting WAL records to disk. Affects every write transaction.
WALSyncFsyncing WAL for durability. Bottleneck on slow storage.
BufFileRead / BufFileWriteReading/writing temp spill files (sorts, hash joins exceeding work_mem).
DataFileFlush / DataFileSyncFsyncing data files (checkpoint activity).
SLRURead / SLRUWriteAccessing SLRU structures (commit log, subtransaction log, etc.).

IO/DataFileRead → working set doesn't fit in shared_buffers or OS cache. IO/WALWrite or IO/WALSync → storage throughput limit on a write-heavy system.

IPC — waiting for another Postgres process to do something.

EventMeaning
ExecuteGatherWaiting for a parallel worker to produce data.
MessageQueueReceive / MessageQueueSendMessage queues between parallel workers.
ParallelFinishWaiting for all parallel workers to complete.
CheckpointDone / CheckpointStartWaiting for a checkpoint.
LogicalSyncData / LogicalSyncStateLogical replication sync.
SyncRepWaiting for a standby to acknowledge WAL (synchronous replication).
BgWorkerStartup / BgWorkerShutdownWaiting for a background worker (pg_cron, logical apply, etc.).

Timeout — the backend is deliberately sleeping or throttled.

EventMeaning
VacuumDelayAutovacuum throttling itself via vacuum_cost_delay.
PgSleepExplicit pg_sleep() call in application code.
RecoveryApplyDelayConfigured delay before applying WAL on a standby.
CheckpointWriteDelayCheckpoint spreading its writes over time to avoid I/O spikes.

Activity — background server processes in their idle loop. Not relevant to query performance.

Example eventsProcess
BgWriterMainBackground writer, idle
CheckpointerMainCheckpointer, idle
WalWriterMainWAL writer, idle
AutoVacuumMainAutovacuum launcher, idle
WalSenderMainWAL sender waiting for changes to replicate

Extension — wait events registered by Postgres extensions (pg_cron, TimescaleDB, etc.). Specific events depend on installed extensions.