ProcArray

Mechanism

ProcArrayLock protects the shared PGPROC/PGXACT arrays used for snapshots, transaction visibility, and transaction end. Snapshot acquisition and updates to process transaction state must briefly coordinate through it.

Expected behavior

Brief waits are expected on busy OLTP systems that start and finish many transactions.

When to investigate

Persistent contention can accompany extreme connection counts, snapshot-heavy workloads, long transactions, or bursts of transaction completion.

Diagnostic actions

• Measure active backends and transaction age, not only total connections.
• Find long-running and idle-in-transaction sessions that keep visibility horizons old.
• Pool connections, shorten transactions, and avoid synchronized bursts of tiny transactions.

Incident patterns

An application reconnect storm creates thousands of short transactions while a reporting query holds an old snapshot, amplifying ProcArray traffic.

Diagnostic queries

Sessions waiting on LWLock/ProcArray

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