VACUUM и ANALYZE в PostgreSQL: как поддерживать производительность

PostgreSQL использует модель многоверсионного управления параллелизмом — MVCC (Multi-Version Concurrency Control). Каждое обновление строки не перезаписывает её на месте, а создаёт новую версию. Старая версия остаётся в таблице как «мёртвый кортеж» (dead tuple) до тех пор, пока её не уберёт VACUUM. Без регулярной очистки таблицы распухают, планировщик начинает работать с устаревшей статистикой, а производительность падает.

Почему появляются мёртвые кортежи

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

Посмотрим, сколько мёртвых кортежей накопилось в таблицах:

SELECT
    relname                          AS table_name,
    n_live_tup                       AS live_rows,
    n_dead_tup                       AS dead_rows,
    round(n_dead_tup::numeric /
        nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Если dead_pct превышает 10–20%, пора задуматься об оптимизации vacuum-политики.

VACUUM vs VACUUM FULL

VACUUM (обычный) — убирает мёртвые кортежи и помечает занятые ими страницы как доступные для повторного использования. Работает параллельно с другими запросами, не блокирует таблицу. Физический размер файла при этом не уменьшается.

VACUUM FULL — полностью перезаписывает таблицу, возвращая место операционной системе. Требует ACCESS EXCLUSIVE блокировку — таблица недоступна на время работы. Используйте только при критическом bloat и в период обслуживания.

-- Обычная очистка (безопасно на prod)
VACUUM orders;

-- Очистка с обновлением статистики
VACUUM ANALYZE orders;

-- Возврат места ОС (требует блокировки)
VACUUM FULL orders;

-- Посмотреть прогресс долгого VACUUM FULL
SELECT * FROM pg_stat_progress_vacuum;

ANALYZE и статистика планировщика

ANALYZE собирает статистику о распределении данных в таблице и сохраняет её в pg_statistic. Планировщик запросов использует эту статистику, чтобы выбрать оптимальный план выполнения: какой индекс использовать, как соединять таблицы, сколько строк ожидать на каждом шаге.

Без актуальной статистики планировщик может сильно ошибиться с оценкой количества строк и выбрать неэффективный план.

-- Только сбор статистики, без vacuum
ANALYZE orders;

-- Посмотреть оценку планировщика vs реальность
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
-- Обращайте внимание на строки:
-- rows=100 (оценка планировщика) vs actual rows=94500 (реальность)
-- Большое расхождение — признак устаревшей статистики

Autovacuum: настройка под нагрузку

По умолчанию autovacuum включён и запускается автоматически. Однако стандартные параметры рассчитаны на небольшие базы. На высоконагруженных системах его нужно настраивать.

Ключевые параметры в postgresql.conf:

# Порог запуска vacuum: 20% мёртвых строк + базовый порог 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50

# Порог запуска analyze: 10% изменений + базовый порог 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50

# Для больших таблиц лучше использовать фиксированный порог
# Переопределение на уровне таблицы:
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1% вместо 20%
    autovacuum_vacuum_threshold = 1000
);

Для таблицы с 10 миллионами строк стандартный порог 20% означает, что autovacuum запустится только после накопления 2 млн мёртвых строк. Снижение scale_factor до 0.01 заставит его реагировать на 100 тысяч изменений.

Мониторинг bloat таблиц

Table bloat — это разрыв между реальным размером таблицы и объёмом, который действительно занимают живые данные.

-- Размер таблиц с учётом bloat
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid))       AS table_size,
    n_dead_tup,
    n_live_tup,
    last_autovacuum::date
FROM pg_stat_user_tables
WHERE pg_relation_size(relid) > 10 * 1024 * 1024  -- таблицы > 10MB
ORDER BY pg_total_relation_size(relid) DESC;

Для более точной оценки bloat можно использовать запрос из pgstattuple (требует расширения):

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_len,
    dead_tuple_len,
    free_space,
    round(dead_tuple_percent::numeric, 1) AS dead_pct
FROM pgstattuple('orders');

Когда запускать VACUUM вручную

Autovacuum справляется с большинством задач, но есть ситуации, когда ручной запуск оправдан:

• После массового удаления данных — DELETE FROM logs WHERE created_at < now() - interval '90 days' оставит миллионы мёртвых строк.
• Перед важным отчётом — чтобы планировщик работал с актуальной статистикой.
• После массового UPDATE — особенно если обновляете колонки, по которым есть индексы.
• При приближении к transaction ID wraparound — PostgreSQL предупредит об этом в логах.

-- Проверить риск wraparound
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2147483648 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;
-- Если xid_age > 1.5 млрд — срочно запускать VACUUM FREEZE

Итог

VACUUM и ANALYZE — не разовые операции, а регулярное обслуживание базы данных. Настройте autovacuum под размер ваших таблиц, отслеживайте pg_stat_user_tables и не допускайте накопления мёртвых кортежей. Это один из самых простых способов поддерживать PostgreSQL в хорошей форме без дорогостоящего рефакторинга запросов.

Хотите отработать SQL на практике? Попробуйте интерактивный тренажёр — реальные задачи с PostgreSQL прямо в браузере.