← назад к разделу

Когда приложение начинает тормозить, первый вопрос — «что происходит в базе?». Без инструментов ответ строится на догадках. PostgreSQL накапливает статистику о каждом запросе, каждой таблице, каждом соединении — нужно только знать, где смотреть.

Топ медленных запросов — pg_stat_statements

По умолчанию PostgreSQL не запоминает, какие запросы выполнялись. Это меняет расширение pg_stat_statements: оно накапливает статистику по всем запросам — сколько раз вызывался, сколько времени занял суммарно и в среднем, сколько данных читал с диска.

Чтобы включить, нужно добавить расширение в конфигурацию и перезапустить сервер:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

После этого можно смотреть топ запросов по суммарному времени:

SELECT
    substring(query, 1, 80) AS query,
    calls,
    round(total_exec_time::numeric, 0) AS total_ms,
    round(mean_exec_time::numeric, 1) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

Запросы нормализованы: конкретные значения заменяются на $1, $2 — поэтому один и тот же запрос с разными параметрами группируется в одну строку.

Если нужно измерить изменение за конкретный период — сбросьте статистику в начале:

SELECT pg_stat_statements_reset();

Автоматический план медленного запроса — auto_explain

pg_stat_statements показывает какой запрос медленный. Но чтобы понять почему — нужен план выполнения (EXPLAIN ANALYZE). Расширение auto_explain делает это автоматически: как только запрос превышает порог по времени, план попадает в лог PostgreSQL.

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = json

Опция log_analyze = on добавляет реальные числа выполнения (а не только оценки планировщика), но создаёт дополнительную нагрузку — около 5–20% на медленные запросы. На высоконагруженных системах включайте только в окне диагностики, не постоянно.

Лог медленных запросов — log_min_duration_statement

Самый простой способ записать медленные запросы в лог — параметр log_min_duration_statement. Каждый запрос, который занял больше порога, попадёт в файл лога с текстом и временем выполнения.

log_min_duration_statement = '500ms'
log_line_prefix = '%m [%p] %q%u@%d '

В продакшене разумный порог — 500 мс до 1 секунды. Значение 100 мс создаст поток логов, который сложно анализировать.

Отличие от auto_explain: этот параметр записывает только текст запроса и время. auto_explain пишет полный план — это нужно при анализе конкретной проблемы.

Что происходит прямо сейчас — pg_stat_activity

pg_stat_activity показывает все активные сессии: что выполняется, сколько времени идёт транзакция, есть ли ожидание блокировки.

SELECT
    pid,
    now() - xact_start AS xact_age,
    now() - query_start AS query_age,
    state,
    wait_event_type, wait_event,
    substring(query, 1, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_age DESC LIMIT 20;

На что смотреть:

  • xact_age > 5 минут — транзакция висит слишком долго и удерживает ресурсы.
  • wait_event_type = 'Lock' стабильно у нескольких процессов — очередь блокировок.
  • state = 'idle in transaction' и xact_age > 1 минуты — соединение открыло транзакцию и забыло закрыть. Это не просто неряшливость: такая транзакция мешает autovacuum и накапливает bloat.

Кто кого блокирует — pg_locks

Если видите wait_event_type = 'Lock', можно найти конкретного виновника:

SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl_lock ON bl_lock.pid = blocked.pid AND NOT bl_lock.granted
JOIN pg_locks gr_lock ON gr_lock.locktype = bl_lock.locktype
    AND gr_lock.granted
JOIN pg_stat_activity blocking ON blocking.pid = gr_lock.pid;

Здоровье таблиц и индексов — pg_stat_user_tables

PostgreSQL обновляет строки не на месте, а оставляя старые версии (это MVCC). Со временем накапливаются «мёртвые» строки — bloat. pg_stat_user_tables показывает, насколько таблица захламлена и когда последний раз отработал autovacuum:

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       n_tup_upd, n_tup_hot_upd,
       seq_scan, idx_scan
FROM pg_stat_user_tables;

Что важно:

  • n_dead_tup / n_live_tup — доля мёртвых строк. Если растёт — autovacuum не справляется.
  • last_autovacuum — давно было? На активных таблицах должен запускаться каждые несколько минут или часов.
  • n_tup_hot_upd / n_tup_upd — чем выше доля HOT-обновлений, тем эффективнее работает таблица (обновление без изменения индексов).

Для индексов отдельная таблица. Индексы, которые не используются — кандидаты на удаление:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;   -- не использовался с момента последнего сброса статистики

Соединения — connection pool

Каждое соединение к PostgreSQL потребляет память и ресурс. Если приложение открывает больше соединений, чем настроено через max_connections, новые будут отклонены. Смотреть состояние соединений:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Состояния: active (выполняет запрос), idle (ждёт следующего), idle in transaction (в транзакции, но молчит), idle in transaction (aborted) (транзакция прервана, но не закрыта).

Сигналы тревоги:

  • active близко к max_connections — пул исчерпан, следующие запросы будут ждать или падать.
  • idle in transaction стабильно больше пяти — где-то в приложении транзакции не закрываются.

Ключевые метрики на дашборде

Минимальный набор метрик, которые стоит вывести в систему мониторинга:

МетрикаИсточникКогда смотреть
TPS (транзакций/сек)pg_stat_database.xact_commit + xact_rollbackпадение от обычного уровня
Используемые соединенияpg_stat_activity.state> 80% от max_connections
Долгие транзакцииpg_stat_activity.xact_age> 5 минут
Отставание репликиpg_stat_replication.replay_lag> 30 секунд
Cache hit ratiopg_stat_database.blks_hit / (blks_hit + blks_read)< 95% — мало RAM
Checkpoint requestspg_stat_bgwriter.checkpoints_reqстабильный рост
Размер базыpg_database_size()неожиданный скачок
Последний autovacuumpg_stat_user_tables.last_autovacuum> 1 дня на активных таблицах
Bloatpgstattuple> 30% на активных таблицах

Cache hit ratio — особая метрика: если она ниже 95%, PostgreSQL читает данные с диска вместо кэша. Первое средство — добавить RAM и увеличить shared_buffers.

Экспорт метрик в Prometheus делается через postgres_exporter — он подключается к базе и публикует всё перечисленное в формате, который понимают Grafana и Alertmanager.

Что включить в продакшене

Базовый минимум, который должен быть везде:

  • pg_stat_statements — без него непонятно, какие запросы тяжёлые.
  • log_min_duration_statement = '1s' — медленные запросы в лог.
  • track_activity_query_size = 4096 — стандартное значение 1024 обрезает длинные запросы в pg_stat_activity.
  • track_io_timing = on — добавляет время IO к статистике запросов.

Дополнительно, если нужна глубже диагностика:

  • auto_explain с log_min_duration = '500ms' — планы медленных запросов в лог.
  • log_lock_waits = on — логировать ожидания блокировок.
  • log_temp_files = 0 — логировать запросы, которые сбрасывают временные файлы на диск (признак нехватки памяти для сортировки).

Как разбирать «всё медленно»

Порядок диагностики, когда пришёл репорт о деградации:

  1. pg_stat_activity — есть ли прямо сейчас долгие запросы или блокировки?
  2. pg_stat_statements — топ-10 по суммарному времени за последний час. Что изменилось?
  3. pg_stat_database.blks_read rate — disk I/O вырос?
  4. pg_stat_user_tables.n_dead_tup — bloat накопился?
  5. pg_stat_replication — реплика не отстаёт?
  6. OS — iostat, top, vmstat — диск, CPU или RAM на пределе?
  7. Сеть — количество соединений от приложения растёт?

Часто причина находится на первых двух шагах.

Частые ошибки

Не включать pg_stat_statements — без него приходится догадываться о причинах нагрузки. Это первое, что нужно после установки PostgreSQL.

Ставить log_min_duration_statement = 0 — логируются все запросы, лог разрастается, полезное тонет в шуме.

Смотреть только TPS и connections — этого мало. Долгие транзакции и bloat убивают производительность постепенно, без видимого пика TPS.

Держать auto_explain.log_analyze = on постоянно на нагруженных системах — создаёт накладные расходы. Включать только во время диагностики.

Сбрасывать pg_stat_statements без предупреждения — теряется история, на которую смотрят коллеги.

Коротко

  • pg_stat_statements — главный инструмент: топ запросов по времени, числу вызовов, IO. Требует shared_preload_libraries и перезапуска сервера.
  • auto_explain автоматически пишет план медленного запроса в лог; на высоком трафике включать только для диагностики.
  • log_min_duration_statement = '500ms–1s' в продакшене — быстрый способ поймать медленные запросы без дополнительных расширений.
  • pg_stat_activity показывает текущие сессии: долгие транзакции, блокировки, «забытые» соединения в idle in transaction.
  • pg_stat_user_tables — состояние таблиц: bloat, последний autovacuum, соотношение HOT-обновлений.
  • Cache hit ratio должен быть выше 95%; ниже — не хватает RAM.
  • Диагностика «медленно»: activity → statements → IO → bloat → replication → OS.

Что почитать дальше

  • EXPLAIN ANALYZE — как читать план выполнения запроса.
  • VACUUM и bloat — почему мёртвые строки накапливаются и как с этим работать.
  • Блокировки — детальный разбор pg_locks и типов блокировок.
  • Индексы — когда индекс помогает, а когда мешает.