• Клиентские сессии зависают со статусом idle in transaction или waiting.
• Ошибки ERROR: deadlock detected и canceling statement due to lock timeout в приложениях.
• Резкий рост использования оперативной памяти и разрастание таблиц из-за невозможности выполнения VACUUM.

1. Поиск блокирующих и заблокированных запросов

Выполните диагностический SQL-запрос для построения дерева ожидания блокировок:

SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid    AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS current_statement_in_blocking_process,
    now() - blocked_activity.query_start AS blocked_duration
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;

2. Принудительное завершение блокирующей сессии

-- Мягкая отмена текущего запроса
SELECT pg_cancel_backend(<blocking_pid>);

-- Жесткий разрыв соединения с откатом транзакции
SELECT pg_terminate_backend(<blocking_pid>);

3. Превентивная настройка таймаутов в postgresql.conf

# Максимальное время ожидания получения блокировки (5 секунд)
lock_timeout = 5000

# Максимальное время нахождения в статусе idle in transaction
idle_in_transaction_session_timeout = 60000

# Максимальное время выполнения одного SQL-запроса
statement_timeout = 120000

Перечитайте конфигурацию:

SELECT pg_reload_conf();