Turn Wait Stats into a Bottleneck Narrative

Tier: Foundation · Workflow area: Performance Diagnostics · ~30 minutes

Every engine keeps score of what sessions wait for: locks, disk, WAL flushes, CPU scheduling. The catch is fluency — hundreds of wait types, engine-specific names, and the eternal trap of benign waits that look scary. This playbook uses AI as the fluent translator and your baseline as the fact-checker.

When to use

• "Everything is slow" with no smoking-gun querywaits tell you whether the instance is starved for I/O, throttled on locks, or drowning in connections before you burn time on query tuning.
• The regular health reviewa monthly wait-profile snapshot, narrated and filed, turns instance health from vibes into a trend line.
• Framing an incident postmortemthe wait profile during the window, explained, is often the spine of the RCA timeline.

When NOT to use

• Without a baselinea wait profile is only meaningful against normal. If you have no baseline, today's job is to capture one, not to diagnose.
• When one query is the known problemgo straight to pb-03. Wait analysis is the wide-angle lens; it wastes time when you already have the close-up.
• To justify hardware purchases from one snapshota single scary I/O number is a conversation starter, not a procurement case. Trends and saturation evidence first.

The recipe

1. 1.

   Capture the wait evidence — cumulative and live. Postgres 14+ gives you both:

   Copy

   -- Live: what is everyone waiting on right now?
   SELECT wait_event_type, wait_event, state, count(*)
   FROM pg_stat_activity
   WHERE state <> 'idle'
   GROUP BY 1, 2, 3
   ORDER BY count(*) DESC;
   
   -- Trend: sample the above every 10s for 10 minutes into a table
   CREATE TABLE IF NOT EXISTS wait_samples AS
   SELECT now() AS sampled_at, wait_event_type, wait_event, count(*)
   FROM pg_stat_activity WHERE state <> 'idle' GROUP BY 2, 3;
   -- (schedule the INSERT variant via cron/pg_cron during the window)
   

   Add the supporting cast: pg_stat_bgwriter / pg_stat_io (Postgres 16+) for I/O context, connection counts, and cache hit ratio. On SQL Server, one call does it: sp_BlitzFirst @ExpertMode = 1 (or sys.dm_os_wait_stats deltas between two snapshots — cumulative-since-restart numbers alone mislead).

2. 2.

   Ask for a narrative with confirming queries — and demand the benign-wait filter.

   Copy

   You are reading PostgreSQL 16 wait statistics. Below: (a) a 10-minute
   wait_event sample series, (b) pg_stat_io summary, (c) connection
   counts, (d) our normal baseline profile from last month.
   
   Produce:
   1. NARRATIVE — the top 3 wait signals in plain language: what each
      wait type means mechanically, and what workload behavior produces it.
   2. BENIGN FILTER — which prominent waits are normal/idle noise for
      this engine version and should be ignored.
   3. DELTA — what is materially different from the baseline I provided.
   4. For each non-benign signal: ONE confirming query I can run to
      test your reading, and what result would confirm vs refute it.
   Rank by likely user impact. No fixes yet — diagnosis only.
   

   The benign filter is where fluency shows: an AI that flags idle replication or checkpoint-writer housekeeping waits as problems is reciting names, not reading your system — push back or re-prompt.

3. 3.

   Run every confirming query before repeating the story. Example — the model says "LWLock:WALWrite dominance suggests commit-rate pressure; confirm with commit counts and WAL volume":

   Copy

   SELECT (SELECT sum(xact_commit) FROM pg_stat_database) AS commits,
          pg_current_wal_lsn(),
          now();
   -- run twice, 60s apart; compute commits/sec and WAL bytes/sec
   

   A confirmed narrative entry looks like:

   Copy

   SIGNAL: WALWrite + IO:WALSync rising through the window
   CONFIRMED: 4,120 commits/sec (baseline ~900); WAL 61 MB/min
     (baseline 14). Matches deploy of order-event writer at 14:02.
   VERDICT: commit-rate pressure, not disk failure — candidate fixes:
     batch the writer, or group commits (commit_delay), test in staging.
   

4. 4.

   Check the counter-evidence. Ask the inverse question — "what in this data does NOT fit your narrative?" — and run those checks too. A story that survives its own counter-evidence is a diagnosis; one that cannot is a first draft.

5. 5.

   Write the one-paragraph verdict against baseline. The deliverable: what changed, what it waits on, what confirmed it, what to test next. File it with the raw exports. If it feeds an incident, it becomes the RCA's technical spine (pb-18); if it names a config suspect, the change flows through pb-08's change-control path.

Make it yours

• SQL Serversp_BlitzFirst for the live pass and wait-delta math done for you; the poison waits (THREADPOOL, RESOURCE_SEMAPHORE) deserve an explicit mention in your prompt so the model checks for them, and SOS_SCHEDULER_YIELD vs CXPACKET/CXCONSUMER readings must come with the benign filter applied.
• Oraclefeed the AWR "Top 10 Foreground Events" section plus load profile; ask the model to read db file sequential read vs scattered read ratios against your baseline AWR, and treat log file sync narratives exactly like the WALWrite example above.
• MySQLwait instrumentation lives in performance_schema.events_waits_summary_global_by_event_name (enable the wait consumers first); the sys schema rollups (sys.wait_classes_global_by_latency) are the friendlier export for an LLM pass.

Quality check

• Deltas, not lifetime counterscumulative waits since restart average away every incident. Two snapshots and subtraction, always.
• The narrative was tested by its confirming queriesevery claim you repeat upward has a query result behind it, filed next to the story.
• Benign waits were filtered with engine-version awarenesswait names change meaning across versions; the model was told your exact version and you sanity-checked its filter against the docs for anything load-bearing.
• The story explains the timelinea real bottleneck narrative names what changed and when. A narrative with no onset moment is a description, not a diagnosis.