Автовакуум не успевал: 40 ГБ таблицы ради 2 ГБ живых данных

MAATRIX

Однажды утром дежурный увидел в графике место на диске: база данных за последнюю неделю выросла на 12 ГБ, хотя бизнес не рос вообще. Разбор растянулся на два дня, потому что все очевидные версии оказались неверными, а настоящая причина пряталась в комбинации из трёх мелочей: одной зависшей транзакции, слишком консервативных настроек автовакуума по умолчанию и таблицы с аномально высокой частотой обновлений. К концу истории одна таблица занимала 40 ГБ на диске, хотя живых данных в ней было около 2 ГБ — остальное оказалось мёртвыми строками, которые никто не подчищал вовремя.

Первый сигнал: диск, а не медленные запросы

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

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

Дальше пошли по стандартному чек-листу:

select
  schemaname,
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) as total_size,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1), 3) as dead_ratio,
  last_autovacuum,
  autovacuum_count
from pg_stat_user_tables
order by pg_total_relation_size(relid) desc
limit 10;

Результат сразу всё расставил по местам: у таблицы orders n_live_tup было около 2 миллионов строк, n_dead_tup — почти 40 миллионов. Соотношение мёртвых строк к живым — примерно 20:1. Поле last_autovacuum показывало дату почти двухнедельной давности, при том что таблица обновляется постоянно.

Что показали логи и метрики

Чтобы понять, что вообще происходило с автовакуумом, включили подробное логирование:

log_autovacuum_min_duration = 0

После перезагрузки конфигурации (SELECT pg_reload_conf();, без рестарта сервиса) в логе начали появляться записи вида:

automatic vacuum of table "app.public.orders": index scans: 1
pages: 0 removed, 512340 remain, 480210 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 1987654 remain, 39812004 are dead but not yet removable

Строка «are dead but not yet removable» — ключевая. Автовакуум честно приходил, честно сканировал таблицу, но не мог удалить мёртвые строки, потому что для базы они всё ещё считались потенциально видимыми какой-то транзакцией. Это принципиально другая картина, чем «автовакуум не запускается» — он запускался регулярно, но работал вхолостую.

Параллельно посмотрели активность:

select pid, state, xact_start, now() - xact_start as duration, query
from pg_stat_activity
where state != 'idle'
order by xact_start
limit 20;

И нашли процесс аналитики, который открывал транзакцию в режиме REPEATABLE READ для построения отчёта и держал её открытой почти сутки — фоновая джоба зависала на медленном внешнем API и не закрывала соединение с базой, пока ждала ответ.

Гипотезы, которые отбросили

Прежде чем дойти до правильного объяснения, проверили и отбросили несколько версий.

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

Версия 2: слот репликации копит WAL. Логичная версия, потому что забытый слот репликации — классическая причина распухания базы, но по другому механизму: через pg_wal, а не через размер конкретной таблицы. Проверили pg_replication_slots — активных или забытых слотов не было, restart_lsn у всех свежий. Про этот сценарий отдельно есть статья про рост WAL из-за забытого слота репликации — стоило исключить его в первую очередь именно потому, что симптом «база резко выросла» очень похож.

Версия 3: просто выросли данные. Отбросили сразу, как только сравнили n_live_tup с реальным количеством строк в бизнес-таблице заказов — они совпадали, расхождение было только в n_dead_tup и физическом размере на диске.

Версия 4: автовакуум вообще не настроен или отключён. Проверили SHOW autovacuum; — включён, autovacuum_naptime и пороги стояли близкие к дефолтным. То есть автовакуум не был выключен административно — процесс работал, просто не справлялся.

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

Настоящая причина: горизонт видимости и заниженные пороги одновременно

Причина оказалась составной, что и делало её неочевидной с первого взгляда.

Первый фактор — открытая долгая транзакция. PostgreSQL не может удалить строку, если теоретически она ещё видна какой-то активной транзакции — так работает MVCC. Долгая транзакция аналитической джобы держала горизонт видимости (xmin horizon) зафиксированным на моменте почти суточной давности. Все строки, помеченные как удалённые или обновлённые после этого момента, автовакуум обязан был сохранять — вдруг та транзакция их ещё прочитает.

Второй фактор — характер нагрузки на таблицу. orders обновлялась не через DELETE, а через частые UPDATE статуса заказа: создан → оплачен → собран → отправлен → доставлен. В PostgreSQL UPDATE — это всегда новая версия строки плюс пометка старой как мёртвой, а не изменение на месте. При нескольких обновлениях статуса на каждый заказ таблица физически генерировала в разы больше мёртвых строк, чем было бы при таблице, где строки только вставляются и читаются.

Третий фактор — пороги автовакуума не подстроены под такую таблицу. Дефолтные autovacuum_vacuum_scale_factor = 0.2 и autovacuum_vacuum_threshold = 50 означают: автовакуум запускается, когда мёртвых строк набирается примерно 20% от размера таблицы. Для таблицы на пару тысяч строк это нормально. Для таблицы на пару миллионов строк с высокой частотой обновлений это означает, что автовакуум ждёт сотни тысяч мёртвых строк перед запуском — а пока ждёт, транзакция аналитики уже успевает продержать горизонт видимости и заблокировать реальную очистку.

Все три фактора по отдельности не привели бы к 40 ГБ на 2 ГБ живых данных. Длинная транзакция без высокой частоты обновлений дала бы умеренный рост. Высокая частота обновлений с быстро завершающимися транзакциями и разумными порогами тоже не была бы проблемой. Проблему создала именно комбинация.

Как оценили масштаб раздувания (bloat) точнее

pg_stat_user_tables даёт оценку по статистике, а не точный физический разбор. Для более точной картины использовали расширение pgstattuple:

create extension if not exists pgstattuple;

select * from pgstattuple('orders');

Результат показал долю мёртвых кортежей (dead_tuple_percent) и долю свободного места (free_percent) в самих страницах таблицы — это подтвердило, что проблема не в статистике планировщика, а в реальном физическом бloat на уровне файлов таблицы и индексов. Индексы у orders тоже раздулись — это стоит проверять отдельно через pgstatindex, потому что вакуум таблицы и переиспользование места в индексах не всегда идут в ногу.

Отдельно проверили корреляцию с записью в WAL — раздувание таблицы само по себе не должно резко увеличивать WAL, но при большом объёме переписываемых страниц рост WAL тоже заметен. Если у вас похожая картина, но акцент именно на WAL, а не на размере таблицы — стоит отдельно посмотреть на частые причины роста WAL в PostgreSQL, там разбираются смежные, но не идентичные сценарии.

Что изменили

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

Немедленно. Нашли и завершили зависшую транзакцию аналитической джобы:

select pg_terminate_backend(pid)
from pg_stat_activity
where pid = <нужный_pid>;

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

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

alter table orders set (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 2000
);

Меньший scale_factor заставляет автовакуум реагировать на гораздо меньшую долю мёртвых строк — для таблицы с высокой частотой UPDATE это осмысленно. Меньший cost_delay и больший cost_limit позволяют автовакууму работать быстрее за один проход, не растягивая уборку на часы. Плата за это — чуть больше нагрузки на IO и CPU во время вакуума, поэтому изменение вносили постепенно и следили за метриками диска, а не применяли вслепую на проде.

Возврат уже занятого места. Изменение порогов останавливает дальнейшее раздувание, но не сжимает уже существующие 40 ГБ обратно. VACUUM (обычный) помечает место как переиспользуемое внутри файла таблицы, но не отдаёт его операционной системе. Чтобы физически сократить размер файла, есть варианты:

Выбрали pg_repack, потому что таблица orders используется круглосуточно и остановить приложение на время VACUUM FULL было нельзя. Если у вас в принципе нестабильно ведёт себя вакуум на проде — отдельно стоит прочитать про частые причины медленного VACUUM в PostgreSQL и про то, почему база вообще растёт при удалении строк — это фундамент, без которого сложно разбирать похожие инциденты быстро.

Контроль на будущее. Добавили в мониторинг отдельную метрику по n_dead_tup / n_live_tup для ключевых таблиц с алертом при превышении разумного порога, и отдельный алерт на транзакции дольше определённого времени в pg_stat_activity. Второй алерт оказался даже важнее первого — именно длинные транзакции чаще всего оказываются корневой причиной, даже когда симптом выглядит как проблема автовакуума.

Общие настройки, которые стоит пересмотреть заранее

Если у вас есть таблицы с высокой частотой обновления статусов, счётчиков или очередей — стоит не дожидаться инцидента, а сразу проверить несколько вещей.

• SHOW autovacuum_max_workers; — если в базе много активных таблиц, дефолтных 3 воркеров может не хватать, и вакуум одной таблицы будет ждать своей очереди.
• SELECT relname, reloptions FROM pg_class WHERE relname = 'ваша_таблица'; — проверить, не заданы ли уже точечные настройки, которые могли устареть.
• Отдельно оценить таблицы-очереди и таблицы-счётчики: для них имеет смысл сразу занижать scale_factor, не дожидаясь, пока они раздуются.
• Пересмотреть код, который открывает длинные транзакции для чтения — аналитика, экспорт, бэкап на уровне приложения. Такие операции лучше переводить на реплику, а не держать открытыми на мастере.

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

Важная оговорка: конкретные значения scale_factor, cost_limit и cost_delay в этой статье — не универсальный рецепт, а то, что подошло для конкретной таблицы с конкретным профилем нагрузки. У вас профиль обновлений может быть другим, и подбирать значения нужно по своим метрикам n_dead_tup, а не копировать цифры из чужого инцидента.

Частые вопросы