В production периодически наблюдается разрастание (bloat) таблиц и индексов PostgreSQL, несмотря на работающий autovacuum, а иногда autovacuum не может завершить очистку старых версий строк. Как диагностировать и спроектировать систему, чтобы снизить риск долгих транзакций, блокирующих автовакуум, и чем это чревато для производительности?

Короткий ответ

Основная причина, при которой autovacuum не может очистить устаревшие версии строк, — долго открытые транзакции (включая «зависшие» idle in transaction соединения) или не продвигающиеся replication slots, которые удерживают старый снимок MVCC; решение — контроль длительности транзакций на уровне приложения/пула (таймауты), мониторинг age транзакций и слотов, и более агрессивная настройка autovacuum для горячих таблиц.

Как это работает подробнее

PostgreSQL использует MVCC — каждое UPDATE/DELETE создаёт новую версию строки, старая остаётся, пока активная транзакция потенциально может её видеть; autovacuum не может продвинуться дальше самой старой активной транзакции или replication slot — отсюда bloat даже при формально работающем autovacuum.

Сравнение

• Компромисс. Слишком агрессивные настройки autovacuum снижают bloat, но увеличивают фоновую нагрузку на I/O/CPU, заметную на пиковой нагрузке; слишком редкий vacuum экономит ресурсы в моменте, но накапливает bloat, замедляющий Seq Scan и индексы — решение требует баланса, основанного на профиле нагрузки конкретной таблицы, а не единой настройки «по умолчанию» для всей БД.

Пример кода

example.sqlКопировать

-- Найти долгие/зависшие транзакции
SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start IS NOT NULL
ORDER BY duration DESC;
-- Найти таблицы с большим числом "мёртвых" строк
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Что использовать на практике

• Практика. Для диагностики смотрят на pg_stat_activity (state = 'idle in transaction'), pg_stat_replication/pg_replication_slots, pg_stat_user_tables (n_dead_tup); меры — idle_in_transaction_session_timeout, более агрессивные настройки autovacuum для «горячих» таблиц, мониторинг возраста самой старой открытой транзакции.

Подводные камни

• Ошибка. Держат транзакцию открытой во время ожидания ответа от внешнего API; игнорируют неиспользуемые replication slots (например, от удалённого consumer логической репликации), которые бесконечно удерживают WAL.