Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

LWLock waits

Contention on PostgreSQL’s internal shared-memory data structures.

LWLock means a backend could not immediately acquire a lightweight lock that protects an internal shared-memory structure. It does not identify a SQL row or table lock, and pg_locks usually cannot name its owner.

Operator rulerepeat samples → isolate one hot tranche → correlate with workload

How to read this class

A single sample is ordinary scheduler noise. Treat the event as contention only when the same LWLock name recurs across consecutive samples and affects foreground sessions whose latency has increased.

  • Normal: brief appearances during WAL generation, snapshot acquisition, buffer lookup, vacuum, or checkpoint work.
  • Watch: the same event occupies at least 10% of active foreground backends in three consecutive 1-second snapshots.
  • Urgent: at least 25% of active foreground backends pile onto one event, waits persist beyond 5 seconds, or throughput collapses at the same time.

The percentages are operational triage thresholds, not PostgreSQL guarantees. Compare with the cluster’s own baseline and exclude background processes whose main loop is expected to wait.

Ten events worth recognizing

Event Protected resource Typical story
BufferContent Contents of one shared buffer Many sessions touch the same hot page
BufferMapping Buffer-table mapping partitions Working-set churn or broad concurrent scans
LockManager Heavyweight lock manager state Large lock fan-out, DDL, or lock storms
ProcArray Shared process/transaction array Snapshot and transaction-ID pressure
WALBufMapping WAL buffer page mapping WAL buffers turn over under write pressure
WALInsert WAL insertion state Many writers serialize while inserting WAL
WALWrite WAL buffer write coordination WAL flush/write path cannot keep up
XactSLRU Transaction-status SLRU pg_xact cache churn or old visibility checks
MultiXactMemberSLRU Multixact-member SLRU Heavy row-locking and multixact churn
SyncRep Synchronous replication wait queues Commit acknowledgements and sender state contend

Common misreads

  1. “LWLock means a leaked lock.” No. It is a short internal critical section; sustained recurrence is the signal.
  2. “The query shown owns the lock.” pg_stat_activity.query belongs to the waiter. The holder can be another backend inside a different source path.
  3. “More CPU fixes it.” Extra concurrency can intensify a shared-memory hotspot. First identify the protected resource and workload shape.
  4. “pg_locks will reveal the blocker.” It covers heavyweight and predicate locks, not general LWLock ownership.

Start with BufferContent for a page-level hotspot and WALInsert for a write-heavy cluster.

1 - LWLock: AddinShmemInit

Waiting to manage an extension’s space allocation in shared memory
PostgreSQL wait event dossier
ClassLWLock EventAddinShmemInit VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to manage an extension’s space allocation in shared memory

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event_names.txt:328. The instrumented operation is: Waiting to manage an extension’s space allocation in shared memory. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as AddinShmemInit. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/AddinShmemInit 
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 = 'AddinShmemInit'
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 = 'AddinShmemInit'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

2 - LWLock: AioUringCompletion

Waiting for another process to complete IO via io_uring
PostgreSQL wait event dossier
ClassLWLock EventAioUringCompletion VersionsPG 18 Evidence3 source location(s)

Official description

Waiting for another process to complete IO via io_uring

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — — ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:180. The instrumented operation is: Waiting for another process to complete IO via io_uring. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as AioUringCompletion. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/AioUringCompletion 
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 = 'AioUringCompletion'
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 = 'AioUringCompletion'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

3 - LWLock: AioWorkerSubmissionQueue

Waiting to access AIO worker submission queue
PostgreSQL wait event dossier
ClassLWLock EventAioWorkerSubmissionQueue VersionsPG 18 Evidence3 source location(s)

Official description

Waiting to access AIO worker submission queue

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — — ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/aio/method_worker.c:253. The instrumented operation is: Waiting to access AIO worker submission queue. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as AioWorkerSubmissionQueue. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/AioWorkerSubmissionQueue 
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 = 'AioWorkerSubmissionQueue'
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 = 'AioWorkerSubmissionQueue'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

4 - LWLock: AutoFile

Waiting to update the postgresql.auto.conf file
PostgreSQL wait event dossier
ClassLWLock EventAutoFile VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to update the postgresql.auto.conf file

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event_names.txt:340. The instrumented operation is: Waiting to update the postgresql.auto.conf file. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as AutoFile. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/AutoFile 
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 = 'AutoFile'
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 = 'AutoFile'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

5 - LWLock: Autovacuum

Waiting to read or update the current state of autovacuum workers
PostgreSQL wait event dossier
ClassLWLock EventAutovacuum VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update the current state of autovacuum workers

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/postmaster/autovacuum.c:611. The instrumented operation is: Waiting to read or update the current state of autovacuum workers. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as Autovacuum. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/Autovacuum 
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 = 'Autovacuum'
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 = 'Autovacuum'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

6 - LWLock: AutovacuumSchedule

Waiting to ensure that a table selected for autovacuum still needs vacuuming
PostgreSQL wait event dossier
ClassLWLock EventAutovacuumSchedule VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to ensure that a table selected for autovacuum still needs vacuuming

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/postmaster/autovacuum.c:2337. The instrumented operation is: Waiting to ensure that a table selected for autovacuum still needs vacuuming. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as AutovacuumSchedule. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/AutovacuumSchedule 
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 = 'AutovacuumSchedule'
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 = 'AutovacuumSchedule'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

7 - LWLock: BackgroundWorker

Waiting to read or update background worker state
PostgreSQL wait event dossier
ClassLWLock EventBackgroundWorker VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update background worker state

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/postmaster/bgworker.c:1070. The instrumented operation is: Waiting to read or update background worker state. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as BackgroundWorker. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/BackgroundWorker 
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 = 'BackgroundWorker'
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 = 'BackgroundWorker'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

8 - LWLock: BtreeVacuum

Waiting to read or update vacuum-related information for a B-tree index
PostgreSQL wait event dossier
ClassLWLock EventBtreeVacuum VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update vacuum-related information for a B-tree index

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/nbtree/nbtutils.c:3519. The instrumented operation is: Waiting to read or update vacuum-related information for a B-tree index. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as BtreeVacuum. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/BtreeVacuum 
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 = 'BtreeVacuum'
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 = 'BtreeVacuum'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

9 - LWLock: BufferContent

Waiting to access a data page in memory
PostgreSQL wait event dossier
ClassLWLock EventBufferContent VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access a data page in memory

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

A backend has found the shared buffer it needs but cannot yet take that buffer descriptor’s content lock. Heap and index code acquire this lock before reading or changing the in-memory page, so many workers touching one page can serialize here.

Normal or trouble?

  • Normal: Short samples are routine while concurrent readers and writers touch shared pages.
  • Investigate: Repeated samples on many foreground sessions usually point to a hot heap/index page, a right-growing index, or concurrent maintenance on the same blocks.

Diagnostic SQL

Sessions waiting on LWLock/BufferContent 
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 = 'BufferContent'
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 = 'BufferContent'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Find the relations and statements shared by the waiters.
  2. Use page/index evidence to confirm a hotspot; do not infer one from the wait name alone.
  3. Spread hot keys, batch writes, or move maintenance away from the peak before considering capacity changes.

Source evidence

Related controls and signals

Typical incident pattern

A monotonically increasing key concentrates concurrent B-tree inserts on the rightmost leaf page; BufferContent rises with insert latency.

10 - LWLock: BufferMapping

Waiting to associate a data block with a buffer in the buffer pool
PostgreSQL wait event dossier
ClassLWLock EventBufferMapping VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to associate a data block with a buffer in the buffer pool

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

The buffer manager hashes a relation/fork/block tag into a partition protected by BufferMappingLock. Lookup, insertion, eviction, and tag reassignment briefly take that partition lock; broad concurrent misses or buffer churn increase collisions.

Normal or trouble?

  • Normal: Brief waits occur when pages enter or leave shared buffers.
  • Investigate: Sustained recurrence suggests a working set that churns through shared buffers, many parallel scans, or concentrated access mapping into a few partitions.

Diagnostic SQL

Sessions waiting on LWLock/BufferMapping 
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 = 'BufferMapping'
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 = 'BufferMapping'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Correlate with buffer hit ratio and read volume by database and statement.
  2. Look for a new scan, undersized cache, or concurrency jump.
  3. Reduce concurrent scan fan-out or fix the access path before simply enlarging shared_buffers.

Source evidence

Related controls and signals

Typical incident pattern

A plan regression launches many concurrent large scans; buffer-table partitions become hot while useful pages churn out of cache.

11 - LWLock: Checkpoint

Waiting to begin a checkpoint.
PostgreSQL wait event dossier
ClassLWLock EventCheckpoint VersionsPG 13 Evidence3 source location(s)

Official description

Waiting to begin a checkpoint.

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ — — — — —

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:765 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/xlog.c:8957. The instrumented operation is: Waiting to begin a checkpoint. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as Checkpoint. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/Checkpoint 
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 = 'Checkpoint'
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 = 'Checkpoint'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

12 - LWLock: CheckpointerComm

Waiting to manage fsync requests
PostgreSQL wait event dossier
ClassLWLock EventCheckpointerComm VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to manage fsync requests

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/postmaster/checkpointer.c:1164. The instrumented operation is: Waiting to manage fsync requests. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as CheckpointerComm. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/CheckpointerComm 
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 = 'CheckpointerComm'
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 = 'CheckpointerComm'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

13 - LWLock: CommitTs

Waiting to read or update the last value set for a transaction commit timestamp
PostgreSQL wait event dossier
ClassLWLock EventCommitTs VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update the last value set for a transaction commit timestamp

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/commit_ts.c:206. The instrumented operation is: Waiting to read or update the last value set for a transaction commit timestamp. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as CommitTs. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/CommitTs 
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 = 'CommitTs'
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 = 'CommitTs'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

14 - LWLock: CommitTsBuffer

Waiting for I/O on a commit timestamp SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventCommitTsBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a commit timestamp SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:141. The instrumented operation is: Waiting for I/O on a commit timestamp SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as CommitTsBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/CommitTsBuffer 
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 = 'CommitTsBuffer'
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 = 'CommitTsBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

15 - LWLock: CommitTsSLRU

Waiting to access the commit timestamp SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventCommitTsSLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the commit timestamp SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:172. The instrumented operation is: Waiting to access the commit timestamp SLRU cache. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as CommitTsSLRU. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/CommitTsSLRU 
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 = 'CommitTsSLRU'
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 = 'CommitTsSLRU'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

16 - LWLock: ControlFile

Waiting to read or update the pg_control file or create a new WAL file
PostgreSQL wait event dossier
ClassLWLock EventControlFile VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update the pg_control file or create a new WAL file

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/xlog.c:2723. The instrumented operation is: Waiting to read or update the pg_control file or create a new WAL file. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ControlFile. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ControlFile 
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 = 'ControlFile'
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 = 'ControlFile'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

17 - LWLock: DSMRegistry

Waiting to read or update the dynamic shared memory registry
PostgreSQL wait event dossier
ClassLWLock EventDSMRegistry VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to read or update the dynamic shared memory registry

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/ipc/dsm_registry.c:98. The instrumented operation is: Waiting to read or update the dynamic shared memory registry. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as DSMRegistry. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/DSMRegistry 
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 = 'DSMRegistry'
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 = 'DSMRegistry'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

18 - LWLock: DSMRegistryDSA

Waiting to access dynamic shared memory registry’s dynamic shared memory allocator
PostgreSQL wait event dossier
ClassLWLock EventDSMRegistryDSA VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to access dynamic shared memory registry’s dynamic shared memory allocator

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:170. The instrumented operation is: Waiting to access dynamic shared memory registry’s dynamic shared memory allocator. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as DSMRegistryDSA. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/DSMRegistryDSA 
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 = 'DSMRegistryDSA'
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 = 'DSMRegistryDSA'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

19 - LWLock: DSMRegistryHash

Waiting to access dynamic shared memory registry’s shared hash table
PostgreSQL wait event dossier
ClassLWLock EventDSMRegistryHash VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to access dynamic shared memory registry’s shared hash table

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:171. The instrumented operation is: Waiting to access dynamic shared memory registry’s shared hash table. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as DSMRegistryHash. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/DSMRegistryHash 
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 = 'DSMRegistryHash'
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 = 'DSMRegistryHash'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

20 - LWLock: DynamicSharedMemoryControl

Waiting to read or update dynamic shared memory allocation information
PostgreSQL wait event dossier
ClassLWLock EventDynamicSharedMemoryControl VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update dynamic shared memory allocation information

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/ipc/dsm.c:549. The instrumented operation is: Waiting to read or update dynamic shared memory allocation information. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as DynamicSharedMemoryControl. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/DynamicSharedMemoryControl 
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 = 'DynamicSharedMemoryControl'
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 = 'DynamicSharedMemoryControl'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

21 - LWLock: InjectionPoint

Waiting to read or update information related to injection points
PostgreSQL wait event dossier
ClassLWLock EventInjectionPoint VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to read or update information related to injection points

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event_names.txt:353. The instrumented operation is: Waiting to read or update information related to injection points. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as InjectionPoint. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/InjectionPoint 
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 = 'InjectionPoint'
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 = 'InjectionPoint'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

22 - LWLock: LockFastPath

Waiting to read or update a process’ fast-path lock information
PostgreSQL wait event dossier
ClassLWLock EventLockFastPath VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update a process’ fast-path lock information

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:151. The instrumented operation is: Waiting to read or update a process’ fast-path lock information. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as LockFastPath. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/LockFastPath 
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 = 'LockFastPath'
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 = 'LockFastPath'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

23 - LWLock: LockManager

Waiting to read or update information about “heavyweight” locks
PostgreSQL wait event dossier
ClassLWLock EventLockManager VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update information about “heavyweight” locks

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

Heavyweight lock bookkeeping is stored in shared hash tables partitioned by LockHashPartitionLock. Acquiring, granting, releasing, or inspecting many heavyweight locks can contend on the partition even when no SQL-level lock conflict exists.

Normal or trouble?

  • Normal: Small bursts accompany ordinary relation and transaction lock traffic.
  • Investigate: A sustained share often follows lock fan-out: very large transactions, many partitions, DDL churn, or thousands of waiting lock requests.

Diagnostic SQL

Sessions waiting on LWLock/LockManager 
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 = 'LockManager'
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 = 'LockManager'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Count pg_locks rows per PID and inspect the blocking tree.
  2. Identify statements touching many relations or partitions.
  3. Shorten transactions and reduce lock fan-out; raise max_locks_per_transaction only for genuine capacity errors.

Source evidence

Related controls and signals

Typical incident pattern

A deployment runs DDL across thousands of partitions while application sessions acquire relation locks, creating internal lock-table contention before a clear blocker is visible.

24 - LWLock: LogicalRepLauncherDSA

Waiting to access logical replication launcher’s dynamic shared memory allocator
PostgreSQL wait event dossier
ClassLWLock EventLogicalRepLauncherDSA VersionsPG 16-18 Evidence3 source location(s)

Official description

Waiting to access logical replication launcher’s dynamic shared memory allocator

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:168. The instrumented operation is: Waiting to access logical replication launcher’s dynamic shared memory allocator. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as LogicalRepLauncherDSA. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/LogicalRepLauncherDSA 
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 = 'LogicalRepLauncherDSA'
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 = 'LogicalRepLauncherDSA'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

25 - LWLock: LogicalRepLauncherHash

Waiting to access logical replication launcher’s shared hash table
PostgreSQL wait event dossier
ClassLWLock EventLogicalRepLauncherHash VersionsPG 16-18 Evidence3 source location(s)

Official description

Waiting to access logical replication launcher’s shared hash table

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:169. The instrumented operation is: Waiting to access logical replication launcher’s shared hash table. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as LogicalRepLauncherHash. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/LogicalRepLauncherHash 
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 = 'LogicalRepLauncherHash'
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 = 'LogicalRepLauncherHash'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

26 - LWLock: LogicalRepWorker

Waiting to read or update the state of logical replication workers
PostgreSQL wait event dossier
ClassLWLock EventLogicalRepWorker VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update the state of logical replication workers

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/replication/logical/launcher.c:189. The instrumented operation is: Waiting to read or update the state of logical replication workers. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as LogicalRepWorker. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/LogicalRepWorker 
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 = 'LogicalRepWorker'
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 = 'LogicalRepWorker'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

27 - LWLock: MultiXactGen

Waiting to read or update shared multixact state
PostgreSQL wait event dossier
ClassLWLock EventMultiXactGen VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update shared multixact state

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/multixact.c:738. The instrumented operation is: Waiting to read or update shared multixact state. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as MultiXactGen. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/MultiXactGen 
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 = 'MultiXactGen'
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 = 'MultiXactGen'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

28 - LWLock: MultiXactMemberBuffer

Waiting for I/O on a multixact member SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventMultiXactMemberBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a multixact member SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:144. The instrumented operation is: Waiting for I/O on a multixact member SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as MultiXactMemberBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/MultiXactMemberBuffer 
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 = 'MultiXactMemberBuffer'
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 = 'MultiXactMemberBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

29 - LWLock: MultiXactMemberSLRU

Waiting to access the multixact member SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventMultiXactMemberSLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the multixact member SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

This lock protects the SLRU cache for pg_multixact/members, which stores the transaction members of multitransaction IDs used by shared row locks. Concurrent row-lock creation and old member lookups meet here.

Normal or trouble?

  • Normal: Brief waits occur in workloads that use SELECT FOR SHARE/KEY SHARE or create multixacts through foreign-key checks.
  • Investigate: Sustained contention points to heavy shared row locking, multixact churn, lagging freeze, or slow pg_multixact storage.

Diagnostic SQL

Sessions waiting on LWLock/MultiXactMemberSLRU 
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 = 'MultiXactMemberSLRU'
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 = 'MultiXactMemberSLRU'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Find statements and tables creating many shared row locks.
  2. Check multixact age and autovacuum progress.
  3. Reduce lock fan-out and unblock multixact freeze; investigate member SLRU I/O if paired with buffer waits.

Source evidence

Related controls and signals

Typical incident pattern

A high-fan-out foreign-key workload locks many parent rows while a long transaction delays multixact cleanup, driving member-cache contention.

30 - LWLock: MultiXactOffsetBuffer

Waiting for I/O on a multixact offset SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventMultiXactOffsetBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a multixact offset SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:143. The instrumented operation is: Waiting for I/O on a multixact offset SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as MultiXactOffsetBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/MultiXactOffsetBuffer 
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 = 'MultiXactOffsetBuffer'
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 = 'MultiXactOffsetBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

31 - LWLock: MultiXactOffsetSLRU

Waiting to access the multixact offset SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventMultiXactOffsetSLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the multixact offset SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:173. The instrumented operation is: Waiting to access the multixact offset SLRU cache. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as MultiXactOffsetSLRU. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/MultiXactOffsetSLRU 
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 = 'MultiXactOffsetSLRU'
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 = 'MultiXactOffsetSLRU'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

32 - LWLock: MultiXactTruncation

Waiting to read or truncate multixact information
PostgreSQL wait event dossier
ClassLWLock EventMultiXactTruncation VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or truncate multixact information

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/multixact.c:2901. The instrumented operation is: Waiting to read or truncate multixact information. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as MultiXactTruncation. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/MultiXactTruncation 
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 = 'MultiXactTruncation'
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 = 'MultiXactTruncation'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

33 - LWLock: NotifyBuffer

Waiting for I/O on a NOTIFY message SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventNotifyBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a NOTIFY message SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:145. The instrumented operation is: Waiting for I/O on a NOTIFY message SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as NotifyBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/NotifyBuffer 
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 = 'NotifyBuffer'
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 = 'NotifyBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

34 - LWLock: NotifyQueue

Waiting to read or update NOTIFY messages
PostgreSQL wait event dossier
ClassLWLock EventNotifyQueue VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update NOTIFY messages

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/commands/async.c:940. The instrumented operation is: Waiting to read or update NOTIFY messages. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as NotifyQueue. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/NotifyQueue 
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 = 'NotifyQueue'
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 = 'NotifyQueue'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

35 - LWLock: NotifyQueueTail

Waiting to update limit on NOTIFY message storage
PostgreSQL wait event dossier
ClassLWLock EventNotifyQueueTail VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to update limit on NOTIFY message storage

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/commands/async.c:2126. The instrumented operation is: Waiting to update limit on NOTIFY message storage. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as NotifyQueueTail. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/NotifyQueueTail 
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 = 'NotifyQueueTail'
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 = 'NotifyQueueTail'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

36 - LWLock: NotifySLRU

Waiting to access the NOTIFY message SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventNotifySLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the NOTIFY message SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:175. The instrumented operation is: Waiting to access the NOTIFY message SLRU cache. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as NotifySLRU. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/NotifySLRU 
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 = 'NotifySLRU'
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 = 'NotifySLRU'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

37 - LWLock: OidGen

Waiting to allocate a new OID
PostgreSQL wait event dossier
ClassLWLock EventOidGen VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to allocate a new OID

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/varsup.c:563. The instrumented operation is: Waiting to allocate a new OID. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as OidGen. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/OidGen 
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 = 'OidGen'
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 = 'OidGen'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

38 - LWLock: OldSnapshotTimeMap

Waiting to read or update old snapshot control information.
PostgreSQL wait event dossier
ClassLWLock EventOldSnapshotTimeMap VersionsPG 13-16 Evidence2 source location(s)

Official description

Waiting to read or update old snapshot control information.

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ — —

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:750 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/time/snapmgr.c:1758. The instrumented operation is: Waiting to read or update old snapshot control information. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as OldSnapshotTimeMap. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/OldSnapshotTimeMap 
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 = 'OldSnapshotTimeMap'
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 = 'OldSnapshotTimeMap'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

39 - LWLock: ParallelAppend

Waiting to choose the next subplan during Parallel Append plan execution
PostgreSQL wait event dossier
ClassLWLock EventParallelAppend VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to choose the next subplan during Parallel Append plan execution

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/executor/nodeAppend.c:69. The instrumented operation is: Waiting to choose the next subplan during Parallel Append plan execution. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ParallelAppend. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ParallelAppend 
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 = 'ParallelAppend'
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 = 'ParallelAppend'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

40 - LWLock: ParallelBtreeScan

Waiting to synchronize workers during Parallel B-tree scan plan execution
PostgreSQL wait event dossier
ClassLWLock EventParallelBtreeScan VersionsPG 18 Evidence3 source location(s)

Official description

Waiting to synchronize workers during Parallel B-tree scan plan execution

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — — ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:156. The instrumented operation is: Waiting to synchronize workers during Parallel B-tree scan plan execution. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ParallelBtreeScan. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ParallelBtreeScan 
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 = 'ParallelBtreeScan'
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 = 'ParallelBtreeScan'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

41 - LWLock: ParallelHashJoin

Waiting to synchronize workers during Parallel Hash Join plan execution
PostgreSQL wait event dossier
ClassLWLock EventParallelHashJoin VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to synchronize workers during Parallel Hash Join plan execution

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/executor/nodeHash.c:71. The instrumented operation is: Waiting to synchronize workers during Parallel Hash Join plan execution. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ParallelHashJoin. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ParallelHashJoin 
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 = 'ParallelHashJoin'
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 = 'ParallelHashJoin'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

42 - LWLock: ParallelQueryDSA

Waiting for parallel query dynamic shared memory allocation
PostgreSQL wait event dossier
ClassLWLock EventParallelQueryDSA VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for parallel query dynamic shared memory allocation

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:157. The instrumented operation is: Waiting for parallel query dynamic shared memory allocation. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ParallelQueryDSA. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ParallelQueryDSA 
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 = 'ParallelQueryDSA'
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 = 'ParallelQueryDSA'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

43 - LWLock: ParallelVacuumDSA

Waiting for parallel vacuum dynamic shared memory allocation
PostgreSQL wait event dossier
ClassLWLock EventParallelVacuumDSA VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting for parallel vacuum dynamic shared memory allocation

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:179. The instrumented operation is: Waiting for parallel vacuum dynamic shared memory allocation. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ParallelVacuumDSA. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ParallelVacuumDSA 
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 = 'ParallelVacuumDSA'
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 = 'ParallelVacuumDSA'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

44 - LWLock: PerSessionDSA

Waiting for parallel query dynamic shared memory allocation
PostgreSQL wait event dossier
ClassLWLock EventPerSessionDSA VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for parallel query dynamic shared memory allocation

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:158. The instrumented operation is: Waiting for parallel query dynamic shared memory allocation. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PerSessionDSA. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PerSessionDSA 
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 = 'PerSessionDSA'
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 = 'PerSessionDSA'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

45 - LWLock: PerSessionRecordType

Waiting to access a parallel query’s information about composite types
PostgreSQL wait event dossier
ClassLWLock EventPerSessionRecordType VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access a parallel query’s information about composite types

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:159. The instrumented operation is: Waiting to access a parallel query’s information about composite types. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PerSessionRecordType. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PerSessionRecordType 
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 = 'PerSessionRecordType'
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 = 'PerSessionRecordType'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

46 - LWLock: PerSessionRecordTypmod

Waiting to access a parallel query’s information about type modifiers that identify anonymous record types
PostgreSQL wait event dossier
ClassLWLock EventPerSessionRecordTypmod VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access a parallel query’s information about type modifiers that identify anonymous record types

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:160. The instrumented operation is: Waiting to access a parallel query’s information about type modifiers that identify anonymous record types. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PerSessionRecordTypmod. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PerSessionRecordTypmod 
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 = 'PerSessionRecordTypmod'
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 = 'PerSessionRecordTypmod'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

47 - LWLock: PerXactPredicateList

Waiting to access the list of predicate locks held by the current serializable transaction during a parallel query
PostgreSQL wait event dossier
ClassLWLock EventPerXactPredicateList VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the list of predicate locks held by the current serializable transaction during a parallel query

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:164. The instrumented operation is: Waiting to access the list of predicate locks held by the current serializable transaction during a parallel query. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PerXactPredicateList. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PerXactPredicateList 
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 = 'PerXactPredicateList'
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 = 'PerXactPredicateList'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

48 - LWLock: PgStatsDSA

Waiting for stats dynamic shared memory allocator access
PostgreSQL wait event dossier
ClassLWLock EventPgStatsDSA VersionsPG 15-18 Evidence3 source location(s)

Official description

Waiting for stats dynamic shared memory allocator access

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:165. The instrumented operation is: Waiting for stats dynamic shared memory allocator access. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PgStatsDSA. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PgStatsDSA 
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 = 'PgStatsDSA'
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 = 'PgStatsDSA'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

49 - LWLock: PgStatsData

Waiting for shared memory stats data access
PostgreSQL wait event dossier
ClassLWLock EventPgStatsData VersionsPG 15-18 Evidence3 source location(s)

Official description

Waiting for shared memory stats data access

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:167. The instrumented operation is: Waiting for shared memory stats data access. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PgStatsData. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PgStatsData 
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 = 'PgStatsData'
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 = 'PgStatsData'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

50 - LWLock: PgStatsHash

Waiting for stats shared memory hash table access
PostgreSQL wait event dossier
ClassLWLock EventPgStatsHash VersionsPG 15-18 Evidence3 source location(s)

Official description

Waiting for stats shared memory hash table access

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:166. The instrumented operation is: Waiting for stats shared memory hash table access. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PgStatsHash. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PgStatsHash 
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 = 'PgStatsHash'
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 = 'PgStatsHash'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

51 - LWLock: PredicateLockManager

Waiting to access predicate lock information used by serializable transactions
PostgreSQL wait event dossier
ClassLWLock EventPredicateLockManager VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access predicate lock information used by serializable transactions

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:154. The instrumented operation is: Waiting to access predicate lock information used by serializable transactions. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as PredicateLockManager. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/PredicateLockManager 
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 = 'PredicateLockManager'
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 = 'PredicateLockManager'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

52 - LWLock: ProcArray

Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session’s transaction ID)
PostgreSQL wait event dossier
ClassLWLock EventProcArray VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session’s transaction ID)

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger 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.

Normal or trouble?

  • Normal: Brief waits are expected on busy OLTP systems that start and finish many transactions.
  • Investigate: Persistent contention can accompany extreme connection counts, snapshot-heavy workloads, long transactions, or bursts of transaction completion.

Diagnostic SQL

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;

Response

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

Source evidence

Related controls and signals

Typical incident pattern

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

53 - LWLock: RelCacheInit

Waiting to read or update a pg_internal.init relation cache initialization file
PostgreSQL wait event dossier
ClassLWLock EventRelCacheInit VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update a pg_internal.init relation cache initialization file

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event_names.txt:323. The instrumented operation is: Waiting to read or update a pg_internal.init relation cache initialization file. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as RelCacheInit. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/RelCacheInit 
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 = 'RelCacheInit'
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 = 'RelCacheInit'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

54 - LWLock: RelationMapping

Waiting to read or update a pg_filenode.map file (used to track the filenode assignments of certain system catalogs)
PostgreSQL wait event dossier
ClassLWLock EventRelationMapping VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update a pg_filenode.map file (used to track the filenode assignments of certain system catalogs)

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event_names.txt:332. The instrumented operation is: Waiting to read or update a pg_filenode.map file (used to track the filenode assignments of certain system catalogs). LWLockAcquire could not immediately take the lightweight-lock tranche displayed as RelationMapping. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/RelationMapping 
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 = 'RelationMapping'
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 = 'RelationMapping'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

55 - LWLock: ReplicationOrigin

Waiting to create, drop or use a replication origin
PostgreSQL wait event dossier
ClassLWLock EventReplicationOrigin VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to create, drop or use a replication origin

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/replication/logical/origin.c:377. The instrumented operation is: Waiting to create, drop or use a replication origin. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ReplicationOrigin. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ReplicationOrigin 
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 = 'ReplicationOrigin'
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 = 'ReplicationOrigin'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

56 - LWLock: ReplicationOriginState

Waiting to read or update the progress of one replication origin
PostgreSQL wait event dossier
ClassLWLock EventReplicationOriginState VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update the progress of one replication origin

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/replication/logical/origin.c:557. The instrumented operation is: Waiting to read or update the progress of one replication origin. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ReplicationOriginState. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ReplicationOriginState 
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 = 'ReplicationOriginState'
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 = 'ReplicationOriginState'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

57 - LWLock: ReplicationSlotAllocation

Waiting to allocate or free a replication slot
PostgreSQL wait event dossier
ClassLWLock EventReplicationSlotAllocation VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to allocate or free a replication slot

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/replication/logical/slotsync.c:543. The instrumented operation is: Waiting to allocate or free a replication slot. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ReplicationSlotAllocation. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ReplicationSlotAllocation 
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 = 'ReplicationSlotAllocation'
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 = 'ReplicationSlotAllocation'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

58 - LWLock: ReplicationSlotControl

Waiting to read or update replication slot state
PostgreSQL wait event dossier
ClassLWLock EventReplicationSlotControl VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update replication slot state

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/replication/logical/logical.c:492. The instrumented operation is: Waiting to read or update replication slot state. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ReplicationSlotControl. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ReplicationSlotControl 
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 = 'ReplicationSlotControl'
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 = 'ReplicationSlotControl'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

59 - LWLock: ReplicationSlotIO

Waiting for I/O on a replication slot
PostgreSQL wait event dossier
ClassLWLock EventReplicationSlotIO VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a replication slot

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:150. The instrumented operation is: Waiting for I/O on a replication slot. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ReplicationSlotIO. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ReplicationSlotIO 
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 = 'ReplicationSlotIO'
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 = 'ReplicationSlotIO'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

60 - LWLock: SInvalRead

Waiting to retrieve messages from the shared catalog invalidation queue
PostgreSQL wait event dossier
ClassLWLock EventSInvalRead VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to retrieve messages from the shared catalog invalidation queue

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/ipc/sinvaladt.c:497. The instrumented operation is: Waiting to retrieve messages from the shared catalog invalidation queue. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SInvalRead. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SInvalRead 
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 = 'SInvalRead'
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 = 'SInvalRead'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

61 - LWLock: SInvalWrite

Waiting to add a message to the shared catalog invalidation queue
PostgreSQL wait event dossier
ClassLWLock EventSInvalWrite VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to add a message to the shared catalog invalidation queue

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/ipc/sinvaladt.c:290. The instrumented operation is: Waiting to add a message to the shared catalog invalidation queue. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SInvalWrite. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SInvalWrite 
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 = 'SInvalWrite'
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 = 'SInvalWrite'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

62 - LWLock: SerialBuffer

Waiting for I/O on a serializable transaction conflict SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventSerialBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a serializable transaction conflict SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:146. The instrumented operation is: Waiting for I/O on a serializable transaction conflict SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SerialBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SerialBuffer 
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 = 'SerialBuffer'
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 = 'SerialBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

63 - LWLock: SerialControl

Waiting to read or update shared pg_serial state
PostgreSQL wait event dossier
ClassLWLock EventSerialControl VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to read or update shared pg_serial state

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/predicate.c:835. The instrumented operation is: Waiting to read or update shared pg_serial state. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SerialControl. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SerialControl 
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 = 'SerialControl'
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 = 'SerialControl'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

64 - LWLock: SerialSLRU

Waiting to access the serializable transaction conflict SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventSerialSLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the serializable transaction conflict SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:176. The instrumented operation is: Waiting to access the serializable transaction conflict SLRU cache. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SerialSLRU. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SerialSLRU 
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 = 'SerialSLRU'
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 = 'SerialSLRU'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

65 - LWLock: SerializableFinishedList

Waiting to access the list of finished serializable transactions
PostgreSQL wait event dossier
ClassLWLock EventSerializableFinishedList VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the list of finished serializable transactions

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/predicate.c:1507. The instrumented operation is: Waiting to access the list of finished serializable transactions. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SerializableFinishedList. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SerializableFinishedList 
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 = 'SerializableFinishedList'
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 = 'SerializableFinishedList'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

66 - LWLock: SerializablePredicateList

Waiting to access the list of predicate locks held by serializable transactions
PostgreSQL wait event dossier
ClassLWLock EventSerializablePredicateList VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the list of predicate locks held by serializable transactions

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/predicate.c:2220. The instrumented operation is: Waiting to access the list of predicate locks held by serializable transactions. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SerializablePredicateList. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SerializablePredicateList 
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 = 'SerializablePredicateList'
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 = 'SerializablePredicateList'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

67 - LWLock: SerializableXactHash

Waiting to read or update information about serializable transactions
PostgreSQL wait event dossier
ClassLWLock EventSerializableXactHash VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update information about serializable transactions

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/predicate.c:1462. The instrumented operation is: Waiting to read or update information about serializable transactions. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SerializableXactHash. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SerializableXactHash 
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 = 'SerializableXactHash'
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 = 'SerializableXactHash'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

68 - LWLock: SharedTidBitmap

Waiting to access a shared TID bitmap during a parallel bitmap index scan
PostgreSQL wait event dossier
ClassLWLock EventSharedTidBitmap VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access a shared TID bitmap during a parallel bitmap index scan

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:162. The instrumented operation is: Waiting to access a shared TID bitmap during a parallel bitmap index scan. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SharedTidBitmap. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SharedTidBitmap 
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 = 'SharedTidBitmap'
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 = 'SharedTidBitmap'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

69 - LWLock: SharedTupleStore

Waiting to access a shared tuple store during parallel query
PostgreSQL wait event dossier
ClassLWLock EventSharedTupleStore VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access a shared tuple store during parallel query

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:161. The instrumented operation is: Waiting to access a shared tuple store during parallel query. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SharedTupleStore. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SharedTupleStore 
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 = 'SharedTupleStore'
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 = 'SharedTupleStore'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

70 - LWLock: ShmemIndex

Waiting to find or allocate space in shared memory
PostgreSQL wait event dossier
ClassLWLock EventShmemIndex VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to find or allocate space in shared memory

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/ipc/shmem.c:392. The instrumented operation is: Waiting to find or allocate space in shared memory. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as ShmemIndex. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/ShmemIndex 
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 = 'ShmemIndex'
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 = 'ShmemIndex'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

71 - LWLock: SubtransBuffer

Waiting for I/O on a sub-transaction SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventSubtransBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a sub-transaction SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:142. The instrumented operation is: Waiting for I/O on a sub-transaction SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SubtransBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SubtransBuffer 
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 = 'SubtransBuffer'
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 = 'SubtransBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

72 - LWLock: SubtransSLRU

Waiting to access the sub-transaction SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventSubtransSLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the sub-transaction SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:177. The instrumented operation is: Waiting to access the sub-transaction SLRU cache. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SubtransSLRU. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SubtransSLRU 
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 = 'SubtransSLRU'
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 = 'SubtransSLRU'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

73 - LWLock: SyncRep

Waiting to read or update information about the state of synchronous replication
PostgreSQL wait event dossier
ClassLWLock EventSyncRep VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update information about the state of synchronous replication

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

SyncRepLock protects synchronous-replication queues and shared sender state. Committers enqueue or inspect their wait position while WAL senders update which LSNs the configured synchronous standbys have acknowledged.

Normal or trouble?

  • Normal: Small bursts occur as synchronous commits queue and WAL senders publish acknowledgements.
  • Investigate: Sustained SyncRep LWLock contention is different from waiting for a standby acknowledgement: it means queue/state coordination itself is hot, usually under extreme commit concurrency or sender churn.

Diagnostic SQL

Sessions waiting on LWLock/SyncRep 
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 = 'SyncRep'
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 = 'SyncRep'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Separate LWLock/SyncRep from IPC/SyncRep and replication-lag waits.
  2. Inspect synchronous standby health, sender churn, and commit rate.
  3. Stabilize replication and smooth commit bursts; change durability policy only with explicit incident authority.

Source evidence

Related controls and signals

Typical incident pattern

A standby flaps while a burst of synchronous committers repeatedly enters and leaves the wait queue, making queue coordination visible as LWLock/SyncRep.

74 - LWLock: SyncScan

Waiting to select the starting location of a synchronized table scan
PostgreSQL wait event dossier
ClassLWLock EventSyncScan VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to select the starting location of a synchronized table scan

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/common/syncscan.c:258. The instrumented operation is: Waiting to select the starting location of a synchronized table scan. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as SyncScan. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/SyncScan 
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 = 'SyncScan'
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 = 'SyncScan'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

75 - LWLock: TablespaceCreate

Waiting to create or drop a tablespace
PostgreSQL wait event dossier
ClassLWLock EventTablespaceCreate VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to create or drop a tablespace

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/commands/tablespace.c:138. The instrumented operation is: Waiting to create or drop a tablespace. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as TablespaceCreate. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/TablespaceCreate 
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 = 'TablespaceCreate'
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 = 'TablespaceCreate'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

76 - LWLock: TwoPhaseState

Waiting to read or update the state of prepared transactions
PostgreSQL wait event dossier
ClassLWLock EventTwoPhaseState VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to read or update the state of prepared transactions

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/twophase.c:329. The instrumented operation is: Waiting to read or update the state of prepared transactions. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as TwoPhaseState. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/TwoPhaseState 
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 = 'TwoPhaseState'
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 = 'TwoPhaseState'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

77 - LWLock: WALBufMapping

Waiting to replace a page in WAL buffers
PostgreSQL wait event dossier
ClassLWLock EventWALBufMapping VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to replace a page in WAL buffers

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger 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.

Normal or trouble?

  • Normal: Short waits appear as WAL generation advances through buffer pages.
  • Investigate: Sustained contention suggests WAL generation is turning over buffers faster than the write path advances, often during write bursts or checkpoints.

Diagnostic SQL

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;

Response

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

Source evidence

Related controls and signals

Typical incident pattern

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

78 - LWLock: WALInsert

Waiting to insert WAL data into a memory buffer
PostgreSQL wait event dossier
ClassLWLock EventWALInsert VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to insert WAL data into a memory buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger 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.

Normal or trouble?

  • Normal: Transient waits are normal during concurrent writes and commit bursts.
  • 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 SQL

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;

Response

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

Source evidence

Related controls and signals

Typical incident pattern

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.

79 - LWLock: WALSummarizer

Waiting to read or update WAL summarization state
PostgreSQL wait event dossier
ClassLWLock EventWALSummarizer VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to read or update WAL summarization state

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/postmaster/walsummarizer.c:273. The instrumented operation is: Waiting to read or update WAL summarization state. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as WALSummarizer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/WALSummarizer 
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 = 'WALSummarizer'
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 = 'WALSummarizer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

80 - LWLock: WALWrite

Waiting for WAL buffers to be written to disk
PostgreSQL wait event dossier
ClassLWLock EventWALWrite VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for WAL buffers to be written to disk

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

WALWriteLock serializes progress that writes shared WAL buffers and advances the written/flushed WAL positions. A waiter is queued behind the backend currently performing or coordinating that work.

Normal or trouble?

  • Normal: Brief waits occur when concurrent committers help write WAL.
  • Investigate: Sustained WALWrite with commit latency usually means the WAL write path is slow or cannot absorb the generated WAL rate.

Diagnostic SQL

Sessions waiting on LWLock/WALWrite 
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 = 'WALWrite'
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 = 'WALWrite'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Compare WAL write and sync time with commit latency.
  2. Check the WAL filesystem/device, virtualization throttling, and checkpoint overlap.
  3. Reduce burstiness; tune wal_writer settings only after storage evidence confirms the path.

Source evidence

Related controls and signals

Typical incident pattern

A cloud volume hits its burst-credit ceiling; WAL writes lengthen, committers queue on WALWrite, and synchronous commits slow together.

81 - LWLock: WaitEventCustom

Waiting to read or update custom wait events information
PostgreSQL wait event dossier
ClassLWLock EventWaitEventCustom VersionsPG 17-18 Evidence3 source location(s)

Official description

Waiting to read or update custom wait events information

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
— — — — ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event.c:193. The instrumented operation is: Waiting to read or update custom wait events information. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as WaitEventCustom. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/WaitEventCustom 
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 = 'WaitEventCustom'
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 = 'WaitEventCustom'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

82 - LWLock: WrapLimitsVacuum

Waiting to update limits on transaction id and multixact consumption
PostgreSQL wait event dossier
ClassLWLock EventWrapLimitsVacuum VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to update limits on transaction id and multixact consumption

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/commands/vacuum.c:1858. The instrumented operation is: Waiting to update limits on transaction id and multixact consumption. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as WrapLimitsVacuum. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/WrapLimitsVacuum 
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 = 'WrapLimitsVacuum'
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 = 'WrapLimitsVacuum'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

83 - LWLock: XactBuffer

Waiting for I/O on a transaction status SLRU buffer
PostgreSQL wait event dossier
ClassLWLock EventXactBuffer VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting for I/O on a transaction status SLRU buffer

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/storage/lmgr/lwlock.c:140. The instrumented operation is: Waiting for I/O on a transaction status SLRU buffer. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as XactBuffer. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/XactBuffer 
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 = 'XactBuffer'
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 = 'XactBuffer'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

84 - LWLock: XactSLRU

Waiting to access the transaction status SLRU cache
PostgreSQL wait event dossier
ClassLWLock EventXactSLRU VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to access the transaction status SLRU cache

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger 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.

Normal or trouble?

  • Normal: Short waits accompany transaction completion and occasional pg_xact cache misses.
  • Investigate: Persistent waits can indicate transaction-status cache churn, access to very old tuples, or storage latency on the pg_xact path.

Diagnostic SQL

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;

Response

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

Source evidence

Related controls and signals

Typical incident pattern

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

85 - LWLock: XactTruncation

Waiting to execute pg_xact_status or update the oldest transaction ID available to it
PostgreSQL wait event dossier
ClassLWLock EventXactTruncation VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to execute pg_xact_status or update the oldest transaction ID available to it

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/varsup.c:357. The instrumented operation is: Waiting to execute pg_xact_status or update the oldest transaction ID available to it. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as XactTruncation. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/XactTruncation 
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 = 'XactTruncation'
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 = 'XactTruncation'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals

86 - LWLock: XidGen

Waiting to allocate a new transaction ID
PostgreSQL wait event dossier
ClassLWLock EventXidGen VersionsPG 13-18 Evidence3 source location(s)

Official description

Waiting to allocate a new transaction ID

PG 13 PG 14 PG 15 PG 16 PG 17 PG 18
✓ ✓ ✓ ✓ ✓ ✓

Trigger mechanism

pgstat_report_wait_start at src/backend/storage/lmgr/lwlock.c:739 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/access/transam/varsup.c:105. The instrumented operation is: Waiting to allocate a new transaction ID. LWLockAcquire could not immediately take the lightweight-lock tranche displayed as XidGen. The generic LWLock reporter publishes the tranche name while the backend sleeps on the internal shared-memory resource.

Normal or trouble?

  • Normal: A brief sample is normal around short internal critical sections.
  • Investigate: Investigate when the same tranche affects at least 10% of active foreground sessions in three consecutive one-second samples; treat 25% or waits beyond five seconds as urgent.

Diagnostic SQL

Sessions waiting on LWLock/XidGen 
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 = 'XidGen'
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 = 'XidGen'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

Response

  1. Repeat the snapshot and isolate one hot tranche.
  2. Correlate it with the protected resource and current workload phase.
  3. Reduce the specific contention source; adding concurrency can make a shared-memory hotspot worse.

Source evidence

Related controls and signals