Раздувание Postgres и настройка autovacuum для ускорения работы

Что такое блоат (bloat) в Postgres и почему autovacuum важен

Блоат — это лишнее, неиспользуемое пространство внутри таблиц и индексов Postgres, которое накапливается со временем. Чаще всего оно появляется при большом количестве UPDATE и DELETE. Postgres не перезаписывает строки на месте: он создаёт новую версию строки и оставляет старую до тех пор, пока фоновые процессы её не уберут.

Это лишнее пространство отрицательно влияет на производительность в нескольких предсказуемых сценариях. Запросам приходится читать больше страниц с диска и из памяти, чтобы найти те же данные. Индексы становятся больше, поэтому поиски и соединения требуют больше работы. Записи тоже могут замедлиться, так как Postgres поддерживает более крупные индексы и затрагивает больше страниц. Резервные копии и восстановление также занимают больше времени, потому что просто больше данных нужно скопировать.

Autovacuum — это фоновой рабочий процесс, который держит ситуацию под контролем. У него две основные задачи:

• Убирать «мертвые» версии строк, чтобы их место можно было переиспользовать (VACUUM)
• Обновлять статистику планировщика, чтобы Postgres выбирал адекватные планы запросов (ANALYZE)

Важно: autovacuum обычно не уменьшает физический файл на диске. Он делает место внутри таблицы пригодным для повторного использования, но объём дискового пространства может не уменьшиться. Чтобы вернуть место ОС, обычно нужны более тяжёлые операции (например, VACUUM FULL или перестройка таблицы), и у них есть реальные компромиссы.

Простой пример: админ‑панель, сгенерированная AI, «обновляет» запись пользователя при каждом открытии страницы — это даёт постоянный поток мёртвых строк. Autovacuum может справляться какое‑то время. Затем приходит всплеск трафика, он отстаёт, и обычные запросы начинают тормозить, хотя объём данных явно не изменился.

Настройка autovacuum — не просто один переключатель. Правильные значения зависят от скорости записей, размера таблиц и того, насколько вам критична фоновая нагрузка. Безопасный путь — итеративный: измеряйте, вносите небольшие изменения, наблюдайте и корректируйте.

Общие признаки, что база страдает от блоата

Блоат часто проявляется как «ничего не менялось, но всё стало медленнее». Данные бизнеса могут выглядеть стабильно, но Postgres вынужден читать и сканировать больше страниц, чтобы ответить на те же запросы. Это превращается в больше IO, больше CPU и больше ожиданий.

Сигналы, которые обычно указывают на блоат:

• Запросы, которые на прошлой неделе были быстрыми, теперь тянут, особенно те, что раньше делали быстрые индексные поиски.
• Использование диска растёт, хотя бизнес‑данные не растут с той же скоростью.
• Повышенная задержка чтения и больше промахов кэша (чаще чтение с диска, а не из памяти).
• Autovacuum запускается часто, но вы не видите ожидаемого восстановления после этого.
• Резервные копии и восстановление занимают дольше, а во время пиковой нагрузки появляется репликационная задержка.

Тонкий признак — когда план запроса остаётся «правильным» (он всё ещё использует ожидаемый индекс), но время выполнения значительно хуже. Так бывает, если индекс раздут и больше, чем нужно: Postgresу требуется больше чтений страниц, чтобы пройти по нему.

Ещё одна закономерность: сканирование таблиц замедляется, хотя число строк схоже. UPDATE и DELETE оставляют «мертвые» кортежи, и если очистка отстаёт, таблица растёт в страницах. Тогда Postgresу приходится просматривать больше страниц и делать больше проверок видимости.

Практический пример: небольшое SaaS‑приложение добавляет запись «update last_seen» при каждом запросе. В часы пик чтения начинают тайм‑аутиться, использование диска прыгает, autovacuum занята целый день. Корень проблемы не в одном плохом запросе, а в том, что база делает лишнюю работу из‑за накопления мёртвых строк и раздутых индексов.

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

Быстрый чек‑лист: подтвердите блоат прежде чем настраивать

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

Начните с простых чисел, которые можно сравнивать неделя к неделе. Сосредоточьтесь на:

• Самых больших таблицах и на том, действительно ли они активно пишутся (холодные таблицы редко объясняют блоат).
• Истории autovacuum по таблице: когда запускался в последний раз, как часто и не растёт ли время выполнения.
• Большом количестве мёртвых кортежей, особенно в часто обновляемых таблицах.
• Больших индексах на столбцах с высокой изменчивостью (индексы могут раздуться, даже если таблица выглядит нормально).
• Долгих транзакциях — они мешают vacuum удалять мёртвые кортежи.

Если нужен быстрый первичный проход, эти запросы обычно дают достаточно сигналов, чтобы решить, что копать дальше:

-- Biggest tables
SELECT relname, pg_total_relation_size(relid) AS bytes
FROM pg_catalog.pg_statio_user_tables
ORDER BY bytes DESC
LIMIT 5;

-- Dead tuples and last vacuum/autovacuum
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Biggest indexes
SELECT indexrelname, pg_relation_size(indexrelid) AS bytes
FROM pg_stat_user_indexes
ORDER BY bytes DESC
LIMIT 10;

-- Long-running transactions (can block cleanup)
SELECT pid, now() - xact_start AS xact_age, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

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

Как измерять блоат таблиц и индексов (практические методы)

Начните с того, что Postgres уже отслеживает. Блоат часто коррелирует с большим числом мёртвых строк и высокой «турбулентностью» (много UPDATE/DELETE) в одних и тех же таблицах.

1) Используйте встроенные статистики, чтобы найти мёртвые кортежи и churn

Эти представления дают быстрый, малорисковый первичный анализ. Ищите таблицы с высоким n_dead_tup, где n_tup_upd и n_tup_del постоянно растут.

-- Tables with lots of dead tuples
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Churn (writes) that tends to create bloat
SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd
FROM pg_stat_user_tables
ORDER BY (n_tup_upd + n_tup_del) DESC
LIMIT 20;

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

2) Оценка блоата без тяжёлых вычислений

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

Также проверьте, росла ли таблица быстро и никогда не уменьшалась. Postgres не возвращает место ОС после обычных DELETE и UPDATE, поэтому блоат и настройка autovacuum часто идут вместе.

3) Проверьте размер индекса и его полезность

Индексы тоже могут раздуваться, и неиспользуемые индексы — частая скрытая нагрузка. Сравните размер индекса и число его обращений.

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.indexrelname AS index_name,
  pg_relation_size(s.indexrelid) AS index_bytes,
  s.idx_scan
FROM pg_stat_user_indexes s
ORDER BY pg_relation_size(s.indexrelid) DESC;

Если индекс большой, а idx_scan близок к нулю при реальной нагрузке, это кандидат на удаление или переработку. Убедитесь, что индекс не нужен для ограничений, уникальности или редких, но критичных запросов.

4) Когда использовать более глубокие инструменты

Если нужны точные показатели блоата, используйте pgstattuple (или pgstattuple_approx для очень больших таблиц). Он может дать отчёт по мёртвому пространству и плотности кортежей, но нагрузка от него выше, поэтому запускайте в часы низкой активности.

5) Зафиксируйте базовую линию

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

Почему autovacuum отстаёт в реальных приложениях

Autovacuum — фон, который убирает мёртвые строки и держит статистику в порядке. Он решает, когда запускаться, на основе порогов, которые частично фиксированы и частично зависят от размера таблицы.

Для каждой таблицы Postgres ждёт, пока число изменённых строк не превысит триггер, состоящий из:

• базового числа (autovacuum_vacuum_threshold)
• плюс процента от размера таблицы (autovacuum_vacuum_scale_factor)

Это работает для многих таблиц. Но таблицы с большим количеством обновлений (сессии, события, очереди задач) могут создавать мёртвые строки быстрее, чем срабатывает триггер, особенно если scale factor слишком велик. Таблица растёт, триггер растёт вместе с ней, и vacuum приходит с опозданием.

Реальные блокеры, которые мешают очистке

Самая частая причина, по которой vacuum не может удалить мёртвые строки — это длинная транзакция. Пока хоть одна транзакция открыта, Postgres обязан хранить старые версии строк. Такое происходит из‑за сессий с открытым транзакционным состоянием (таб в браузере), зависших воркеров или задач, которые оборачивают слишком много работы в одну транзакцию.

Даже если vacuum разрешено работать, оно может быть слишком «мягким». Autovacuum имеет лимиты стоимости (сколько IO он может использовать до сна). На загруженных системах эти лимиты часто консервативны, поэтому vacuum продвигается медленно, в то время как приложение продолжает генерировать новые мёртвые строки.

Скрытые причины, которые всё усугубляют

Несколько паттернов регулярно ставят autovacuum в неудобное положение: крупные пакетные UPDATE/DELETE, которые затрагивают большинство строк за раз; «горячие» таблицы с частыми UPDATE (даже минимальными); idle‑in‑transaction соединения; долгие отчёты, держащие транзакции открытыми; и всплески трафика, между которыми vacuum не успевает догнать.

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

Пошагово: безопасная настройка autovacuum (начинайте с малого)

Самый безопасный путь — менять по одному параметру для одной таблицы и наблюдать в течение суток. Большая часть проблем с autovacuum исходит от нескольких «горячих» таблиц (sessions, events, логи, таблицы очередей), а не от всей базы.

Выберите таблицу с большим количеством обновлений или удалений, которая показывает замедления. Хороший кандидат обычно имеет быстро растущее число мёртвых кортежей и частые записи в обычном режиме работы.

Контролируемая последовательность действий:

1. Выберите одну «горячую» таблицу и зафиксируйте базовую линию: число строк, мёртвые кортежи и частоту автovacuum.
2. Понизьте масштабирующие факторы автovacuum для этой таблицы, чтобы обслуживание запускалось раньше (до того, как таблица сильно вырастет). Делайте изменения умеренно.
3. Если vacuum идёт слишком медленно, аккуратно поднимите ресурсы: увеличьте autovacuum_max_workers или autovacuum_vacuum_cost_limit, чтобы vacuum мог быстрее продвигаться, не дробя приложение.
4. Убедитесь, что ANALYZE поддерживает точную статистику планировщика — это должно снизить неожиданные смены планов и стабилизировать время запросов.
5. Наблюдайте полный день реальной нагрузки и сравните: мёртвые кортежи, частота vacuum, задержки запросов, и использование CPU/IO.

Практическая per‑table настройка может выглядеть так:

ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_threshold = 1000
);

Затем проверьте статистику, которую вы уже собираете:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 10;

Пример: приложение записывает 5 миллионов событий в день и удаляет старые пачками ежечасно. С дефолтными настройками vacuum начинает слишком поздно и мёртвые строки накапливаются. Понизив scale factor только для events, вы заставите autovacuum работать чаще, время запросов станет стабильнее, и вы избежите рискованных тяжёлых операций в пиковое время.

Если вы унаследовали AI‑сгенерированное приложение с хаотичным паттерном записей (циклы повторов, дублирующие вставки, штормы обновлений), стоит сначала исправить исходную проблему. Иначе вы будете постоянно настраивать autovacuum, чтобы компенсировать плохое поведение приложения.

Пошагово: вернуть место и скорость без простоя

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

1) Запустите обычный VACUUM (обычно безопасно и часто достаточно)

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

По возможности делайте это по очереди (одна большая таблица за раз) в часы низкой активности. Пример:

VACUUM (ANALYZE) public.big_table;

2) Перестройка раздутых индексов (если запросы всё ещё медленные)

Если обычный VACUUM помог мало, возможно, основная проблема — индексы. Раздутые индексы замедляют чтение и увеличивают случайный IO. В продакшне предпочтительнее конкаррентная перестройка, чтобы обеспечить работу чтения и записи:

REINDEX INDEX CONCURRENTLY public.big_table_some_idx;

Ожидайте, что это займёт время и потребует дополнительного временного дискового пространства для новой структуры индекса.

3) Используйте VACUUM FULL только если готовы к даунтайму

VACUUM FULL переписывает таблицу и может вернуть место ОС. Он же блокирует таблицу эксклюзивно, поэтому вставки, обновления и часто чтения будут заблокированы. Используйте его только с планом обслуживания и когда освобождение места критично.

4) Более безопасные варианты для очень больших таблиц

Если таблица огромна и нужно вернуть место без долгих блокировок, запланируйте окно обслуживания и примените стратегии: перестройте самые плохие индексы REINDEX CONCURRENTLY, подумайте о партицировании или архивировании старых данных и удалении старых партиций, или выполните переписывание таблицы (копирование в новую таблицу и swap), когда можно контролировать переключение.

5) Подтвердите, что вы действительно улучшили ситуацию

Не полагайтесь на ощущения. Сравните до и после простыми проверками:

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

Также верните размеры таблиц и время запросов. Если блоат возвращается быстро, корень проблемы часто в поведении приложения (массовые обновления, отсутствие нужных индексов, долгие транзакции).

График обслуживания, который не даст блоату вернуться

Хороший план обслуживания специально скучен. Он превращает блоат в рутинную задачу, а не в неожиданный инцидент. Цель — не разовая победа, а устойчивый ритм, соответствующий тому, как ваше приложение пишет данные.

Начните с выбора окон обслуживания по реальной нагрузке. Избегайте самых загруженных 30–60 минут в день. Если у вас нет графиков, ориентируйтесь на: время оформления заказов, ежедневные пакетные задания, рассылки маркетинга и периоды повышенной поддержки.

Простой недельный ритм, работающий для многих команд:

• Просмотр топ‑5 крупнейших таблиц и индексов. Проверка мёртвых кортежей и последних запусков vacuum.
• Запуск ручного VACUUM (ANALYZE) по самым горячим таблицам, если autovacuum регулярно отстаёт.
• Проверка долгих транзакций, которые блокируют vacuum.

Раз в месяц добавляйте более тяжёлый проход в окно минимальной нагрузки. Autovacuum должен справляться с обычным churn; ручная работа нужна для исключений: всплесковые удаления, крупные пакетные работы и изменения схемы.

Для ежемесячного чек‑листа держите его коротким и измеримым:

• Сравните рост таблиц и индексов по сравнению с прошлым месяцем.
• Проверьте, не растёт ли время выполнения vacuum.
• Решите, нужна ли отдельная настройка autovacuum для каких‑то таблиц (порог, scale factor, cost limits).
• Запланируйте целевую перестройку индексов или контролируемую реконструкцию только когда сможете выдержать дополнительную нагрузку.

Отслеживайте несколько метрик со временем: n_dead_tup, размер таблицы (включая toast), размеры индексов, и метки времени vacuum/analyze. При выпуске новых фич планируйте заранее: любая новая «горячая» таблица должна сразу получить разумные настройки autovacuum и попасть в еженедельный обзор.

Распространённые ошибки, которые усугубляют блоат и производительность

Большинство проблем с блоатом worsens потому, что «починка» применяется грубо и быстро. Лучше менять один параметр, измерять эффект, затем менять следующий.

1) Чрезмерная настройка autovacuum и IO‑штурмы

Сильное поднятие настроек autovacuum по всей базе может вызвать всплеск чтений/записей на диске. Это замедлит пользовательские запросы, увеличит задержки и всё равно не решит проблемы в худших таблицах, если реальные проблемы только в нескольких «горячих» таблицах.

Безопаснее настраивать параметры на уровне таблиц для нескольких таблиц с наибольшим churn (sessions, event logs, очереди).

2) Использовать VACUUM FULL на занятой таблице без плана

VACUUM FULL возвращает место, но переписывает таблицу и обычно блокирует доступ. На занятой продакшен‑таблице это выглядит как просто‑тайм. Если нужно много места, планируйте окно обслуживания, рассматривайте альтернативы и убедитесь, что у вас достаточно свободного диска для переписки.

3) Игнорировать долгие транзакции и винить autovacuum

Autovacuum не сможет удалить мёртвые строки, если есть длинные транзакции, держащие старые снимки. Вы увидите рост n_dead_tup, даже если autovacuum постоянно запускается.

Частые причины — забытые админ‑сессии, фоновые задачи, которые не коммитят, или код приложения, открывающий транзакцию и затем выполняющий медленную работу.

4) Менять только глобальные настройки вместо исправления горячих точек

Глобальные настройки autovacuum — грубый инструмент. Блоат концентрируется в отдельных таблицах и индексах, и они часто требуют собственных порогов и ограничений стоимости. Если вы настраиваете глобально, можно тратить ресурсы на «холодные» таблицы, пока настоящая проблема растёт.

5) Измерить один раз, сменить пять вещей и потерять базу

Если менять много параметров одновременно, вы не поймёте, что именно помогло. Держите простую базовую линию (размеры таблиц, n_dead_tup, время vacuum/analyze, латентность запросов) и меняйте по одному параметру.

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

Стартап выпустил activity feed: лайки, комментарии и метки «seen». За ночь записи выросли с десятков запросов в секунду до нескольких тысяч. Приложение работает, но страницы, которые раньше грузились за 200 мс, теперь занимают 2–5 секунд.

Через два дня выявились две вещи: индексы растут быстро, использование диска постоянно растёт, даже после удаления старых строк, и на оповещения приходят сообщения о недостатке места. Задержки запросов стали непредсказуемыми.

Что мы нашли

Горячая точка — одна таблица, часто изменяющаяся, например activity_events. В ней частые UPDATE и DELETE (очистка по ретенции). Autovacuum запускается, но постоянно опаздывает: таблица быстро достигает порога, затем продолжает меняться, пока vacuum пытается догнать. Мёртвые кортежи накапливаются, индексы раздуваются, и простые запросы читают намного больше страниц, чем нужно.

Что мы поменяли (без рискованных «больших» операций)

Мы исправили это небольшими целевыми шагами:

• Установили per‑table настройки autovacuum, чтобы эта «горячая» таблица вакумилась раньше и чаще.
• Увеличили рабочую способность autovacuum для этой таблицы (ограничение стоимости или число воркеров), чтобы оно успевало до следующего всплеска.
• Во время окна низкой нагрузки перестроили самые раздутые индексы.
• Запланировали еженедельное обслуживание для самых шумных таблиц.
• Изменили задания ретенции, чтобы удалять пачками поменьше, давая vacuum возможность идти в ногу.

К концу недели рост дискового использования стабилизировался. Задержки запросов перестали резко скакать, и лента активности снова стала предсказуемо быстрой.

Следующие шаги: удерживать стабильность и исправить корневую причину

После того как блоат под контролем, цель меняется: не допустить его возвращения. Самый безопасный путь — вносить по одному изменению и наблюдать его эффект, чтобы не обменять одну проблему на другую.

Начните с самого плохого места (одна таблица или очередь с большим числом обновлений) и сделайте одно понятное изменение. Например: понизьте пороги и scale factor для этой таблицы, поднимите autovacuum_vacuum_cost_limit, уменьшите churn в приложении (избегайте лишних перезаписей), или добавьте оповещения по n_dead_tup, отставанию vacuum и длинным транзакциям.

После каждого изменения отслеживайте пару метрик неделю: рост таблицы, n_dead_tup, частоту autovacuum и p95 времени запросов для эндпоинтов, которые бьют по таблице. Если улучшений нет, откатите и попробуйте другую идею.

Запишите короткий ранбук, который команда сможет повторять:

• Какая метрика инициирует действие (например: n_dead_tup выше X% или отставание vacuum более Y часов)
• Что вы меняете сначала (настройки на уровне таблицы перед глобальными изменениями)
• Как вы подтверждаете успех (две метрики и одна проверка от пользователя)
• Что не делать (ручной VACUUM в пик, длинные транзакции)
• Кто принимает решение и когда эскалировать

Если ваше приложение было сгенерировано AI, поищите в коде паттерны, создающие постоянный churn: «болтливые» записи (обновления при каждом запросе), отсутствующие индексы по foreign key, и плохая работа с сессиями, оставляющая транзакции открытыми. Частый пример — прототип, который обновляет last_seen при каждом загрузке страницы.

Когда вы продолжаете настройку, но производительность всё равно деградирует, чаще всего это проблема поведения продукта, а не vacuum. Если у вас AI‑сгенерированная кодовая база, которая «пилит» Postgres странными способами (сломанная авторизация, петли повторов, нескончаемые очереди), FixMyMess at fixmymess.ai может помочь диагностировать и исправить эти участки кода, чтобы база перестала вести бесполезную борьбу.