Перейти к содержимому

8. Перестроение таблиц и индексов

Суть

  • Обычная очистка освобождает место внутри страниц, но файл почти никогда не уменьшается. Раздувание (bloat) → медленнее полное сканирование, хуже кеширование, лишний уровень B-дерева, место на диске и в бэкапах.
  • VACUUM FULL перестраивает таблицу и все ее индексы с нуля в новые файлы (меняется filenode), плотно и с учетом fillfactor. Держит AccessExclusiveLock (не почитать даже SELECT) всё время работы и требует места под старую и новую копию. Прогресс — pg_stat_progress_cluster.
  • Плотность оценивают pgstattuple (tuple_percent, для индексов pgstatindex(...).avg_leaf_density), быстрее — pgstattuple_approx, грубо — по каталогу (запросы из wiki «Show database bloat»).
  • При перестроении версии замораживаются, но карта видимости и PD_ALL_VISIBLE не заполняются (в отличие от COPY FREEZE) — страницы будут переписаны еще раз первой VACUUM.
  • Аналоги: CLUSTER (то же + упорядочить по индексу, порядок не поддерживается), REINDEX (только индексы; есть CONCURRENTLY), TRUNCATE (новый пустой файл вместо пометки строк). Без долгих блокировок: pg_repack (триггер + подмена в каталоге, короткие блокировки в начале и конце), pgcompacttable (фиктивные UPDATE двигают строки к началу файла + VACUUM усекает хвост).
  • Профилактика: не держать долгие транзакции рядом с активными обновлениями (OLAP — на реплику; idle_in_transaction_session_timeout, transaction_timeout), и не обновлять все строки разом: обрабатывать пакетами с FOR UPDATE SKIP LOCKED, давая очистке время.
симулятор Раздувание: VACUUM vs VACUUM FULL
страниц: 10tuple_percent ≈ 100%pg_relation_filepath = base/16391/16526
INSERT … 100 строк: 10 страниц, плотность ≈ 100%
актуальная версиямертваясвободно
CREATE EXTENSION pgstattuple;
SELECT tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('vac');
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('vac_s');
SELECT * FROM pgstattuple_approx('vac'); -- пропускает all-visible страницы
SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
pg_size_pretty(pg_indexes_size('vac')) AS index_size;
SELECT pg_relation_filepath('vac'); -- после VACUUM FULL путь меняется

Пример из книги: 500 тыс. строк, DELETE 90 % → после VACUUM размеры те же (67 МБ / 109 МБ), tuple_percent 9 %, avg_leaf_density 7 %; после VACUUM FULL — 6,9 МБ / 6,5 МБ, плотность 91 % (индекс плотнее исходного: построение «с нуля» эффективнее построчной вставки).

Команда Блокировка Что делает Когда
VACUUM ShareUpdateExclusive чистит мертвое, место внутри файла всегда (автоочистка)
VACUUM FULL AccessExclusive перестраивает таблицу + индексы окно обслуживания, сильный bloat
CLUSTER idx AccessExclusive как FULL + физический порядок по индексу редко, корреляция для range-запросов
REINDEX [CONCURRENTLY] AccessExclusive / ShareUpdateExclusive только индексы раздутые/битые индексы
TRUNCATE AccessExclusive новый пустой файл очистить таблицу целиком
pg_repack короткие AccessExclusive копия + триггер + подмена прод без простоя, нужно 2× места
pgcompacttable короткие при усечении UPDATE-ы двигают строки + VACUUM прод, нет места под копию
WITH batch AS (
SELECT id FROM vac WHERE NOT processed
LIMIT 1000
FOR UPDATE SKIP LOCKED -- занятые другими строки пропускаем
)
UPDATE vac SET processed = true
WHERE id IN (SELECT id FROM batch);
-- между пакетами успевает пройти (авто)очистка, и новые версии
-- занимают место вычищенных: таблица почти не растет
Чем VACUUM отличается от VACUUM FULL?базовый

VACUUM работает параллельно с нагрузкой, удаляет мертвые версии и оставляет место внутри файла для повторного использования; размер файла почти не меняется. VACUUM FULL полностью переписывает таблицу и индексы в новые файлы, возвращая место ОС, но блокирует таблицу эксклюзивно (даже на чтение) и требует свободного места под копию.

Как понять, что таблица раздута?средний

Точно — pgstattuple (tuple_percent, dead_tuple_percent, free_percent) и pgstatindex (avg_leaf_density), но они читают объект целиком; приблизительно — pgstattuple_approx; грубо — сравнить размер файла с оценкой по reltuples и ширине строк из статистики. Косвенные признаки: размер растет при стабильном числе строк, n_dead_tup велико, долгие транзакции в pg_stat_activity.

Как убрать раздувание без простоя?средний

pg_repack: создает копию таблицы, триггером накапливает изменения, применяет их и подменяет таблицу в каталоге — эксклюзивная блокировка нужна только на короткое время в начале и конце; нужно место под копию. pgcompacttable: многократные фиктивные UPDATE перемещают строки в начало файла, а VACUUM усекает хвост — дольше, но без лишнего места. Для индексов — REINDEX CONCURRENTLY.

Почему после VACUUM FULL следующая VACUUM опять пишет все страницы?глубокий

VACUUM FULL замораживает версии, но не заполняет карту видимости и не ставит PD_ALL_VISIBLE в заголовках страниц. Первая обычная очистка обойдет все страницы, проставит флаги и отметит их в карте видимости/заморозки — каждая страница будет перезаписана еще раз. COPY FREEZE (v14+) этого недостатка не имеет (кроме toast-таблиц).

Как обновить 100 млн строк, не раздув таблицу?глубокий

Пакетами в отдельных транзакциях (LIMIT … FOR UPDATE SKIP LOCKED), с паузами или явной VACUUM между ними, чтобы мертвые версии успевали вычищаться и место переиспользовалось; следить, чтобы горизонт не держала долгая транзакция. Если обновляются неиндексированные столбцы — заранее понизить fillfactor, чтобы работали HOT-обновления. Альтернатива для «переписать всё»: создать новую таблицу через CREATE TABLE AS/INSERT … SELECT и переключиться.