Postgres VACUUM и bloat на собеседовании Data Engineer

Зачем нужен VACUUM

Postgres хранит данные по модели MVCC (multiversion concurrency control): чтобы читатели не блокировали писателей, каждая версия строки живёт в таблице отдельно. Когда вы делаете UPDATE или DELETE, старая версия строки не удаляется физически — она помечается как «мёртвый кортеж» (dead tuple) и остаётся лежать в файле таблицы. VACUUM — это фоновая уборка, которая освобождает место, занятое такими мёртвыми версиями, и делает его доступным для новых записей.

Если VACUUM не отрабатывает, мёртвые кортежи копятся, и таблица «раздувается» (bloat): физический размер файла растёт, хотя живых строк столько же. Раздутая таблица означает больше страниц для чтения, распухшие индексы и медленные запросы — даже простой SELECT по индексу начинает тянуть лишние блоки с диска. Поэтому на собесе DE VACUUM спрашивают не как «команду обслуживания», а как ключ к пониманию того, почему таблица тормозит и растёт без роста числа строк.

Мёртвые кортежи и MVCC

Разберём механику на примере обычного апдейта:

UPDATE users SET name = 'New' WHERE id = 42;

Под капотом происходит вот что:

1. Старая версия строки помечается как удалённая — в её служебном поле xmax записывается ID текущей транзакции.
2. Вставляется новая версия строки с новым xmin (ID транзакции, которая её создала).
3. Транзакции со старым снапшотом продолжают видеть старую версию, новые — видят новую. Ни одна не блокирует другую.

Мёртвый кортеж можно физически удалить только тогда, когда его уже не видит ни одна активная транзакция — то есть когда все снапшоты «моложе», чем удаляющий xmax. Эту границу Postgres называет vacuum horizon (горизонт очистки). VACUUM проходит по таблице, находит кортежи за горизонтом и помечает занятое ими место как свободное для повторного использования (записывает его во free space map). Важно: обычный VACUUM не возвращает место операционной системе — он лишь освобождает его под будущие вставки внутри той же таблицы.

Autovacuum

Руками VACUUM почти никто не гоняет — за это отвечает фоновый процесс autovacuum. Он запускается по таблице, когда число мёртвых кортежей превышает порог:

порог = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × число_строк

Дефолты — threshold = 50 и scale_factor = 0.2 (20% от размера таблицы). Для маленькой таблицы это ок, но для таблицы на 100 млн строк ждать, пока накопится 20 млн мёртвых кортежей, — уже дорого. Поэтому большим и горячим таблицам scale_factor снижают точечно:

-- убирать мёртвые строки, когда их набралось 5%, а не 20%
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.05);

Главная ловушка — долгие транзакции. Пока открыта старая транзакция (или висит забытый BEGIN, или idle in transaction), горизонт очистки не двигается: Postgres обязан сохранять версии строк, которые эта транзакция теоретически может увидеть. В результате autovacuum отрабатывает, но удалить ничего не может — bloat растёт, хотя вроде бы «всё чистится». Тот же эффект дают неиспользуемые слоты репликации и hot_standby_feedback на реплике. Отдельно autovacuum отвечает за заморозку старых xid (freeze) — это защита от transaction ID wraparound, и её нельзя отключать «для скорости».

VACUUM FULL и pg_repack

Если таблица уже сильно раздута, обычный VACUUM её не ужмёт — он вернёт место под переиспользование, но файл на диске меньше не станет. Чтобы физически сжать таблицу и вернуть место ОС, есть VACUUM FULL:

VACUUM FULL my_table;

Он переписывает всю таблицу в новый файл без мёртвых кортежей и заодно перестраивает индексы. Цена — блокировка ACCESS EXCLUSIVE на всю таблицу: на время операции недоступны и чтения, и записи. Плюс нужен запас места на диске под вторую копию таблицы. Поэтому VACUUM FULL уместен только в maintenance-окно, когда таблицу реально можно погасить.

Онлайн-альтернатива — расширение pg_repack. Оно перепаковывает таблицу без долгой эксклюзивной блокировки: создаёт копию, догоняет изменения по триггеру и берёт короткий лок только в самом конце на переключение.

pg_repack -t my_table -d my_db

На собесе про эту пару спрашивают почти всегда: «Чем VACUUM FULL отличается от pg_repack и когда что выбрать?». Короткий ответ — pg_repack для продакшена под нагрузкой, VACUUM FULL когда есть окно и не хочется ставить расширение.

Как измерить bloat

Быстрая прикидка — через системное представление pg_stat_user_tables. Смотрим долю мёртвых кортежей к живым:

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS size,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Ориентир: dead_ratio выше 20% — уже повод разбираться, почему autovacuum не справляется. Это оценка «на глаз»: точные цифры по фрагментации даёт расширение pgstattuple, а также готовые запросы вроде pg_bloat_check, которые считают ожидаемый и фактический размер таблицы. Но для собеса достаточно знать, что первый сигнал — растущий n_dead_tup и dead_ratio в pg_stat_user_tables.

Как это спрашивают на собесе

Тему любят раскручивать от практического симптома, а не от определения.

«Таблица растёт, а число строк стабильно. Почему?» Классика про bloat: мёртвые кортежи копятся быстрее, чем autovacuum их убирает. Дальше интервьюер ждёт, что вы вспомните про долгие транзакции, заниженный scale_factor и слоты репликации как причины, по которым горизонт очистки не двигается.

«Сделал VACUUM, а место на диске не освободилось. В чём дело?» Обычный VACUUM не отдаёт место ОС, а только помечает его для переиспользования внутри таблицы. Чтобы физически сжать файл — VACUUM FULL (с эксклюзивной блокировкой) или онлайн через pg_repack.

«Что такое transaction ID wraparound?» Счётчик транзакций 32-битный и зациклен. Если старые строки вовремя не «заморозить» (freeze), возможна ситуация, когда старые данные начнут выглядеть будущими и «пропадут». Именно поэтому нельзя глушить autovacuum — он отвечает и за freeze.

«Как ускорить autovacuum на большой горячей таблице?» Снизить autovacuum_vacuum_scale_factor для конкретной таблицы, поднять autovacuum_vacuum_cost_limit / уменьшить cost_delay, при необходимости добавить воркеров. Интервьюер смотрит, понимаете ли вы, что тюнить надо точечно, а не глобально.

Частые ошибки

• Считать, что VACUUM возвращает место на диск. Обычный VACUUM освобождает место только под переиспользование внутри таблицы. Диску его вернёт VACUUM FULL или pg_repack.
• Отключать autovacuum «ради производительности». Это отложенная бомба: bloat вырастет, а без freeze вы рискуете словить transaction ID wraparound и аварийную остановку базы.
• Игнорировать долгие транзакции. Один забытый BEGIN или зависшая idle in transaction держат горизонт очистки, и никакой VACUUM не удалит мёртвые строки.
• Гнать VACUUM FULL на проде под нагрузкой. Блокировка ACCESS EXCLUSIVE кладёт таблицу целиком. Под нагрузкой — только pg_repack и maintenance-окно.
• Оставлять дефолтный scale_factor на огромных таблицах. 20% от 100 млн строк — это 20 млн мёртвых кортежей до первой уборки. Для больших таблиц порог снижают точечно.

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

• Транзакции и MVCC для DE
• EXPLAIN и план запроса для DE
• Connection pooling для DE
• Postgres extensions для аналитики DE
• Подготовка к собесу Data Engineer

FAQ

Чем отличается VACUUM от VACUUM FULL?

Обычный VACUUM убирает мёртвые кортежи и помечает их место как свободное для будущих вставок в ту же таблицу, работает без блокировки записи и не возвращает место ОС. VACUUM FULL переписывает таблицу целиком в новый файл, физически сжимает её и возвращает место на диск, но берёт эксклюзивную блокировку и на время делает таблицу недоступной.

Почему после VACUUM таблица не уменьшилась?

Так и задумано: обычный VACUUM не отдаёт место операционной системе, он лишь делает его доступным для повторного использования внутри таблицы. Если нужно физически вернуть место на диск — используйте VACUUM FULL в maintenance-окно или pg_repack на живой базе.

Что мешает autovacuum удалять мёртвые строки?

Чаще всего — долгие или зависшие транзакции (idle in transaction), которые держат горизонт очистки: пока такая транзакция открыта, Postgres обязан сохранять версии строк, и удалить их нельзя. Реже виноваты неиспользуемые слоты репликации и hot_standby_feedback на реплике.

Как быстро понять, что таблица раздута?

Посмотреть n_dead_tup и отношение мёртвых кортежей к живым в pg_stat_user_tables: доля выше 20% — сигнал, что autovacuum не справляется. Для точного измерения фрагментации подключают расширение pgstattuple или используют готовые bloat-запросы.

Что такое transaction ID wraparound и при чём тут VACUUM?

ID транзакций в Postgres 32-битный и зациклен по кругу. Чтобы старые строки со временем не начали выглядеть «из будущего» и не исчезли, их нужно замораживать (freeze) — этим тоже занимается VACUUM/autovacuum. Поэтому autovacuum нельзя полностью отключать: без freeze база рано или поздно уйдёт в аварийный режим для защиты от wraparound.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.