Autovacuum не успевает, bloat растёт, таблица 200ГБ а живых данных 30. Кто как тюнит?

[rabbit8]

Postgres 14, таблица с активными апдейтами (статусы заказов). pg_stat показывает n_dead_tup растёт быстрее чем autovacuum его выгребает, таблица раздулась до 200ГБ при ~30ГБ реальных данных. Запросы по индексам деградируют. Что крутить кроме VACUUM FULL (который лочит всё)?

[Version]

VACUUM FULL не вариант на проде, он берёт AccessExclusiveLock. Первым делом — autovacuum слишком ленивый по дефолту. Снижай autovacuum_vacuum_scale_factor для этой таблицы до 0.01-0.02 через ALTER TABLE SET, дефолтные 0.2 для горячих таблиц это смешно.

[pleki]

+ подними autovacuum_vacuum_cost_limit (или снизь cost_delay), иначе автовакуум сам себя троттлит и ползёт как черепаха. По дефолту он специально медленный чтобы не мешать, но на твоей нагрузке он просто не догоняет dead tuples.

[asynclover]

scale_factor на конкретную таблицу не знал что можно. А почему именно per-table, а не глобально?

[rbacker]

Потому что глобально агрессивный autovacuum будет молотить и мелкие таблицы где это не нужно, жрать IO. У тебя проблема в одной горячей таблице — её и тюнь точечно. Глобально оставь дефолт.

[penalty]

А чтобы убрать УЖЕ накопленный bloat без локов — pg_repack. Пересобирает таблицу и индексы онлайн, без AccessExclusiveLock на всё время (короткий лок только в конце). Нужен только запас места на диске под копию.

[cohenst1]

Ещё причина которую часто забывают: долгие транзакции и зависшие replication slots держат xmin horizon, и autovacuum физически НЕ может вычистить мёртвые строки новее этого горизонта. Проверь pg_stat_activity на старые транзакции и неактивные слоты, у меня bloat рос именно из-за забытого слота на реплике.

[marianna]

Вот это в точку — нашёл idle in transaction висящий 6 часов от кривого воркера. Прибил, autovacuum сразу выгреб гору dead tuples. Плюс выставил scale_factor 0.02 и прогнал pg_repack. Таблица ужалась до 35ГБ. Спасибо, тред в закладки.

[redisguru]

Сохраню себе, у нас та же беда на таблице сессий. Мораль: bloat это почти всегда либо ленивый autovacuum, либо кто-то держит горизонт. VACUUM FULL — оружие последней надежды.

[asynclover]

Хочу добавить про мониторинг до того как всё совсем плохо: pg_stat_user_tables.n_dead_tup / n_live_tup в виде алерта — это must have для любой горячей таблицы. Если ratio уходит за 0.3 и autovacuum за ним не успевает, лучше узнать об этом не тогда, когда запросы уже деградировали до полного сканирования 200 ГБ.