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

[marianna]

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

[matguyvr]

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 часа работы при этом прод не стоит.

[regexveteran]

Параллельно с 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 работал быстрее и не топтался на месте.

[marianna]

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;

[makler]

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

[k8s2000]

@marianna, Ещё стоит посмотреть на долгоживущие транзакции — они блокируют 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 чтобы такого не накапливалось.

[peekatwo]

Добавлю практический момент по pg_repack который не всегда очевиден: перед запуском проверьте что на таблице нет активного logical replication slot с отставанием — pg_repack создаёт триггеры и пишет дельту в отдельную очередь, и если слот лагает, это дельта будет накапливаться быстрее чем успевает применяться. Видел кейс где pg_repack завис на фазе apply_log на 800M-строчной таблице именно из-за этого. Команда для проверки перед стартом: SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) FROM pg_replication_slots WHERE active = false; Если видите там гигабайты — сначала разберитесь со слотами.

[Bowden]

@regexveteran, про index bloat абсолютно верно. Добавлю способ быстро оценить раздутость конкретного индекса без сторонних расширений: SELECT pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE relname = 'orders' ORDER BY pg_relation_size(indexrelid) DESC; Если видите индекс 40+ GB при реальных данных на 180 GB — это верный признак. После REINDEX CONCURRENTLY у нас на похожей таблице индексы ужались с 45 GB до 18 GB, и планировщик сразу вернулся к index scan.

[ohavt]

@matguyvr, настройки autovacuum правильные, только ещё один параметр часто забывают: autovacuum_analyze_scale_factor тоже стоит опустить для горячих таблиц, иначе статистика устаревает и планировщик продолжает выбирать кривые планы даже после того как bloat убрали. Ставлю 0.005 вместе с vacuum_scale_factor 0.01 — и планы нормализуются заметно быстрее после pg_repack.