VACUUM, autovacuum и bloat в PostgreSQL: причины и решения

Вы замечаете, что дисковое пространство тает быстрее, чем растёт реальный объём данных. Простые запросы, которые ещё вчера отрабатывали за миллисекунды, начинают «ползти». Вы смотрите на метрики: объём новых вставок скромный, таблицы вроде бы не изменились - но база всё равно тяжелеет и тормозит. Скорее всего, вы встретились с bloat - «раздуванием» таблиц и индексов из‑за накопившихся, но не убранных «мёртвых» строк. Разберёмся, откуда берётся этот мусор, как его убирают штатные механизмы PostgreSQL и почему одного только autovacuum часто недостаточно.

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

PostgreSQL использует механизм многоверсионности (MVCC), чтобы обеспечить одновременный доступ к данным без грубых блокировок. Каждая запись в таблице - это не просто ячейка, а кортеж (tuple) с двумя скрытыми полями: xmin - идентификатор транзакции, которая создала эту версию строки, и xmax - идентификатор транзакции, которая «удалила» или обновила её. Пока xmax не выставлен или ссылается на ещё незавершённую транзакцию, строка считается видимой для других запросов.

При UPDATE старая версия строки не переписывается на месте. Вместо этого создаётся новый кортеж с обновлёнными данными, а у прежнего выставляется xmax, который помечает его как «устаревший». При DELETE происходит то же самое - строка остаётся на диске, но помечается как невидимая для новых транзакций. Так образуются dead tuples - мёртвые строки, которые больше не нужны ни одному активному запросу, но физически продолжают лежать в куче (heap) и в индексах.

VACUUM: что он делает на самом деле

Обычный VACUUM - освобождение места для переиспользования (без сжатия файлов)

Команда VACUUM проходит по страницам таблицы и находит dead tuples, которые гарантированно не видны ни одной текущей транзакции. Вместо того чтобы физически удалить их, она помечает занятое ими пространство как доступное для повторного использования в будущих INSERT или UPDATE. Одновременно VACUUM обновляет карту видимости (visibility map) - специальную структуру, которая помогает пропускать уже очищенные страницы при последующих сканированиях.

Важно: обычный VACUUM не возвращает дисковое пространство операционной системе и не уменьшает размер файла таблицы. Файл может оставаться гигантским, даже если реальных данных в нём совсем мало, - просто внутри появляются свободные блоки, готовые принять новые строки.

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

VACUUM FULL - полное переписывание таблицы и цена блокировок

VACUUM FULL действует радикально. Он полностью перестраивает таблицу: записывает только живые строки в новый файл, убирая все dead tuples и сжимая физический размер до минимума. Плата за это - эксклюзивная блокировка (AccessExclusiveLock), которая на время операции практически останавливает и чтение, и запись. В высоконагруженных системах такой простой обычно неприемлем.

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

ANALYZE и сбор статистики - почему они часто идут в паре

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

Autovacuum: автоматическая очистка и её настройка

Вручную запускать VACUUM на каждой таблице после каждой серии обновлений нереально. В PostgreSQL за это отвечает демон autovacuum. Он работает в фоне и следит за таблицами, в которых накопилось достаточно изменений, чтобы запустить очистку и сбор статистики.

Решение о запуске принимается по простой формуле:

количество мёртвых кортежей >= autovacuum_vacuum_threshold 
+ autovacuum_vacuum_scale_factor × общее_число_строк_в_таблице

По умолчанию параметры равны: autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2.

Посмотрим на два примера:

Таблица из 1 000 строк → порог срабатывания ≈ 50 + 0.2 × 1000 = 250 мёртвых кортежей.
Таблица из 1 000 000 строк → порог ≈ 50 + 0.2 × 1 000 000 = 200 050 мёртвых кортежей.

Для небольших таблиц это нормально: вакуум запускается довольно часто и не даёт копиться мусору. Но для таблиц‑гигантов с интенсивными UPDATE/DELETE порог в 200 тысяч строк может означать, что autovacuum приходит раз в несколько часов, а то и реже, хотя dead tuples появляются каждую секунду. За это время запросы уже ощущают лишнюю работу, а таблица на диске распухает.

Почему bloat всё равно растёт

Даже при включённом и работающем autovacuum можно столкнуться с ситуацией, когда база постепенно «толстеет», не освобождая места.

Долгоживущие транзакции и xmin horizon
VACUUM может убрать только те dead tuples, чей xmax меньше самого старого активного идентификатора транзакции во всей системе (так называемый «горизонт xmin»). Если какой‑нибудь администратор забыл закрыть транзакцию, начатую полдня назад, или фоновый процесс «завис» в длительном SELECT, горизонт застывает. Все версии строк, созданные или удалённые после этого момента, остаются «неприкасаемыми» - даже если они давно не нужны. Они копятся, таблицы раздуваются.

Репликация и hot_standby_feedback
На read‑реплике может выполняться длительный аналитический запрос. Если включён параметр hot_standby_feedback, реплика сообщит мастеру, что у неё открыт старый снимок данных. Мастер обязан сохранить все версии строк, видимые этому снимку, чтобы реплика не получила ошибку. По сути, это тот же застывший горизонт xmin, но переданный по сети. Неаккуратное использование реплик для тяжёлых отчётов нередко становится причиной внезапного всплеска bloat на первичном сервере.

Блокировки, мешающие вакууму
VACUUM тоже нуждается в блокировках - пусть и слабых. Если на таблице висит долгая аналитическая операция, удерживающая конфликтующую блокировку, фоновый вакуум может ждать и ждать, а dead tuples тем временем продолжают накапливаться. В системной статистике это выглядит как «vacuum застрял».

Index bloat
Отдельная головная боль - индексы. Когда VACUUM освобождает место в таблице, он всего лишь помечает страницы индекса как доступные для переиспользования, но не сжимает само дерево. При многократных обновлениях индексные страницы фрагментируются, их физический размер растёт. В результате индексное сканирование читает больше блоков, чем нужно, увеличивая IO и замедляя отклик.

Как bloat разрушает производительность

Раздутые таблицы и индексы бьют по производительности сразу в нескольких направлениях:

Лишнее чтение страниц. Sequential scan читает все страницы таблицы подряд, включая наполовину заполненные мусором. Index scan проходит через разбухшее дерево, тратя больше операций ввода‑вывода.
Вытеснение полезных данных из кэша. Мёртвые кортежи занимают место в shared_buffers, выдавливая оттуда действительно нужные строки. Кэш работает менее эффективно, нагрузка на диски растёт.
Задержки и рост времени отклика. Каждый лишний визит к диску увеличивает время выполнения запроса. В системах с жёсткими SLA даже 20–30 % лишнего объёма данных могут превратить стабильный сервис в источник постоянных жалоб.

Кроме того, раздувание увеличивает время репликации: streaming‑протокол пересылает изменения страниц целиком, и если те распухли, восстановление или начальная синхронизация могут стать проблемой.

Мониторинг bloat и эффективности VACUUM

Что смотреть в pg_stat_user_tables

Системное представление pg_stat_user_tables даёт первую подсказку:

n_dead_tup - примерное число мёртвых кортежей, известное планировщику.
last_vacuum, last_autovacuum - время последней ручной и автоматической очистки.
vacuum_count, autovacuum_count - сколько раз запускался каждый тип.

Если n_dead_tup стабильно высок, а last_autovacuum отстаёт на десятки минут или часы - это тревожный сигнал. Также полезно мониторить процесс autovacuum через pg_stat_progress_vacuum, чтобы видеть, не застрял ли он на конкретной таблице.

Представления и расширения для оценки bloat

В модуле pgstattuple (стандартный contrib‑модуль) есть функция pgstattuple, которая возвращает количество живых и мёртвых кортежей, а также процент свободного места в таблице. Её вызов SELECT * FROM pgstattuple('имя_таблицы'); даёт прямой срез фрагментации, но на больших таблицах может выполняться долго, так как читает весь файл.

Существуют и сторонние расширения, например pg_bloat_check, которые стараются оценить bloat без полного сканирования, сравнивая фактический и «идеальный» размеры. Они удобны для регулярного аудита крупных инсталляций, но в пре‑продакшене нужно понимать, как именно выводится оценка, чтобы не принимать ложные решения.

Практические запросы для оценки уровня bloat

Можно использовать запросы, которые сравнивают реальный размер таблицы с её «ожидаемым» размером, вычисленным по среднему числу строк на страницу. Упрощённый шаблон:

SELECT schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    round(
        n_dead_tup * 100 / NULLIF((n_live_tup + n_dead_tup), 0),
        2
    ) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Если dead_ratio превышает 10–20 %, стоит присмотреться внимательнее. Более точные оценки можно строить с участием pgstattuple или pg_class с pg_relation_size, однако для регулярного мониторинга важно избегать тяжёлых сканирующих запросов в часы пик.

Тюнинг autovacuum: когда и как снижать пороги

Адаптация autovacuum_vacuum_scale_factor для больших таблиц

Для активно изменяемых таблиц с десятками миллионов строк параметр scale_factor = 0.2 означает, что autovacuum приходит слишком редко. Можно глобально уменьшить его, например до 0.01, чтобы порог срабатывания снижался до ~10 тысяч мёртвых кортежей для миллионной таблицы. Но глобальное изменение затрагивает все таблицы, поэтому на маленьких это вызовет чересчур частые вакуумы. Компромиссный вариант - глобально оставить умолчания, а точечно настраивать отдельные «проблемные» объекты.

Ручные настройки на уровне отдельных таблиц

PostgreSQL позволяет переопределять параметры автовакуума прямо в свойствах таблицы:

ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE my_table SET (autovacuum_vacuum_threshold = 100);

Так для таблицы в 10 млн строк порог станет приблизительно 100 + 0.01 × 10 000 000 = 100 100 dead tuples - значительно чаще, чем умолчательные 50 + 0.2 × 10 млн = 2 000 050. Но помните: снижение порога ведёт к более частым проходам VACUUM и отъёму дискового ресурса. Настройки нужно тестировать на нагрузке, близкой к реальной.

Агрессивные вакуумы, autovacuum_freeze_max_age и предотвращение wraparound

Отдельная угроза - зацикливание идентификаторов транзакций. PostgreSQL использует 32‑битные XID, которые могут переполниться. Чтобы этого не случилось, старые строки «замораживаются» - им присваивается специальный xmin, не участвующий в вычислении горизонта. Когда возраст самой старой ещё не замороженной транзакции приближается к autovacuum_freeze_max_age (по умолчанию 200 млн), демон запускает агрессивный вакуум, который форсирует заморозку даже на таблицах с малым количеством dead tuples. Этот процесс потребляет много ресурсов, но его отключение или откладывание грозит остановом всей базы с ошибкой «transaction ID wraparound». Здесь важен мониторинг age(relfrozenxid) и своевременная обработка предупреждений.

pg_repack и альтернативы - когда VACUUM FULL нежелателен

Если bloat уже накоплен, и простые вакуумы не уменьшают физического размера, а VACUUM FULL слишком груб, на помощь приходят утилиты вроде pg_repack. Они создают новую копию таблицы, переносят живые строки, а затем в короткой транзакции подменяют старый файл новым, сняв блокировку только на финальном этапе. Индексы при этом также перестраиваются, избавляясь от собственного bloat. Похожий подход используют pg_squeeze и некоторые онлайн‑сервисы (в облачных PostgreSQL отдельные провайдеры предоставляют свой инструментарий). Главное правило - не запускать такие операции без тестирования на похожей копии, чтобы оценить время и нагрузку.

Распространённые ошибки и мифы

«VACUUM замораживает таблицу»
Обычный VACUUM не требует эксклюзивной блокировки и спокойно уживается с читающими и пишущими запросами. Временные задержки возможны только из‑за возрастающей дисковый активности, но не из‑за блокировки уровня ACCESS EXCLUSIVE.

«Автовакуум всегда справится»
Автовакуум часто работает на стандартных настройках, которые для больших таблиц с интенсивным UPDATE/DELETE просто не успевают срабатывать вовремя. Без ручной подстройки порога можно получить стремительный bloat, даже если autovacuum формально запущен.

«Отключу autovacuum, чтобы не грузить сервер»
Это один из самых опасных мифов. Без регулярной очистки dead tuples не только не удаляются, но и растут риски переполнения XID. Да, на короткое время нагрузка снизится, но потом придётся экстренно запускать агрессивный вакуум или даже исправлять последствия в даунтайме. Лучше настроить частоту и интенсивность, а не выключать механизм целиком.

«VACUUM уменьшает размер файла на диске»
Эту роль выполняет только VACUUM FULL (или его аналоги). Обычный вакуум лишь освобождает внутренние страницы для переиспользования, но не обрезает файл.

FAQ

Может ли VACUUM уменьшить размер таблицы на диске?
Нет, обычный VACUUM только помечает мёртвое пространство как доступное для повторного использования. Физический размер файла не уменьшается. Чтобы вернуть дисковое пространство, нужен VACUUM FULL (но он блокирует всю таблицу) либо утилита типа pg_repack.

Как часто нужно запускать VACUUM вручную?
Если autovacuum настроен адекватно и справляется с темпом изменений, ручной VACUUM не требуется. Вмешательство вручную обычно нужно в двух случаях: когда после массового удаления или обновления хочется немедленно освободить пространство для следующих INSERT, либо когда autovacuum категорически не успевает и требуется разовый прогон до перенастройки порогов.

Почему autovacuum не запустился, хотя dead tuples много?
Возможные причины:

Порог срабатывания ещё не достигнут (даже при большом абсолютном числе dead tuples, если таблица огромна).
Долгоживущая транзакция или hot_standby_feedback удерживают горизонт xmin, и vacuum не может удалить эти строки.
На таблице висит конфликтующая блокировка.
Сам демон autovacuum «занят» на других таблицах или отключён.

Что делать, если таблица уже раздулась в несколько раз?
Первое - убедиться, что нет длинных транзакций и что настройки автовакуума для этой таблицы реалистичны. Затем, если нужен быстрый эффект без долгого простоя, применить pg_repack или аналогичный инструмент. В крайнем случае, при возможности обслуживания в окно простоя - VACUUM FULL. После сжатия обязательно пересмотреть параметры autovacuum, чтобы исключить повторное раздувание.

Вреден ли VACUUM для производительности в момент выполнения?
Он даёт дополнительную дисковую нагрузку: читает и переписывает страницы. Поэтому на системах с жёсткими лимитами IO или слабыми дисками одновременный vacuum может замедлить другие запросы. Но эта нагрузка, как правило, меньше, чем постоянное торможение из‑за раздутых таблиц. Честный баланс достигается через autovacuum_vacuum_cost_limit и подобные параметры, ограничивающие интенсивность очистки.

Что в итоге

Bloat в PostgreSQL - не баг, а неизбежное следствие многоверсионной архитектуры. Без регулярной и своевременной очистки dead tuples таблицы и индексы распухают, а производительность неумолимо падает. Встроенный autovacuum берёт на себя основную работу, но его стандартные пороги часто не поспевают за темпом изменений в крупных таблицах. Мониторинг pg_stat_user_tables, вдумчивый тюнинг autovacuum_vacuum_scale_factor, контроль за долгими транзакциями и аккуратное использование реплик позволяют держать bloat под контролем. А когда пространство уже потеряно, на помощь приходят неразрушающие инструменты перестроения, оставляющие VACUUM FULL для экстренных случаев. Забота о чистоте мёртвых строк - такая же рутинная часть эксплуатации PostgreSQL, как резервное копирование или обновление статистики.

VACUUM, autovacuum и bloat в PostgreSQL: почему база «толстеет» и падает производительность