pg_repack: онлайн‑перестройка таблиц и борьба с bloat без простоя

Когда база живёт под реальной нагрузкой, bloat — разрастание таблиц и индексов — становится не «если», а «когда». MVCC, длительные транзакции, частые UPDATE/DELETE и запаздывающий autovacuum приводят к накоплению «мёртвых» версий строк. Итог — рост файлов на диске, падение эффективности кэшей, избыточные чтения и деградация планов запросов. Стандартный VACUUM чистит видимость, но почти не возвращает место ОС; VACUUM FULL возвращает, но требует эксклюзивной блокировки и фактически означает простой. Решение для продакшена — pg_repack: перестройка таблиц и индексов онлайн с минимальной паузой на финальный атомарный свап.

Что такое bloat и чем он опасен

Bloat — это несоответствие между полезными данными и занимаемым на диске пространством из-за удалённых/устаревших версий строк и фрагментации. Он бывает табличным и индексным:

• Табличный bloat — «дыры» и фрагментация страниц. Страницы читаются чаще, кэш греется сильнее, последовательные сканы долго гоняют лишние мегабайты.
• Индексный bloat — разрастание B-Tree: высота дерева растёт, кэшируемость падает, увеличиваются случайные чтения.

Симптомы: рост pg_relation_size без роста строк, увеличение n_tup_del/n_tup_hot_upd, индексы стали толще таблицы, планы запросов переключились на «неожиданные» сканы.

Почему VACUUM не решает всё

VACUUM помечает версии строк как мёртвые, но не ужимает файл отношения. Свободное место остаётся внутри файлов и используется под будущие вставки. Возврат места ОС выполняет VACUUM FULL, но он:

• берёт ACCESS EXCLUSIVE на таблицу;
• переписывает таблицу и индексы;
• блокирует запись и чтение — это простой.

Похожая история с CLUSTER. Безопасные варианты для индексов — REINDEX CONCURRENTLY, но с таблицами всё сложнее. Здесь и нужен pg_repack.

Как работает pg_repack (в общих чертах)

pg_repack создаёт «теневую» таблицу и копирует в неё актуальные строки в желаемом порядке, параллельно ставит триггеры на исходную таблицу, чтобы дублировать DML (INSERT/UPDATE/DELETE) в теневую. После выравнивания данных выполняется короткая финальная блокировка, в течение которой происходит атомарная подмена файлов (swap relfilenode) и перестроенные индексы «прикручиваются» к основной таблице. Весь тяжёлый этап копирования проходит под обычными DML‑операциями.

Идентификатор таблицы (OID) сохраняется, меняется только физический контейнер (relfilenode). Для приложения это прозрачно.

Требования и ограничения

• Доступ: как минимум владелец таблицы или суперпользователь для запуска и настройки расширения.
• Расширение установлено в каждой целевой БД: CREATE EXTENSION pg_repack;.
• Свободное место на диске: примерно размер таблицы + индексы (на этапе копирования потребуется дополнительное место).
• Нагрузка: при интенсивном DML возрастает WAL, I/O и нагрузка на триггеры.
• Ключи: наличие PRIMARY KEY или UNIQUE NOT NULL индекса желательны для безопасного отслеживания изменений. Без них возможны дополнительные блокировки, время простоя на финальном этапе может вырасти.

Когда выбирать pg_repack

• Нужна онлайн‑операция без длительной блокировки.
• Таблица активно используется, «ночного окна» нет.
• VACUUM FULL/CLUSTER неприемлемы из‑за простоя.
• Резерв свободного места позволяет копирование.

Оценка bloat перед запуском

Начните с простых метрик по «мёртвым» кортежам:

-- Топ по числу мёртвых строк
SELECT relname,
       n_live_tup,
       n_dead_tup,
       ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Для точной оценки пригодится расширение pgstattuple (считывает страницы и оценивает реальный dead space):

-- По одной таблице
SELECT * FROM pgstattuple('public.big_table');

-- По всем таблицам (на свой страх и риск, дорого):
SELECT c.relname,
       (pgstattuple(c.oid)).table_len AS table_len,
       ROUND((pgstattuple(c.oid)).dead_tuple_percent, 2) AS dead_pct
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY dead_pct DESC
LIMIT 20;

Индексный bloat ищут по размеру индекса относительно таблицы и по статистике сканов:

SELECT i.relname AS index,
       pg_relation_size(i.oid) AS bytes,
       pg_size_pretty(pg_relation_size(i.oid)) AS size
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
WHERE t.relname = 'big_table'
ORDER BY pg_relation_size(i.oid) DESC;

Установка и подготовка

На Debian/Ubuntu сперва ставим пакет с клиентом и функциями:

sudo apt update
sudo apt install postgresql-16-pg-repack

Инициализируем расширение в целевой БД:

psql -d appdb -c 'CREATE EXTENSION IF NOT EXISTS pg_repack;'

Проверьте, что аккаунт имеет права на целевые схемы/таблицы. На уровне сессии можно задать консервативные таймауты:

-- Безопасные таймауты в сессии psql
SET lock_timeout = '2s';
SET statement_timeout = '0';

Если нет тестового стенда, удобно развернуть его на отдельном сервере: изолируйте нагрузку и отработайте сценарий на копии данных. Для таких задач подойдёт VDS с быстрым SSD и достаточным объёмом диска под копию таблиц и WAL.

Базовые сценарии запуска

Перестроить одну таблицу «как есть»

pg_repack -h 127.0.0.1 -p 5432 -U postgres -d appdb -t public.big_table --wait-timeout=60

--wait-timeout задаёт, сколько секунд ждать короткой финальной блокировки. Если в окне нет паузы, pg_repack будет переопробовать.

Только индексы

pg_repack -d appdb -t public.big_table --only-indexes --wait-timeout=60

Это помогает, когда табличный bloat умеренный, а индексы заметно разрослись.

Вся база, параллельно

pg_repack -d appdb --all --jobs=4 --wait-timeout=60

--jobs параллелит по разным таблицам. Следите за I/O и WAL.

Сухой прогон и выборочное перестроение индекса

# Посмотреть, что будет сделано
pg_repack -d appdb -t public.big_table --dry-run

# Перестроить конкретный индекс
pg_repack -d appdb -i public.big_table_idx1 --wait-timeout=60

Что происходит внутри и где «узкие места»

Алгоритм работает так:

1. Создаётся временная таблица и индексы по исходной схеме.
2. Копируются актуальные строки, параллельно триггеры реплицируют DML.
3. Короткая пауза для атомарного свапа файлов.
4. Опционально обновляется статистика (--analyze).

Риски и ресурсы:

• Диск: нужен запас. На пике задействуется объём исходной таблицы + индексы + временные объекты.
• WAL: копирование и перестройка генерируют большой WAL. На репликации следите за max_wal_size, wal_keep_size, размером диска на слейвах и задержкой apply.
• I/O: последовательное чтение исходной и запись в теневую таблицу; на SSD обычно ок, на HDD планируйте окно.
• Лок: финальный ACCESS EXCLUSIVE на доли секунды. Он может ждать, если висят долгие транзакции/DDL.

Практические приёмы для продакшена

• Перед запуском проверьте долгоживущие транзакции и блокировки: pg_stat_activity, pg_locks. Грязная длительная транзакция удержит видимость «мёртвых» строк и увеличит копируемый объём.
• Ставьте ограничение ожидания: --wait-timeout. Лучше пусть задача аккуратно переедет на следующий цикл, чем схватит блокировку в горячий час.
• Запускайте с --dry-run, чтобы увидеть план действий, особенно в незнакомой схеме.
• Если таблица без PK, подумайте о добавлении PRIMARY KEY или UNIQUE NOT NULL индекса. Это повысит надёжность онлайн‑копирования.
• После свапа выполните ANALYZE (или добавьте --analyze), чтобы планы запросов сразу адаптировались к новой плотности данных.
• Для больших баз используйте --jobs, но не переразгоняйте — ориентируйтесь на дисковую подсистему и лимиты по WAL.

Проверка свободного места и нагрузки

Оцените бюджет по диску заранее. Упрощённо: если таблица 200 ГБ и индексы 50 ГБ, планируйте запас ≈ 250–300 ГБ на период операции (с учётом пикового WAL). На стороне PostgreSQL имеет смысл убедиться, что maintenance_work_mem достаточен для перестройки индексов.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Наблюдение за прогрессом

У pg_repack нет детального встроенного прогресс‑репортинга. Но вы можете:

• Смотреть активные операции по application_name и текущим запросам: pg_stat_activity.
• Отслеживать прирост файлов: pg_relation_size и системные утилиты на диске.
• Мониторить WAL и задержку реплик через системные вьюхи статистики.

SELECT pid, state, query
FROM pg_stat_activity
WHERE application_name LIKE 'pg_repack%';

Секционированные таблицы

Для декларативного секционирования удобнее запускать перестройку по партишенам отдельно. Родительская таблица обычно содержит только метаданные. Скриптуйте обход дочерних отношений и применяйте pg_repack к каждому разделу, учитывая локальные индексы.

Типовые проблемы и решения

• Висим на финальной блокировке: проверьте долгие транзакции, DDL и блокировки на таблице. Увеличьте --wait-timeout, перенесите задачу на менее загруженные часы, временно ограничьте тяжёлые фоновые batch‑процессы.
• WAL заполняет диск: заранее расширьте лимиты, очистите неиспользуемые replication‑слоты, проверьте, что реплики успевают применять изменения. Оцените временное повышение max_wal_size.
• «relation ... is already being repacked»: не запускайте параллельно два pg_repack на одну таблицу. Дождитесь завершения или остановите предыдущую сессию.
• Недостаточно места: перестраивайте поочерёдно, сначала индексы, затем таблицу. Рассмотрите перенос во временный таблспейс через --tablespace и --tablespace-index.

Чек‑лист перед запуском

1. Есть резервное копирование и план отката.
2. Оценён bloat и выбран порядок целей.
3. Проверено свободное место и лимиты по WAL.
4. Нет долгих транзакций и конфликтующих DDL.
5. Прогон с --dry-run и тест на стенде схожего размера.
6. Поставлены консервативные таймауты и окна запуска.

Сравнение с альтернативами

• VACUUM FULL: максимально освобождает место, но требует эксклюзивной блокировки — простой.
• CLUSTER: переупорядочивает таблицу по индексу, но с блокировкой и простоем.
• REINDEX CONCURRENTLY: хорошо против индексного bloat, таблицу не чинит.
• pg_repack: минимальная пауза на свап, чинит и таблицу, и индексы, цена — место и WAL.

Послеоперационные действия

• ANALYZE таблиц со сброшенной статистикой или используйте --analyze.
• Проверьте планы ключевых запросов и фактические времена.
• Снимите новые метрики размеров, зафиксируйте экономию.
• Подумайте об улучшении настроек autovacuum и дисциплины долгих транзакций, чтобы растягивать интервалы между репаками. Полезно ознакомиться с материалом про настройку autovacuum и индексов: тюнинг autovacuum и индексов в PostgreSQL.

Автоматизация и расписание

Для повторяющихся задач используйте планировщик: группируйте таблицы по критичности и размеру, не запускайте одновременно тяжёлые цели на одном диске. Хорошая стратегия — еженедельные прогонки для горячих таблиц и ежемесячные для «тяжёлых» архивов, с обязательным --dry-run и предварительным контрольным запросом на долгие транзакции. Не забудьте про защиту данных и откаты: пригодится руководство по PITR/WAL: резервное копирование и восстановление PostgreSQL через WAL и PITR.

Настройки PostgreSQL, которые помогают

• maintenance_work_mem: влияет на скорость перестройки индексов. Не завышайте до значения, которое способно удушить памятью остальную нагрузку.
• max_wal_size и wal_keep_size: дайте запас под тонны WAL на время операции.
• autovacuum_vacuum_scale_factor и autovacuum_analyze_scale_factor: уменьшение значений для «горячих» таблиц снижает темпы накопления bloat.
• lock_timeout: на стороне сессии применяйте короткий таймаут блокировок, чтобы не повесить прод на пике.

Итоги

pg_repack — практичный способ вернуть место и производительность PostgreSQL без простоя. Он требует дисциплины: оценка bloat, подготовка места, внимание к WAL и блокировкам. Зато награда — компактные таблицы и индексы, более тёплый кэш и предсказуемые планы запросов. Встраивайте репаки в регламент обслуживания, автоматизируйте и контролируйте метрики — и база отблагодарит стабильной производительностью.