В модуле про VACUUM мы выяснили: lazy VACUUM возвращает место не на диск, а в FSM. Если ничего не делать ещё — таблица растёт, а на ней появляется bloat: соотношение «занимаемый размер на диске» к «полезный логический размер» становится не 1:1, а 2:1, 5:1, или хуже.

Bloat — не косметическая проблема. Раздутая таблица — это:

• Больше страниц для Seq Scan → дольше выполняются запросы.
• Меньше hit ratio в shared_buffers (больше «холодных» страниц).
• Дольше VACUUM (он обходит все страницы, не только живые).
• Раздутые индексы тоже снижают cache hit и увеличивают глубину B-tree.

Этот урок — про то, как обнаружить, предотвратить и вылечить bloat.

Диагностика: как понять, что bloat есть

Самый дешёвый сигнал — поле n_dead_tup в pg_stat_user_tables:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  pg_size_pretty(pg_relation_size(relid)) AS heap_size,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Если dead_pct > 20% — таблица «грязная» по dead tuples. Если > 50% — bloat, скорее всего, ощутимо влияет на план. Но это оценка: n_live_tup/n_dead_tup обновляются ANALYZE’ом и могут отставать от реальности на дни.

Точная диагностика — extension

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len: 1.2 GB
-- tuple_count: 800K
-- tuple_len: 600 MB
-- tuple_percent: 50.0
-- dead_tuple_count: 250K
-- dead_tuple_len: 200 MB
-- dead_tuple_percent: 16.7
-- free_percent: 30.5

dead_tuple_percent + free_percent = bloat в процентах. Если суммарно >30% — рассматривай curative-меры.

Минус pgstattuple: полный scan таблицы. На 1 TB — десятки минут IO. Для регулярного мониторинга есть pgstattuple_approx — быстрее, но менее точно (использует FSM/VM как hint).

Что вызывает bloat

Несколько типичных причин:

1. Высокий write-rate + слабый autovacuum. Таблица получает 10K UPDATE/sec, autovacuum запускается раз в 10 минут — между запусками копится bloat.

2. Долгие транзакции. Открытая транзакция держит snapshot, и VACUUM не может убрать dead tuples моложе этого snapshot. Один idle in transaction процесс на 6 часов = 6 часов bloat’а на write-heavy таблице.

3. Открытый replication slot с устаревшим xmin. То же самое, что (2), но «висит» не локальная транзакция, а реплика, которая ушла далеко по WAL.

4. UPDATE-many-non-HOT. Если обновляется колонка, входящая в индекс — HOT не сработает, и появятся не только dead tuples в heap, но и обновления индексов (свой bloat).

5. Bulk delete + продолжение использования. DELETE 50% строк — VACUUM пометит как unused, но место не вернёт. Если потом таблица растёт обычным INSERT темпом, она будет долго заполнять «дырки» в FSM, но размер на диске останется большим.

Профилактика: fillfactor

Что плохо: первый же UPDATE на эту страницу не сможет сделать HOT (для HOT нужно ~free space ≥ размер новой версии кортежа). Новая версия пойдёт на другую страницу → ctid поменяется → все индексы получают обновления → bloat в индексах.

Снижение fillfactor резервирует место:

ALTER TABLE orders SET (fillfactor = 80);
-- Перестроить таблицу для применения:
VACUUM FULL orders;
-- или для прод-flow:
-- pg_repack -t orders

Теперь страницы заполняются на 80%, остаётся 20% места. UPDATE’ы могут пройти как HOT — новая версия ложится на ту же страницу, ctid меняется внутри страницы, но индексы не трогаются. Эффект: меньше bloat в индексах, меньше IO на update.

Рекомендации по fillfactor:

• 100 (default) — для append-only таблиц (logs, events). UPDATE’ов нет, экономить место незачем.
• 80-90 — для read-mostly OLAP с редкими update’ами.
• 70-80 — для transactional таблиц с update-rate средним.
• 50-70 — для hot-update таблиц (например, queue, sessions, current state per user).

Профилактика: autovacuum тюнинг

Autovacuum запускается по триггеру: когда n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup. По умолчанию scale_factor = 0.2 и threshold = 50 — то есть autovacuum запустится, когда мёртвых > 20% живых + 50.

Для крупной hot-таблицы 0.2 — слишком позднее срабатывание. На таблице с 100M строк autovacuum проснётся при 20M dead tuples — это уже 20% bloat’а до того, как мы начали что-то делать.

Per-table override:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 2000
);

scale_factor = 0.05 — срабатывает на 5% dead tuples. cost_limit = 2000 (default 200) — autovacuum работает в 10 раз агрессивнее, не уходит в sleep между batch’ами.

Anti-pattern: глобально снизить autovacuum_vacuum_scale_factor для всех таблиц. На мелких таблицах это ок, на крупных read-only — пустая трата IO. Делай per-table.

Curative 1: VACUUM FULL

Команда VACUUM FULL пересоздаёт таблицу с нуля: читает все живые кортежи, пишет их в новый файл компактно, потом атомарно подменяет старый файл новым. Результат — минимальный размер на диске, ноль bloat.

Цена: AccessExclusiveLock на всё время операции. Никаких SELECT/INSERT/UPDATE/DELETE/DDL. Для 100 GiB таблицы это могут быть часы downtime.

Когда применять:

• Mini-таблицы (до GiB) — operation в секундах.
• Запланированный maintenance-window — есть 2 часа простоя.
• Stand-by реплика → switchover → переключение + VACUUM FULL на бывшем primary.

Подводный камень: VACUUM FULL требует место. Новый файл = размер живых данных + index размер. На диске нужно 2× места.

Curative 2: pg_repack

pg_repack — extension, который делает то же самое, что VACUUM FULL, но без блокировки:

1. Создаёт shadow-таблицу с тем же schema.
2. Копирует все живые строки в shadow через INSERT.
3. Триггер на основной таблице записывает все INSERT/UPDATE/DELETE в лог-таблицу.
4. После копирования — догоняет лог.
5. Атомарно подменяет relfilenode (одна короткая ExclusiveLock в самом конце).

Цена: временно нужно 2× места на диске (shadow + main). И много IO/CPU пока работает.

Когда применять:

• Production-таблица без maintenance-window.
• Размер 10-200 GiB (на 1 TB pg_repack работает часами).
• Достаточно свободного места на диске.

Не работает: на таблицах без PK/UNIQUE-индекса; на toasted-only таблицах (rare).

Curative 3: REINDEX CONCURRENTLY

Bloat в индексах — отдельный сюжет. B-tree индексы тоже копят dead entries, и n_dead_tup их не показывает. Признак: запрос с Index Scan стал медленнее, размер индекса вырос непропорционально таблице.

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

REINDEX CONCURRENTLY index_name (PostgreSQL 12+) пересоздаёт индекс параллельно с основной работой:

1. Создаёт новый индекс рядом со старым (_ccnew суффикс).
2. Атомарно подменяет старый новым.
3. Дропает старый.

Берёт лёгкие локи (ShareUpdateExclusiveLock) — параллельный SELECT/INSERT/UPDATE продолжают работать. Длительность — сопоставима с обычным CREATE INDEX.

Curative 4: CLUSTER

CLUSTER — это VACUUM FULL + физическая пересортировка по выбранному индексу. Полезно, если у тебя range-запросы по конкретной колонке: CLUSTER упорядочивает heap по этой колонке, и Index Range Scan читает sequentially страницы.

CLUSTER orders USING orders_placed_at_idx;

Цена та же — AccessExclusiveLock. И ещё: после первого CLUSTER Postgres не поддерживает порядок автоматически. Новые INSERT/UPDATE пишутся куда попало в FSM, и через какое-то время «кластерность» теряется.

Алгоритм принятия решений

Когда обнаружил bloat:

1. Срочно ли? Если dead_pct < 30% и таблица не «горит» — добавь fillfactor + autovacuum tuning и жди следующего цикла.
2. Есть ли maintenance window? Если да и таблица < 100 GiB — VACUUM FULL. Просто и надёжно.
3. Нужно zero-downtime? pg_repack для таблицы; REINDEX CONCURRENTLY для индексов.
4. Хочется ребилда + физического порядка? CLUSTER (но помни — порядок временный).
5. После лечения — настрой профилактику. Иначе вернёшься сюда через месяц.

Чек-лист

• Диагностика быстрая: n_dead_tup в pg_stat_user_tables, ratio dead/live. Точная: pgstattuple (тяжело) или pgstattuple_approx.
• Профилактика: fillfactor 70-80 для hot-update таблиц; per-table autovacuum_scale_factor 0.05 для крупных hot-таблиц.
• VACUUM FULL — AccessExclusiveLock, downtime, требует 2× места. Только для small таблиц или с window.
• pg_repack — zero-downtime, 2× места, IO-heavy. Production-friendly choice.
• REINDEX CONCURRENTLY — для bloated indexes, без блокировки SELECT/INSERT.
• CLUSTER = VACUUM FULL + физический order; порядок не сохраняется автоматически после INSERT.
• Главное — после лечения настроить профилактику: bloat возвращается, если причина не устранена.