🟢 Junior Level

VACUUM — процесс очистки PostgreSQL от “мёртвых” строк, которые остались после UPDATE и DELETE.

Простая аналогия: Представьте доску для заметок. Когда вы стираете заметку, остаётся место. VACUUM — это когда вы убираете стёртые клочки бумаги и освобождаете место для новых заметок.

Зачем нужен:

• В PostgreSQL UPDATE не перезаписывает строку, а создаёт новую. Старая остаётся “мёртвой”.
• DELETE не удаляет строку физически, а помечает как удалённую.
• Без VACUUM таблица будет расти бесконечно.

Пример:

-- Автоматический VACUUM (работает сам)
-- Настраивается в postgresql.conf:
-- autovacuum = on

-- Ручной запуск
VACUUM users;                    -- Обычный
VACUUM FULL users;               -- Полный (возвращает место ОС)
VACUUM VERBOSE users;            -- С подробностями

Важно:

• Обычный VACUUM НЕ возвращает место операционной системе
• Он лишь помечает место как свободное для новых данных
• VACUUM FULL возвращает место, но блокирует таблицу

---

🟡 Middle Level

Как MVCC создаёт мёртвые строки

-- Таблица users: 1000 строк

UPDATE users SET status = 'ACTIVE' WHERE id = 1;
-- Старая версия строки остаётся (dead tuple)
-- Новая версия создаётся

DELETE FROM users WHERE id = 2;
-- Строка помечена как удалённая (dead tuple)
-- Физически остаётся в файле

-- После 10,000 UPDATE/DELETE:
-- Живых строк: 1000
-- Мёртвых строк: 10,000  ← занимают место!

Обычный VACUUM vs VACUUM FULL

Параметр	VACUUM	VACUUM FULL
Возвращает место ОС	❌ Нет	✅ Да
Блокировка	Нет (чтение/запись)	AccessExclusiveLock
Скорость	Быстро	Медленно
Place для новых данных	✅ Освобождает	✅ Файл сжимается
Production	✅ Безопасен	❌ Только maintenance

Когда допустим VACUUM FULL:

1. В maintenance window, когда нет нагрузки
2. На реплике, временно отключённой от балансировщика
3. Для одноразовой «реанимации» после массового DELETE

Для регулярной очистки — настраивайте autovacuum, используйте pg_repack.

Фазы VACUUM

1. Scanning Heap — поиск мёртвых строк
   → Пропускает "чистые" страницы через Visibility Map

2. Vacuuming Indexes — удаление ссылок из индексов
   → Может быть параллельным (PG 13+)

3. Vacuuming Heap — очистка страниц таблицы

4. Truncating — отрезание пустых страниц в конце файла
   → Единственная фаза, возвращающая место ОС

Autovacuum

Автоматический VACUUM, который запускается сам:

-- Настройки по умолчанию
SHOW autovacuum;                        -- on
SHOW autovacuum_vacuum_threshold;       -- 50 строк
SHOW autovacuum_vacuum_scale_factor;    -- 0.2 (20%)

-- Запускается, когда:
-- dead_tuples > threshold + scale_factor * total_rows

-- Для таблицы 1000 строк:
-- 50 + 0.2 * 1000 = 250 мёртвых строк → запуск
-- Для таблицы 1 млн строк:
-- 50 + 0.2 * 1,000,000 = 200,050 мёртвых строк → запуск
-- Заметьте: для больших таблиц scale_factor 20% → autovacuum запускается
-- ОЧЕНЬ редко (нужно ждать 200K dead tuples)!

Настройка для конкретных таблиц

-- Для большой таблицы: чаще и агрессивнее
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.05,  -- 5% вместо 20%
    autovacuum_vacuum_cost_delay = 1        -- Меньше пауза → быстрее
);

-- Для маленькой таблицы: реже
ALTER TABLE settings SET (
    autovacuum_enabled = false  -- Отключить (если редко меняется)
);

Типичные проблемы

1. VACUUM не успевает

   -- Проверка: сколько мёртвых строк
   SELECT relname, n_dead_tup, last_autovacuum
   FROM pg_stat_user_tables
   WHERE n_dead_tup > 100000;
      
   -- Решение: настроить агрессивнее
   ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
   

2. Длинные транзакции мешают

   -- VACUUM не может удалить строки, которые видит открытая транзакция
      
   -- Найти виновника
   SELECT pid, now() - xact_start as duration, query
   FROM pg_stat_activity
   WHERE state = 'active'
   ORDER BY xact_start;
   

3. VACUUM FULL заблокировал таблицу

   -- ❌ В production!
   VACUUM FULL orders;  -- Все INSERT/UPDATE/DELETE ждут!
      
   -- ✅ Альтернатива без блокировки
   -- pg_repack
   pg_repack -t orders -d mydb
   

---

🔴 Senior Level

Visibility Map (VM) оптимизация

VM — битовая карта страниц таблицы
Каждая страница = 2 бита:
  - all-visible: ВСЕ строки видимы всем транзакциям
  - all-frozen: ВСЕ строки заморожены (для wraparound)

VACUUM использует VM:
  - Пропускает страницы с all-visible = 1
  → В 10-100 раз быстрее для "чистых" таблиц
  → Зависит от доли «чистых» страниц: если 95% all-visible → читает 5%.
    Если таблица постоянно обновляется — выигрыша почти нет.

Micro-vacuum (HOT)

Это НЕ отдельный процесс. PostgreSQL удаляет старые версии из HOT-цепочек во время обычных операций, если все версии на странице стали невидимы. «Мгновенная» очистка без autovacuum.

-- Благодаря HOT (Heap Only Tuple) обновлениям
-- PostgreSQL может делать "мини-очистку" во время SELECT/INSERT

-- Если цепочка HOT версий в одной странице:
-- - Старые версии удаляются сразу
-- - Без ожидания основного VACUUM

-- Это снижает нагрузку на autovacuum
-- Особенно для таблиц с частыми UPDATE

Cost-based VACUUM

Чтобы VACUUM не “положил” диск, он считает “стоимость” операций:

Операция                Стоимость
----------------------------------
Чтение из кэша          1.0
Чтение с диска          10.0
Запись                  20.0

autovacuum_vacuum_cost_limit = 200  -- Лимит перед паузой
autovacuum_vacuum_cost_delay = 2ms  -- Пауза после лимита

Для больших таблиц (ТБ):

-- Увеличиваем лимит → VACUUM работает быстрее
ALTER TABLE huge_table SET (
    autovacuum_vacuum_cost_limit = 1000,  -- Вместо 200
    autovacuum_vacuum_cost_delay = 1      -- Меньше пауза
);

Parallel VACUUM (PG 13+)

-- Параллельная очистка индексов
VACUUM (PARALLEL 4) orders;

-- autovacuum тоже может использовать параллелизм
-- Для таблиц с множеством индексов → ускорение в 3-5 раз

На PG 12 и ниже VACUUM выполняется в одном потоке. Для ускорения: настройте cost-based VACUUM (увеличьте cost_limit, уменьшите delay).

Anti-wraparound VACUUM

-- Самая важная задача VACUUM — предотвращение XID Wraparound

-- age(datfrozenxid) > autovacuum_freeze_max_age (200 млн)
-- → Агрессивный VACUUM:
--    - Невозможно отменить (CANCEL не работает)
--    - Будет работать, пока не "заморозит" всё
--    - Блокирует создание новых транзакций при > 1.5 млрд

-- Мониторинг:
SELECT 
    datname,
    age(datfrozenxid),
    ROUND(100.0 * age(datfrozenxid) / 2000000000, 2) as wraparound_pct
FROM pg_database;

-- > 1.5 млрд = 75% → КРИТИЧНО!

pg_stat_progress_vacuum (PG 12+)

-- Мониторинг в реальном времени
SELECT 
    pid,
    relid::regclass as table_name,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    ROUND(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) as progress_pct
FROM pg_stat_progress_vacuum;

-- Phase:
-- 'scanning heap'
-- 'vacuuming indexes'
-- 'vacuuming heap'
-- 'truncating heap'

На PG 11 и ниже pg_stat_progress_vacuum недоступен. Мониторьте через n_dead_tup в pg_stat_user_tables.

Index-Only VACUUM (PG 14+)

-- Пропуск очистки индексов, если они не нуждаются
VACUUM (INDEX_CLEANUP false) orders;

-- Экстренная очистка только Heap
-- → Быстрее, но индексы не обновляются
-- → Использовать ТОЛЬКО если индексы в порядке

На PG 13 и ниже этот параметр недоступен — VACUUM всегда чистит и heap, и индексы.

Борьба с Bloat: VACUUM FULL vs pg_repack

VACUUM FULL:

-- ❌ Блокирует таблицу (AccessExclusiveLock)
-- ❌ Пересоздаёт таблицу и ВСЕ индексы
-- ✅ Возвращает место ОС
-- ✅ Встроенный, не требует расширений

VACUUM FULL orders;

pg_repack:

-- ✅ Не блокирует (кратковременная блокировка в конце)
-- ✅ Возвращает место ОС
-- ✅ Требует установку расширения

-- Установка:
CREATE EXTENSION pg_repack;

-- Запуск из CLI:
pg_repack -t orders -d mydb

-- Как работает:
-- 1. Создаёт временную таблицу
-- 2. Копирует данные
-- 3. Ловит изменения через триггеры
-- 4. Мгновенная подмена + DROP старой

Edge Cases

1. hot_standby_feedback и Bloat

   -- На реплике:
   hot_standby_feedback = on
      
   -- Реплика просит мастер не удалять строки
   -- → Мастер копит dead tuples
   -- → Bloat на мастере!
      
   -- Решение: 
   -- - Мониторить Bloat на мастере
   -- - Регулярный VACUUM на реплике
   

2. Partitioned Tables

   -- VACUUM для партицированной таблицы
   -- → Запускается для КАЖДОЙ партиции отдельно
      
   -- Настройка на уровне партиций:
   ALTER TABLE orders_y2024 SET (
       autovacuum_vacuum_scale_factor = 0.01
   );
   

3. Temporary Tables

   -- autovacuum НЕ видит временные таблицы!
   -- Нужен ручной VACUUM
      
   VACUUM ANALYZE temp_import_data;
   

Production Experience

Реальный сценарий #1: Autovacuum не справляется

• E-commerce: 50 млн INSERT/UPDATE в день в orders
• Autovacuum: 20% scale factor → запускается редко
• Bloat: таблица 100 ГБ (должна быть 10 ГБ)
• Решение:

  ALTER TABLE orders SET (
      autovacuum_vacuum_threshold = 10000,
      autovacuum_vacuum_scale_factor = 0.01,
      autovacuum_vacuum_cost_limit = 1000
  );
  

• Результат: Bloat < 20%, производительность стабильна

Реальный сценарий #2: Wraparound emergency

• База: autovacuum отключён 2 года “для производительности”
• age(datfrozenxid) = 1.8 млрд (90%)
• Логи: “WARNING: database must be vacuumed within 200M transactions”
• Решение:
  • Срочный VACUUM FREEZE на всей БД
  • 18 часов работы
  • Включение autovacuum обратно
• Урок: НЕ отключайте autovacuum ГЛОБАЛЬНО. Для отдельных таблиц при bulk load — можно временно, но сразу включите обратно.

Monitoring

-- 1. Dead tuples
SELECT 
    relname, 
    n_dead_tup,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 2. XID age
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age DESC;

-- 3. Progress
SELECT * FROM pg_stat_progress_vacuum;

-- 4. Autovacuum настройки таблицы
SELECT 
    c.relname,
    c.reloptions
FROM pg_class c
WHERE c.reloptions::text LIKE '%autovacuum%';

-- 5. Bloat оценка
SELECT 
    schemaname, 
    relname, 
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC;

Best Practices

1. НЕ отключайте autovacuum ГЛОБАЛЬНО — настраивайте. Для отдельных редко обновляемых таблиц (справочники) можно локально отключить, но мониторьте XID age.
2. Мониторьте n_dead_tup — > 10% = проблема
3. age(datfrozenxid) < 1.5 млрд — предотвращайте wraparound
4. pg_repack вместо VACUUM FULL в production
5. Увеличивайте cost_limit для больших таблиц
6. Уменьшайте scale_factor для часто обновляемых таблиц
7. Parallel VACUUM (PG 13+) для таблиц с множеством индексов
8. hot_standby_feedback = on → мониторьте Bloat на мастере

Резюме для Senior

• VACUUM удаляет dead tuples, НЕ возвращает место ОС
• VACUUM FULL возвращает место, но блокирует таблицу
• Visibility Map оптимизирует: пропускает “чистые” страницы
• HOT делает micro-vacuum во время обычных операций
• Wraparound — реальная угроза → мониторьте age(datfrozenxid)
• pg_repack для production без блокировок
• autovacuum настраивайте индивидуально для горячих таблиц
• Длинные транзакции — главный враг VACUUM

---

🎯 Шпаргалка для интервью

Обязательно знать:

• VACUUM: удаляет dead tuples (помечает место свободным), НЕ сжимает файлы
• VACUUM FULL: пересоздаёт таблицу → возвращает место ОС, но AccessExclusiveLock
• Autovacuum: запускается когда dead_tuples > threshold + scale_factor × total_rows
• Visibility Map: пропускает «чистые» страницы → VACUUM в 10-100 раз быстрее
• HOT (Heap Only Tuple): micro-vacuum — удаление старых версий в одной странице
• Cost-based VACUUM: стоимость операций → пауза чтобы не «положить» диск
• Anti-wraparound VACUUM: age > 200 млн → агрессивный, CANCEL не работает
• pg_stat_progress_vacuum (PG 12+): мониторинг в реальном времени
• pg_repack: VACUUM FULL без блокировки (триггеры + подмена)
• Parallel VACUUM (PG 13+): параллельная очистка индексов
• Index-Only VACUUM (PG 14+): VACUUM (INDEX_CLEANUP false) — только Heap

Частые уточняющие вопросы:

• «Почему autovacuum не справляется?» → scale_factor 20% слишком много для горячих таблиц
• «Как бороться с Bloat без блокировки?» → pg_repack
• «Что будет, если age(datfrozenxid) = 1.9 млрд?» → Критично! Срочный VACUUM FREEZE
• «Почему длинные транзакции мешают VACUUM?» → VACUUM не удаляет строки, которые видит транзакция

Красные флаги (НЕ говорить):

• ❌ «VACUUM возвращает место ОС» (нет, только помечает свободным)
• ❌ «autovacuum можно отключить» (Wraparound → READ ONLY!)
• ❌ «VACUUM FULL безопасен в production» (блокирует ВСЕ операции!)
• ❌ «hot_standby_feedback без последствий» (Bloat на мастере!)

Связанные темы:

• [[Как работает MVCC в PostgreSQL]] → dead tuples, Hint Bits, XID Wraparound
• [[Зачем нужен ANALYZE]] → Autovacuum запускает ANALYZE
• [[Как оптимизировать медленные запросы]] → Bloat влияет на производительность