Autovacuum PostgreSQL: настройка без риска для production

Запросы к таблице стали медленнее, хотя данных не прибавилось, а EXPLAIN показывает неожиданный Seq Scan. Это классический симптом bloat - раздутия таблицы мёртвыми строками. Разберём, почему это происходит, как это диагностировать за три минуты и как настроить autovacuum так, чтобы он справлялся без риска для production.

Почему PostgreSQL «толстеет»: механика MVCC и bloat

PostgreSQL - версионная СУБД. Это значит: при каждом UPDATE старая версия строки не стирается сразу, а остаётся в файле таблицы рядом с новой. Механизм называется MVCC (Multi-Version Concurrency Control - многоверсионное управление конкурентным доступом). Благодаря ему несколько транзакций читают данные параллельно без блокировок. Плата за это - мёртвые кортежи (dead tuples).

Как MVCC создаёт мёртвые кортежи

Допустим, вы обновили строку 100 раз. На диске - 101 версия одной записи. Все старые версии помечены как «невидимые» для новых транзакций, но физически занимают место на страницах размером по 8 КБ. Счётчик n_dead_tup в pg_stat_user_tables растёт.

VACUUM приходит и «помечает» эти страницы свободными - место становится доступным для новых строк, но файл таблицы не сжимается.

Авторская ремарка. Я нередко вижу проекты, где команда отключила autovacuum «чтобы не мешал» во время миграции - и забыла включить обратно. Через неделю таблица users с тремя миллионами строк занимала 40 ГБ вместо 2 ГБ.

Когда bloat становится проблемой

Небольшой bloat - до 20% - считается нормой и работает как буфер для новых данных (аналог fillfactor). Критическим уровнем считается 50% и выше: при полном сканировании Postgres читает вдвое больше страниц, чем нужно. Индексы тоже раздуваются по той же механике. Планировщик строит планы на устаревшей статистике и выбирает неоптимальные методы доступа.

Почему VACUUM не освобождает место на диске

Стандартный VACUUM не возвращает место операционной системе. Он только освобождает страницы внутри файла под новые вставки. Физически место возвращается лишь если пустые страницы оказались в хвосте файла - тогда VACUUM их «обрезает». VACUUM FULL перестраивает файл полностью, но захватывает AccessExclusiveLock - даже SELECT-ы блокируются. Подробнее об инструментах без даунтайма - в разделе про pg_repack ниже.

Диагностика: находим раздутые таблицы за 3 минуты

Перед тем как что-то менять в конфиге, убедитесь, что проблема реальна. Три запроса - и вы знаете, кто виноват.

Запросы к pg_stat_user_tables

-- Топ-20 таблиц по мёртвым кортежам
SELECT
    schemaname,
    relname AS table,
    n_dead_tup AS dead_rows,
    n_live_tup AS live_rows,
    round(
        100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0),
        1
    ) AS dead_pct,
    last_autovacuum,
    autovacuum_count
FROM
    pg_stat_user_tables
ORDER BY
    n_dead_tup DESC
LIMIT
    20;

Колонка dead_pct покажет процент мусора. Если last_autovacuum - несколько часов назад при активной нагрузке - autovacuum не успевает.

Контроль XID wraparound

-- Возраст баз данных - риск wraparound
SELECT datname, age (datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

PostgreSQL поддерживает 2 миллиарда транзакций до wraparound. Если age превышает 1.5 млрд - autovacuum уже работает в принудительном freeze-режиме, игнорируя все настройки throttling. Это аварийная ситуация, которая может привести базу в read-only.

Оценка bloat через pgstattuple

-- Требует расширения: CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('public.orders');

Расширение pgstattuple физически сканирует страницы и возвращает точный процент мусора. Для быстрой оценки без нагрузки на prod подходят SQL-скрипты с открытым кодом от PostgreSQL Experts - погрешность составляет 10–20%.

Параметры autovacuum: что менять и зачем

Дефолтные значения PostgreSQL рассчитаны на умеренные нагрузки. В production с миллионными таблицами они часто работают слишком редко и слишком осторожно. Разберём каждый ключевой параметр.

autovacuum_vacuum_scale_factor - главный рычаг

Это доля от размера таблицы, которая должна накопиться в мёртвых кортежах, прежде чем запустится VACUUM. По умолчанию: 0.2 (20%). Таблица в 10 миллионов строк → autovacuum запустится только после 2 миллионов мёртвых строк. К этому моменту bloat уже значительный. Для крупных таблиц рекомендуется снижать до 0.01–0.05.

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

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

где reltuples - приблизительное число строк в pg_class.

autovacuum_max_workers и naptime

По умолчанию запускается 3 воркера с проверкой каждую 1 минуту. Для OLTP с интенсивными обновлениями это мало. Data Egret рекомендует на нагруженных системах устанавливать autovacuum_max_workers = 10 и autovacuum_naptime = 1s.

⚠️ Важно: autovacuum_max_workers требует перезапуска PostgreSQL. Все остальные параметры применяются через pg_reload_conf() без рестарта:

SELECT pg_reload_conf();

Cost-based throttling: delay и limit

Autovacuum по умолчанию намеренно «притормаживается», чтобы не перегружать I/O.

Регулировка происходит через модель стоимости:

Параметр	По умолчанию	Комментарий
`autovacuum_vacuum_cost_delay`	2 ms	Пауза после достижения лимита
`autovacuum_vacuum_cost_limit`	-1 (= `vacuum_cost_limit` = 200)	Лимит «баллов» за I/O-операции
`vacuum_cost_page_hit`	1	Цена чтения страницы из буфера
`vacuum_cost_page_miss`	2	Цена чтения с диска
`vacuum_cost_page_dirty`	20	Цена записи страницы

На SSD-серверах cost_delay можно снизить до 0.5–1 ms, cost_limit поднять до 400–800 - vacuum пройдёт быстрее без ущерба для остальных запросов.

Настройка на уровне таблицы без перезапуска

В одной базе часто живут таблицы с кардинально разным профилем нагрузки: таблица заказов обновляется тысячи раз в секунду, а таблица-справочник стран - раз в год. Глобальный параметр scale_factor = 0.05 будет слишком агрессивным для справочника и всё равно недостаточным для orders.

Решение - переопределить autovacuum на уровне таблицы через ALTER TABLE. Изменения вступают немедленно, перезапуск не нужен.

Синтаксис ALTER TABLE для autovacuum

-- Агрессивные настройки для горячей таблицы orders
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 100,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_analyze_threshold = 50,
    autovacuum_vacuum_cost_delay = 1
);

-- Отключить autovacuum для таблицы-справочника (запускаем вручную)
ALTER TABLE dict_countries SET (
    autovacuum_enabled = false
);

Просмотреть текущие настройки на уровне таблиц:

SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL;

Стратегия для горячих таблиц

Разделите таблицы на три категории и назначьте политику:

Тип таблицы	scale_factor	threshold	cost_delay
Горячая (>1000 UPD/сек)	0.005–0.01	50–100	0.5–1 ms
Средняя	0.05	50	2 ms (default)
Холодная (справочник)	autovacuum_enabled=false	-	-

Опасность длинных транзакций

«Висячая» транзакция в состоянии idle in transaction блокирует autovacuum: он не удаляет версии строк, которые ещё видимы этой транзакции. Мониторинг:

SELECT
    pid,
    now () - xact_start AS duration,
    state,
    query
FROM
    pg_stat_activity
WHERE
    state = 'idle in transaction'
    AND xact_start < now () - interval '5 minutes'
ORDER BY
    duration DESC;

Установите таймаут в postgresql.conf:

idle_in_transaction_session_timeout = '5min'

Когда autovacuum не справляется: pg_repack и аварийный VACUUM

Если bloat уже вырос - autovacuum его не уберёт. Стандартный vacuum только остановит дальнейший рост. Нужны дополнительные инструменты.

VACUUM FULL vs pg_repack

	VACUUM FULL	pg_repack
Тип блокировки	`AccessExclusiveLock` (блокирует всё)	`ShareUpdateExclusiveLock` + финальный lock ~2–5 сек
Читают/пишут ли в это время	Нет	Да
Даунтайм	Полный на время работы	Фактически нет
Установка	Встроен	`CREATE EXTENSION pg_repack`
Ограничения	Нет	Проблемы с deferred constraints

Для production с SLA pg_repack - очевидный выбор. Исключение - плановое техническое окно ночью, когда VACUUM FULL допустим.

Пошаговый запуск pg_repack

# 1. Установить расширение (один раз)
psql -d mydb -c "CREATE EXTENSION pg_repack;"

# 2. Запустить перепаковку конкретной таблицы
pg_repack -h localhost -d mydb -t orders

# 3. Проверить результат
psql -d mydb -c "
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables WHERE relname = 'orders';"

pg_repack создаёт лог-таблицу, копирует «живые» строки в новую таблицу без мусора, перестраивает индексы и в финале - в течение нескольких секунд - подменяет старую таблицу на новую.

Авторская ремарка. На практике видел кейс, где pg_repack за 40 минут сжал таблицу транзакций с 180 ГБ до 22 ГБ. Производительность SELECT-запросов на эту таблицу выросла в 4–5 раз - просто за счёт уменьшения числа страниц при seq scan.

XID wraparound: аварийный сценарий

Если age(datfrozenxid) приближается к 2 млрд транзакциям - PostgreSQL запускает принудительный freeze-vacuum на все таблицы, игнорируя cost_delay. При превышении порога база переходит в режим read-only с сообщением:

ERROR: database is not accepting commands to avoid wraparound data loss

Превентивные меры:

-- Принудительная заморозка конкретной таблицы
VACUUM FREEZE VERBOSE orders;

-- Снизить порог принудительного freeze

-- В postgresql.conf:
-- autovacuum_freeze_max_age = 150000000
-- vacuum_freeze_min_age = 1000000

Альтернативный взгляд

Некоторые DBA рекомендуют не трогать глобальный autovacuum_vacuum_scale_factor и работать только через ALTER TABLE - это точнее и безопаснее. Аргумент: агрессивный глобальный scale_factor заставляет autovacuum излишне часто обходить маленькие справочные таблицы, создавая ненужную нагрузку на I/O и lock manager. Подход «глобальные параметры - консервативные, таблично-специфичные - агрессивные» работает надёжнее в гетерогенных базах.

Нетривиальный факт

Даже если autovacuum = off в postgresql.conf, PostgreSQL всё равно запустит autovacuum-процессы при угрозе XID wraparound. Это жёстко зашито в ядро СУБД и не отключается никакими параметрами. Смысл: база данных защищает сама себя от катастрофы с идентификаторами транзакций, даже если администратор выключил автоочистку.

FAQ

Что такое autovacuum в PostgreSQL и зачем он нужен?

Autovacuum - фоновый демон PostgreSQL, который автоматически запускает VACUUM и ANALYZE. Он удаляет мёртвые кортежи, образующиеся из-за MVCC-архитектуры, обновляет статистику для планировщика запросов и защищает от критической проблемы XID wraparound. Без него база неизбежно деградирует по производительности.

Почему autovacuum по умолчанию плохо работает на больших таблицах?

Параметр autovacuum_vacuum_scale_factor по умолчанию равен 0.2 (20%). Для таблицы с 10 млн строк это означает запуск только после накопления 2 млн мёртвых кортежей. К этому моменту bloat уже значительный. Снизьте значение до 0.01–0.05 для крупных таблиц через ALTER TABLE или глобально.

Можно ли изменить параметры autovacuum без перезапуска PostgreSQL?

Большинство параметров - autovacuum_vacuum_scale_factor, autovacuum_naptime, autovacuum_vacuum_cost_delay - применяются командой SELECT pg_reload_conf(); без рестарта. Исключение: autovacuum_max_workers требует полного перезапуска PostgreSQL.

Как autovacuum связан с проблемой XID wraparound?

PostgreSQL использует 32-битный счётчик транзакций. Когда он приближается к ~2 млрд, autovacuum запускает принудительный VACUUM FREEZE для «заморозки» старых строк. Если autovacuum отключён или не успевает - база переходит в режим read-only с потерей возможности записи. Мониторьте через SELECT datname, age(datfrozenxid) FROM pg_database.

Что лучше для устранения bloat в production: VACUUM FULL или pg_repack?

Для production без окна обслуживания - pg_repack. Он работает онлайн, захватывает строгую блокировку лишь на несколько секунд в финале операции. VACUUM FULL удерживает AccessExclusiveLock на всё время работы - даже SELECT-запросы не проходят. При наличии планового технического окна VACUUM FULL также допустим.

Как длинные транзакции влияют на autovacuum?

Транзакция в состоянии idle in transaction не позволяет autovacuum удалять строки, видимые этой транзакции. Bloat продолжает расти, даже если autovacuum исправно работает. Установите idle_in_transaction_session_timeout = '5min' в postgresql.conf и мониторьте через pg_stat_activity.

Какой уровень bloat считается критическим?

До 20% bloat - рабочая норма (аналог fillfactor). При 50% и выше начинаются ощутимые проблемы: лишние чтения страниц при seq scan, разбухшие индексы, неоптимальные планы. Измеряйте через pg_stat_user_tables или расширение pgstattuple.

Источники

PostgreSQL Documentation - Автоматическая очистка (глава 20.10) - https://postgrespro.ru/docs/postgresql/16/runtime-config-autovacuum
Habr - Postgres: bloat, pg_repack и deferred constraints - https://habr.com/ru/articles/499444/
Habr - Как эффективно настроить autovacuum в Postgres для 1С - https://habr.com/ru/articles/741566/
Microsoft Azure - Настройка autovacuum в базе данных Azure для PostgreSQL - https://learn.microsoft.com/ru-ru/azure/postgresql/troubleshoot/how-to-autovacuum-tuning
Yandex Cloud - Настройки параметров кластера PostgreSQL - https://yandex.cloud/ru/docs/managed-postgresql/qa/configuration
Proselyte - Что backend-разработчик должен знать про PostgreSQL - https://proselyte.net/postgres-for-devs/

Как настроить autovacuum в PostgreSQL и не уронить production