Wait Events, Locks, I/O Visibility, WAL/Replication Stats, and Incident Diagnosis

Learning outcomes

A ServiceHub incident page says “database CPU 90%.” Another says “storage slow.” A third shows replication lag. PostgreSQL waits and cumulative I/O/WAL statistics help separate these hypotheses, but no database view can replace host/storage metrics. This lesson follows one incident as a timeline: live backend state → blockers/waits → per-backend and cluster I/O → WAL → replication context.

1. Wait events answer “what resource is this backend waiting for now?”

SELECT pid,       application_name,       state,       wait_event_type,       wait_event,       query_id,       now() - query_start AS query_age,       left(query,100) AS query_excerptFROM pg_stat_activityWHERE backend_type = 'client backend'ORDER BY state, query_start NULLS LAST;

An active backend with a non-NULL wait event can be blocked/waiting while a statement is active. An active backend with NULL wait event is currently not waiting on an instrumented PostgreSQL wait; it may be executing CPU work or runnable, but host CPU evidence is required before declaring it CPU-bound.

2. Translate names through pg_wait_events

SELECT type, name, descriptionFROM pg_wait_eventsWHERE type IN ('Lock','IO','Client','IPC')ORDER BY type, name;

Wait-event names change and expand as PostgreSQL adds instrumentation. Using the server's pg_wait_events view avoids hard-coding descriptions in a monitoring agent. Alert categories should generally be stable symptom classes, not every individual event name.

3. Incident branch A: lock wait

Reuse Lesson 1's blocker/waiter setup. The waiter should show a lock wait and pg_blocking_pids() should point to the blocker.

SELECT a.pid,       a.application_name,       a.state,       a.wait_event_type,       a.wait_event,       pg_blocking_pids(a.pid) AS blocking_pidsFROM pg_stat_activity AS aWHERE a.application_name IN ('ch21_blocker','ch21_waiter');SELECT pid, locktype, mode, granted,       relation::regclass AS relation,       transactionidFROM pg_locksWHERE pid IN (  SELECT pid  FROM pg_stat_activity  WHERE application_name IN ('ch21_blocker','ch21_waiter'))ORDER BY pid, granted, locktype;

When the wait class is Lock and a blocker is known, storage tuning is not the first intervention. Resolve the transaction/blocking cause.

4. Incident branch B: I/O evidence in PostgreSQL 18

SELECT name, setting, sourceFROM pg_settingsWHERE name IN ('track_io_timing','track_wal_io_timing')ORDER BY name;

Operation counts/bytes are available without timing, while wait-time columns are zero unless the corresponding timing option is enabled. Timing repeatedly reads the system clock and can add overhead, so measure that tradeoff for your platform rather than enabling it by reflex.

SELECT backend_type,       object,       context,       reads,       read_bytes,       read_time,       writes,       write_bytes,       write_time,       writebacks,       extends,       fsyncs,       fsync_time,       stats_resetFROM pg_stat_ioWHERE object IN ('relation','wal')ORDER BY object, backend_type, context;

PostgreSQL 18 reports read/write bytes directly and includes WAL I/O rows. The view is cluster-wide by backend type/object/context. It does not tell which SQL statement caused every byte and cannot distinguish physical storage reads from kernel-page-cache service.

5. PostgreSQL 18 adds per-backend I/O statistics

SELECT a.pid,       a.application_name,       io.backend_type,       io.object,       io.context,       io.reads,       io.read_bytes,       io.read_time,       io.writes,       io.write_bytes,       io.write_timeFROM pg_stat_activity AS aCROSS JOIN LATERAL pg_stat_get_backend_io(a.pid) AS ioWHERE a.application_name = 'ch21_io_probe'ORDER BY io.object, io.context;

Use a named probe session that runs a large scan or COPY, then inspect its backend I/O. This narrows I/O attribution compared with cluster-wide pg_stat_io, but it is still backend cumulative data—not one EXPLAIN node's accounting.

SET application_name = 'ch21_io_probe';SELECT count(*), avg(length(payload))FROM app.ch21_workloadWHERE payload LIKE '%ZZ%';

As with other backend-level statistics, observing another user's backend is privilege-sensitive; use a narrowly scoped monitoring identity such as one granted pg_read_all_stats rather than making the monitoring application a superuser.

6. WAL statistics: use the PostgreSQL 18 columns

SELECT wal_records,       wal_fpi,       wal_bytes,       wal_buffers_full,       stats_resetFROM pg_stat_wal;

PostgreSQL 18 moved WAL read/write/fsync I/O visibility into pg_stat_io; older columns such as WAL write/sync timing in pg_stat_wal are not the PostgreSQL 18 contract. Use wal_bytes/wal_fpi as generated-WAL volume context and the object='wal' rows in pg_stat_io for I/O activity.

7. Generate controlled WAL and compare deltas

UPDATE app.ch21_workloadSET status = CASE status               WHEN 'open' THEN 'queued'               ELSE status             ENDWHERE tenant_id BETWEEN 1 AND 5;SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_resetFROM pg_stat_wal;

The second snapshot should normally show larger cumulative WAL counters, but exact deltas depend on tuple/index changes, full-page images, checkpoint timing, and other concurrent sessions. To attribute a statement shape more directly, Lesson 3's pg_stat_statements.wal_bytes and EXPLAIN WAL are better tools.

8. Replication views are conditional evidence

SELECT application_name,       client_addr,       state,       sync_state,       sent_lsn,       write_lsn,       flush_lsn,       replay_lsn,       write_lag,       flush_lag,       replay_lagFROM pg_stat_replicationORDER BY application_name;

On a standalone lab this returns zero rows; that is expected and means no connected physical standby WAL senders are represented. In an HA topology, LSN positions let you compute byte gaps and the lag intervals describe recent acknowledgement delay. They are not a prediction of “seconds until the replica catches up.”

SELECT status,       sender_host,       sender_port,       slot_name,       written_lsn,       flushed_lsn,       latest_end_lsn,       last_msg_receipt_time,       latest_end_timeFROM pg_stat_wal_receiver;

9. Do not confuse Client waits with database saturation

Backends can wait for clients to send more data or receive results. A Client wait may indicate a slow consumer, network backpressure, an idle session, or normal protocol behavior. Killing these sessions because “wait time is high” can punish healthy applications.

10. Wrong incident response: terminate every active backend

11. Incident worksheet

Authoritative references

Statistics and logging fields evolve across PostgreSQL majors. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Monitoring / pg_stat_io, pg_stat_wal, replication
• PostgreSQL 18 — pg_wait_events
• PostgreSQL 18 — Release 18 Observability Changes
• PostgreSQL 18 — Run-time Statistics / I/O Timing
• PostgreSQL 18 — Streaming Replication