Представьте, запросы к PostgreSQL резко замедлились после релиза или роста данных. Как вы будете искать причину?

Расследование резкого замедления запросов в PostgreSQL

Резкое замедление после релиза или роста данных — разные сценарии, но подход один: сначала измеряем, потом выдвигаем гипотезы, не наоборот. Ниже — пошаговый playbook.

Фаза 1: Установить факты за 5 минут

-- 1. Кто тормозит прямо сейчас?
SELECT pid, now() - query_start AS age, state,
       wait_event_type, wait_event, left(query, 200) AS q
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY age DESC LIMIT 20;

-- 2. Есть ли блокировки?
SELECT bl.pid AS blocked, a.query AS blocked_q,
       kl.pid AS blocker, ka.query AS blocker_q
FROM pg_blocking_pids(bl.pid) AS kl(pid)
JOIN pg_stat_activity bl ON bl.pid = ANY(pg_blocking_pids(bl.pid))
JOIN pg_stat_activity a  ON a.pid = bl.pid
JOIN pg_stat_activity ka ON ka.pid = kl.pid;

-- 3. Сколько соединений?
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

Ответы на эти три вопроса сразу показывают: блок на локе → смотрим что держит лок; много idle in transaction → утечка незакрытых транзакций в приложении; max_connections исчерпан → нужен пуллер или срочная перезагрузка.

Фаза 2: Определить «плохие» запросы

-- Топ по total_exec_time (нужен pg_stat_statements)
SELECT queryid, calls, round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       left(query, 120) AS q
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Сравниваем mean_exec_time с историческими данными (Grafana / pgBadger)
-- Резкий рост среднего — план изменился или статистика устарела

Фаза 3: Проверить план конкретного запроса

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.email, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

Что смотреть в выводе:

• Rows estimated vs actual: расхождение в 100x — устаревшая статистика, запустить ANALYZE.
• Seq Scan на большой таблице: нет индекса или plannerне использует его (см. random_page_cost).
• Buffers: shared read=huge: данные не в кэше, нагрузка на диск. Проверить shared_buffers и реальное потребление RAM.
• Sort Method: external merge: work_mem не хватает, сортировка идёт через диск.
• Hash Batches: >1: hash join сделал spill на диск из-за малого work_mem.

Фаза 4: Гипотезы по сценарию «после релиза»

• Новый код добавил N+1 запросы: в pg_stat_statements появилось 10K calls/s запросов, которых раньше не было. Ищем по queryid или по query текстом.
• Миграция взяла лок и держит его: долгая транзакция ALTER TABLE. Проверяем pg_locks + pg_stat_activity.
• Новый запрос без индекса стал делать Seq Scan: в pg_stat_user_tables вырос seq_scan на нужной таблице.
• Изменился параметр приложения: например, limit убрали → запрос возвращает 1M строк вместо 100.

Фаза 5: Гипотезы по сценарию «после роста данных»

• Планировщик переключился с Index Scan на Seq Scan: при росте таблицы selectivity индекса упала, и планировщик решил что Seq дешевле. Решение: ANALYZE + проверить work_mem/random_page_cost.
• Bloat: таблица выросла физически из-за удалений без vacuum. SELECT n_dead_tup FROM pg_stat_user_tables.
• IOPS лимит в облаке: EBS gp2 burst balance исчерпан при росте объёма IO.
• Checkpoint storm: при росте write-нагрузки checkpoint стал слишком частым. Проверить: SELECT * FROM pg_stat_bgwriter;, если checkpoints_req растёт — увеличить max_wal_size.

Фаза 6: Быстрые remediation

-- Устаревшая статистика
ANALYZE VERBOSE orders;

-- Убить зависшую транзакцию
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

-- Принудительный vacuum
VACUUM ANALYZE orders;

-- Если планировщик упорно выбирает Seq Scan (временный workaround)
SET enable_seqscan = off;  -- только для отладки, не в production постоянно

Инструменты мониторинга

• pgBadger: анализ логов PostgreSQL, топ медленных запросов по времени.
• pg_activity: htop-подобный real-time монитор запросов.
• Grafana + postgres_exporter: метрики pg_stat_statements, pg_stat_bgwriter, pg_stat_replication в реальном времени.
• auto_explain: автоматически логирует EXPLAIN для запросов дольше порога.

-- auto_explain в postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

Подводные камни

• EXPLAIN ANALYZE выполняет запрос по-настоящему — для DELETE/UPDATE используйте BEGIN ... ROLLBACK, иначе изменения применятся.
• pg_stat_statements.max по умолчанию 5000 — при большом количестве уникальных запросов старые вытесняются и история теряется.
• Параметр work_mem глобальный, но умножается на количество параллельных sort/hash операций в запросе — осторожно при увеличении.
• Bloat индексов не показывается в pg_stat_user_tables — нужен pgstattuple или регулярный REINDEX CONCURRENTLY.
• После ANALYZE план может измениться и стать хуже в редких случаях — сохраняйте старый план через pg_hint_plan для отката.
• checkpoint_completion_target по умолчанию 0.9 — хорошее значение; не снижайте его, это размажет I/O от checkpoint.
• Рост числа соединений сам по себе деградирует PostgreSQL из-за lock manager overhead — держите активные соединения в пределах 2–4x CPU cores.
• В replica-сценарии медленные запросы на replica могут быть вызваны replication lag — replica читает старые страницы, не успевающие примениться.

• Сразу обвинять PostgreSQL, не проверив соседние слои системы
• Чинить симптом без минимального воспроизведения и evidence
• Не учитывать версии, конфигурацию, окружение и recent changes

• Умеет локализовать проблему вокруг PostgreSQL
• Двигается от симптома к гипотезам и проверкам
• Отличает баг инструмента от ошибки использования или окружения