Alert rule: PostgreSQLBackendsWaiting

Overview

This alert triggers when the number of backends waiting on locks has been increasing rapidly over the last 5 minutes. This indicates growing lock contention in the application, which can degrade performance significantly.

Steps for Debugging

There are two ways to solve this issue:

First Option: Identify the source of lock contention and resolve it. Check which queries are waiting and what they are waiting for.

Find waiting backends and the locks they are waiting on

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
ORDER BY wait_event_type;

Find blocking queries

SELECT blocking_locks.pid AS blocking_pid,
       blocking_activity.query AS blocking_query,
       blocked_locks.pid AS blocked_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Second Option: Reduce lock contention in the application by shortening transactions, using row-level locking instead of table-level locking, or batching writes differently.