VACUUM в PostgreSQL — очистка, autovacuum, MVCC

VACUUM убирает «мёртвые» строки, которые остаются после UPDATE и DELETE из-за MVCC. Без регулярного VACUUM таблицы раздуваются и запросы замедляются. Разбираем MVCC, виды VACUUM, autovacuum и как диагностировать раздутие.

MVCC — почему появляются мёртвые строки

-- В PostgreSQL UPDATE не изменяет строку на месте:
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Происходит следующее:
-- 1. Старая строка помечается как "мёртвая" (dead tuple)
-- 2. Создаётся новая строка с новыми данными
-- 3. Старые транзакции, которые начались до UPDATE,
--    продолжают видеть старую строку (MVCC — многоверсионность)

-- То же для DELETE:
DELETE FROM sessions WHERE expires_at < NOW();
-- Строки не удаляются физически — только помечаются как мёртвые!

-- Посмотреть количество живых и мёртвых строк:
SELECT relname, n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
WHERE relname = 'users';

-- n_dead_tup — мёртвые строки, занимают место на диске
-- VACUUM освобождает это место для повторного использования

VACUUM — основные команды

-- Очистить конкретную таблицу (не блокирует чтение/запись):
VACUUM users;

-- Очистить и обновить статистику для планировщика:
VACUUM ANALYZE users;

-- Очистить все таблицы в текущей базе:
VACUUM;

-- Подробный вывод:
VACUUM VERBOSE users;

-- VACUUM FULL — дефрагментация, возвращает место ОС:
VACUUM FULL users;
-- ВНИМАНИЕ: блокирует таблицу полностью на всё время выполнения!
-- На большой таблице — часы простоя. Используйте pg_repack для онлайн-дефрагментации.

-- ANALYZE — только обновить статистику (без очистки):
ANALYZE users;
-- Нужен чтобы планировщик строил правильные планы запросов

-- Из командной строки:
vacuumdb -U postgres mydb
vacuumdb -U postgres --analyze-only mydb
vacuumdb -U postgres --full mydb  # аналог VACUUM FULL для всей базы

Autovacuum — автоматическая очистка

Autovacuum — фоновый процесс, который автоматически запускает VACUUM и ANALYZE. Включён по умолчанию и достаточен для большинства случаев:

-- Проверить что autovacuum включён:
SHOW autovacuum;  -- on

-- Основные параметры autovacuum в postgresql.conf:
autovacuum = on                      -- включить
autovacuum_vacuum_threshold = 50     -- запустить если мёртвых строк > 50
autovacuum_vacuum_scale_factor = 0.2 -- или > 20% таблицы
-- autovacuum сработает когда: n_dead_tup > threshold + scale_factor * n_live_tup

autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1 -- ANALYZE после 10% изменений

autovacuum_vacuum_cost_delay = 2ms   -- пауза между операциями (меньше = быстрее)
autovacuum_max_workers = 3           -- параллельных процессов autovacuum

-- Настройка autovacuum для конкретной таблицы (переопределяет глобальные):
ALTER TABLE high_update_table SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 1% вместо 20%
  autovacuum_vacuum_threshold = 100
);

-- Для очень активных таблиц (logs, events):
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 10000  -- каждые 10000 мёртвых строк
);

Диагностика раздутия таблиц

-- Состояние VACUUM для всех таблиц:
SELECT
  schemaname,
  relname AS table_name,
  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_vacuum,
  last_autovacuum,
  pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Таблицы с большим количеством мёртвых строк (>10%) нуждаются в VACUUM:
SELECT relname, n_dead_tup, n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
  AND ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) > 10
ORDER BY dead_pct DESC;

-- Активные процессы autovacuum:
SELECT pid, query, state, now() - pg_stat_activity.query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%';

Transaction ID Wraparound — критическая ситуация

-- PostgreSQL использует 32-битные Transaction ID (XID)
-- При достижении предела (~2 млрд транзакций) база переходит в READ ONLY!
-- VACUUM предотвращает этот сценарий через заморозку (freeze) старых строк

-- Проверить "возраст" баз (сколько транзакций до проблемы):
SELECT datname,
  age(datfrozenxid) AS xid_age,
  2000000000 - age(datfrozenxid) AS txids_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- Если xid_age > 1.5 млрд — нужно немедленно запустить VACUUM FREEZE

-- Принудительная заморозка:
VACUUM FREEZE users;     -- заморозить конкретную таблицу
VACUUM FREEZE;           -- заморозить все таблицы в базе

-- Посмотреть таблицы которым нужна заморозка:
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Часто задаваемые вопросы

В чём разница между VACUUM и VACUUM FULL?

Обычный VACUUM помечает место мёртвых строк как доступное для повторного использования внутри таблицы, но не возвращает место операционной системе. Выполняется параллельно с другими операциями. VACUUM FULL создаёт новую копию таблицы без мёртвых строк, возвращает место ОС — но блокирует таблицу полностью на всё время выполнения. На таблице 10GB это могут быть часы. Для онлайн-дефрагментации используйте pg_repack — работает без блокировки как VACUUM FULL, но не блокирует чтение и запись.

Почему autovacuum не успевает за нагрузкой?

По умолчанию autovacuum настроен консервативно — делает паузы между операциями чтобы не нагружать диск. На интенсивно обновляемых таблицах этого может не хватать. Решения: 1) Уменьшить autovacuum_vacuum_scale_factor для конкретной таблицы — запускать чаще. 2) Уменьшить autovacuum_vacuum_cost_delay глобально (например с 2ms до 0) — убрать паузы. 3) Увеличить autovacuum_max_workers. 4) Запускать VACUUM вручную в моменты низкой нагрузки. Просматривайте pg_stat_user_tables.n_dead_tup для мониторинга.

Как понять что таблица «раздута» в PostgreSQL?

Признаки раздутия: 1) n_dead_tup в pg_stat_user_tables > 10% от n_live_tup. 2) Размер таблицы (pg_relation_size) в разы больше чем должен быть при текущем количестве строк. 3) Постепенно замедляются запросы без видимых причин. Формула ожидаемого размера: количество строк × средний размер строки. Сравните с реальным: SELECT pg_size_pretty(pg_relation_size('users'));. Для визуального анализа раздутия используйте расширение pgstattuple: SELECT * FROM pgstattuple('users');.