← назад к разделу

Вы заметили, что таблица на диске занимает 10 ГБ, хотя реальных данных там в три раза меньше. Или PostgreSQL вдруг начинает тормозить без видимой причины. Скорее всего, дело в bloat — накопившихся мёртвых строках. Разберём, почему это происходит и как с этим справляться.

Почему строки не удаляются сразу

PostgreSQL использует подход, который называется MVCC (Multi-Version Concurrency Control — управление конкурентным доступом через версии). Идея такая: когда вы делаете UPDATE или DELETE, старая строка не стирается немедленно. Она помечается как «мёртвая» и остаётся в файле таблицы.

Зачем? Потому что в этот момент другая транзакция может читать старую версию данных — и должна видеть её консистентной. Пока хоть одна открытая транзакция может видеть старую строку, PostgreSQL не имеет права её удалить.

В итоге после интенсивной работы с данными в таблицах накапливаются мёртвые строки (dead tuples). Именно от них и раздувается размер таблицы — это и есть bloat.

Что делает VACUUM

VACUUM — это команда уборки. Она проходит по таблице и делает три вещи:

Освобождает место от мёртвых строк. Место не возвращается операционной системе — файл таблицы не уменьшается. Зато освобождённые страницы заносятся в FSM (Free Space Map, карту свободного места), и новые строки записываются туда, не раздувая файл дальше.

Обновляет visibility map. Это карта страниц, где все строки гарантированно видны всем транзакциям. Она нужна для эффективной работы Index Only Scan — без неё планировщик вынужден лезть в саму таблицу даже при запросе только по индексу.

Предотвращает XID wraparound. Об этом подробнее в отдельном разделе ниже.

VACUUM не блокирует чтение и запись — он работает с блокировкой типа SHARE UPDATE EXCLUSIVE. Ждать придётся только командам DDL вроде ALTER TABLE.

Три варианта команды

-- Обычный VACUUM: освобождает мёртвые строки, не блокирует таблицу
VACUUM order_doc;

-- С анализом: заодно обновляет статистику для планировщика
VACUUM ANALYZE order_doc;

-- Полная перезапись таблицы: возвращает место ОС, но блокирует всё
VACUUM FULL order_doc;

VACUUM и VACUUM ANALYZE — безопасно запускать в любое время.

VACUUM FULL — принципиально другая операция. Она полностью перезаписывает таблицу с нуля и при этом берёт блокировку ACCESS EXCLUSIVE. Это значит: пока идёт VACUUM FULL, таблица недоступна ни для чтения, ни для записи. На больших таблицах это могут быть часы. В продакшне почти никогда не применяют.

Альтернатива VACUUM FULL без блокировки — утилита pg_repack (или pg_squeeze). Она перестраивает таблицу в фоне, не мешая работе приложения:

pg_repack -d mydb -t order_doc

VACUUM ANALYZE стоит запускать вручную после массового UPDATE или DELETE — не ждите, пока сработает автоматика: планировщик может долго работать с устаревшей статистикой.

autovacuum — автоматическая уборка

PostgreSQL запускает VACUUM автоматически через демон autovacuum. Он следит за каждой таблицей и запускает уборку, когда накопилось достаточно мёртвых строк.

Порог срабатывания по умолчанию:

мёртвые строки > 50 + 0.2 × количество живых строк

На таблице в миллион строк autovacuum запустится при ~200 000 мёртвых. Для большинства таблиц этого достаточно.

Но для больших и активных таблиц дефолтные 20% — это слишком много. Настройку можно задать прямо на таблице:

ALTER TABLE order_doc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.05
);

Теперь autovacuum запустится при ~50 000 мёртвых строк вместо 200 000 — bloat будет меньше.

Как обнаружить bloat

Быстрый взгляд через системную статистику:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;

На что смотреть:

  • dead_pct > 20% — таблица кандидат на ручной VACUUM.
  • last_autovacuum давно (больше суток на горячей таблице) — autovacuum не успевает.

Для точной картины есть расширение pgstattuple:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('order_doc');
-- dead_tuple_percent > 30% — таблица явно распухла

Индексы тоже распухают — отдельно от таблиц. Лечится командой REINDEX CONCURRENTLY (доступна с PostgreSQL 12) — не блокирует таблицу во время перестройки.

fillfactor — место под обновления

По умолчанию PostgreSQL заполняет страницы данными до конца (fillfactor = 100). При UPDATE обновлённая строка чаще всего не помещается на ту же страницу и пишется на новую — а в индексах появляется лишняя запись.

Если таблица активно обновляется, имеет смысл оставить на страницах запас:

ALTER TABLE order_doc SET (fillfactor = 85);
VACUUM FULL order_doc;  -- применяет новый fillfactor (один раз, в окно обслуживания)

С fillfactor = 85 страницы заполняются только на 85%. Когда приходит UPDATE, обновлённая строка часто помещается на ту же страницу — это называется HOT update (Heap-Only Tuple). Индекс при этом не трогается, нагрузка меньше.

Когда autovacuum не справляется

Есть несколько ситуаций, когда autovacuum работает, но мёртвые строки всё равно накапливаются.

Долгая транзакция. MVCC запрещает удалять строки, которые видит хоть одна открытая транзакция. Если у вас висит транзакция часами — bloat будет расти независимо от autovacuum.

SELECT pid,
       age(now(), xact_start) AS xact_age,
       state,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

Если xact_age больше 5 минут — стоит разобраться, что там. Типичные причины: незакрытая сессия в коде, зависший фоновый процесс.

Хорошая практика — установить таймаут idle_in_transaction_session_timeout = '30s': он автоматически закрывает транзакции, которые начали, но ничего не делают.

Зависший replication slot. Если реплика или подписчик сильно отстала, PostgreSQL держит WAL-файлы до момента, пока они не будут прочитаны. Это мешает и autovacuum освобождать старые строки.

Prepared-транзакции. Команды PREPARE TRANSACTION создают «подвешенные» транзакции, которые живут до явного COMMIT PREPARED или ROLLBACK PREPARED.

SELECT * FROM pg_prepared_xacts;

Если там что-то зависло — это блокирует уборку так же, как долгая обычная транзакция.

autovacuum выключен. Иногда его отключают перед массовой загрузкой данных и забывают включить обратно.

SELECT name, setting FROM pg_settings WHERE name = 'autovacuum';

SELECT relname, reloptions
FROM pg_class
WHERE relkind = 'r' AND reloptions::text LIKE '%autovacuum%';

XID wraparound — критическая ситуация

У PostgreSQL есть 32-битный счётчик транзакций (XID). Когда он исчерпывает ~2.1 миллиарда значений и «проходит круг», база может перепутать старые и новые транзакции. Чтобы этого не произошло, VACUUM «замораживает» старые строки — помечает их как видимые всем навсегда.

Проверить, насколько близко до опасной отметки:

SELECT datname,
       age(datfrozenxid)         AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_left
FROM pg_database;

Если xid_age больше 1.5 миллиарда — autovacuum не справляется, нужно вмешаться вручную. PostgreSQL в таком состоянии начинает сам замедлять запись, предупреждая об опасности.

Что делать, если таблица распухла

Алгоритм диагностики по шагам:

  1. Смотрим n_dead_tup > 20% от n_live_tup — autovacuum не отрабатывает.
  2. Смотрим last_autovacuum — если давно, ищем причину: долгие транзакции, replication slots, отключённый autovacuum.
  3. pgstattuple dead_tuple_percent > 30% — запускаем VACUUM <table> вручную. Если не помогает — снижаем autovacuum_vacuum_scale_factor для этой таблицы.
  4. fillfactor дефолтный на активно обновляемой таблице — меняем на 80–90.
  5. Нужно вернуть место ОС — pg_repack в рабочие часы вместо VACUUM FULL.
  6. Bloat индексов — REINDEX CONCURRENTLY.

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

VACUUM FULL в продакшне в рабочее время. Блокирует таблицу полностью. Используйте pg_repack.

autovacuum отключён глобально. Иногда отключают для ускорения массовой загрузки, но забывают вернуть. Без autovacuum bloat растёт неконтролируемо.

Дефолтный scale_factor 0.2 на больших таблицах. На таблице в 10 миллионов строк это 2 миллиона мёртвых — слишком много. Снижайте до 0.05 для активных таблиц.

Долгие транзакции без таймаута. Одна висящая транзакция блокирует уборку для всей базы. Установите idle_in_transaction_session_timeout.

Не делать VACUUM ANALYZE после массового обновления. Планировщик будет работать с устаревшей статистикой, пока autovacuum не доберётся сам — а это может занять время.

Коротко

  • PostgreSQL не удаляет строки сразу — старые версии накапливаются как dead tuples (MVCC).
  • VACUUM освобождает место внутри файла, обновляет visibility map и предотвращает XID wraparound.
  • VACUUM не возвращает место операционной системе — файл не уменьшается.
  • VACUUM FULL возвращает место, но блокирует таблицу полностью. В продакшне вместо него — pg_repack.
  • autovacuum работает автоматически, но на больших таблицах дефолтный scale_factor 0.2 слишком высокий — снижайте до 0.05.
  • Долгие транзакции, зависшие replication slots и prepared-транзакции мешают уборке.
  • fillfactor 80–90 на активно обновляемых таблицах снижает нагрузку через HOT updates.
  • XID wraparound — критическая ситуация: следите за age(datfrozenxid).

Что почитать дальше

  • Индексы в PostgreSQL — как работает Index Only Scan и visibility map.
  • Уровни изоляции транзакций — MVCC и видимость строк.
  • Мониторинг PostgreSQL — pg_stat_user_tables и другие метрики.