Postgres Wait Events: Stop Guessing, Start Counting

TL;DR

• wait_event_type is the bucket (nine of them), wait_event is the specific detail. Read both or you learn nothing.
• Postgres reports state, never accumulated wait duration. One SELECT * FROM pg_stat_activity is a single frame of a movie. Sample it.
• Filter out state='idle' and wait_event_type='Activity' before you count anything, or background processes sitting in their main loop will drown your profile.
• Rough ownership: Lock is your transactions, LWLock is Postgres fighting itself, IO is storage or a missing index, Client is your application, Timeout is usually vacuum throttling.
• The sampler and rollup SQL are below. Copy them, run them for sixty seconds, then argue about shared_buffers.

📖 Read the full guide: Postgres Wait Events: What Every Backend Is Blocked On

The video version

That clip is the three-minute mental model: two columns, nine buckets, sample don't snapshot. This post is the toolkit. Sampler script, rollup query with the averaging math spelled out, a per-event decoder table, the blocking-tree query, and the version gotchas that make ten-year-old blog queries return zero rows on a modern server.

Two columns, nine buckets

The wait columns landed in 9.6, replacing the old boolean waiting flag. wait_event_type tells you the category, wait_event names the exact thing. The documented types, with who owns the problem:

wait_event_type	What it is	Who owns the fix
Activity	Background process idling in its main loop	Nobody. Noise. Exclude it.
BufferPin	Waiting for exclusive access to a pinned buffer	Rare — usually vacuum vs. a long-running scan
Client	Waiting on your application over the socket	Your app team
Extension	Whatever extension author registered it	Read their docs
IO	Reading or writing files	Storage, or a query reading pages it shouldn't need
IPC	Waiting on another Postgres process	Parallel workers, sync replication
Lock	Heavyweight locks	Application-level contention, always
LWLock	Internal shared memory contention	Postgres versus Postgres
Timeout	A deliberate sleep	Vacuum cost delay, mostly

On PG 17 and later, SELECT * FROM pg_wait_events lists every event name and description for the exact version you're running. Stop searching blog posts for what WalSync means. Ask the server.

The trap: NULL does not mean CPU

Every wait-event tutorial I've read says "NULL wait event means the backend is on CPU." The docs are more careful, and so should you be: a NULL wait_event means the backend is not currently waiting on a tracked wait event. That's usually on-CPU. It is not a guarantee. Untracked waits exist, and a backend can be runnable but not scheduled.

Practically: if your profile shows 12 active sessions with wait_event IS NULL on a 4-core box, do not immediately go tune the planner. Look at the OS first. top, vmstat 1, whatever your monitoring gives you. If system CPU is at 30% and load average is 40, you have a scheduler or noisy-neighbour problem, not a query problem. I've watched a team spend a day rewriting a join because the wait profile "showed CPU," when the actual answer was a hypervisor stealing 60% of the cycles.

One snapshot tells you nothing

Postgres core does not accumulate per-event wait time. There is no total_wait_ms column, anywhere. That is the entire reason the pg_wait_sampling extension exists.

So you sample. The arithmetic is simple enough to do in your head. Take N samples over a window. If a given wait_event shows up in M rows across those samples, then M/N is the average number of sessions sitting on that event during the window. That number has a name in other database communities: average active sessions (AAS). Compare it to your core count and you know instantly whether the box is saturated.

Concretely: 600 samples at 100ms intervals is a 60-second window. If Lock:transactionid appears in 5,400 rows, that's 5400/600 = 9 sessions, on average, doing nothing but waiting for other transactions to commit. For a whole minute. That's not a tuning opportunity, that's an outage.

100ms is my default. It's cheap, and it catches anything lasting longer than a tenth of a second, which is everything that matters during an incident. Going to 10ms is fine on a quiet box and starts to cost you on a busy one.

The 10-line sampler you can run right now

CREATE UNLOGGED TABLE IF NOT EXISTS wait_samples (
  ts              timestamptz NOT NULL DEFAULT clock_timestamp(),
  pid             int,
  leader_pid      int,
  state           text,
  wait_event_type text,
  wait_event      text,
  query_id        bigint,
  datname         text,
  usename         text,
  query           text
);

UNLOGGED matters. You are writing a few hundred rows a second during an incident and you do not want that in WAL, competing with the thing you're diagnosing.

Now the collector. Put this in sample.sql:

INSERT INTO wait_samples
  (pid, leader_pid, state, wait_event_type, wait_event,
   query_id, datname, usename, query)
SELECT pid, leader_pid, state, wait_event_type, wait_event,
       query_id, datname, usename, left(query, 120)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()          -- don't sample the sampler
  AND state <> 'idle'                  -- idle backends are not waiting on anything you care about
  AND wait_event_type IS DISTINCT FROM 'Activity';  -- walwriter et al. idling in their main loop

Run it:

psql -d prod -f - <<'EOF'
\i sample.sql
\watch 0.1
EOF

Or, if \watch with a sub-second interval isn't available on your client version, a shell loop:

end=$((SECONDS+60))
while [ $SECONDS -lt $end ]; do
  psql -qAt -d prod -f sample.sql >/dev/null
  sleep 0.1
done

Skip the Activity filter once and you'll see why it's there. WalWriterMain, CheckpointerMain, AutoVacuumMain and LogicalLauncherMain will be present in essentially every sample, and your top wait will be "background workers successfully doing nothing."

Permissions: track_activities must be on (it is by default), and a non-superuser needs pg_monitor or pg_read_all_stats to see other users' query text and wait details. Without it those columns come back NULL and you will misread the entire profile as idle. GRANT pg_monitor TO app_dba; and move on.

The rollup

WITH n AS (SELECT count(DISTINCT ts)::numeric AS samples FROM wait_samples)
SELECT coalesce(wait_event_type, 'CPU/untracked') AS type,
       coalesce(wait_event, '-')                  AS event,
       count(*)                                   AS hits,
       round(count(*) / n.samples, 2)             AS avg_active_sessions,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM wait_samples, n
GROUP BY 1, 2, n.samples
ORDER BY hits DESC
LIMIT 15;

Real output from a payments box last month, 600 samples, 8 vCPU:

      type      |      event       | hits | avg_active_sessions | pct
----------------+------------------+------+---------------------+------
 Lock           | transactionid    | 5412 |                9.02 | 63.1
 CPU/untracked  | -                | 1188 |                1.98 | 13.9
 Client         | ClientRead       |  901 |                1.50 | 10.5
 LWLock         | LockManager      |  402 |                0.67 |  4.7
 IO             | DataFileRead     |  331 |                0.55 |  3.9
 Lock           | tuple            |  208 |                0.35 |  2.4
 IO             | WALSync          |   77 |                0.13 |  0.9
 Timeout        | VacuumDelay      |   41 |                0.07 |  0.5
(8 rows)

Read it out loud. Eight cores, 14.3 AAS total, and 9 of those sessions are parked on Lock:transactionid. Nobody is CPU-bound: 1.98 AAS of CPU on an 8-core box is 25% busy. This database is not slow. It is blocked. Every millisecond spent on work_mem or random_page_cost here is wasted. Go find the transaction everyone is queued behind.

Decoder ring

Event	What it means	First thing to check
Lock:transactionid	Waiting for another txn to commit or roll back (row conflict)	pg_blocking_pids, transaction length
Lock:relation	Table/index-level lock. Someone ran DDL, VACUUM FULL, or REINDEX	Who holds the AccessExclusiveLock, right now
Lock:tuple	Queued behind other waiters for one hot row	Counter/sequence-in-a-table patterns
LWLock:WALInsert	Contention inserting into WAL buffers under write load	wal_buffers, commit rate, synchronous_commit
LWLock:BufferMapping	Shared buffer lookup table contention, high eviction churn	Working set vs shared_buffers, seq scans
LWLock:BufferContent	Multiple backends fighting over one page's content	Hot index root/leaf pages, update patterns
LWLock:LockManager	Fast-path lock slots exhausted, locks spilled to shared manager	Partition count per query, prepared statements
IO:DataFileRead	Buffer miss, reading a relation page from the filesystem	EXPLAIN (ANALYZE, BUFFERS), missing index
IO:DataFileWrite	Backend writing dirty pages itself	Checkpoint settings, bgwriter, write burst
IO:WALWrite / IO:WALSync	Commit-path WAL write and fsync	Commit rate, fsync latency, storage
IO:BufFileRead / IO:BufFileWrite	Temp file spill from sorts, hashes, materialize	work_mem, log_temp_files = 0
Client:ClientRead	Waiting for the client to send the next command	Your app. Round trips, pooling
Client:ClientWrite	Client isn't reading results fast enough	Result set size, network, cursor use
IPC:SyncRep	Waiting for a synchronous standby to acknowledge commit	Replication RTT, synchronous_standby_names
Timeout:VacuumDelay	Cost-based vacuum throttling	vacuum_cost_delay, vacuum_cost_limit
BufferPin	Waiting for exclusive access to a pinned buffer	Long-running scans overlapping vacuum

Lock waits: find the blocker, not the victim

Your profile says Lock:transactionid. Every session you look at is a victim. You need the root of the tree.

WITH RECURSIVE tree AS (
  SELECT a.pid, a.pid AS root, 0 AS depth,
         a.wait_event_type, a.wait_event, a.state,
         a.xact_start, left(a.query, 80) AS query
  FROM pg_stat_activity a
  WHERE cardinality(pg_blocking_pids(a.pid)) = 0
    AND EXISTS (SELECT 1 FROM pg_stat_activity b
                WHERE a.pid = ANY (pg_blocking_pids(b.pid)))
  UNION ALL
  SELECT c.pid, t.root, t.depth + 1,
         c.wait_event_type, c.wait_event, c.state,
         c.xact_start, left(c.query, 80)
  FROM pg_stat_activity c
  JOIN tree t ON t.pid = ANY (pg_blocking_pids(c.pid))
)
SELECT repeat('  ', depth) || pid AS pid_tree, depth, state,
       wait_event_type, wait_event,
       now() - xact_start AS xact_age, query
FROM tree
ORDER BY root, depth;

pg_blocking_pids() arrived in 9.6 and is the supported way to do this. Stop hand-joining pg_locks to itself; that query is wrong in edge cases and you will not notice.

The depth-0 row is your culprit. Nine times out of ten it's in state = 'idle in transaction' with an xact_age of four minutes, because someone opened a transaction, called an external API, and the API is timing out.

Fixes, in order of how much I trust them: shorten transactions (do the API call outside the transaction), batch updates in a consistent key order to avoid pile-ups, set lock_timeout on migration sessions so DDL fails fast instead of freezing the table, and turn on log_lock_waits. That last one is off by default and logs any wait longer than deadlock_timeout (1s default). Turn it on everywhere. It costs nothing and it gives you the post-mortem you'll want at 3am.

Lock:relation is a different animal. That's table-level, which means DDL, VACUUM FULL, or an unlucky ALTER TABLE sitting behind a long read and blocking everything behind it. Lock:tuple means a queue on one specific row, which almost always means a counter table.

LWLock waits: when Postgres fights itself

LWLock:WALInsert is a write-throughput ceiling. Backends are queuing to copy their WAL records into shared buffers. Look at wal_buffers, at whether you're committing one tiny row at a time from a hundred connections, and at whether synchronous_commit = off is acceptable for some of your workload (it is, for anything you'd be willing to lose the last 200ms of).

LWLock:BufferMapping and BufferContent mean shared buffer churn or a genuinely hot page. Bigger shared_buffers sometimes helps the first, never helps the second.

LWLock:LockManager is the one that catches people out. Each backend gets a fixed number of fast-path lock slots (16 per backend before PG 18). Blow past that and every lock goes through the shared lock manager. The classic trigger is a query touching a partitioned table with 200 partitions: even with pruning, planning can grab locks on more relations than you'd think. PG 18 scales those slots with max_locks_per_transaction, which helps, but the real fix is fewer partitions per query.

Here's my position: LWLock waits are almost never fixed by a config knob alone. They're a symptom of a workload shape. Change the shape.

IO waits: disk, or a missing index pretending to be disk

IO:DataFileRead means a page wasn't in shared_buffers. That's all it means. The read may well have been served from the OS page cache in 20 microseconds. I have watched people buy faster NVMe because of a DataFileRead-heavy profile, when the actual problem was a sequential scan on a 40GB table that needed one index.

Cross-check before you conclude anything. Turn on track_io_timing (off by default) so EXPLAIN (ANALYZE, BUFFERS) and pg_stat_statements report real I/O time. On PG 16+, pg_stat_io breaks reads and writes down by backend type and context, which tells you whether it's client backends, autovacuum, or the checkpointer. If EXPLAIN (ANALYZE, BUFFERS) shows a million shared reads and near-zero I/O time, your storage is fine and your query plan is not.

IO:BufFileRead / BufFileWrite means temp files: sorts, hashes, or materialization spilling past work_mem. Set log_temp_files = 0, find the queries, then decide whether to raise work_mem (per-session, not globally) or fix the plan.

IO:WALWrite and IO:WALSync are commit-bound. Query tuning won't touch them. Look at fsync latency on the WAL device and at your commit rate.

Client waits are almost always your application's fault

If your profile is 70% Client:ClientRead, no amount of shared_buffers tuning will save you. Go fix your ORM.

Client:ClientRead means the backend has finished and is waiting for the client to say something. Dominant ClientRead means chatty round trips, no connection pooler, or sessions holding a transaction open while doing nothing. That last one is the actual bug, and this query separates it from the harmless case:

SELECT state,
       count(*) FILTER (WHERE wait_event = 'ClientRead') AS client_read,
       max(now() - xact_start)                           AS oldest_xact,
       max(now() - state_change)                         AS oldest_idle
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY 2 DESC;

        state        | client_read |   oldest_xact   |   oldest_idle
---------------------+-------------+-----------------+-----------------
 idle                |         214 |                 | 00:41:12.339
 idle in transaction |          17 | 00:06:48.771    | 00:06:44.102
 active              |           3 | 00:00:00.412    | 00:00:00.400

214 plain-idle sessions on ClientRead is a pool at rest. Ignore them, which is exactly what the state <> 'idle' filter in the sampler does. The 17 sessions idle in transaction for nearly seven minutes are holding locks and pinning old snapshots so vacuum can't clean up. Set idle_in_transaction_session_timeout and make the application fail loudly instead of quietly bloating your tables.

Conflating a plain-idle pooled connection with an idle-in-transaction one is why people panic-tune connection pools that were never the problem. The state column, not the wait event alone, is what tells them apart.

Going continuous

Manual sampling is an incident tool. For trending, either install pg_wait_sampling, which runs a background worker doing exactly this and exposes both a live profile and per-process history without you polling, or keep your own table and store query_id with every sample.

query_id landed in pg_stat_activity in PG 14 and is populated when compute_query_id is on. It's already in my sampler DDL above. With it you can answer the question that actually ends arguments:

WITH n AS (SELECT count(DISTINCT ts)::numeric AS samples FROM wait_samples)
SELECT s.queryid, round(count(*) / n.samples, 2) AS aas_lock,
       left(s.query, 70) AS query
FROM wait_samples w, n
JOIN pg_stat_statements s ON s.queryid = w.query_id
WHERE w.wait_event_type = 'Lock'
GROUP BY s.queryid, s.query, n.samples
ORDER BY 2 DESC LIMIT 5;

"Which query burns the most Lock:transactionid time" now has a numeric answer instead of a hunch.

Disclosure: I write for pgdba, and we run MyDBA, which does a free health check if you'd rather have someone else read the profile. The queries above work fine without it.

Version gotchas that will bite you

PG 13 standardized LWLock event names (buffer_mapping became BufferMapping) and merged LWLockNamed and LWLockTranche into a single LWLock type. Any monitoring query you copied from a 2018 blog post will silently return zero rows on a modern server — which is worse than an error, because it reads as "no contention" instead of "your query is stale." PG 13 also added leader_pid, so parallel workers stop looking like mystery sessions with no query text — join on it to roll workers back up to their query. PG 16 reworked relation extension locking. PG 17 gave you pg_wait_events so you can look events up locally.

And again: pg_monitor or pg_read_all_stats, or you're profiling NULLs.

60-second triage runbook

1. Create the unlogged table, start the sampler at \watch 0.1, wait 60 seconds.
2. Run the rollup. Sum the avg_active_sessions column.
3. Compare that total to your core count. Above it means saturation, below it means you have a latency problem, not a capacity one.
4. Dominant Lock → run the blocking tree, kill or fix the depth-0 session, turn on log_lock_waits.
5. Dominant Client:ClientRead → check the idle-in-transaction breakdown, set idle_in_transaction_session_timeout, talk to the app team.
6. Dominant IO → track_io_timing = on, check pg_stat_io, then EXPLAIN (ANALYZE, BUFFERS) the top query before touching storage.
7. Dominant LWLock → identify the specific lock, look at workload shape (partition count, commit rate, buffer churn) before any config change.
8. Dominant NULL → verify against OS CPU before you blame the planner.
9. DROP TABLE wait_samples; when you're done. It's unlogged, but it's still disk.

Nine buckets, one sampler, one rollup. Write down the numbers. Not the vibe. Stop guessing, count something.