WALInsert

Mechanism

A backend must hold one of the WAL insertion locks while reserving and copying a WAL record into shared WAL buffers. High concurrent WAL producers can queue when insertion critical sections lengthen.

Expected behavior

Transient waits are normal during concurrent writes and commit bursts.

When to investigate

A recurring foreground pile-up indicates WAL insertion has become a serialization point, often with very high write concurrency, full-page images, or slow buffer turnover.

Diagnostic actions

• Rank statements by WAL generation and call rate.
• Check checkpoint/full-page-image timing and concurrent writer count.
• Batch tiny writes, reduce needless index churn, and address WAL write latency.

Incident patterns

A fan-out job issues many single-row commits immediately after a checkpoint; full-page images and writer concurrency turn WAL insertion into the bottleneck.

Diagnostic queries

Sessions waiting on LWLock/WALInsert

SELECT pid, backend_type, usename, datname, application_name,
       state, now() - query_start AS query_age,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
  AND wait_event = 'WALInsert'
ORDER BY query_age DESC NULLS LAST;

Current LWLock cohort

SELECT wait_event, count(*) AS waiting_sessions,
       count(*) FILTER (WHERE state = 'active') AS active_waiters,
       max(now() - query_start) AS oldest_query
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
GROUP BY wait_event
ORDER BY waiting_sessions DESC, wait_event;

Lock and relation context for these sessions

SELECT a.pid, l.locktype, l.mode, l.granted, l.fastpath,
       d.datname, n.nspname, c.relname,
       l.page, l.tuple, l.virtualxid, l.transactionid,
       l.classid, l.objid, l.objsubid
FROM pg_stat_activity AS a
LEFT JOIN pg_locks AS l ON l.pid = a.pid
LEFT JOIN pg_database AS d ON d.oid = l.database
LEFT JOIN pg_class AS c
  ON c.oid = l.relation
 AND l.database = (
       SELECT oid FROM pg_database WHERE datname = current_database()
     )
LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE a.wait_event_type = 'LWLock'
  AND a.wait_event = 'WALInsert'
ORDER BY a.pid, l.granted, l.locktype, l.mode;