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, давая очистке время.
Оценка плотности
Заголовок раздела «Оценка плотности»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 | прод, нет места под копию |
Пакетная обработка вместо «UPDATE всего»
Заголовок раздела «Пакетная обработка вместо «UPDATE всего»»WITH batch AS ( SELECT id FROM vac WHERE NOT processed LIMIT 1000 FOR UPDATE SKIP LOCKED -- занятые другими строки пропускаем)UPDATE vac SET processed = trueWHERE 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 и переключиться.