VACUUM и autovacuum в PostgreSQL: почему таблица растёт

После массового UPDATE или DELETE таблица PostgreSQL может остаться прежнего размера или продолжить расти. Это не доказывает, что autovacuum сломан. При MVCC старые версии строк некоторое время нужны другим транзакциям, а после очистки обычный VACUUM оставляет освободившееся место внутри файла для повторного использования.

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

SQL / 01

VACUUM освобождает место для повторного использования

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

Почему UPDATE и DELETE не уменьшают файл

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

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

Рост бывает штатным. Например, таблица однажды увеличилась с 20 до 30 ГБ, затем стабилизировалась, а свободное место стало повторно использоваться. Проблема начинается, когда размер продолжает расти при сопоставимом количестве живых строк либо очистка систематически не успевает за нагрузкой.

Что делает обычный VACUUM

Обычный VACUUM работает с таблицей на месте. Он удаляет доступные для очистки старые версии строк, может очистить связанные записи индексов, обновляет free space map и visibility map, а также замораживает достаточно старые версии для защиты от переполнения идентификаторов транзакций.

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

Обычный VACUUM совместим с обычными чтениями и изменениями данных, но создаёт I/O-нагрузку и конфликтует с операциями, которые меняют определение таблицы. Ручной запуск в часы пик всё равно требует оценки нагрузки.

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

Почему VACUUM FULL не стоит запускать первым

VACUUM FULL переписывает таблицу в новый компактный файл и заново строит её индексы. Так он действительно возвращает неиспользуемое место операционной системе, но платой становятся дополнительный диск на время операции и блокировка ACCESS EXCLUSIVE. Пока она удерживается, другие обращения к таблице ждут.

Это не усиленный режим планового обслуживания. VACUUM FULL оправдан после разового крупного сокращения данных, когда место нужно вернуть сейчас, свободного диска хватает для новой копии, а окно полной блокировки согласовано. Для постоянно обновляемой таблицы частый обычный VACUUM полезнее: после VACUUM FULL файл всё равно вырастет под рабочий объём.

Autovacuum никогда не запускает VACUUM FULL. Перед ручной переписью проверьте последствия блокировки по разбору блокировок PostgreSQL.

VACUUM и ANALYZE решают разные задачи

VACUUM освобождает место для повторного использования, поддерживает карты видимости и защищает от wraparound. ANALYZE читает выборку строк и собирает статистику распределения данных для планировщика запросов. Команды можно запускать отдельно или вместе:

VACUUM public.orders;

ANALYZE public.orders;

VACUUM (ANALYZE) public.orders;

Свежий ANALYZE не убирает dead tuples. Успешный VACUUM без ANALYZE не обязан исправить плохие оценки количества строк в плане. У autovacuum тоже два независимых условия запуска: одно для VACUUM, другое для ANALYZE. Если оценки планировщика расходятся с фактом, используйте EXPLAIN ANALYZE BUFFERS и отдельно проверяйте last_analyze и last_autoanalyze.

Как PostgreSQL 18 вычисляет порог autovacuum

Для обновлённых и удалённых строк PostgreSQL 18 сравнивает число устаревших версий после последнего VACUUM с порогом таблицы:

vacuum threshold = min(
  autovacuum_vacuum_max_threshold,
  autovacuum_vacuum_threshold
    + autovacuum_vacuum_scale_factor × pg_class.reltuples
)

pg_class.reltuples — оценка количества строк, а не точный COUNT(*). В конфигурации PostgreSQL 18 по умолчанию базовый порог равен 50, scale factor — 0.2, а верхняя граница — 100 000 000 устаревших версий. Для оценки в 1 000 000 строк порог составит около 200 050 версий. Это иллюстрация заводских настроек, а не рекомендуемое значение для любой миллионной таблицы.

Таблицы только со вставками тоже требуют vacuum: для обновления visibility map и заморозки старых XID. У них действует отдельное условие:

vacuum insert threshold =
  autovacuum_vacuum_insert_threshold
  + autovacuum_vacuum_insert_scale_factor
    × pg_class.reltuples
    × (1 - pg_class.relallfrozen / pg_class.relpages)

В PostgreSQL 18 базовый insert threshold по умолчанию равен 1000, а insert scale factor — 0.2. Значение -1 у autovacuum_vacuum_insert_threshold отключает только запуск по числу вставок. Оно не отменяет vacuum по другим причинам, включая защиту от wraparound.

Глобальные значения можно переопределить для отдельной таблицы. Поэтому перед расчётом проверяйте и pg_settings, и параметры хранения в reloptions:

SELECT
  name,
  setting,
  unit,
  source
FROM pg_settings
WHERE name IN (
  'autovacuum',
  'autovacuum_max_workers',
  'autovacuum_naptime',
  'autovacuum_vacuum_threshold',
  'autovacuum_vacuum_scale_factor',
  'autovacuum_vacuum_max_threshold',
  'autovacuum_vacuum_insert_threshold',
  'autovacuum_vacuum_insert_scale_factor',
  'autovacuum_vacuum_cost_limit',
  'autovacuum_vacuum_cost_delay'
)
ORDER BY name;

SELECT
  n.nspname AS schemaname,
  c.relname,
  c.reltuples::bigint AS estimated_rows,
  c.reloptions
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND array_to_string(c.reloptions, ',') LIKE '%autovacuum%'
ORDER BY n.nspname, c.relname;

Универсального «правильного процента» нет. Для большой активно меняющейся таблицы заводской scale factor может давать слишком редкий запуск; для маленькой таблицы тот же параметр может быть приемлем. Настройку подтверждают трендом размера, скоростью появления dead tuples, длительностью vacuum и влиянием на I/O.

Первый снимок: pg_stat_user_tables

Начните с лёгкого запроса по пользовательским таблицам:

SELECT
  schemaname,
  relname,
  pg_size_pretty(pg_table_size(relid)) AS table_size,
  n_live_tup,
  n_dead_tup,
  n_ins_since_vacuum,
  last_vacuum,
  last_autovacuum,
  vacuum_count,
  autovacuum_count,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_table_size(relid) DESC;

n_live_tup и n_dead_tup — оценки. Они могут запаздывать, меняться после обновления статистики и не обязаны совпадать с точным числом строк. last_autovacuum IS NULL тоже не всегда означает проблему: таблица могла быть новой, статистику могли сбросить, а порог ещё не достигнут.

Полезен не разовый рейтинг по n_dead_tup, а одинаковый снимок по расписанию. Сохраняйте размер, оценки живых и мёртвых строк, время и счётчики vacuum. Затем сопоставляйте интервалы с релизами, пакетными загрузками и массовыми изменениями. Так видно, стабилизируется ли таблица после очистки или отставание накапливается.

Как увидеть выполняющийся VACUUM

Обычный VACUUM, включая работу autovacuum worker, появляется в pg_stat_progress_vacuum:

SELECT
  pid,
  relid::regclass AS table_name,
  phase,
  heap_blks_scanned,
  heap_blks_total,
  indexes_processed,
  indexes_total,
  delay_time
FROM pg_stat_progress_vacuum
ORDER BY pid;

Представление показывает текущую фазу: сканирование таблицы, очистку индексов, очистку страниц таблицы, усечение хвоста или финальную обработку. Отношение heap_blks_scanned к heap_blks_total полезно только в фазе scanning heap; весь VACUUM может несколько раз переходить между таблицей и индексами.

Пустой результат означает лишь, что прямо сейчас обычный VACUUM не выполняется. Быстрый worker мог закончить между двумя запросами. VACUUM FULL ищите в pg_stat_progress_cluster, потому что он переписывает таблицу, а не очищает её на месте.

Долгие транзакции удерживают старые версии

VACUUM не может удалить версию строки, если её ещё способен увидеть активный снимок. Поэтому одна забытая сессия idle in transaction иногда мешает очистке сильнее, чем настройки scale factor.

Проверьте старые транзакции и их горизонт видимости:

SELECT
  pid,
  usename,
  state,
  xact_start,
  now() - xact_start AS xact_age,
  backend_xmin,
  age(backend_xmin) AS xmin_age,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
   OR backend_xmin IS NOT NULL
ORDER BY xact_start NULLS LAST;

Не завершайте сессию только по возрасту. Сначала найдите владельца, назначение запроса и допустимый способ остановки. После устранения причины проверьте, возобновилась ли очистка и перестал ли расти размер.

Слоты репликации тоже могут удерживать старые версии

У слота репликации xmin задаёт горизонт удержания: VACUUM не может убрать версии строк, удалённые более поздними транзакциями. Важен момент удаления старой версии, а не её создания. catalog_xmin действует так же, но только на версии строк системных каталогов. Если потребитель отстал или исчез, старый xmin способен удерживать bloat пользовательских таблиц. restart_lsn отдельно удерживает WAL; это другая часть дискового риска.

SELECT
  slot_name,
  slot_type,
  active,
  inactive_since,
  xmin,
  age(xmin) AS xmin_age,
  catalog_xmin,
  age(catalog_xmin) AS catalog_xmin_age,
  restart_lsn
FROM pg_replication_slots
WHERE xmin IS NOT NULL
   OR catalog_xmin IS NOT NULL
   OR NOT active
ORDER BY inactive_since NULLS LAST;

Не удаляйте неактивный слот автоматически. Если он принадлежит живой реплике или системе CDC, удаление может потребовать пересоздания потребителя или реплики. Сначала подтвердите владельца и план восстановления.

Anti-wraparound vacuum нельзя отключать

Идентификаторы транзакций XID имеют конечный диапазон. VACUUM замораживает старые версии, чтобы после оборота счётчика они не стали выглядеть как данные «из будущего». Если безопасный горизонт не удаётся продвинуть, PostgreSQL сначала предупреждает, а затем прекращает операции, которым нужен новый XID.

Даже при autovacuum = off сервер запускает autovacuum для защиты от wraparound. Это аварийная страховка, а не режим эксплуатации. Не отключайте autovacuum и не отменяйте anti-wraparound worker ради снижения нагрузки: он запустится снова, а запас XID продолжит сокращаться.

Возраст по базам виден отдельным запросом:

SELECT
  datname,
  age(datfrozenxid) AS oldest_unfrozen_xid_age
FROM pg_database
ORDER BY oldest_unfrozen_xid_age DESC;

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

Безопасный чек-лист диагностики

Снимите pg_table_size, n_live_tup, n_dead_tup, n_ins_since_vacuum, последние времена и счётчики vacuum. Не делайте вывод по одному значению.
Повторите тот же снимок через фиксированный интервал. Сравните скорость роста файла с потоком изменений и количеством живых строк.
Проверьте реальные глобальные настройки и reloptions таблицы. Рассчитайте её порог по reltuples, учитывая autovacuum_vacuum_max_threshold и отдельный insert threshold.
Посмотрите pg_stat_progress_vacuum и журнал autovacuum. Отсутствие строки в progress view не доказывает, что worker не запускался.
Найдите старые xact_start и backend_xmin, затем проверьте xmin и catalog_xmin слотов репликации. Не завершайте сессии и не удаляйте слоты без владельца и плана восстановления.
Если очистка нужна сейчас, запустите обычный VACUUM (VERBOSE) для конкретной таблицы в подходящее окно и следите за I/O. После него повторите исходные измерения.
Меняйте одну настройку за раз: частоту запуска, число доступных worker-процессов или cost-based задержки. Чужой scale factor без данных вашей нагрузки не служит безопасным шаблоном.
Рассматривайте VACUUM FULL только для подтверждённой задачи вернуть место операционной системе после крупного разового сокращения. Заранее оцените блокировку, дополнительный диск и время переписи.

Диагноз должен объяснять динамику: какие изменения создают старые версии, почему они удерживаются, когда запускается очистка и переиспользует ли таблица свободное место. Эти связи важнее единичного размера файла и команды VACUUM FULL, запущенной наугад.