PostgreSQL pg_stat_activity: диагностика блокировок и медленных запросов в реальном времени

Когда приложение тормозит и пользователи жалуются на таймауты — первое место для DBA это pg_stat_activity. Представление показывает все соединения к PostgreSQL в реальном времени: кто подключён, что выполняет, на чём застрял. В связке с pg_locks оно позволяет за минуты найти блокировку, парализовавшую половину бэкенда.

pg_stat_activity — ключевые поля

Ключевые колонки:

• pid — ID процесса. Используется для cancel/terminate.
• state — текущее состояние: active, idle, idle in transaction, idle in transaction (aborted).
• wait_event_type / wait_event — на чём ждёт процесс (Lock, IO, LWLock, Client и др.).
• query — текст последнего (или текущего) запроса.
• query_start — когда запрос начал выполняться.
• xact_start — начало текущей транзакции.
• backend_start — когда процесс подключился.
• state_change — когда последний раз менялось состояние.

Базовый запрос для обзора активности:

SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS query_duration,
    left(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

Находим блокировки: pg_locks + pg_stat_activity

Сама по себе pg_stat_activity покажет, что процесс ждёт (wait_event_type = 'Lock'). Но чтобы понять, кто кого блокирует, нужен JOIN с pg_locks:

-- Кто кого блокирует (lock tree)
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    left(blocked.query, 60) AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    left(blocking.query, 60) AS blocking_query,
    now() - blocked.query_start AS wait_duration
FROM pg_locks bl
JOIN pg_stat_activity blocked ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid
WHERE NOT bl.granted;

Более универсальный вариант через pg_blocking_pids() (PostgreSQL 9.6+):

SELECT
    pid,
    usename,
    pg_blocking_pids(pid) AS blocked_by,
    state,
    wait_event_type,
    left(query, 80) AS query,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Этот запрос сразу покажет все заблокированные процессы и PID-ы, которые их держат.

Убиваем зависшие запросы

PostgreSQL предоставляет две функции:

• pg_cancel_backend(pid) — отменяет текущий запрос. Процесс остаётся подключённым. Мягкий вариант.
• pg_terminate_backend(pid) — убивает соединение целиком. Жёсткий вариант, если cancel не помог.

-- Мягко: отменяем запрос
SELECT pg_cancel_backend(12345);

-- Жёстко: убиваем соединение
SELECT pg_terminate_backend(12345);

-- Массово: убить все idle in transaction старше 10 минут
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

Важно: pg_cancel_backend сработает только если процесс выполняет запрос. Если он idle in transaction — cancel ничего не сделает, нужен terminate.

Мониторинг: idle in transaction и long-running queries

Два главных врага продакшена:

• idle in transaction — открытая транзакция, которая ничего не делает. Держит блокировки, мешает VACUUM, раздувает таблицы.
• Long-running queries — запросы, работающие дольше допустимого. Часто из-за отсутствия индекса или неоптимального плана.

Запросы для мониторинга:

-- Транзакции idle in transaction дольше 5 минут
SELECT pid, usename, application_name,
       now() - xact_start AS tx_duration,
       left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes';

-- Активные запросы дольше 30 секунд
SELECT pid, usename,
       now() - query_start AS duration,
       wait_event_type,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
  AND pid != pg_backend_pid();

Подключите эти запросы к Prometheus (postgres_exporter) или Zabbix для алертинга.

Практика: таймауты и автоматическая защита

PostgreSQL позволяет автоматически убивать проблемные сессии через конфигурацию:

-- Максимальное время выполнения запроса (на уровне сессии или глобально)
SET statement_timeout = '30s';

-- Глобально в postgresql.conf:
-- statement_timeout = '60s'

-- Автоматически убивать idle in transaction сессии
-- postgresql.conf (PG 9.6+):
-- idle_in_transaction_session_timeout = '300000'  -- 5 минут в мс

-- Логировать ожидания блокировок дольше 1 секунды
-- postgresql.conf:
-- log_lock_waits = on
-- deadlock_timeout = '1s'

Что дают эти настройки:

• statement_timeout — запрос, работающий дольше лимита, автоматически отменяется. Защита от случайных full scan.
• idle_in_transaction_session_timeout — сессия в состоянии idle in transaction дольше лимита будет убита. Спасает от забытых транзакций.
• log_lock_waits = on — в лог попадут все ожидания блокировок дольше deadlock_timeout. Бесценно для post-mortem анализа.

Рекомендуемые значения для продакшена:

# postgresql.conf
statement_timeout = '60s'
idle_in_transaction_session_timeout = '300000'
log_lock_waits = on
deadlock_timeout = '1s'
log_min_duration_statement = '1000'  # логировать запросы дольше 1с

Комбинация pg_stat_activity + правильных таймаутов + алертинга закрывает большинство проблем с блокировками и зависшими запросами. Главное — не ждать инцидента, а настроить защиту заранее.