VACUUM, bloat и autovacuum

Таблица orders занимает 180 ГБ вместо ожидаемых 80 ГБ. Autovacuum запускался 14 раз за ночь. Диск полон к 7 утра. Почему VACUUM не помог — и как остановить это в следующий раз?

Откуда берётся bloat

Каждый UPDATE создаёт новую версию tuple плюс помечает старую мёртвой — оба на диске. До коммита оба существуют. После коммита мёртвая версия остаётся на диске, пока VACUUM не решит, что она больше никому не нужна.

Модель затрат:

Пока VACUUM не уберёт мёртвые tuple, каждый sequential scan пробегает мимо них, каждый индексный lookup может на них наткнуться, и каждая страница больше необходимого минимума.

Правило большого пальца: целевая доля мёртвых tuple менее 20% для таблиц больше 50 ГБ.

Три варианта уборки

Как autovacuum решает запускаться

Autovacuum — фоновый пул процессов (3 воркера по умолчанию), просыпающийся каждую минуту и проверяющий каждую таблицу на пороговое условие:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

Дефолт: 50 + 20% живых tuple. Когда таблица переходит порог, autovacuum посылает воркера.

Шаги одного прогона autovacuum:

Oldest xmin: почему долгие транзакции блокируют уборку

Autovacuum может убирать только tuple, чей t_xmax старше глобально старейшего xmin по всем сессиям. Если одна транзакция держится открытой, её backend_xmin пинит oldest-xmin.

Диагностика:

SELECT pid, backend_xmin,
       now() - xact_start AS duration,
       state, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin
LIMIT 5;

Самый старый backend_xmin — это то, что блокирует уборку.

Нюанс hot_standby_feedback

Стриминговые реплики можно настроить с hot_standby_feedback = on, которая отсылает primary старейший активный xmin реплики. Без неё долгий аналитический запрос на реплике может упасть с canceling statement due to conflict with recovery.

С ней bloat primary — заложник самого долгого запроса реплики. Большинство production-сетапов выбирают off на аналитической реплике и принимают периодические отмены запросов — закрепление bloat primary навсегда хуже.

Числа