VACUUM и раздувание таблиц

Откуда берутся мёртвые кортежи

Почему таблица вырастает до 9 ГБ, держа всего 4000 живых строк? Механизм встроен в каждую запись. Глава MVCC трека databases выводит это глубоко; SQL-автору нужно следствие. При MVCC Postgres никогда не перезаписывает строку на месте. UPDATE пишет новую версию строки и помечает старую мёртвой; DELETE просто помечает строку мёртвой. Старая версия задерживается, потому что снимок какой-то ещё работающей транзакции может захотеть её увидеть. Поэтому каждый UPDATE и DELETE порождает мёртвый кортеж — строку, невидимую новым запросам, но всё ещё физически занимающую страницу на диске.

Пока эти мёртвые кортежи не очищены, они стоят дважды: занимают место, и каждый последовательный или индексный скан всё равно читает страницы, на которых они сидят, и пропускает их. Таблица на 90% из мёртвых кортежей делает примерно 10× I/O за те же живые данные.

-- Посмотри ущерб на любой таблице:
SELECT 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
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'events', 'line_items')
ORDER BY n_dead_tup DESC;

Autovacuum: что его запускает

autovacuum — фоновый процесс, запускающий VACUUM автоматически. Он не ужимает файл — он помечает место мёртвых кортежей как переиспользуемое, чтобы новые строки заполняли дыры вместо расширения таблицы. Он также запускает ANALYZE, держащий статистику свежей (урок 03), и обновляет visibility map.

Он срабатывает на таблицу, когда:

n_dead_tup  >  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

Дефолты: threshold = 50, scale_factor = 0.2. Так таблица автовакуумится, когда мёртвые кортежи превышают 20% живых строк. Эти 0.2 нормальны для таблицы в 10k строк (вакуум при 2k мёртвых), но ужасны для таблицы в 100M строк — он ждёт 20M мёртвых кортежей, к которым сканы уже раздуты. Прод-правило: снижай scale_factor на больших/горячих таблицах, например ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);, чтобы он вакуумил при 2% нагрузки.

Visibility map и index-only scans

VACUUM поддерживает visibility map: битмап, помечающий страницы, где каждый кортеж видим всем транзакциям. Именно это делает возможным index-only scan — если страница вся-видима, Postgres может ответить из одного индекса без обращения к heap для проверки видимости (глава индексов трека databases копает глубже). Раздутая, недовакуумленная таблица имеет устаревший visibility map, поэтому «index-only» сканы тихо деградируют в heap-обращения. Вакуум — не просто сборка мусора; это то, что держит твой быстрейший тип скана быстрым.

VACUUM против VACUUM FULL — и прод-ловушка

• VACUUM (обычный) — возвращает мёртвое место на месте под переиспользование. Берёт лишь SHARE UPDATE EXCLUSIVE блокировку; чтения и записи продолжаются. Это то, что запускает autovacuum. Он не возвращает диск ОС, но останавливает рост раздувания.
• VACUUM FULL — переписывает всю таблицу в новый компактный файл, возвращая место ОС. Но он берёт ACCESS EXCLUSIVE блокировку на всю перезапись — каждое чтение и запись блокируются до конца. На большой таблице это минуты полного простоя. Никогда не запускай VACUUM FULL на живой прод-таблице.

Чтобы реально вернуть диск на раздутой прод-таблице, используй pg_repack (расширение): он перестраивает таблицу в свежую копию онлайн, беря exclusive-блокировку только на короткий финальный своп. Та же компактизация, без простоя.