После четырёх уроков про MVCC у нас вырисовывается тёмная сторона картины: каждое изменение оставляет след. Если приложение пишет интенсивно, а VACUUM не успевает за ним — таблица постоянно растёт за счёт мёртвых версий, которые никто никогда не увидит.

Эта проблема называется bloat, и её мониторинг — обязательная часть production-эксплуатации Postgres.

Что такое dead tuple

После прошлых уроков определение очевидно:

• Live tuple — кортеж, чей xmin закоммитился и не имеет committed xmax (или его xmax > глобального xmin, и его может видеть какая-то транзакция).
• Dead tuple — кортеж, чей xmax закоммитился и меньше глобального xmin. Никакая текущая или будущая транзакция его не увидит. Место можно освободить.

Также dead — это кортежи с aborted xmin (например, ROLLBACK после большого INSERT).

Dead tuples занимают место и заставляют:

1. Seq Scan читать больше страниц (хотя строк живых столько же).
2. Index Scan проходить через больше «мёртвых» записей в индексе (визиты в heap, где tuple невидим).
3. Visibility map иметь все эти страницы как not-all-visible, ломая Index Only Scan.
4. WAL и backup’ы — иметь физически больший объём.

Bloat ratio

Самая удобная метрика:

bloat_ratio = dead_tuples / (live_tuples + dead_tuples)

Это доля dead среди всех физически лежащих кортежей. На свежей таблице после VACUUM это 0%. На сильно изменённой без VACUUM — может быть 50% и больше: «половина таблицы — мусор».

Грубая шкала тревоги:

• 0-10% — норма. VACUUM справляется.
• 10-30% — заметный bloat, autovacuum явно отстаёт, стоит посмотреть параметры (autovacuum_vacuum_scale_factor).
• >30% — серьёзная проблема. IO теряется впустую, нужно ручное вмешательство.
• >50% — катастрофа. Возможно, есть long-running query, блокирующая VACUUM, или таблица бessмыссленно перезаписывается.

Источник: pg_stat_user_tables

Базовый мониторинг доступен из коробки:

Это первое представление: процент мёртвых tuples в каждой таблице. Заметь, что n_dead_tup обновляется через ANALYZE (autoanalyze) — это оценка, не точное число.

Видно, что три прохода UPDATE’ов оставили примерно 3000 dead tuples (по 1000 на каждый, если не сработал HOT). Соотношение dead:live зависит от размера таблицы.

pgstattuple: точная статистика

Для серьёзной диагностики есть extension pgstattuple:

CREATE EXTENSION pgstattuple;

SELECT
  table_len,
  tuple_count,
  tuple_len,
  dead_tuple_count,
  dead_tuple_len,
  free_space,
  approx_free_percent
FROM pgstattuple('orders');

Это полный скан таблицы — даёт точные числа: сколько байт занято dead tuples, сколько free space. На большой таблице может быть медленно (~минута на 10 GiB). Для быстрой оценки используют pgstattuple_approx — она читает только sample страниц.

В pglite этого extension нет, но в production он необходим, когда n_dead_tup из pg_stat_user_tables кажется ненадёжным или для оценки результата VACUUM.

Что усиливает bloat

Главные факторы:

1. Long-running transactions — блокируют глобальный xmin, и VACUUM не может убрать dead tuples, чей xmax больше этого xmin. Один открытый BEGIN в psql — и за час dead-tuples в активной таблице накапливаются миллионами.

2. Repeating UPDATE’ы той же строки без HOT (например, индекс на updated_at, который трогается каждым UPDATE’ом). 1000 UPDATE/sec одной row = 1000 dead/sec, плюс 5x dead в индексах.

3. Слишком консервативный autovacuum_vacuum_scale_factor. По умолчанию 0.2 = VACUUM запускается, когда n_dead_tup > 0.2 × n_live_tup. Для таблицы в 100M строк это 20M dead tuples — между запусками. Для крупных таблиц рекомендуют выставлять явно (например, 0.05) и/или абсолютный порог.

4. Канал-перезапись (queue-like pattern): INSERT 1000 rows → process → DELETE 1000 rows → повтор. Каждый цикл оставляет 1000 dead tuples; если delete’ы не догоняются vacuum’ом, таблица растёт, хотя «полезный размер» = 1000 строк.

5. ROLLBACK после больших операций. INSERT 10M, потом ROLLBACK — на диске остаётся 10M dead tuples. Часто это не очевидно («ну я же откатил»), но physically данные остались.

Важный нюанс: обычный VACUUM не уменьшает размер файла на диске. Он только освобождает слоты внутри страниц для повторного использования. Чтобы реально вернуть место OS — нужен VACUUM FULL (требует exclusive lock и блокирует таблицу) или pg_repack (онлайн-альтернатива, extension).

Index bloat

У индексов своя проблема. Когда строка обновляется не через HOT — её старая запись в индексе становится «dead», но место в индексе не освобождается автоматически. B-tree-страница может содержать 100 dead entries и 10 live; индекс при этом раздут x10.

Мониторинг indexes:

Если индекс на маленькой колонке имеет размер, сравнимый с heap’ом — это явный bloat. Лечение: REINDEX (блокирующий, в Postgres 12+ есть REINDEX CONCURRENTLY).

Симптомы bloat в боевой ситуации

В production bloat проявляется как:

• EXPLAIN BUFFERS показывает большое Buffers: shared read=... для запросов, которые «должны» читать мало.
• pg_relation_size('table') растёт без roста реального содержимого (SELECT count(*) стабилен).
• Index Only Scan’ы перестают быть Index Only (Heap Fetches растёт).
• Median latency запросов растёт без видимой причины.

Первое, что смотрят:

SELECT pid, age(backend_xmin), state, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND age(backend_xmin) > 1000000
ORDER BY age(backend_xmin) DESC;

Если есть транзакции с большим xid_age — почти гарантированно они источник bloat’а. Дальше — найти и убить (pg_terminate_backend(pid)), потом запустить VACUUM.

Что дальше

Мы научились видеть проблему. В следующем модуле подробно разбираем VACUUM: что он делает, как настраивать autovacuum_* параметры, чем VACUUM FREEZE отличается от обычного, как работают transaction id wraparound и vacuumdb -j.

Чек-лист

• Dead tuple — кортеж с committed xmax (или aborted xmin), уже невидимый ни одной транзакции.
• Bloat ratio = dead / (live + dead). Норма < 10%, тревога > 30%, катастрофа > 50%.
• Основные источники: long-running transactions, UPDATE на индексируемых колонках, слабый autovacuum, queue-like patterns, большой ROLLBACK.
• Мониторинг: pg_stat_user_tables (n_dead_tup, оценка), pgstattuple (точно, медленно), pg_stat_user_indexes (для индексов).
• Обычный VACUUM освобождает слоты, но не возвращает место OS. Для shrink нужен VACUUM FULL или pg_repack.
• Индексы тоже распухают и требуют REINDEX периодически.
• Первый шаг диагностики: SELECT age(backend_xmin), ... FROM pg_stat_activity — ищем долгие транзакции.