PostgreSQL VACUUM и autovacuum: тонкая настройка для продакшена

PostgreSQL работает на MVCC (Multi-Version Concurrency Control) — механизме, при котором обновление строки не удаляет старую версию, а оставляет её висеть с пометкой «мёртвая» (dead tuple). Транзакции изолированы — это хорошо. Но «мёртвые» строки никуда не деваются сами по себе и тихо съедают место на диске.

VACUUM делает три вещи, без которых база рано или поздно встанет:

• Освобождение места — помечает пространство «мёртвых» строк как пригодное для новых данных (именно помечает, а не возвращает ОС — для этого нужен VACUUM FULL)
• Обновление карты видимости — без актуальной visibility map Index-Only Scans вынуждены лезть в heap, теряя всё своё преимущество по скорости
• Заморозка старых транзакций — предотвращает Transaction ID Wraparound, который в худшем случае кладёт базу полностью и безвозвратно

Без регулярного VACUUM база раздуется до неприличных размеров, запросы начнут спотыкаться о «мёртвые» строки при каждом сканировании, а после ~2 миллиардов транзакций PostgreSQL просто остановится сам — принудительно, чтобы не допустить wraparound. Мы видели такое в проде. Это больно.

Autovacuum — фоновый демон, который сам следит за таблицами и запускает VACUUM, когда «мёртвых» строк накопилось достаточно. Порог запуска считается по формуле:

dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

С настройками по умолчанию (threshold = 50, scale_factor = 0.2) для таблицы на миллион строк autovacuum проснётся только после того, как появятся 200 050 «мёртвых» строк. Это 20% таблицы — огромный балласт. Для небольших таблиц ещё терпимо, но на больших такой подход убивает производительность.

Смотрим, что сейчас делает autovacuum:

SELECT schemaname, relname, 
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct,
       last_autovacuum, last_autoanalyze,
       autovacuum_count, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Если dead_pct стабильно держится выше 10% — autovacuum не вывозит нагрузку и пора его настраивать.

Для высоконагруженных серверов в postgresql.conf меняем следующие параметры:

# Количество воркеров autovacuum (по умолчанию 3)
autovacuum_max_workers = 6

# Порог запуска: вакуумить чаще
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05  # 5% вместо 20%

# Порог ANALYZE
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.025  # 2.5%

# Агрессивность: меньше пауз между итерациями
autovacuum_vacuum_cost_delay = 2ms  # по умолчанию 2ms в PG15+
autovacuum_vacuum_cost_limit = 800   # по умолчанию 200

# Заморозка для предотвращения wraparound
vacuum_freeze_min_age = 10000000
vacuum_freeze_table_age = 150000000
autovacuum_freeze_max_age = 200000000

Параметр autovacuum_vacuum_cost_limit — это лимит «работы» за один проход до паузы. По умолчанию 200. Поднимаем до 800 — autovacuum становится в 4 раза шустрее. На серверах с SSD смело ставьте 1200–2000, железо выдержит.

Bloat — это мёртвый груз внутри таблиц и индексов: место занято, но данных там нет. Мы мониторим его регулярно — раз в неделю минимум:

-- Оценка bloat таблиц (упрощённый запрос)
SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(
        pg_total_relation_size(schemaname || '.' || tablename) - 
        pg_relation_size(schemaname || '.' || tablename)
    ) AS index_size,
    CASE WHEN pg_relation_size(schemaname || '.' || tablename) > 0
        THEN round(
            (1 - (n_live_tup::numeric * 8192) / 
            NULLIF(pg_relation_size(schemaname || '.' || tablename), 0)) * 100, 1
        )
    END AS est_bloat_pct
FROM pg_stat_user_tables
JOIN pg_tables USING (schemaname, tablename)
WHERE n_live_tup > 1000
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Хотите точные цифры — поставьте расширение pgstattuple. Оно считает честнее, чем оценочные запросы по системным таблицам:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('public.orders');
-- dead_tuple_percent покажет точный процент bloat

Transaction ID в PostgreSQL — 32-битное число. Максимум ~4,3 миллиарда значений. Когда счётчик подбирается к лимиту, PostgreSQL переходит в аварийный режим и блокирует все транзакции до устранения проблемы. Мы не драматизируем — это реальный сценарий, который случался в продакшене у разных компаний.

Следим за возрастом транзакций по всем базам:

SELECT datname, 
       age(datfrozenxid) AS xid_age,
       round(age(datfrozenxid)::numeric / 2000000000 * 100, 2) AS pct_to_wraparound,
       current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY xid_age DESC;

Ориентиры такие: xid_age около 200 миллионов — autovacuum переходит в агрессивный режим заморозки. Около 1,2 миллиарда — в логах начнут сыпаться предупреждения. Около 2 миллиардов — аварийная остановка. До последнего лучше не доводить.

Самые «старые» таблицы — первые кандидаты на принудительный VACUUM FREEZE:

SELECT schemaname, relname, 
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
JOIN pg_stat_user_tables USING (relname)
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

За годы работы с PostgreSQL мы собрали несколько типичных ситуаций, когда autovacuum не справляется. Вот они с решениями:

Проблема 1: autovacuum постоянно прерывается длинными транзакциями.

-- Найти длинные транзакции, блокирующие vacuum
SELECT pid, now() - xact_start AS duration, 
       state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start < now() - interval '1 hour'
ORDER BY xact_start;

Решение простое — настройте idle_in_transaction_session_timeout = '10min'. Зависшие транзакции будут завершаться автоматически, не мешая autovacuum работать.

Проблема 2: autovacuum не успевает обойти все таблицы.

Добавляем воркеров и сокращаем задержку между запусками:

autovacuum_max_workers = 8
autovacuum_naptime = 15s  # проверять чаще (по умолчанию 1 мин)
autovacuum_vacuum_cost_delay = 0  # без пауз на SSD

Проблема 3: VACUUM ползёт часами на огромных таблицах.

Таблицы больше 100 ГБ лучше партиционировать. VACUUM обрабатывает каждую партицию отдельно — быстрее и параллельно. На практике это меняет картину кардинально.

Когда bloat уже критический и обычный VACUUM не помогает — берём pg_repack. Он пересоздаёт таблицу целиком, но без exclusive lock:

# Установка
sudo apt install postgresql-16-repack

# Создание расширения
psql -d mydb -c "CREATE EXTENSION pg_repack;"

# Перепаковка таблицы
pg_repack -d mydb -t public.orders --no-superuser-check

# Перепаковка всех таблиц с bloat > 30%
pg_repack -d mydb --all-tables

Механизм такой: pg_repack создаёт копию таблицы, все изменения на время перестройки перехватывает триггер, затем происходит атомарная подмена. Сервис не останавливается. Но место на диске нужно заранее — понадобится столько же, сколько занимает таблица вместе с индексами.

Удобно автоматизировать через cron — раз в неделю перепаковываем самые раздутые таблицы:

#!/bin/bash
# /opt/scripts/repack-bloated.sh
TABLES=$(psql -d mydb -t -c "
  SELECT schemaname || '.' || relname
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 100000
    AND n_dead_tup::float / NULLIF(n_live_tup, 0) > 0.3
")

for TABLE in $TABLES; do
    echo "Repacking $TABLE..."
    pg_repack -d mydb -t "$TABLE" --no-superuser-check 2>&1
done