Мониторинг и slow queries

Опирается на правила: PG-MON-001 … PG-MON-094 из PostgreSQL Style Guide → раздел Мониторинг и slow queries.

Важно знать

• pg_stat_statements — топ запросов. Требует shared_preload_libraries + рестарт.
• auto_explain — автоматический EXPLAIN на медленных. Overhead 5-20%.
• log_min_duration_statement: 500ms-1s в проде.
• pg_stat_activity — текущие запросы, алёрты на xact_age > 5 минут.
• pg_stat_user_tables — bloat, last_autovacuum.
• Cache hit ratio > 95% — иначе мало RAM.
• Цикл диагностики: activity → statements → IO → bloat → replication → OS.

«У нас всё медленно» — самый частый репорт. Без мониторинга — разговор «по ощущениям». UCP формулирует что включить в проде, что мониторить.

pg_stat_statements

PG-MON-001..004:

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

CREATE EXTENSION pg_stat_statements;

Топ-N запросов:

-- по общему времени
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;

-- по средней латентности
ORDER BY mean_exec_time DESC

-- по IO
ORDER BY (shared_blks_read + shared_blks_dirtied) DESC

Запросы нормализованы: SELECT * FROM o WHERE id = $1 — параметры → placeholder, одинаковые агрегируются.

SELECT pg_stat_statements_reset(); — сбросить для измерения за период.

auto_explain

PG-MON-010..012: автоматический EXPLAIN на медленных.

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 имеет overhead 5-20% на медленные запросы (но это уже плохо).

log_min_duration_statement

PG-MON-020..021: лог медленных.

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

Прод: 500ms-1s. Снижение до 100ms = море логов.

Не путать с auto_explain — это только текст и время. auto_explain — план.

pg_stat_activity

PG-MON-030..032: текущие запросы.

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 минут — долгая TX.
• wait_event_type = 'Lock' стабильно — лок-очередь.
• state = 'idle in transaction' AND xact_age > 1 минута — забытая TX.

pg_locks

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 / indexes

PG-MON-040..043:

-- таблицы
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;

-- индексы
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;   -- неиспользуемые, кандидаты на DROP

• n_dead_tup/n_live_tup — bloat.
• last_autovacuum — работает?
• n_tup_upd/n_tup_hot_upd — HOT-ratio.
• idx_scan = 0 после месяца — кандидат на DROP.

PG14+: pg_stat_wal — генерация WAL. pg_replication_slots — replication slots.

Connection pool

PG-MON-050:

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 стабильно > 5 — забытые TX.

Дашборд

PG-MON-060: минимальный набор.

Метрика	Источник	Алёрт
TPS	pg_stat_database.xact_commit + xact_rollback	падение от baseline
Connections used	pg_stat_activity.state	> 80% от max_connections
Long transactions	pg_stat_activity.xact_age max	> 5 минут
Replication lag	pg_stat_replication.replay_lag	> 30 сек
WAL throughput	pg_stat_wal.wal_bytes rate	резкий рост
Cache hit ratio	pg_stat_database.blks_hit / (blks_hit + blks_read)	< 95%
Checkpoints	pg_stat_bgwriter.checkpoints_req	растёт
Disk size	pg_database_size()	нестандартный рост
autovacuum lag	pg_stat_user_tables.last_autovacuum	> 1 дня на горячих
Bloat	pgstattuple	> 30% на горячих

Для Spring Boot — micrometer-registry-prometheus + pg-stat-statements-exporter дают всё это автоматически.

Цикл диагностики «медленно»

PG-MON-070: алгоритм.

1. pg_stat_activity — сейчас идут долгие запросы? Блокировки?
2. pg_stat_statements — топ-10 по total_exec_time за час. Что нового?
3. pg_stat_database.blks_read rate — disk IO растёт?
4. pg_stat_user_tables.n_dead_tup — bloat растёт?
5. pg_stat_replication — replication lag?
6. OS — iostat/top/vmstat — disk, CPU, RAM на пределе?
7. Сеть — ss -t state established — connections от приложения растут?

Что должно быть включено

PG-MON-080:

Всегда:

• pg_stat_statements.
• log_min_duration_statement = '1s'.
• track_activity_query_size = 4096 (default 1024 режет длинные).
• track_io_timing = on.

Опционально:

• auto_explain с log_min_duration = '500ms'.
• log_lock_waits = on.
• log_temp_files = 0 — лог temp-files (sort на диск).

Что запрещено

Антипаттерн	Правило	Что взамен
Не включать pg_stat_statements	PG-MON-090	включи сейчас
log_min_duration_statement = 0 в проде	PG-MON-091	500ms-1s
Только TPS и connections в дашборде	PG-MON-092	+ long TX, bloat, replication
auto_explain.log_analyze = on на high-throughput	PG-MON-093	debug-окно
pg_stat_statements_reset без согласования	PG-MON-094	потеряли историю
Игнорировать idle in transaction > 1 минута	PG-MON-031	алёрт
Cache hit ratio < 95% без действия	PG-MON-060	больше RAM или индексы

Куда дальше

• PG → Мониторинг — нормативные формулировки.
• Расширения — pg_stat_statements, pgstattuple.
• EXPLAIN ANALYZE — анализ конкретного плана.
• VACUUM — last_autovacuum.
• Блокировки — pg_locks.
• WAL — pg_stat_wal.
• Observability → metrics — Micrometer экспорт.