Запускать VACUUM руками в продакшене раз в час — путь к выгоранию. Поэтому в PostgreSQL встроен autovacuum — фоновый демон (а точнее, набор воркеров под управлением autovacuum launcher), который сам решает, когда какую таблицу нужно vacuum’ить и analyze’ить. Дефолтные настройки разумны для маленькой OLTP-базы, но на write-heavy workload почти всегда требуют тюнинга. Этот урок — про формулу запуска, per-table настройки и типичные ловушки.

Как autovacuum решает, что таблицу пора vacuum’ить

Каждые autovacuum_naptime секунд (по умолчанию 60) launcher просыпается и для каждой таблицы во всех базах считает два порога:

• VACUUM threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
• ANALYZE threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Где reltuples — оценочное число строк в таблице (из pg_class.reltuples, обновляется ANALYZE), а пороги по умолчанию:

Если n_dead_tup (из pg_stat_user_tables) превысил VACUUM threshold — launcher шлёт worker’а делать VACUUM. Если n_mod_since_analyze превысил ANALYZE threshold — делать ANALYZE.

Числовой пример. Таблица 10 миллионов строк:

• VACUUM threshold = 50 + 0.2 × 10_000_000 = 2_000_050.
• ANALYZE threshold = 50 + 0.1 × 10_000_000 = 1_000_050.

Значит дефолтный autovacuum не тронет таблицу, пока в ней не накопится 2 миллиона dead tuples. На write-heavy workload это часто слишком поздно: за это время bloat успевает вырасти на десятки процентов, а индексы — раздуться. Решение — снизить scale_factor для горячих таблиц.

Per-table настройки

Менять глобальные параметры в postgresql.conf — грубый инструмент: ваши 100 маленьких справочных таблиц получат тот же агрессивный scale_factor, что и одна горячая events-таблица, и autovacuum-воркеры будут тратить время впустую. Гораздо точнее — per-table reloptions через ALTER TABLE:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 1000
);

Это значит: на конкретно этой таблице вакуум запускается, когда dead tuples ≥ 5% от reltuples (вместо 20%), analyze — при 2% изменений (вместо 10%), и cost_limit для воркера повышен (быстрее проходит за раз). Все остальные таблицы продолжают использовать глобальные значения.

Посмотреть текущие per-table настройки можно так:

SELECT relname, reloptions
FROM pg_class
WHERE relkind = 'r' AND reloptions IS NOT NULL;

В pglite autovacuum как фоновый демон не работает (нет background process’а), но синтаксис ALTER TABLE ... SET (autovacuum_*) поддерживается — настройки записываются в pg_class.reloptions. Это полезно: вы можете отрепетировать DDL и проверить, что синтаксис верный, ещё до того, как применять на production.

Cost limit и cost delay

Чтобы autovacuum не задушил production, у него есть бюджет работы. Каждое действие воркера имеет «цену»:

• Чтение страницы из shared buffers — 1 (vacuum_cost_page_hit).
• Чтение страницы с диска — 10 (vacuum_cost_page_miss).
• Запись измененной страницы — 20 (vacuum_cost_page_dirty).

Когда накопленная цена достигает autovacuum_vacuum_cost_limit (по умолчанию 200), воркер спит autovacuum_vacuum_cost_delay миллисекунд (по умолчанию 2 в PG 12+). Получается: воркер не может писать страницы быстрее, чем cost_limit / cost_delay / page_dirty_cost = 200 / 0.002 / 20 = 5000 dirty-pages в секунду на одного воркера. Это ~40 MiB/sec — комфортно для production.

На write-heavy workload это становится узким местом: воркер просто не успевает за изменениями. Тюнинг:

• Увеличить autovacuum_vacuum_cost_limit до 1000-2000 — воркер делает больше работы между паузами.
• Уменьшить autovacuum_vacuum_cost_delay до 0 — воркер не спит вообще (для серверов с хорошим IO).
• Увеличить autovacuum_max_workers с 3 до 5-10 — параллельно vacuum’ить больше таблиц.

Пример агрессивной конфигурации для write-heavy сервера:

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 0;
ALTER SYSTEM SET autovacuum_max_workers = 8;
ALTER SYSTEM SET maintenance_work_mem = '2GB';
SELECT pg_reload_conf();

maintenance_work_mem влияет на размер in-memory массива tid’ов, который собирает VACUUM до прохода по индексам. Больше памяти — реже двойные проходы по heap, быстрее операция. Для крупных таблиц рекомендуют 1-2 GiB.

Демо: считаем threshold для разных scale_factor

Видно, что на orders (100K строк) дефолтный порог ~20K dead tuples, а агрессивный — ~5K. На таблицах с 10M строк разница уже 2M vs 500K — и это уже большой разрыв в стабильном уровне bloat’а.

Pitfalls

Несколько граблей, на которые наступают чаще всего:

1. Lock-конфликты с DDL. Autovacuum держит ShareUpdateExclusiveLock. Если ваш миграционный скрипт пытается сделать ALTER TABLE ADD COLUMN, он будет ждать. PG 11+ детектит ситуацию и прерывает autovacuum в пользу DDL — но если DDL короткий (миллисекунды), это вызывает «vacuum starvation»: каждый запуск vacuum’а прерывается на каждом коммите, и таблица никогда не очищается. Решение — мониторить pg_stat_user_tables.last_autovacuum: если оно «застряло на прошлой неделе», ищите конфликтующие DDL.

2. Долгие транзакции блокируют освобождение. Открытая транзакция держит xmin: VACUUM физически работает, но не может пометить старые версии как unused, потому что их формально ещё видит этот snapshot. Если в pg_stat_activity есть state = 'idle in transaction' старше нескольких минут — это первый подозреваемый при растущем bloat’е, даже если autovacuum выглядит здоровым.

3. Anti-wraparound vacuum останавливает свет. Когда таблице грозит wraparound (об этом следующий урок), autovacuum запускает aggressive freeze — он не отдаёт блокировку даже DDL’у. Это нормально, но если ваш CREATE INDEX CONCURRENTLY упёрся в висящий autovacuum на той же таблице — посмотрите pg_stat_progress_vacuum.phase = 'cleaning up indexes' и подождите.

4. autovacuum_freeze_max_age создаёт штормы. Когда relfrozenxid отстаёт от текущего xid больше чем на этот параметр (по умолчанию 200 миллионов), autovacuum принудительно запускает «aggressive vacuum» на всех затронутых таблицах — даже если dead tuples мало. На больших инсталляциях это вызывает синхронные штормы. Решение — стагерить (поднимать autovacuum_freeze_max_age для разных таблиц по-разному через ALTER TABLE) или повышать глобально.

Чек-лист

• Формула autovacuum: threshold + scale_factor * reltuples. Default scale_factor = 0.2 (20%) — слишком поздно для write-heavy таблиц.
• Per-table тюнинг через ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ..., ...). Точечно, не глобально.
• Cost limit ограничивает скорость воркера. Default ~5K dirty pages/sec — повышать на write-heavy.
• autovacuum_max_workers — сколько параллельно. Default 3, типовая прод-настройка 5-10.
• maintenance_work_mem — память на in-memory tid-array. Чем больше, тем реже многократные проходы по индексам.
• Pitfalls: lock-конфликты с DDL прерывают vacuum; долгие транзакции держат xmin и не дают освободить место; anti-wraparound autovacuum нельзя отменить; autovacuum_freeze_max_age создаёт синхронные штормы на больших инсталляциях.
• Мониторить pg_stat_user_tables.last_autovacuum и n_dead_tup — это основной dashboard здоровья vacuum’а.