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

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

VACUUM выполняет три критические функции:

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

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

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

dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

При настройках по умолчанию (threshold = 50, scale_factor = 0.2) для таблицы с 1 миллионом строк VACUUM запустится после накопления 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 определяет, сколько «работы» autovacuum может выполнить до паузы. Значение 800 (вместо 200 по умолчанию) в 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;

Для точного измерения bloat используйте расширение pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

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

Transaction ID (XID) в 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 миллиардам — аварийная остановка.

Самые «старые» таблицы:

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;

Типичные ситуации, когда 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' для автоматического завершения зависших транзакций.

Проблема 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