Пятница, вечер. Один из эндпоинтов начал отвечать секунд по восемь вместо привычных двухсот миллисекунд. Графики в Grafana — зелёные. CPU спокойный, память на месте, диск не забит. По всем дашбордам база здорова. А она не здорова.

Знакомая ситуация: мониторинг показывает, что сервер жив, но не показывает, что внутри базы что-то медленно гниёт. Раздулась таблица. Появился индекс, которым никто не пользуется, но который тормозит каждый INSERT. Висит забытая транзакция и держит блокировку. Ничего из этого не «падает» — оно просто потихоньку делает базу хуже, пока в пятницу вечером не станет совсем плохо.

У меня для таких случаев годами жил файл queries.sql — свалка запросов, которые я копипастил в psql, когда что-то уже горело. Потом мы с коллегой собрали эти запросы в одно место. Ниже — те из них, которыми я реально пользуюсь. Все работают на голом PostgreSQL, без агентов и платных мониторингов, расширение нужно ровно в одном месте — и там оно нужно честно. Открываете psql и проверяете свою базу прямо сейчас.

Запросы рабочие на современных версиях PostgreSQL (проверялись на 13 и новее). Единственное версионное различие отмечено по ходу.

1. Самый дешёвый сигнал — мёртвые строки

PostgreSQL не удаляет строки физически. UPDATE и DELETE оставляют мёртвые версии строк (dead tuples), а убирает их потом autovacuum. Пока он не пришёл — или не успевает — мёртвые строки лежат в таблице, занимают место и заставляют каждый запрос пролистывать лишнее.

Самый быстрый способ увидеть масштаб — pg_stat_user_tables. Никаких расширений, доступно всегда:

SELECT
    schemaname,
    relname AS table,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

Примерный вывод:

 schemaname |  table   | n_live_tup | n_dead_tup | dead_ratio |     last_autovacuum
------------+----------+------------+------------+------------+--------------------------
 public     | orders   |   12041233 |    3502118 |       22.5 | 2026-05-12 03:14:09+00
 public     | sessions |     840112 |     791044 |       48.5 |

На что смотреть. dead_ratio выше 20% на большой таблице — повод разобраться. Строка sessions в примере особенно показательна: почти половина таблицы — это мусор, а last_autovacuum пустой, то есть autovacuum по ней не отрабатывал ни разу. Почему так бывает — вернёмся к этому в секции 5, там вся история сходится в одну точку.

Точное число даст расширение pgstattuple, но оно читает таблицу целиком — на сорокагигабайтной таблице это недёшево. Для регулярной проверки оценки из статистики достаточно.

Что делать. Если autovacuum просто не поспевает — настраивается порог срабатывания: autovacuum_vacuum_scale_factor для конкретной горячей таблицы можно опустить с дефолтных 0.2 до, скажем, 0.05. А вот уже накопившийся bloat обычным VACUUM не убрать — он помечает место как свободное, но не возвращает его операционной системе. Возвращает VACUUM FULL, но он берёт ACCESS EXCLUSIVE lock и переписывает таблицу целиком: на проде это значит, что таблица недоступна всё время операции. На живой системе вместо него — pg_repack: делает почти то же самое без долгой блокировки, ценой места на диске под копию и наличия первичного ключа.

2. Кто на самом деле съедает время сервера

Здесь понадобится расширение — pg_stat_statements. Скорее всего, оно у вас уже есть: его включают по умолчанию во многих сборках и почти во всех облаках. Если нет — shared_preload_libraries = 'pg_stat_statements' в postgresql.conf, рестарт, CREATE EXTENSION pg_stat_statements;. Оно того стоит: это самый полезный диагностический инструмент, который вообще есть в PostgreSQL из коробки.

Расширение копит статистику по каждому уникальному запросу: сколько раз вызван, сколько суммарно времени съел, сколько в среднем.

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

Ключевой момент — сортировка по total_exec_time, а не по среднему. Это контринтуитивно. Запрос, который отрабатывает за 4 мс, выглядит безобидно. Но если он вызывается два миллиона раз в сутки, он съедает сервера больше, чем трёхсекундный отчёт, который гоняют два раза в день. total_exec_time это ловит, mean_exec_time — нет. Колонка pct сразу показывает, какой процент всего времени БД уходит на конкретный запрос; обычно первые три-четыре строки — это и есть весь ваш perf-backlog.

Нюанс версий: в PostgreSQL 12 и старше колонки назывались total_time и mean_time, без _exec. С 13-й версии появилось разделение на planning и execution — отсюда total_exec_time и total_plan_time.

Запросы в выводе нормализованы: конкретные значения заменены на $1, $2, поэтому одинаковые по форме запросы с разными параметрами схлопываются в одну строку — это и нужно. Когда что-то поправили и хотите померить заново — SELECT pg_stat_statements_reset(); обнуляет накопленную статистику.

Дальше по найденному запросу — EXPLAIN (ANALYZE, BUFFERS), и вот там уже видно, где именно он буксует. Чтение плана — отдельная большая тема (и, скажу честно, разбирать глазами текстовый план на двести строк — то ещё удовольствие).

3. Таблицы, которые постоянно читают целиком

Sequential scan — это не зло. На маленькой таблице планировщик осознанно выбирает seq scan, потому что прочитать её целиком быстрее, чем лезть в индекс. Проблема начинается, когда целиком читают большую таблицу, и делают это часто.

SELECT
    schemaname,
    relname AS table,
    seq_scan,
    idx_scan,
    seq_tup_read,
    CASE WHEN seq_scan > 0
         THEN seq_tup_read / seq_scan
         ELSE 0
    END AS avg_rows_per_scan,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

seq_tup_read — сколько всего строк прочитано последовательными сканами. Если у большой таблицы это число огромное, а idx_scan рядом скромный — её регулярно перебирают целиком. avg_rows_per_scan показывает, насколько тяжёлый каждый отдельный скан.

Оговорка: этот запрос даёт кандидатов, а не диагноз. Он не скажет, какую колонку индексировать, — он только показывает, куда смотреть. Дальше всё равно EXPLAIN на конкретном запросе из секции 2: связка «вот запрос, который ест время» плюс «вот таблица, которую перебирают целиком» обычно сходится в одной точке, и там уже виден нужный индекс. И не бросайтесь индексировать таблицу на тысячу строк — для неё seq scan правильный, оставьте как есть.

4. Индексы, которые только мешают

Обратная сторона. Лишний индекс — это не «просто занимает место». Он замедляет каждый INSERT, UPDATE и DELETE по таблице (его тоже надо обновлять), занимает диск и вытесняет из кэша то, что реально нужно. А появляются такие индексы легко: добавили под фичу, фичу выпилили, индекс остался жить.

SELECT
    s.schemaname,
    s.relname AS table,
    s.indexrelname AS index,
    s.idx_scan,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

idx_scan = 0 — индексом ни разу не воспользовались. Из выборки я сразу выкидываю уникальные индексы и первичные ключи: они обеспечивают ограничения целостности, и неважно, что по ним не ходят SELECT-ы, — удалять их нельзя.

Две оговорки, без которых можно выстрелить себе в ногу. Первая: статистика считается с момента последнего сброса или старта сервера. Индекс под квартальный отчёт сегодня честно показывает ноль — а первого числа он внезапно нужен. Смотрите на статистику, накопленную хотя бы за пару недель, а лучше за месяц. Вторая: статистика использования индексов локальна для каждого узла. Индекс, который простаивает на мастере, может вовсю работать на реплике, куда вы отправили читающую аналитику. Проверяйте на всех нодах.

Если всё сошлось — DROP INDEX CONCURRENTLY, чтобы не блокировать таблицу на время удаления.

5. Транзакция, которая висит и тихо ломает всё вокруг

Самый недооценённый пункт. Открытая, но ничего не делающая транзакция — состояние idle in transaction — выглядит безобидно. На деле она держит блокировки и, что хуже, держит назад xmin horizon: autovacuum не может убрать мёртвые строки новее этой транзакции. То есть одна забытая транзакция (приложение взяло коннект, начало транзакцию и ушло думать) добавляет bloat по всей базе. Круг замкнулся — вот вам и пустой last_autovacuum из секции 1.

SELECT
    pid,
    state,
    now() - xact_start  AS xact_age,
    now() - query_start AS query_age,
    wait_event_type,
    left(query, 80)     AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 20;

xact_age — возраст транзакции. Если там idle in transaction и возраст в десятки минут — это ваш клиент, который забыл закоммитить. Лечится на уровне сервера параметром idle_in_transaction_session_timeout: поставьте минуту-другую, и PostgreSQL сам прибьёт такие сессии.

Второй запрос — кто кого блокирует. Когда что-то «зависло» прямо сейчас:

SELECT
    blocked.pid              AS blocked_pid,
    left(blocked.query, 60)  AS blocked_query,
    blocking.pid             AS blocking_pid,
    left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Слева — кто ждёт, справа — из-за кого. На загруженной базе это бывает целая цепочка: один держит, второй ждёт первого, третий ждёт второго. Функция pg_blocking_pids() доступна начиная с PostgreSQL 9.6.

Бонус: одна цифра за тридцать секунд

Если совсем некогда — хотя бы это:

SELECT
    round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

Доля чтений, которые попали в кэш, а не пошли на диск. Для OLTP-базы здоровое значение — 99% и выше. Просело до 90% — либо данным стало тесно в shared_buffers, либо какой-то запрос регулярно вычитывает пол-таблицы с диска (привет, секция 3).

Что с этим делать в понедельник

Соберите эти запросы в один файл и прогоните по основной базе — без повода, просто чтобы зафиксировать исходную точку. Дальше — раз в пару недель. Половина инцидентов уровня «в пятницу всё стало медленно» — это то, что неделю назад уже было видно в pg_stat_user_tables, просто никто туда не смотрел.

На самом деле гонять эти запросы руками скучно, и именно поэтому это не делается. Файл queries.sql есть у всех, открывают его раз в квартал, когда уже горит. У меня было точно так же.

В какой-то момент мы с коллегой устали копипастить и собрали из этих запросов отдельный экран в нашем инструменте — ide99, десктопной IDE для PostgreSQL. Health Screen внутри — это ровно то, что выше: bloat, топ запросов из pg_stat_statements, недоиспользуемые индексы, висящие транзакции. Посчитано по расписанию, показано одним списком, с понятными порогами и кнопкой «починить» там, где починка безопасна. Рядом — нормальный визуализатор EXPLAIN, потому что разбирать текстовый план на двести строк глазами невозможно. IDE бесплатная, с открытым кодом, ставится за полминуты: ide99.ru.

Дисклеймер: ide99 — наш проект, так что абзац выше читайте с поправкой на это. Но запросы из статьи самодостаточны и не зависят ни от какого инструмента — забирайте их себе в queries.sql и пользуйтесь, даже если ничего ставить не собираетесь. Для того и написано.

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