Все предыдущие уроки модуля были про как Postgres борется с dead tuples. Этот — про то, как заметить, что борьба идёт не так. Без мониторинга bloat растёт молча: запросы постепенно замедляются, диск медленно заполняется, и в один прекрасный день — алерт «БД упала с no space left». Хороший мониторинг ловит проблему за дни или недели до этого момента.

Инструментов два эшелона:

1. Дешёвые, всегда доступные: pg_stat_user_tables, pg_class, pg_stat_activity. Дают приближённую картину почти в реальном времени, не нагружают базу. С них начинают.
2. Точные, тяжёлые: extension pgstattuple. Делает полный скан страниц, возвращает реальный bloat в байтах. Запускать раз в сутки на топ-N таблиц.

Уровень 1: pg_stat_user_tables

Системная вьюшка, в которой по каждой таблице есть приближённые счётчики:

Главная метрика — dead ratio = n_dead_tup / (n_live_tup + n_dead_tup). На здоровой OLTP-таблице это число должно быть в районе 0.05-0.15 (5-15%). Если оно стабильно > 0.3 — bloat растёт, autovacuum не справляется.

Топовый запрос диагностики:

В pglite после инициализации этот запрос показывает таблицы customers и orders с почти нулём dead — потому что autovacuum в pglite не работает. Но добавим UPDATE:

После 20K UPDATE на таблице 100K строк ожидаем dead_pct ≈ 17-20% — типичный сигнал «autovacuum давно не приходил».

Уровень 2: pgstattuple

Extension pgstattuple (поставляется в contrib-пакетах) делает то, чего pg_stat_user_tables не может: проходит по всем страницам таблицы и считает реальное распределение байтов:

Пример вызова:

SELECT * FROM pgstattuple('orders');

Это сканирует всю таблицу — на 100 GiB занимает минуты и читает все страницы. Поэтому на production обычно ставится ночное джоба, которая обходит топ-20 таблиц по размеру и пишет результаты в табличку для дашборда.

Для быстрой оценки без полного скана есть pgstattuple_approx (PG 9.5+):

SELECT * FROM pgstattuple_approx('orders');

Использует Visibility Map, чтобы пропускать all-visible-страницы — на холодных таблицах работает в сотни раз быстрее, с погрешностью в пределах нескольких процентов. На современных кластерах это умолчание.

В pglite extension pgstattuple не установлен по умолчанию, поэтому это упражнение мы делаем умозрительно — но в реальной БД это первый инструмент после pg_stat_user_tables показал подозрительно высокий dead ratio.

Оценочный запрос без extension

Иногда extension’ы недоступны (managed service, политика безопасности). Тогда — оценочный bloat. В сообществе используется запрос из check_postgres / pgexperts, который считает «ожидаемый» размер таблицы исходя из reltuples, средней длины кортежа и оверхедов, и сравнивает с реальным pg_relation_size.

Упрощённая версия:

Множитель 100 — это «среднее число байт на строку», и его нужно заменить реальной средней длиной для вашей таблицы. Полноценные query из pgexperts используют pg_attribute для подсчёта более точного значения; они громоздкие, в этот урок их не вставим, но в production-репозитории такой запрос обычно живёт в monitoring/bloat.sql.

Какие алерты ставить

Минимальный набор алертов для production:

1. n_dead_tup > 1_000_000 на пользовательской таблице. Корректировать порог под размер БД.
2. dead_ratio > 30% на таблице с n_live_tup > 100_000. Маленькие таблицы пропускаем (там 30% — пустяк).
3. last_autovacuum IS NULL или last_autovacuum < now() - 24h для таблицы с n_dead_tup > 100_000. Значит autovacuum не приходил — нужно расследовать.
4. age(relfrozenxid) > 100M для любой таблицы (см. урок про wraparound). Половина default autovacuum_freeze_max_age — alert, ¾ — критикал.
5. pg_stat_activity.state = 'idle in transaction' старше 10 минут. Это первый подозреваемый при любой проблеме с vacuum’ом.
6. pg_replication_slots.xmin старше 1 часа. Слот держит xmin → vacuum не отгружает мусор.
7. Скорость роста pg_database_size: если бд растёт быстрее, чем за прошлую неделю, и нет очевидного источника — bloat. Trend-alert работает лучше пороговых.

В Prometheus/Grafana мире эти алерты обычно выражаются через postgres_exporter: он экспортирует pg_stat_user_tables_n_dead_tup, pg_stat_user_tables_last_autovacuum, и pg_class_age_relfrozenxid. На них и навешиваются alertmanager rules.

Daily query: топ-bloated таблицы

В production-репозитории часто живёт скрипт, который ежедневно (cron / pg_cron) делает следующее:

1. Берёт топ-20 пользовательских таблиц по pg_relation_size.
2. На каждой запускает pgstattuple_approx.
3. Пишет результаты в служебную таблицу monitoring.bloat_history.
4. Если для какой-то таблицы dead_tuple_percent рос больше N дней подряд — алерт инженеру дежурному.

Без такого скрипта вы узнаёте о проблеме когда диск кончился. С ним — за неделю до того, как это станет проблемой.

Чек-лист

• Эшелон 1 — pg_stat_user_tables: дёшево, постоянно. n_dead_tup, dead_ratio, last_autovacuum — основные метрики.
• Эшелон 2 — pgstattuple/_approx: точно, тяжело. На топ-20 таблиц ночью.
• Алерты: dead_ratio > 30%, last_autovacuum > 24h, age(relfrozenxid) > 100M, idle in transaction > 10 min, replication slot xmin > 1h.
• При тревоге: сначала проверять long transactions и replication slots, потом тюнинг autovacuum, и только потом — ручной VACUUM/VACUUM FULL/pg_repack.
• Daily script для топ-bloated: pgstattuple_approx → history → trend-alert.
• Без мониторинга bloat не виден до момента «no space left». С мониторингом — за дни/недели.