Карта pg_stat_* и pg_stat_statements

Две природы статистики

Представления pg_stat_* делятся на два сорта, и путать их - частая ошибка при диагностике.

• Мгновенный снимок - что происходит сейчас. pg_stat_activity (по строке на backend), pg_locks, pg_stat_replication (см. streaming-replication), pg_stat_progress_vacuum. Читаешь - видишь текущее состояние.
• Накопленные счётчики - сумма с момента сброса статистики. pg_stat_user_tables, pg_statio_user_tables, pg_stat_database, pg_stat_statements. Само число мало о чём говорит; смысл - в разнице двух замеров (дельте) за интервал.

Главное следствие: по накопленным счётчикам нельзя сказать «сейчас плохо». Можно сказать «за час между замерами выросло вот это».

Кто что делает прямо сейчас

pg_stat_activity - первый экран при «база тормозит». Строка на каждое соединение: state (active / idle / idle in transaction), текущий query, время начала, wait_event.

-- активные дольше 30 секунд и зависшие в транзакции

SELECT pid, state, wait_event_type, wait_event,

       now() - xact_start AS xact_age, left(query, 60) AS q

FROM pg_stat_activity

WHERE state <> 'idle'

ORDER BY xact_age DESC NULLS LAST;

idle in transaction с большим xact_age - тревога: такой backend держит горизонт (см. transaction-horizon) и мешает vacuum, даже ничего не делая.

Здоровье таблиц

pg_stat_user_tables отвечает на «нужен ли vacuum» и «берётся ли индекс»: n_live_tup/n_dead_tup, last_autovacuum, seq_scan против idx_scan. pg_statio_user_tables - про кеш: heap_blks_hit против heap_blks_read - попадания в буфер против чтений с диска (см. buffer-states).

Поиск медленного SQL: pg_stat_statements

Расширение агрегирует выполнение по нормализованному тексту: запросы WHERE id = 1 и WHERE id = 2 схлопываются в один WHERE id = $1. Поэтому одно тяжёлое место видно как одна строка с суммой по всем вызовам - в отличие от лога, где это тысячи отдельных записей.

SELECT queryid, calls, round(total_exec_time) AS total_ms,

       round(mean_exec_time, 1) AS mean_ms, left(query, 70) AS q

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 10;

Сортируй по total_exec_time (суммарное время), а не по mean - запрос на 2 мс, вызванный миллион раз, съедает больше, чем разовый отчёт на 10 секунд. Это и есть «виновник» нагрузки.

Дополняет картину log_min_duration_statement - порог, выше которого каждый медленный запрос пишется в лог сервера целиком, с параметрами. pg_stat_statements говорит «какой класс запросов жрёт время», лог - «вот конкретный вызов с этими значениями».

Дальше: настройка памяти под найденные запросы - memory-config; переписывание плохих запросов - anti-patterns.