Vacuum в PostgreSQL: почему база растёт, когда вы удаляете данные

MAATRIX

Вы почистили таблицу логов, удалили миллион старых строк — а du -sh на каталоге базы показывает ту же цифру, что и до чистки. Иногда даже больше. Это не баг и не глюк файловой системы: так устроен PostgreSQL изнутри. Разберёмся, куда девается место при DELETE, почему UPDATE тоже раздувает таблицу, и что с этим делать на практике — от чтения bloat до VACUUM FULL.

Как PostgreSQL хранит версии строк: MVCC простыми словами

PostgreSQL использует модель многоверсионного контроля параллелизма — MVCC (Multi-Version Concurrency Control). Смысл в том, что база не блокирует строку на время чтения: читающая транзакция видит свой снапшот данных, а пишущая в это же время может создавать новую версию той же строки. Никто друг другу не мешает, никаких блокировок на SELECT.

Технически это значит, что строка в таблице — это не единственная неизменяемая запись, а последовательность версий. Каждая версия называется tuple (кортеж). У каждого кортежа есть служебные поля xmin и xmax — номера транзакций, которые его создали и (если применимо) удалили. Когда транзакция читает таблицу, она смотрит на свой снапшот и решает, какие кортежи ей видны: те, что созданы до её старта и не удалены транзакцией, которая тоже до неё завершилась.

Из этого следует ключевая вещь: PostgreSQL физически никогда не изменяет строку на месте. Ни UPDATE, ни DELETE не трогают существующие байты на диске так, как это делает, например, простой файл при перезаписи. Вместо этого:

• DELETE помечает кортеж как удалённый (проставляет xmax текущей транзакцией), но не стирает его физически.
• UPDATE — это фактически DELETE старой версии плюс INSERT новой. Старый кортеж помечается удалённым, а рядом (обычно на той же странице, если хватает места, иначе на другой) появляется новая версия строки со своими индексными записями.

Это нужно для того, чтобы параллельные транзакции, стартовавшие раньше, продолжали видеть старую версию строки до своего завершения — иначе MVCC бы не работал.

Что происходит при UPDATE и DELETE на самом деле

Возьмём конкретный пример. У вас есть таблица orders на 10 млн строк, и вы выполняете:

UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '1 year';

Допустим, под условие попадает 3 млн строк. PostgreSQL для каждой из них:

1. Находит текущий видимый кортеж.
2. Проставляет ему xmax = номер вашей транзакции — кортеж стал "мёртвым" (dead tuple), но физически лежит на странице как лежал.
3. Создаёт новую версию строки со значением status = 'archived' и текущей транзакцией в xmin.
4. Обновляет индексы: в каждом индексе, где участвует изменённая строка, добавляется запись на новую версию (старая индексная запись тоже не удаляется мгновенно).

В результате после такого UPDATE у вас физически стало больше данных на диске, а не столько же. 3 млн старых версий строк никуда не делись — они просто помечены как невидимые для новых транзакций. Место, которое они занимают, освобождается не сразу и не автоматически в момент коммита — этим отдельно занимается VACUUM.

С DELETE логика похожа, только без создания новой версии: строка помечается удалённой и висит мёртвым кортежем, пока какой-то процесс не решит, что её точно никто больше не увидит, и не пометит место как переиспользуемое.

Отсюда и происходит то, что многие описывают как "распухание" — table bloat: физический размер файла таблицы (и её индексов) растёт быстрее, чем растёт количество актуальных строк, потому что мёртвые кортежи накапливаются и никуда не деваются сами по себе.

Почему таблица физически не уменьшается сама

Важно понимать разницу между "логическим" и "физическим" размером. Логически после DELETE 3 млн строк из таблицы на 10 млн у вас должно остаться 7 млн — и SELECT count(*) действительно покажет 7 млн. Но файл таблицы на диске (base/<oid>/<relfilenode> в файловой системе) как занимал условные N страниц по 8 КБ, так и продолжает их занимать. PostgreSQL не сжимает файл при удалении строк — он просто помечает место внутри страниц как свободное для повторного использования.

Здесь важна работа VACUUM (обычного, не FULL):

• Он проходит по страницам таблицы и находит мёртвые кортежи, которые уже не видны никакой активной транзакции (то есть старше самой старой открытой транзакции в системе — важна связка с xmin horizon).
• Помечает место этих кортежей как свободное внутри существующих страниц файла — но не возвращает эти страницы операционной системе.
• Обновляет visibility map — служебную карту, которая говорит планировщику, какие страницы содержат только гарантированно видимые всем строки (это ускоряет index-only scan и следующий vacuum).
• Обновляет статистику для планировщика запросов (частично — полную статистику собирает ANALYZE).

То есть после обычного VACUUM файл таблицы не уменьшается в байтах на диске. Освобождённое место просто становится доступным для новых INSERT и UPDATE — база начинает переиспользовать эти "дыры" вместо того, чтобы наращивать файл дальше. Если вставок в таблицу будет меньше, чем было удалений, место останется неиспользованным, и файл так и будет больше, чем "логически нужно". Именно это люди и называют bloat.

Если же VACUUM в принципе не выполняется вовремя (или отключен), мёртвые кортежи копятся, свободное место внутри страниц не помечается, и каждая новая вставка вынуждена выделять новые страницы в конце файла — таблица физически растёт безостановочно, даже если вы параллельно активно удаляете старые строки.

Autovacuum: как он работает и когда не успевает

С PostgreSQL версии 8.3 обычный VACUUM запускается автоматически фоновым процессом autovacuum — руками его в норме вызывать не нужно. Autovacuum смотрит на статистику по таблицам (сколько строк изменено/удалено с прошлого прохода) и решает, когда таблице пора на обработку. Решение основано на двух параметрах на таблицу:

• autovacuum_vacuum_threshold — минимальное число изменённых строк, чтобы вообще рассматривать таблицу;
• autovacuum_vacuum_scale_factor — доля от общего числа строк таблицы, которая добавляется к порогу.

Условие запуска примерно такое: изменённых_строк > threshold + scale_factor * количество_строк_в_таблице. Для маленьких и средних таблиц дефолтные значения (threshold = 50, scale_factor = 0.2, то есть 20% от размера таблицы) работают нормально. Для очень больших таблиц (десятки-сотни миллионов строк) 20% — это огромное число строк, и autovacuum будет срабатывать редко и опаздывать: пока накопится 20% мёртвых строк от таблицы на 200 млн записей, bloat успеет вырасти прилично.

Типичные причины, почему autovacuum не успевает за нагрузкой:

• Очень интенсивный поток UPDATE/DELETE — например, очередь заданий или таблица сессий, где строки обновляются тысячи раз в секунду. Autovacuum физически не успевает пройти таблицу целиком между накоплением новых мёртвых кортежей.
• Долгие открытые транзакции — если где-то в приложении осталась незакрытая транзакция (забытый BEGIN без COMMIT, зависшее соединение), она держит "горизонт видимости" (xmin horizon) неподвижным. VACUUM видит, что мёртвые кортежи новее этого горизонта потенциально ещё нужны, и не может их вычистить — сколько бы раз он ни запускался. Это одна из самых частых причин внезапного bloat при формально работающем autovacuum.
• autovacuum отключен глобально (autovacuum = off в postgresql.conf) или для конкретной таблицы (ALTER TABLE ... SET (autovacuum_enabled = false)) — иногда это делают ради временного ускорения массовой загрузки данных и забывают включить обратно.
• Не хватает workers — параметр autovacuum_max_workers ограничивает число параллельных процессов автовакуума; на инстансе с десятками активных таблиц они могут стоять в очереди друг за другом.
• Долгий vacuum на одну и ту же большую таблицу мешает подступиться к ней снова, пока не закончится текущий проход, и одновременно занимает I/O, из-за чего его иногда специально троттлят (autovacuum_vacuum_cost_delay), что дополнительно замедляет цикл.

Подробнее о том, почему именно вакуум может идти медленно и упираться в ресурсы, разобрано в статье про медленный VACUUM в PostgreSQL — там же практические настройки cost-based delay.

Как посмотреть bloat таблицы на практике

Прежде чем что-то чинить, стоит убедиться, что проблема действительно в bloat, а не в чём-то ещё. Несколько рабочих способов.

Базовая статистика по таблице — сколько живых и мёртвых кортежей PostgreSQL видит прямо сейчас:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Если n_dead_tup сопоставим или больше n_live_tup, а last_autovacuum был давно (или пусто) — это прямой сигнал, что вакуум не успевает или не работает на этой таблице.

Физический размер таблицы и индексов:

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Это покажет физический размер на диске, включая индексы и TOAST — сравните с тем, сколько места "логически" должны занимать актуальные строки (по количеству строк и примерному размеру записи), чтобы понять масштаб раздувания.

Точная оценка bloat — расширение pgstattuple даёт честные цифры по факту сканирования таблицы (это дороже по ресурсам, чем чтение статистики, потому что реально читает страницы):

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

В выводе будут поля dead_tuple_count, dead_tuple_percent, free_percent — доля мёртвых кортежей и доля свободного, но не возвращённого ОС места. Именно dead_tuple_percent и free_percent в сумме дают представление о том, сколько "воздуха" в таблице.

Для быстрой прикидки без установки расширений многие используют публичные bloat-запросы (эвристика по статистике страниц и столбцов) — они дают приблизительную оценку без полного сканирования, но могут ошибаться на таблицах с нестандартной структурой (много NULL, TOAST-данные и т.п.), поэтому воспринимайте их как ориентир, а не точную цифру.

VACUUM, VACUUM FULL и их реальная цена

Если видно, что bloat реальный и мешает (растёт I/O, медленнее работают запросы, база банально не помещается на диск), есть два инструмента.

Обычный VACUUM (можно запускать вручную поверх autovacuum):

VACUUM (VERBOSE, ANALYZE) orders;

Он не блокирует чтение и запись в таблицу — работает параллельно с обычной нагрузкой (использует только легковесную блокировку, которая не мешает SELECT/INSERT/UPDATE/DELETE). Освобождает место внутри страниц для переиспользования, но не уменьшает физический размер файла на диске. Это безопасная операция, которую можно гонять хоть постоянно.

VACUUM FULL — совсем другая история:

VACUUM FULL orders;

Он физически пересобирает таблицу: создаёт новый файл, переносит в него только живые строки в компактном виде, перестраивает индексы и подменяет старый файл новым. В результате файл на диске реально уменьшается до размера, соответствующего актуальным данным.

Цена этого — эксклюзивная блокировка таблицы (ACCESS EXCLUSIVE) на всё время операции. Это значит:

• Никакие SELECT, INSERT, UPDATE, DELETE к этой таблице не выполнятся, пока VACUUM FULL не закончится — они встанут в очередь и будут ждать.
• На большой таблице (десятки-сотни гигабайт) это может занять от минут до часов в зависимости от размера, скорости диска и загрузки — конкретное время сильно зависит от вашего железа и объёма данных, заранее точную цифру не назовёт никто.
• Дополнительно нужно временное место на диске — примерно в размере новой (компактной) копии таблицы и её индексов, пока старая версия ещё не удалена.

Из-за блокировки VACUUM FULL на проде почти никогда не запускают "как есть" в рабочее время на активно используемую таблицу — это фактически плановый даунтайм для конкретной таблицы. Практические альтернативы:

• Запускать в окно обслуживания с минимальной нагрузкой.
• Использовать расширение pg_repack — оно делает то же самое (пересобирает таблицу компактно), но без длительной эксклюзивной блокировки: создаёт копию таблицы, накатывает изменения через триггеры и подменяет таблицу быстрым атомарным переключением в конце.
• Настроить autovacuum агрессивнее для конкретной таблицы заранее (ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.05)), чтобы bloat вообще не накапливался до состояния, требующего VACUUM FULL.

Что касается ресурсов сервера под это — если таблицы крупные и вакуум регулярно упирается в диск, вопрос обычно упирается не только в настройки, а в железо: медленный диск делает и обычный VACUUM, и autovacuum ощутимо дольше. Если планируете конфигурацию сервера под базу с нагрузкой на запись, есть отдельный разбор тюнинга PostgreSQL под сервер, а если параллельно растёт ещё и WAL — это смежная, но отдельная тема, разобранная в статье про рост размера WAL в PostgreSQL.

Если же bloat уже привёл к тому, что диска физически не хватает и нужно быстро освободить место или увеличить том — это уже вопрос инфраструктуры, а не только базы, здесь пригодится общий разбор что делать, если на VPS закончилось место на диске.

Частые вопросы