Bloat в PostgreSQL убивает производительность — pg_repack или VACUUM FULL?

[kira_api82]

Ситуация: таблица orders, 800M строк, активные UPDATE и DELETE (статусы заказов), autovacuum настроен стандартно. Мониторинг показывает dead_tup ratio около 35%, размер таблицы 280 GB хотя живых данных по оценке ~180 GB. Запросы стали заметно медленнее за последние 2 месяца, explain analyze показывает seq scan там где раньше был index scan. Смотрю на варианты: VACUUM FULL (но это эксклюзивный лок, прод встанет), pg_repack (слышал, не пробовал). Что посоветуете?

[sshdaemon6696]

VACUUM FULL на проде с живым трафиком — только если вы готовы к даунтайму. Он берёт ACCESS EXCLUSIVE lock, никто не читает и не пишет пока идёт. На 280 GB это легко 2-4 часа. pg_repack — правильный выбор для онлайн-дефрагментации. Работает так: создаёт теневую копию таблицы, наполняет её живыми строками, логирует изменения за время копирования, применяет их, потом делает быстрый swap с коротким эксклюзивным локом в самом конце (секунды, не часы). Ставится через:

CREATE EXTENSION pg_repack;
pg_repack -h localhost -U postgres -d mydb -t orders --jobs 4

Флаг --jobs распараллеливает работу. На вашей таблице ожидайте 1-2 часа работы при этом прод не стоит.

[cachego9376]

Параллельно с pg_repack нужно починить autovacuum иначе через месяц вернётесь к той же ситуации. Для высокообновляемых таблиц стандартные настройки не работают. Добавьте per-table override:

ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_limit = 3000,
autovacuum_vacuum_cost_delay = 2
);

scale_factor = 0.01 означает что vacuum запускается когда накопилось 1% мёртвых строк, а не 20% по умолчанию. cost_limit повышаем чтобы vacuum работал быстрее и не топтался на месте.

[danila1359]

35% dead_tup при том что seq scan вместо index scan — скорее всего дело в index bloat, а не только в table bloat. VACUUM убирает dead tuples из таблицы но индексы при этом остаются раздутыми. Нужно ещё REINDEX CONCURRENTLY:

REINDEX INDEX CONCURRENTLY orders_status_idx;

CONCURRENTLY позволяет делать это без блокировки. PostgreSQL 18 кстати немного ускорил процесс reindex. Проверьте размеры индексов через:

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) FROM pg_indexes WHERE tablename = 'orders' ORDER BY pg_relation_size(indexname::regclass) DESC;

[netvue4299]

Из личного опыта с похожей таблицей (700M строк, высокий update): pg_repack отработал за 80 минут, таблица похудела с 260 GB до 160 GB, запросы вернулись к нормальным планам. Но важный момент — во время работы pg_repack нагружает диски и процессор. У нас в пике он занимал 40% IO. Запускайте в ночное окно или в часы минимальной нагрузки. И обязательно мониторьте replication lag если есть реплики — pg_repack WAL трафик заметно поднимает.

[matvey5884]

@freelancer_ru, Ещё стоит посмотреть на долгоживущие транзакции — они блокируют vacuum и это частая причина накопления bloat. Запрос:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE state != 'idle' AND now() - pg_stat_activity.query_start > interval '5 minutes' ORDER BY duration DESC;

Если видите транзакции старше 30 минут — это потенциальные блокировщики autovacuum. Также настройте statement_timeout и idle_in_transaction_session_timeout чтобы такого не накапливалось.