Vacuum и bloat в Postgres: MVCC, autovacuum, wraparound

PostgreSQL никогда не перезаписывает строку на месте. UPDATE записывает новую версию и оставляет старую на странице; DELETE лишь помечает версию как удалённую. Эти мёртвые версии остаются, пока VACUUM не освободит место, и вся история с bloat таблиц, загадочным ростом таблицы с постоянным числом строк и тревожным сообщением в логе про wraparound идентификаторов транзакций - именно об этом.

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

Почему Postgres оставляет мёртвые строки#

PostgreSQL использует многоверсионное управление конкурентным доступом. У каждой версии строки есть два скрытых столбца: xmin - транзакция, которая её создала, и xmax - транзакция, которая её удалила или заменила. Транзакция видит версию, если xmin зафиксирована до начала её снимка, а xmax либо пуст, либо принадлежит транзакции, которую она не видит.

SELECT xmin, xmax, id, status FROM app.orders WHERE id = 42;

Такая схема даёт нечто ценное: читатели никогда не блокируют писателей, а писатели - читателей. Длинный отчёт может выполняться десять минут на согласованном снимке, пока приложение продолжает писать, потому что нужные ему старые версии всё ещё на месте.

Цена в том, что они всё ещё на месте. Таблица, в которой каждая строка обновляется десять раз в день, к вечеру хранит одиннадцать версий каждой строки, если только что-нибудь не убирает остальные десять. Это «что-нибудь» - vacuum.

Что делает VACUUM, а что нет#

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

• Удаляет мёртвые версии строк и их записи в индексах.
• Записывает освобождённое место в free space map, чтобы новые строки могли писаться в него.
• Обновляет карту видимости, благодаря которой возможны index-only scan.
• Замораживает старые версии строк, чтобы их идентификаторы транзакций можно было использовать повторно.
• Обрезает пустые страницы в конце таблицы, на время беря для этого краткую эксклюзивную блокировку.

Чего он не делает - не возвращает место операционной системе. Таблица, выросшая до 4 GB и очищенная vacuum до 1 GB живых данных, всё равно занимает на диске 4 GB; разница - это свободное место внутри файла, которое PostgreSQL будет использовать повторно. Для таблицы, которая обновляется с постоянной скоростью, это именно то, что нужно: место вечно перерабатывается, и ничего переписывать не требуется. Проблема возникает только после единичного события, например удаления 80% таблицы, когда это место больше никогда не понадобится.

ANALYZE - отдельная задача, которая делает выборку из таблицы и обновляет статистику для планировщика. Autovacuum выполняет обе, по разным порогам. VACUUM (VERBOSE, ANALYZE) app.orders; запускает их вместе вручную и печатает, что нашёл, - это первая команда, которую стоит выполнить, когда вы что-то подозреваете.

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

Пороги autovacuum в реальных числах#

Autovacuum просыпается каждые autovacuum_naptime (по умолчанию одна минута), просматривает каждую таблицу и запускает worker, если таблица превысила порог. Порогов три, и они вычисляются арифметически, без магии:

Vacuum по вставкам добавлен в PostgreSQL 13 и важен для таблиц только с добавлением, где нет мёртвых строк, но всё равно нужны заморозка и поддержка карты видимости.

Ещё три настройки определяют, насколько усердно worker работает после запуска: autovacuum_max_workers (3), autovacuum_vacuum_cost_limit (наследует vacuum_cost_limit, 200) и autovacuum_vacuum_cost_delay (2 ms начиная с PostgreSQL 12, а раньше 20 ms). Вместе они притормаживают vacuum, чтобы он не насыщал диск. На современных накопителях стандартное ограничение консервативно; поднять cost limit - обычно первое изменение, а как это выглядит в контексте остальной конфигурации, показано в статье настройка PostgreSQL для небольших серверов.

Стандартный масштабный коэффициент 20% - настройка, с которой стоит поспорить. На таблице в 10 миллионов строк это означает два миллиона мёртвых строк до того, как что-либо произойдёт, а потом один громадный vacuum. На небольшом сервере лучше меньше и чаще.

Почему autovacuum отстаёт#

Vacuum может удалить версию строки, только если её не может понадобиться ни одной работающей транзакции. Это единственное правило стоит за каждым случаем «autovacuum работает постоянно, а таблица продолжает расти».

Долгая транзакция. Сессия, выполнившая BEGIN час назад, держит снимок, и каждую мёртвую строку, созданную с тех пор, придётся сохранять на случай, если она в неё заглянет. Сюда входит и сессия в состоянии idle in transaction - обычно это приложение, которое открыло транзакцию, отправило запрос к чему-то другому и забыло о ней.

Брошенный слот репликации. Слот удерживает горизонт xmin ради реплики, которая, возможно, никогда не вернётся. Неактивный слот с лёгкостью остановит vacuum во всём кластере.

Зависшая подготовленная транзакция. Двухфазная фиксация, которую подготовили и так и не зафиксировали. Случай редкий и совершенно незаметный, пока не начнёте искать.

Обратная связь от standby. При hot_standby_feedback = on долгий запрос на реплике задерживает очистку на основном сервере. Для этого настройка и существует, но получается, что отчёт на реплике может раздуть основной сервер.

Все четыре находятся тремя запросами:

-- Sessions holding back cleanup, oldest firstSELECT pid, state, age(backend_xmin) AS xmin_age,       now() - xact_start AS transaction_age, left(query, 60) AS queryFROM pg_stat_activityWHERE backend_xmin IS NOT NULLORDER BY age(backend_xmin) DESC;-- Replication slots, active or notSELECT slot_name, active, age(xmin) AS xmin_age, wal_statusFROM pg_replication_slots;-- Forgotten prepared transactionsSELECT gid, prepared, owner, database FROM pg_prepared_xacts;

Исправление для первой причины - idle_in_transaction_session_timeout, заданный для роли, чтобы забытая сессия закрывалась сама через минуту, а не через выходные. Для второй - удалить слот, когда вы убедились, что реплики больше нет. Стоит назвать ещё две причины: таблица настолько велика, что один worker не успевает закончить до начала следующего цикла, и блокировки: VACUUM уступает DDL, так что autovacuum могут раз за разом отменять из-за миграции, которая продолжает брать конфликтующую блокировку.

Как измерить мёртвые строки и bloat#

Представления статистики дают счётчики мёртвых строк бесплатно. Вот запрос, который стоит сохранить:

SELECT relname AS table,       n_live_tup AS live, n_dead_tup AS dead,       round(100 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1)) AS dead_pct,       last_autovacuum, last_autoanalyze, autovacuum_countFROM pg_stat_user_tablesWHERE n_dead_tup > 1000ORDER BY n_dead_tup DESCLIMIT 20;

Сигнал - таблица с долей мёртвых строк выше примерно 20% и last_autovacuum, равным null или отстоящим на дни. Для точного ответа, а не оценки, расширение pgstattuple прочитает таблицу и скажет всё до конца:

CREATE EXTENSION IF NOT EXISTS pgstattuple;SELECT * FROM pgstattuple('app.orders');          -- exact, reads everythingSELECT * FROM pgstattuple_approx('app.orders');   -- fast estimate

Важны два столбца: dead_tuple_percent и free_percent. Таблица с 5% свободного места здорова; та, у которой свободно 60%, через что-то прошла.

Пока идёт vacuum, pg_stat_progress_vacuum показывает, на какой он фазе и какую часть таблицы уже прошёл, - это отвечает на вопрос «завис он или просто медленный». А если ваш сервер пишет логи в стандартный вывод, то log_autovacuum_min_duration = 1s добавляет строку в лог каждый раз, когда vacuum длится дольше секунды, - самое дешёвое раннее предупреждение, и оно попадает туда, где вы уже читаете консоль. Общий принцип изложен в статье мониторинг, который что-то вам говорит: число, на которое никто не смотрит, - не мониторинг.

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

Глобальные настройки - тупой инструмент, потому что таблица, которой нужно внимание, почти никогда не является средней. Параметры хранения на уровне таблицы переопределяют их:

-- A hot table: clean at 1% dead, and do not throttleALTER TABLE app.sessions SET (  autovacuum_vacuum_scale_factor = 0.01,  autovacuum_vacuum_threshold = 100,  autovacuum_vacuum_cost_delay = 0);-- A big append-only table: analyze more often, vacuum on insertsALTER TABLE app.events SET (  autovacuum_analyze_scale_factor = 0.02,  autovacuum_vacuum_insert_scale_factor = 0.05);

Ещё один параметр, который стоит знать, - fillfactor. По умолчанию он равен 100, то есть страницы заполняются целиком. Если поставить 90 для таблицы с частыми обновлениями строк, PostgreSQL оставляет на каждой странице место, чтобы новая версия строки могла лежать рядом со старой. Это позволяет выполнить heap-only tuple update, которому вообще не нужно трогать индексы, - гораздо дешевле, и vacuum остаётся меньше работы. Параметр действует только на страницы, записанные после изменения, поэтому вступает в силу постепенно.

ALTER TABLE app.sessions SET (fillfactor = 90);

Учтите взаимодействие с индексами: HOT-обновление возможно, только если не изменился ни один индексированный столбец. Индекс на столбец, который ваше приложение постоянно обновляет, обходится вам дважды, и это одна из причин, по которым статья индексы PostgreSQL простыми словами выступает за меньшее число индексов, а не за большее.

Wraparound идентификаторов транзакций#

Идентификаторы транзакций - 32-разрядные числа. PostgreSQL видит около двух миллиардов транзакций в прошлое; за этим пределом идентификатор стал бы казаться находящимся в будущем, и старые строки стали бы невидимыми - тихая катастрофическая потеря данных. Чтобы этого не случилось, vacuum замораживает старые версии строк, помечая их видимыми для всех независимо от идентификатора транзакции.

Поэтому vacuum не бывает необязательным, и поэтому PostgreSQL запустит anti-wraparound vacuum для таблицы, у которой самая старая незамороженная транзакция достигла autovacuum_freeze_max_age (200 миллионов), даже если autovacuum полностью выключен. Проверьте, где вы находитесь:

SELECT datname, age(datfrozenxid) AS xid_ageFROM pg_database ORDER BY xid_age DESC;SELECT relname, age(relfrozenxid) AS xid_ageFROM pg_class WHERE relkind IN ('r','m','t')ORDER BY xid_age DESC LIMIT 10;

Меньше 200 миллионов - норма. Если значение стабильно растёт дальше, значит anti-wraparound vacuum блокирует одна из причин из раздела выше, - найдите её сейчас же, потому что часы не останавливаются. По мере приближения к пределу сервер пишет в лог всё более суровые предупреждения, а до наступления реальной опасности отказывается начинать транзакции, которым нужен новый идентификатор. Сам VACUUM в этот момент по-прежнему работает, и именно так вы восстанавливаетесь; подсказка в логе про однопользовательский режим - крайняя мера, а не первый шаг.

В современных версиях есть два смягчающих средства. PostgreSQL 14 добавил failsafe, который заставляет vacuum пропускать очистку индексов и игнорировать паузы по стоимости, когда таблица становится опасно старой, так что он завершается так быстро, как позволяет диск. А PostgreSQL 17 переработал то, как vacuum хранит в памяти указатели на мёртвые строки, убрав прежний фактический потолок в 1 GB на память, которую мог использовать один vacuum, что существенно сокращает худшие случаи. Ни то ни другое не заменяет выяснения, что вообще заблокировало очистку.

У идентификаторов multixact есть собственная параллельная версия этой проблемы, которой управляет autovacuum_multixact_freeze_max_age (400 миллионов). Их расходуют блокировки на уровне строк вроде SELECT ... FOR SHARE, так что нагрузка с интенсивными блокировками может упереться в этот предел первой.

Исправление bloat, который уже случился#

Когда таблица уже раздулась, обычный vacuum файл не уменьшит. Варианты, от худшего к лучшему:

1. Оставить как есть. Если таблица будет использовать это место в своём обычном темпе, не делайте ничего. Это верный ответ чаще, чем принято считать.
2. `VACUUM FULL app.orders;` Переписывает таблицу, возвращает место, берёт эксклюзивную блокировку и требует диск под вторую копию. Годится для таблицы в 200 MB в три часа ночи, но не для 40 GB днём.
3. `pg_repack`. Расширение, которое делает ту же перезапись, оставляя таблицу доступной и беря краткую блокировку только в момент подмены. Ему всё равно нужно место на диске под копию, и его нужно установить на сервер, а это разрешает не каждый хостинг.
4. Дамп и восстановление. Для целой базы данных, которая пошла совсем не так, pg_dump и восстановление в свежую базу дают максимально компактный результат. Это также простой. Форматы и параллельные опции описаны в руководстве по pg_dump и pg_restore.
5. Партиционирование, затем удаление. Если bloat возникает из-за удаления старых строк, партиционируйте по времени. Удаление партиции мгновенно и не оставляет ничего для vacuum.

Индексы тоже раздуваются, и отдельно. REINDEX INDEX CONCURRENTLY orders_created_idx; (PostgreSQL 12 и новее) перестраивает индекс, не блокируя запись, и обычно это лучший первый шаг, чем полная перезапись таблицы, потому что bloat индексов встречается чаще, а перестройка дешевле.

Две практические заметки про диск. И VACUUM FULL, и pg_repack требуют одновременно примерно вдвое большего размера таблицы свободного места, а на тарифе в 20 GB это реальное ограничение, так что проверьте pg_total_relation_size до начала. И сначала сделайте бэкап. В RE:NODE слоты для бэкапов идут с каждым тарифом баз данных, бэкапы хранятся вне машины, которую защищают, а восстановление - это кнопка, а не тикет в поддержку; панель также рисует график диска относительно лимита тарифа, так что вы увидите, поместится ли перезапись. Довод в пользу того, чтобы один раз нажать restore намеренно, изложен в статье резервное копирование базы данных и доказательство того, что она восстанавливается.

И последний полезный приём: чтобы полностью очистить таблицу, TRUNCATE мгновенен и сразу освобождает место, потому что создаёт новый пустой файл, а не помечает строки мёртвыми. DELETE FROM table на миллионе строк создаёт миллион мёртвых строк, а потом очень долгий vacuum.

FAQ#

Что такое bloat таблицы в PostgreSQL?

Место внутри файлов таблицы, занятое версиями строк, которых больше никто не видит. Оно возникает от обновлений и удалений, которые никогда не перезаписывают на месте. Vacuum делает это место пригодным для повторного использования; файл он не уменьшает, пока вы не перепишете таблицу.

Нужно ли запускать VACUUM вручную?

Обычно нет: autovacuum справляется с рутиной лучше, чем задача в cron, потому что реагирует на реальную интенсивность изменений. Запускайте VACUUM (ANALYZE) вручную после массовой загрузки или массового удаления, когда нужно обновить статистику сразу, а не при следующем пороге.

Безопасно ли запускать VACUUM FULL в продакшене?

Для ваших данных безопасно, для вашей доступности враждебно: он берёт эксклюзивную блокировку на всё время перезаписи и требует места на диске под вторую копию таблицы. Запланируйте его или используйте pg_repack, если ваш хостинг разрешает это расширение.

Почему autovacuum работает постоянно, а таблица всё растёт?

Что-то удерживает горизонт xmin, и vacuum не находит того, что ему разрешено удалить. Поищите старую транзакцию в pg_stat_activity, неактивный слот репликации или подготовленную транзакцию, которую так и не зафиксировали.

Что будет, если wraparound транзакций всё-таки наступит?

Сервер перестанет выдавать новые идентификаторы транзакций, чтобы защитить данные, а не потерять их. Vacuum при этом работает, так что восстановление состоит в том, чтобы дать anti-wraparound vacuum завершиться, устранив то, что его блокировало. Этого можно полностью избежать, следя за age(datfrozenxid).

Замедляет ли большое число мёртвых строк запросы?

Да, двумя способами. Таблица занимает больше страниц, поэтому сканирования читают больше, а кэш вмещает пропорционально меньше настоящих данных. А таблица, с которой vacuum не справляется, теряет index-only scan, потому что карта видимости никогда не помечается актуальной.