Диагностика узких мест в PostgreSQL: от задержек p95 до pg_stat_statements

Знакомая многим инженерам картина: мониторинг бэкенда фиксирует неприятный рост задержек ответа базы данных на перцентилях p95 и p99, очередь HTTP-запросов начинает угрожающе расти, а график утилизации CPU на сервере базы показывает скромные 15–20%. Первая импульсивная реакция руководства — совершить спонтанный вертикальный апгрейд («давайте перенесем базу на инстанс с 64 ядрами вместо 16»). Но после перезапуска и оплаты огромного счета за облако ничего не меняется: задержки остаются прежними.

Причина проста: в подавляющем большинстве случаев узкое место СУБД кроется не в нехватке вычислительной мощности, а в архитектурных заторах — невидимых блокировках транзакций, неоптимальных планах сканирования и промахах мимо буферного кэша.

В руководстве сообщества SQL Portal («Поиск узких мест в базе данных») систематизирован четкий алгоритм диагностики PostgreSQL, позволяющий за минуты изолировать проблему без остановки продакшена.

Разделение времени: вычисления против ожиданий

Первый шаг грамотного расследования — разделить совокупную задержку (latency) на две независимые компоненты: чистое время выполнения инструкций процессором (execution time) и время вынужденного простоя в очереди (wait time).

Для этого мы заглядываем в системное представление pg_stat_activity. Если запрос висит секундами, но процессор не загружен, он почти наверняка ждет освобождения ресурса. Колонка wait_event_type прямо указывает на причину:

• Lock: сессия ожидает снятия блокировки строки или таблицы, которую удерживает другая транзакция;
• IO: запрос уперся в скорость дискового накопителя (чтение страниц данных или ожидание сброса журнала WAL);
• Client: сервер ждет действий от клиентского приложения (классическое состояние idle in transaction).

Вот проверенный SQL-запрос, который мгновенно вскрывает дерево блокировок и находит сессию-виновника:

SELECT 
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.wait_event_type,
    blocked.wait_event,
    left(blocked.query, 60) AS blocked_query,
    left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking 
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE NOT blocked.pid = pg_backend_pid();

Если ты видишь сессию в статусе idle in transaction, удерживающую исключительную блокировку, ее можно точечно завершить вызовом SELECT pg_terminate_backend(blocking_pid);, моментально вернув кластер к жизни.

Поиск виновников через pg_stat_statements

Когда блокировок нет, но база страдает от медленных выборок, на помощь приходит расширение pg_stat_statements. Оно нормализует SQL-запросы (заменяя конкретные ID на константы) и накапливает детальную статистику по всему кластеру.

В нашем практическом исследовании о том, как искать тяжелые запросы через pg_stat_statements при свободном процессоре, мы подробно разбирали метрики буферов. В 80% случаев деградацию базы вызывают всего два-три запроса, выполняющих последовательное сканирование (Seq Scan) многомиллионных таблиц и вымывающих shared_buffers.

Следующий запрос изолирует топ запросов по суммарному времени выполнения с оценкой эффективности кэширования:

SELECT 
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0) * 100)::numeric, 1) AS hit_percent,
    left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Если процент попадания в кэш (hit_percent) опускается ниже 95%, запрос принудительно заставляет ядро читать данные с диска.

В материале о том, как диагностировать высококардинальные нагрузки в pg_stat_statements, мы показывали важность параметризации запросов для предотвращения переполнения буфера самого расширения.

Вместо слепого масштабирования серверов потрать 15 минут на рентген блокировок и статистики буферов — и твоя база данных заработает как швейцарские часы.