PostgreSQL VACUUM: как работает автовакуум и когда настраивать вручную

PostgreSQL не удаляет строки физически. При UPDATE или DELETE старая версия строки (dead tuple) остаётся на диске — она нужна для MVCC, чтобы параллельные транзакции видели консистентные данные. Но когда транзакция завершена, мёртвые строки становятся мусором. Если его не убирать — таблица раздувается, индексы растут, запросы замедляются. Для уборки существует VACUUM.

Зачем VACUUM: dead tuples, bloat, wraparound

Три главные проблемы без VACUUM:

• Dead tuples — мёртвые строки занимают место. Таблица на 10M живых строк может физически содержать 50M строк, из которых 40M — мусор. Seq Scan читает всё, включая мусор.
• Bloat — раздувание таблиц и индексов. Таблица в 2 ГБ может занимать 8 ГБ на диске. Индексы раздуваются пропорционально.
• Transaction ID wraparound — PostgreSQL использует 32-битные ID транзакций (≈4 млрд). Без VACUUM база рискует остановиться для принудительного VACUUM при приближении к лимиту.

Autovacuum: параметры по умолчанию

Autovacuum — фоновый процесс, который запускает VACUUM автоматически. Дефолтные настройки:

-- Когда запускать VACUUM на таблице:
autovacuum_vacuum_threshold = 50          -- мин. dead tuples
autovacuum_vacuum_scale_factor = 0.2      -- + 20% от размера таблицы

-- Формула: vacuum запустится когда dead_tuples > threshold + scale_factor * n_live_tup
-- Для таблицы в 10M строк: 50 + 0.2 * 10000000 = 2000050 dead tuples

-- Ресурсные лимиты:
autovacuum_vacuum_cost_limit = 200        -- "бюджет" I/O за цикл
autovacuum_vacuum_cost_delay = 2ms        -- пауза после исчерпания бюджета
autovacuum_max_workers = 3                -- параллельных воркеров

Проблема дефолтов: для таблицы в 100M строк autovacuum запустится только после накопления 20 миллионов мёртвых строк. А cost_limit = 200 с паузой 2мс означает, что VACUUM будет ползти часами.

Тюнинг для нагруженных таблиц

Глобальные настройки можно переопределить per-table:

-- Для горячей таблицы: запускать VACUUM после 1% dead tuples
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 0
);

-- Для таблицы с частыми UPDATE: агрессивный VACUUM
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 5000
);

Рекомендации для продакшена: таблицы >10M строк — scale_factor = 0.01-0.05, cost_limit = 1000-5000 на SSD, max_workers = 5-6, cost_delay = 0 на SSD.

VACUUM FULL vs обычный VACUUM

Обычный VACUUM помечает мёртвые строки как свободное место для переиспользования, но не уменьшает физический размер файла. Таблица не сжимается.

VACUUM FULL перезаписывает таблицу целиком, убирая весь bloat. Но:

• Берёт ACCESS EXCLUSIVE lock — таблица полностью недоступна на время операции
• Требует свободного места на диске = размер таблицы (создаёт копию)
• Перестраивает все индексы
• На таблице 100 ГБ может работать часы

-- Обычный VACUUM (не блокирует):
VACUUM orders;
VACUUM (VERBOSE) orders;  -- с подробным выводом

-- VACUUM FULL (блокирует!):
VACUUM FULL orders;  -- только в maintenance window!

Альтернатива без блокировки — pg_repack: перестраивает таблицу онлайн.

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

Ключевые метрики для отслеживания здоровья VACUUM:

SELECT
  schemaname, 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,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 10;

Красные флаги:

• dead_pct > 20% — autovacuum не справляется
• last_autovacuum давно или NULL — проверьте настройки

Реальный кейс: таблица 500M строк с 40% bloat

Ситуация: таблица audit_log — 500M строк, 180 ГБ на диске. Ожидаемый размер — ~110 ГБ. Bloat около 40%. Autovacuum не успевает — таблица получает 2M UPDATE/день.

Диагностика:

SELECT n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'audit_log';
-- n_dead_tup: 95000000, n_live_tup: 500000000
-- last_autovacuum: 3 дня назад (не завершился — был убит)

Решение:

-- 1. Агрессивный autovacuum для этой таблицы
ALTER TABLE audit_log SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 10000,
  autovacuum_vacuum_cost_delay = 0
);

-- 2. Ручной VACUUM в maintenance window
VACUUM (VERBOSE, PARALLEL 4) audit_log;

-- 3. Долгосрочно: партиционирование по месяцам + DROP старых партиций

Результат: после ручного VACUUM dead_pct упал с 40% до 2%. Autovacuum с новыми настройками держит bloat ниже 5%.

Вывод

Autovacuum — не «set and forget». Дефолтные настройки рассчитаны на маленькие базы. Для таблиц >10M строк обязательно тюньте scale_factor и cost_limit. Мониторьте n_dead_tup и last_autovacuum. Если bloat уже накопился — используйте pg_repack вместо VACUUM FULL. А для таблиц, которые только растут — подумайте о партиционировании.