re:re:actable
Урок 134 минуты чтения

Что такое VACUUM, ANALYZE и статистика планировщика

На этой странице
Кратко

VACUUM — обслуживание, необходимое из-за MVCC: он помечает место «мёртвых» версий строк (после UPDATE/DELETE) для повторного использования, обновляет visibility map (нужна для Index Only Scan) и выполняет freeze старых идентификаторов транзакций, защищая от wraparound. VACUUM не блокирует обычную работу, но и не возвращает место ОС; VACUUM FULL переписывает таблицу и возвращает место, но под ACCESS EXCLUSIVE блокировкой. ANALYZE собирает статистику распределения значений (pg_stats: частые значения, гистограммы, число различных), по которой планировщик оценивает число строк и выбирает план. Обе операции автоматически запускает autovacuum; его не отключают, а настраивают для больших и активно меняющихся таблиц.

Вопрос

Что такое VACUUM и ANALYZE и зачем нужна статистика планировщика?

Зачем VACUUM

UPDATE products SET price = price * 1.1;   -- 1 млн строк
 
до:   1 млн живых версий
после: 1 млн живых + 1 млн мёртвых → таблица ×2, сканирование ×2
VACUUM: мёртвые версии → свободное место для новых строк (размер файла не уменьшается)
КомандаЧто делаетБлокировка
VACUUM tОчищает мёртвые версии, обновляет visibility map, freezeНе мешает чтению и записи
VACUUM (ANALYZE) t+ собирает статистикуТо же
VACUUM FULL tПереписывает таблицу компактно, возвращает место ОСACCESS EXCLUSIVE — всё ждёт
ANALYZE tТолько статистикаЛёгкая

Вместо VACUUM FULL в проде используют расширение pg_repack — перестройка таблицы без длительной блокировки.

Autovacuum

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Autovacuum запускается, когда мёртвых строк больше autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup (по умолчанию 50 + 20%). Для таблицы на 100 млн строк это 20 млн мёртвых — слишком поздно, поэтому для больших таблиц порог уменьшают:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005);

Что мешает VACUUM

  • Долгие и idle in transaction транзакции — VACUUM не может удалить версии, которые они теоретически видят.
  • Забытые слоты репликации и prepared transactions.
  • Слишком «медленный» autovacuum (autovacuum_vacuum_cost_limit) на интенсивной записи.

Wraparound

Идентификаторы транзакций 32-битные. Чтобы счётчик не «обернулся», VACUUM замораживает старые строки. Если freeze не успевает, PostgreSQL сначала предупреждает, а в крайнем случае перестаёт принимать запись. Следят за возрастом:

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

ANALYZE и статистика

ANALYZE orders;
 
SELECT attname, null_frac, n_distinct, most_common_vals, histogram_bounds
FROM pg_stats WHERE tablename = 'orders';
ПолеЗначение
null_fracДоля NULL
n_distinctЧисло различных значений (отрицательное — доля от числа строк)
most_common_vals/freqsЧастые значения и их доли
histogram_boundsГраницы равных по количеству корзин для остальных значений
correlationСовпадение логического и физического порядка (важно для BRIN и диапазонов)

Когда запускать вручную: после массовой загрузки, после создания индекса по выражению, после миграции данных — до того, как autovacuum дойдёт сам.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;          -- детальнее для перекошенного столбца
CREATE STATISTICS orders_deps (dependencies) ON city, country FROM orders;  -- коррелированные столбцы

Bloat индексов

После массовых изменений индексы тоже раздуваются. Перестройка без блокировки записи:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

Частые ошибки

  • Отключить autovacuum «для скорости».
  • VACUUM FULL в рабочее время.
  • Не делать ANALYZE после загрузки данных — плохие планы.
  • Долгие транзакции, из-за которых bloat растёт несмотря на autovacuum.

Что важно помнить

  • VACUUM убирает мёртвые версии строк, ANALYZE обновляет статистику.
  • Autovacuum обязателен; для больших таблиц его настраивают.
  • Хорошие планы невозможны без актуальной статистики.

Мини-шпаргалка

  • VACUUM (ANALYZE) t.
  • pg_stat_user_tables.n_dead_tup.
  • autovacuum_vacuum_scale_factor per table.
  • REINDEX CONCURRENTLY, pg_repack.
  • pg_stats, CREATE STATISTICS.

Документация

PostgreSQL: Routine Vacuuming, PostgreSQL: Statistics Used by the Planner, pg_repack.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам