Опирается на правила: PG-V-001 … PG-V-080 из PostgreSQL Style Guide → раздел VACUUM, autovacuum и bloat.

Важно знать

  • MVCC: UPDATE/DELETE помечают старую версию мёртвой, пишут новую. Dead tuples копятся.
  • VACUUM освобождает место в FSM, обновляет visibility map, предотвращает XID wraparound.
  • VACUUM не возвращает место ОС — таблица не уменьшается на диске.
  • VACUUM не блокирует чтение/запись (SHARE UPDATE EXCLUSIVE).
  • VACUUM FULL почти никогда в проде (ACCESS EXCLUSIVE). Альтернатива — pg_repack.
  • autovacuum работает автоматически, default scale_factor 0.2.
  • На больших горячих таблицах — снизь scale_factor до 0.05.
  • Bloat: dead_pct > 20% в pg_stat_user_tables; pgstattuple для точной картины.
  • fillfactor 80-90 на write-heavy для HOT updates.
  • autovacuum не справляется: долгие транзакции, replication slots, prepared TX.

PostgreSQL использует MVCC — dead tuples копятся, VACUUM их убирает. UCP формулирует — когда autovacuum хватает, когда нет.

Зачем VACUUM

PG-V-001..002: три задачи.

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

Не блокирует чтение/запись (SHARE UPDATE EXCLUSIVE). Только DDL (ALTER TABLE) ждёт.

Три варианта

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

VACUUM FULL — почти никогда

PG-V-010..011: ACCESS EXCLUSIVE блокирует всех на минуты-часы. Только в окне обслуживания.

Альтернатива — pg_repack / pg_squeeze (re-write без блокировки).

VACUUM ANALYZE — после массовой загрузки/обновления, не жди autovacuum.

autovacuum

PG-V-020..022: работает автоматически.

Дефолтные пороги (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 dead. Для большинства — норм.

На больших горячих таблицах дефолт мал:

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

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

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

PG-V-030..032:

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.
  • last_autovacuum старше суток на горячей — autovacuum не успевает.

Точная картина — pgstattuple:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('order_doc');
-- dead_tuple_percent > 30% — распухла

Bloat индексов — отдельная история. Лечится REINDEX CONCURRENTLY (PG12+) — не блокирует таблицу.

fillfactor

PG-V-040: на write-heavy.

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

С fillfactor < 100 страницы заполняются не до конца, остаётся место под HOT (Heap-Only Tuple) — UPDATE без перемещения строки. Подробно — WAL.

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

PG-V-050..053:

Долгая транзакция

PG не может пометить tuple свободным, пока его видит хоть одна открытая TX (MVCC).

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, висящий тест.

Висящий replication slot

Если читатель WAL отстал, PG не освобождает WAL → autovacuum не освобождает dead tuples.

Prepared transaction

SELECT * FROM pg_prepared_xacts;

Живут до явного COMMIT PREPARED / ROLLBACK PREPARED.

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%';

Иногда выключают для bulk-load и забывают вернуть.

VACUUM в миграциях

PG-V-060..061:

После big UPDATE/DELETE:

UPDATE order_doc SET status = 'ARCHIVED' WHERE created_at < now() - interval '1 year';
VACUUM ANALYZE order_doc;

Иначе планировщик долго будет считать most_common_vals неверными.

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

VACUUM FULL — когда оправдан

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

Сценарии:

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

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

pg_repack -d mydb -t order_doc

XID wraparound

PG-V-080: 32-битный счётчик, 2.1 млрд значений.

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

Алёрт: xid_age > 1.5 млрд — autovacuum не справляется.

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

  1. n_dead_tup > 20% от n_live_tup → autovacuum не отрабатывает.
  2. last_autovacuum давно → долгие TX, replication slots, autovacuum_enabled.
  3. pgstattuple dead_tuple_percent > 30% → VACUUM <table> руками. Если не помогает — autovacuum_vacuum_scale_factor.
  4. fillfactor дефолтный на write-heavy → 80-90.
  5. Окно обслуживания + pg_repack.
  6. Bloat индексов → REINDEX CONCURRENTLY.

Что запрещено

АнтипаттернПравилоЧто взамен
VACUUM FULL в проде онлайнPG-V-010pg_repack
autovacuum = off глобальноPG-V-053оставить включённым
Дефолт scale_factor 0.2 на больших горячихPG-V-0210.05
Игнорировать n_dead_tupPG-V-030мониторинг
fillfactor 100 на write-heavyPG-V-04080-90 + VACUUM FULL один раз
Долгая TX без idle_in_transaction_session_timeoutPG-V-05030s timeout
Bloat индексов без REINDEX CONCURRENTLYPG-V-032PG12+ rebuild
Не делать VACUUM ANALYZE после big UPDATEPG-V-060руками

Куда дальше

  • PG → VACUUM — нормативные формулировки.
  • WAL — fillfactor и HOT updates.
  • Composite-индексы — Heap Fetches и visibility map.
  • Расширения — pgstattuple, pg_repack.
  • Уровни изоляции — idle_in_transaction_session_timeout.
  • Мониторинг — pg_stat_user_tables.