XactSLRU

Mechanism

The transaction-status SLRU lock protects pg_xact cache metadata and pages while PostgreSQL reads or updates transaction commit status. Visibility checks for uncached or old XIDs can bring this path into the foreground.

Expected behavior

Short waits accompany transaction completion and occasional pg_xact cache misses.

When to investigate

Persistent waits can indicate transaction-status cache churn, access to very old tuples, or storage latency on the pg_xact path.

Diagnostic actions

• Check old transactions and vacuum/freeze health.
• Correlate with XactBuffer and pg_xact I/O waits.
• Remove visibility-horizon blockers and restore vacuum progress before changing SLRU-related capacity.

Incident patterns

A long-lived snapshot prevents cleanup while queries revisit cold, old tuple versions, creating pg_xact cache churn and XactSLRU contention.

Diagnostic queries

Sessions waiting on LWLock/XactSLRU

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