Java Interview Questions

Головна / Усі відповіді / Бази даних SQL PostgreSQL

Питання 18 · Розділ 1

Що таке VACUUM в PostgreSQL?

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

Ukrainian Бази даних SQL PostgreSQL Вихідний Markdown
Мовні версії: English Russian Ukrainian

🟢 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
Швидкість Швидко Повільно
Місце для нових даних ✅ Звільняє ✅ Файл стискається
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 впливає на продуктивність