PostgreSQL: VACUUM, autovacuum и bloat

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

В типичном проекте VACUUM работает сам через autovacuum. Разработчику почти ничего настраивать не нужно. Но иногда autovacuum не справляется, и важно понимать почему — bloat, медленные запросы, долгий restart, всё это последствия одного и того же.

1. Зачем нужен VACUUM

PG-V-001 VACUUM делает три вещи:

1. Освобождает место от мёртвых tuple'ов — заносит свободные слоты в FSM (Free Space Map), чтобы новые INSERT могли их занять. Не возвращает место ОС — таблица не уменьшается на диске.
2. Обновляет visibility map — карту страниц, где все tuple'ы видны всем транзакциям. Это позволяет Index Only Scan работать без Heap Fetches.
3. Предотвращает transaction ID wraparound — счётчик XID 32-битный, через ~2 млрд транзакций «проходит круг»; VACUUM «замораживает» старые tuple'ы, чтобы не путались.

PG-V-002 VACUUM не блокирует чтение и запись. Берёт SHARE UPDATE EXCLUSIVE lock — параллельные SELECT/INSERT/UPDATE/DELETE работают. Только DDL (ALTER TABLE) ждёт.

2. Три варианта VACUUM

Команда	Что делает	Lock	Возвращает место ОС
VACUUM	Помечает dead tuples свободными в FSM	SHARE UPDATE EXCLUSIVE (нет блокировки чтения/записи)	Нет
VACUUM FULL	Перезаписывает таблицу с нуля без dead tuples	ACCESS EXCLUSIVE (блокирует ВСЁ)	Да
VACUUM ANALYZE	Обычный VACUUM + сбор статистики для планировщика	SHARE UPDATE EXCLUSIVE	Нет

PG-V-010 VACUUM FULL — почти никогда в проде. ACCESS EXCLUSIVE блокирует всех читателей и писателей на время перезаписи (минуты-часы на больших таблицах). Используй только в окне обслуживания. Альтернатива — pg_repack / pg_squeeze (re-write без блокировки).

PG-V-011 VACUUM ANALYZE — после массовой загрузки/обновления. autovacuum запустит ANALYZE сам, но с задержкой; если планировщик нужен правильный сразу — руками.

3. autovacuum — что включено по умолчанию

PG-V-020 autovacuum работает автоматически и тюнен на дефолты. В большинстве случаев его трогать не надо.

Дефолтные пороги (PG14+):

• autovacuum_vacuum_threshold = 50 — минимум dead tuples для запуска.
• autovacuum_vacuum_scale_factor = 0.2 — плюс 20% от размера таблицы.
• Триггер: dead_tuples > threshold + scale_factor × n_live_tup.

То есть на таблице 1M строк autovacuum запустится, когда мёртвых будет ~200K. Для большинства бизнес-нагрузок норм.

PG-V-021 На больших горячих таблицах дефолт мал. 200K dead tuples при UPDATE-нагрузке набегают за минуты, autovacuum гоняется постоянно и не успевает. Решение — снизить scale_factor до 0.05 (5%) для конкретной таблицы:

ALTER TABLE order_doc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.05
);

Теперь VACUUM запустится при ~50K dead tuples — реже простаивает, реже копится bloat.

PG-V-022 Параметр autovacuum_naptime = 1min — autovacuum-launcher проверяет таблицы раз в минуту. Это нижняя граница «как быстро сработает».

4. Bloat — как обнаружить

Bloat = разница между «полезным размером данных» и «реальным размером таблицы/индекса». Если 50% таблицы — пустые места от dead tuples, у тебя 2x bloat.

PG-V-030 Базовый мониторинг — pg_stat_user_tables:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC LIMIT 10;

dead_pct > 20% — кандидат на ручной VACUUM или подкрутку scale_factor. last_autovacuum старше суток на горячей таблице — autovacuum не успевает (см. §6).

PG-V-031 Фактический bloat — через расширения pgstattuple или pg_visibility:

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('order_doc');
-- table_len, tuple_count, tuple_percent, dead_tuple_count, dead_tuple_percent, free_space

dead_tuple_percent > 30% — таблица распухла, нужен VACUUM (или VACUUM FULL в окно обслуживания, или pg_repack).

PG-V-032 Bloat индексов — отдельная история. Индексы тоже распухают при UPDATE на индексируемой колонке. Показывает pgstattuple. Лечится REINDEX CONCURRENTLY (PG12+) — не блокирует таблицу.

5. fillfactor — снизить bloat на write-heavy таблицах

PG-V-040 На таблицах с частыми UPDATE — fillfactor = 80–90. Подробно — в статье про WAL, правила PG-W-020/PG-W-021.

Кратко: PG умеет HOT (Heap-Only Tuple) — UPDATE без перемещения строки, если на странице есть свободное место. С fillfactor < 100 страницы заполняются не до конца, остаётся место под HOT, dead tuples реже плодятся.

ALTER TABLE order_doc SET (fillfactor = 85);
VACUUM FULL order_doc;   -- переразложит таблицу под новый fillfactor (один раз, в окно)

6. Когда autovacuum не справляется

PG-V-050 Открытая долгая транзакция блокирует освобождение dead tuples. PG не может пометить tuple свободным, пока его видит хоть какая-то открытая транзакция (для MVCC). Долгая транзакция = bloat растёт.

-- найди долгие транзакции:
SELECT pid, age(now(), xact_start) AS xact_age, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC LIMIT 10;

Алёрт: xact_age > 5 минут — обычно сломанная сессия в IDE, висящий тест, забытый BEGIN без COMMIT. Подробно — WAL §8.

PG-V-051 Висящий replication slot. Если читатель WAL отстал, PG не освобождает WAL → autovacuum не освобождает dead tuples. Тот же WAL §9.

PG-V-052 Long-running query / prepared transaction — те же эффекты. Prepared transactions (PREPARE TRANSACTION) живут в pg_prepared_xacts до явного COMMIT PREPARED / ROLLBACK PREPARED. Проверка:

SELECT * FROM pg_prepared_xacts;

PG-V-053 Бан-лист autovacuum = off. Кто-то выключил autovacuum глобально (autovacuum = off в postgresql.conf) или для конкретной таблицы (ALTER TABLE ... SET (autovacuum_enabled = false)). Иногда оправдано (массовая загрузка staging), но забывают вернуть.

-- проверь, что autovacuum ВКЛЮЧЕН
SELECT name, setting FROM pg_settings WHERE name = 'autovacuum';

-- per-table:
SELECT relname, reloptions FROM pg_class
WHERE relkind = 'r' AND reloptions::text LIKE '%autovacuum%';

7. VACUUM как часть миграции

PG-V-060 После big UPDATE или DELETE миграции — VACUUM ANALYZE руками. Не жди autovacuum.

-- миграция: пометить старые заказы архивными
UPDATE order_doc SET status = 'ARCHIVED' WHERE created_at < now() - interval '1 year';

-- сразу после:
VACUUM ANALYZE order_doc;

Иначе планировщик ещё долго будет считать, что 99% — NEW/PAID, и выбирать неоптимальные планы для запросов по status = 'ARCHIVED'.

PG-V-061 CREATE INDEX CONCURRENTLY обновляет visibility map — после построения индекса для Index Only Scan нужен VACUUM, чтобы Heap Fetches упали до нуля.

8. Когда VACUUM FULL оправдан

PG-V-070 VACUUM FULL — только в окно обслуживания и при сильном bloat (>50%).

Сценарии:

• После массового DELETE старых данных, которые занимали >30% таблицы.
• После изменения fillfactor — переразложить под новый параметр.
• Bloat обнаружен через pgstattuple, обычный VACUUM не помогает (потому что UPDATE нагрузка постоянно создаёт новые dead tuples).

Альтернатива без блокировки — pg_repack:

pg_repack -d mydb -t order_doc

Запускается как обычный процесс, не блокирует, переписывает таблицу в фон. На больших объёмах безопаснее.

9. Транзакционный wraparound

PG-V-080 PG XID-счётчик 32-битный — 2.1 млрд значений. Через wraparound кластер встаёт.

VACUUM «замораживает» старые tuple'ы (vacuum_freeze_min_age, vacuum_freeze_table_age) — снимает с них видимость по XID.

В нормальной работе этим занимается autovacuum, разработчик не видит. Но на очень нагруженных кластерах (миллионы транзакций в час) wraparound подкрадывается за месяцы — мониторь:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_left
FROM pg_database;

Если xid_age > 1.5 млрд — алёрт, autovacuum не справляется (см. §6).

10. Чек-лист «таблица распухла»

1. n_dead_tup в pg_stat_user_tables — большой (>20% от n_live_tup)? Да → autovacuum не отрабатывает.
2. last_autovacuum — давно (часы/дни)? Проверь долгие транзакции, replication slots, autovacuum_enabled.
3. pgstattuple показывает dead_tuple_percent > 30%? Запусти VACUUM <table>; руками. Если не помогает — копит быстрее, чем чистит → подкрути autovacuum_vacuum_scale_factor.
4. fillfactor оставлен дефолтным (100) на write-heavy таблице? Снизь до 80–90.
5. Если ничего не помогает и нужно вернуть место ОС — окно обслуживания + pg_repack или VACUUM FULL.
6. Bloat индексов — REINDEX CONCURRENTLY.

---

Чек-лист на ревью кода и схемы

• [ ] На write-heavy таблицах (UPDATE-интенсивных) — fillfactor = 80–90.
• [ ] После большой миграции (UPDATE/DELETE миллионов строк) — VACUUM ANALYZE в той же миграции.
• [ ] autovacuum_enabled = false на таблице — только если есть план обратного включения.
• [ ] Нет долгих транзакций (> 5 мин) в обычных операциях — @Transactional не оборачивает HTTP/Kafka/S3.
• [ ] pg_stat_user_tables.last_autovacuum мониторится — алёрт на «давно не было» для горячих таблиц.
• [ ] pg_replication_slots.active = false или большой lag → починить, иначе autovacuum заблокирован.

---

Связанные

• WAL — длинные транзакции и replication slots мешают autovacuum.
• Чтение EXPLAIN — Heap Fetches > 0 на Index Only Scan = устаревший visibility map → нужен VACUUM.
• Селективность — ANALYZE обновляет статистику для планировщика.