WALBufMapping

Mechanism

WALBufMappingLock coordinates the mapping between WAL page numbers and the finite wal_buffers slots. A backend needs it when advancing to or replacing a WAL buffer page.

Expected behavior

Short waits appear as WAL generation advances through buffer pages.

When to investigate

Sustained contention suggests WAL generation is turning over buffers faster than the write path advances, often during write bursts or checkpoints.

Diagnostic actions

• Correlate with WAL bytes, WAL writes, and checkpoint timing.
• Check whether wal_buffers is repeatedly exhausted during bursts.
• Smooth write bursts and fix WAL device latency before tuning buffer size.

Incident patterns

A bulk load saturates WAL generation while the WAL device stalls, forcing frequent WAL buffer remapping.

Diagnostic queries

Sessions waiting on LWLock/WALBufMapping

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 = 'WALBufMapping'
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 = 'WALBufMapping'
ORDER BY a.pid, l.granted, l.locktype, l.mode;