PostgreSQL: auto_explain, pg_stat_statements и простая APM-диагностика запросов

Когда говорят «APM для PostgreSQL», многие представляют отдельный агент, трассировку, дашборды и тонны метрик. Но в реальности для большинства задач (найти самые дорогие запросы, понять, почему они дорогие, подтвердить эффект оптимизации) часто хватает встроенных инструментов: pg_stat_statements, auto_explain и аккуратно настроенного логирования через log_min_duration_statement.

Ниже — рабочая схема «минимальной APM» для админов/DevOps: что включить, как не утопить сервер логами, как связать «топ запросов» со «странным планом» и как читать EXPLAIN (ANALYZE, BUFFERS) так, чтобы это приводило к конкретным действиям.

Какие задачи решают pg_stat_statements и auto_explain

pg_stat_statements отвечает на вопрос: какие запросы в сумме съели больше всего времени/IO/вызовов. Это агрегированная статистика по «нормализованным» запросам (с параметрами вместо литералов), удобная для поиска долгих трендов.

auto_explain отвечает на другой вопрос: почему конкретный запрос медленный прямо в момент выполнения. Он автоматически пишет в лог план выполнения для запросов, которые превышают заданный порог времени.

Вместе они дают удобный цикл:

• Находим «подозреваемых» в pg_stat_statements.
• Ловим планы этих запросов в бою через auto_explain (с порогом, чтобы не зашуметь логи).
• Подтверждаем гипотезы через EXPLAIN (ANALYZE, BUFFERS) на стенде или в безопасное окно.

Включаем pg_stat_statements: расширение, параметры, перезагрузка

pg_stat_statements требует подключения в shared_preload_libraries, то есть перезапуска PostgreSQL. Лучше планировать это как небольшое техокно.

1) Подключение в shared_preload_libraries

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Если у вас уже есть значения, добавьте через запятую:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

2) Базовые настройки pg_stat_statements для продакшена

Ниже — практичный минимум: даёт хорошую видимость и обычно не создаёт неприятных сюрпризов.

# postgresql.conf
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

• pg_stat_statements.max — сколько уникальных «шаблонов запросов» хранить. 10k обычно достаточно; если приложений много и запросы разнообразные, можно поднять.
• pg_stat_statements.track = top — считаем запросы верхнего уровня (то, что реально пришло от приложения).
• pg_stat_statements.track_utility = off — не засоряем статистику DDL/служебными командами (при расследованиях можно временно включать).
• pg_stat_statements.save = on — сохранять статистику при рестарте (полезно для длительной диагностики).

3) Рестарт и создание расширения

sudo systemctl restart postgresql

Внутри нужной базы (выполняется один раз на базу):

psql -d yourdb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

4) Быстрые запросы к pg_stat_statements (топ по времени и IO)

Топ по суммарному времени (полезен, когда «мелкие, но частые» съедают сервер):

psql -d yourdb -c "SELECT round(total_exec_time::numeric, 2) AS total_ms, calls, round(mean_exec_time::numeric, 2) AS mean_ms, rows, shared_blks_hit, shared_blks_read, temp_blks_read, temp_blks_written, left(query, 120) AS query_sample FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;"

Топ по среднему времени (полезен для редких, но «тяжёлых» запросов):

psql -d yourdb -c "SELECT round(mean_exec_time::numeric, 2) AS mean_ms, calls, round(total_exec_time::numeric, 2) AS total_ms, shared_blks_read, temp_blks_written, left(query, 120) AS query_sample FROM pg_stat_statements WHERE calls > 10 ORDER BY mean_exec_time DESC LIMIT 20;"

Дальше смысл «APM без агента» простой: вы регулярно смотрите эти срезы и фиксируете baseline. Если какой-то запрос внезапно начинает «расти» по total_exec_time, shared_blks_read или temp_blks_written, у вас есть зацепка для расследования.

auto_explain: ловим планы медленных запросов автоматически

auto_explain пишет планы выполнения в лог, когда запрос превысил порог. Это удобно, потому что «ручной» EXPLAIN часто не воспроизводит проблему: кэш прогрет, параметры другие, конкуренции нет, данные меньше.

1) Подключение auto_explain

Как и в случае с pg_stat_statements, удобнее подключить модуль в shared_preload_libraries и дальше управлять параметрами через конфиг или ALTER SYSTEM.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

2) Настройки auto_explain для продакшена (разумный старт)

# postgresql.conf
auto_explain.log_min_duration = '300ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off
auto_explain.log_verbose = off
auto_explain.log_nested_statements = off
auto_explain.log_format = text

• auto_explain.log_min_duration — основной «предохранитель». Начните с 300–500 мс. Если логов мало — снижайте. Если много — повышайте.
• auto_explain.log_analyze = on — добавляет фактические времена выполнения узлов плана.
• auto_explain.log_buffers = on — включает BUFFERS, чтобы видеть цену по IO.
• auto_explain.log_timing = off — уменьшает накладные расходы; детальный timing на загруженных системах может быть заметен.
• auto_explain.log_nested_statements = off — не уходим в глубину функций/триггеров без необходимости.

Смысл auto_explain не в том, чтобы логировать всё подряд, а в том, чтобы выхватывать «хвост» медленных запросов с планом и буферами. Тогда лог становится диагностическим инструментом, а не мусорной свалкой.

3) Временное включение auto_explain только для одной сессии

Если не хотите включать глобально, можно включать на время в конкретной сессии (например, при расследовании):

psql -d yourdb

LOAD 'auto_explain';

SET auto_explain.log_min_duration = '50ms';

SET auto_explain.log_analyze = on;

SET auto_explain.log_buffers = on;

Так планы в лог будут писать только запросы из этой сессии.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

log_min_duration_statement: «сетка» для медленных запросов

log_min_duration_statement — самый простой способ начать наблюдение: он пишет в лог сами запросы, которые выполнялись дольше заданного порога, но без плана.

Практичная схема: ставим умеренный порог, чтобы собрать фактуру, и уже затем точечно подключаем auto_explain, чтобы получить планы именно для «хвостов».

# postgresql.conf
log_min_duration_statement = 200

Здесь значение в миллисекундах. Важно не путать с log_statement: тот легко превращает лог в поток всех запросов.

Как выбрать порог

• Для OLTP-приложения часто начинают со 100–300 мс.
• Для аналитики/отчётов — 500–2000 мс, иначе будет слишком шумно.
• Если вы ловите деградацию во время инцидента, лучше временно снизить порог и ограничить окно сбора (например, 10–30 минут), чем жить с низким порогом постоянно.

Как читать EXPLAIN (ANALYZE, BUFFERS): быстрый чек-лист

Привычка, которая реально окупается: смотреть не только оценки планировщика, но и фактическое выполнение и работу с буферами.

Команда для ручной проверки:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Что выносить из вывода в первую очередь:

• Расхождение Estimated vs Actual. Если ожидали 100 строк, а получили 100000, почти всегда проблема в статистике, селективности условий, корреляции или «плохих» параметрах.
• BUFFERS: shared hit/read. Много read — упираетесь в дисковое чтение; много hit при большом времени — чаще упираетесь в CPU, сортировки, тяжёлые join’ы, вычисления.
• temp read/written. Если сортировка/хэш уходят во временные файлы — смотрите work_mem и реальный размер промежуточных наборов.
• Узлы Sort/HashAggregate/Hash Join. Частые источники памяти/времени, особенно при неверных оценках кардинальности.

Типовой пример: индекс есть, но не используется

Сценарий: в pg_stat_statements запрос часто встречается, среднее время растёт, а в auto_explain видно Seq Scan по большой таблице.

Что проверять по порядку:

• Условие реально соответствует индексу (колонка, оператор, порядок, выражение)?
• Нет ли неявных кастов (например, сравнение text и integer)?
• Статистика актуальна? Иногда достаточно ANALYZE или корректировки autovacuum-порогов.
• Селективность условия: если возвращается большая доля таблицы, Seq Scan может быть честно быстрее.

Если упираетесь в автоочистку и распухание таблиц/индексов, держите под рукой разбор по практике: настройка autovacuum и работа с индексами в PostgreSQL.

Склеиваем «топ запросов» и планы: рабочий процесс

Эту схему удобно делать как регулярную процедуру (например, раз в неделю) или во время деградации.

1. Снимите топ-20 по total_exec_time и топ-20 по mean_exec_time из pg_stat_statements.

2. Для 3–5 наиболее подозрительных запросов подберите порог и включите auto_explain.log_min_duration так, чтобы в лог попадали именно они, а не весь шум.

3. Соберите планы из логов. Отметьте узлы, которые занимают большую часть времени, и блоки BUFFERS.

4. Сформулируйте гипотезу: индекс, переписывание запроса, изменение порядка join, увеличение статистики, настройка памяти, устранение N+1 на уровне приложения.

5. Подтвердите гипотезу на стенде или в низконагруженное окно через EXPLAIN (ANALYZE, BUFFERS), затем проверьте эффект по pg_stat_statements.

Если подозреваете, что медленность усиливается из-за очередей в коннектах и «шторма» подключений, полезно свериться с пуллингом: гайд по PgBouncer и пулу подключений PostgreSQL.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Частые ошибки и как их избежать

Слишком низкий порог логирования

Если поставить log_min_duration_statement = 0 или auto_explain.log_min_duration = '0ms', вы быстро получите раздувание логов и лишнюю нагрузку на диск и CPU, а разбор станет сложнее из-за шума.

Правило простое: начинайте с высокого порога, снижайте кратковременно и осознанно.

Неверная интерпретация «много shared hit»

Высокий shared_blks_hit не означает «всё хорошо». Это означает, что данные были в кеше, но запрос мог быть тяжёлым из-за вычислений на строках, больших сортировок, сложных join’ов или неверных оценок кардинальности.

Слепая вера в среднее время

mean_exec_time сглаживает хвосты. Для эксплуатационной диагностики важны именно хвосты: редкие медленные выполнения из-за холодного кэша, конкуренции, autovacuum, чекпоинтов, роста данных. Здесь и помогает auto_explain с порогом и/или временное снижение log_min_duration_statement.

Минимальная «APM-конфигурация» для PostgreSQL: итоговый набор

Если нужно собрать всё в один практичный стартовый конфиг, обычно начинают так (дальше пороги подбираются под реальную нагрузку):

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

# pg_stat_statements
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

# slow query logging
log_min_duration_statement = 200

# auto_explain
auto_explain.log_min_duration = '300ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off
auto_explain.log_nested_statements = off

Что делать, если медленно, а план «нормальный»

Иногда EXPLAIN (ANALYZE, BUFFERS) показывает «приличный» план, но пользователи всё равно жалуются. Часто причина не в оптимизаторе, а вокруг него:

• Блокировки: запрос ждёт, а не исполняется.
• IO/FS проблемы: всплески latency на диске, конкуренция с бэкапом, перегруженный storage.
• Чекпоинты: просадки из-за записи грязных страниц.
• Параллелизм приложения: слишком много одновременных тяжёлых запросов, агрессивный пул подключений.

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

Финальные рекомендации

Связка «pg_stat_statements + auto_explain + log_min_duration_statement» — хороший базовый APM-слой без внешних компонентов. Он прост в сопровождении и быстро окупается в эксплуатации.

• Держите pg_stat_statements включённым постоянно и снимайте «топы» регулярно.
• log_min_duration_statement используйте как лёгкий радар; не ставьте слишком низко на постоянной основе.
• auto_explain включайте с порогом и BUFFERS, чтобы видеть реальную цену по IO.
• Оптимизацию делайте итеративно: нашли → поймали план → исправили → проверили по статистике.

Если вы размещаете PostgreSQL на отдельном сервере под базу и хотите предсказуемые ресурсы под нагрузку, практичнее всего использовать VDS и держать сбор логов/метрик под контролем. Для небольших проектов и тестовых окружений иногда достаточно виртуального хостинга (если требования к СУБД и доступам это допускают).