# Перестроение таблиц и индексов Индекс LLMS: [llms.txt](/ru/llms.txt) --- ## 8.1. Полная очистка {#internal-ru-section-8-1} ### Необходимость {#internal-ru-subsection-star-130} Обычная очистка освобождает больше места, чем самоочистка, но и она не всегда решает задачу полностью. Если файлы таблицы или индекса по каким-то причинам сильно выросли в размерах, то обычная очистка освобождает место внутри существующих страниц, но число страниц в большинстве случаев не уменьшается. Единственная ситуация, при которой очистка возвращает место операционной системе, — образование полностью пустых страниц в самом конце файла, что происходит нечасто. (*с.* [133](/ru/06-vacuum-and-freezing/#internal-ru-page-134)) Излишний размер приводит к неприятным последствиям: - замедляется полное сканирование таблицы (или индекса); - может потребоваться больший буферный кеш (ведь кешируются страницы, а плотность полезной информации в них падает); - в B-дереве может появиться лишний уровень, который будет замедлять индексный доступ; - нерационально расходуется место на диске и в резервных копиях. Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить *полную очистку* [^internal-ru-172-1] командой `VACUUM FULL`. Она последовательно перестраивает сначала таблицу, а затем и все ее индексы с нуля, максимально компактно упаковывая данные (с учетом параметра *fillfactor*). В процессе перестроения объекта на диске хранятся и старые, и новые файлы[^internal-ru-172-2], поэтому для работы полной очистки может потребоваться довольно много свободного места. (*с.* [111](/ru/05-page-pruning-and-hot-updates/#internal-ru-page-112)) Следует учитывать, что на все время перестроения таблица полностью блокируется и для записи, и для чтения. ### Оценка плотности информации {#internal-ru-subsection-star-132} Для иллюстрации вставим в таблицу некоторое количество строк, отключив автоматическую очистку: ```text {label="psql"} => TRUNCATE vac; => ALTER TABLE vac SET (autovacuum_enabled = off); => INSERT INTO vac(id,s) SELECT id, id::text FROM generate_series(1,500000) id; ``` Плотность информации удобно оценивать с помощью специального расширения `pgstattuple`: ```text {label="psql" hl_lines="7"} => CREATE EXTENSION pgstattuple; => SELECT * FROM pgstattuple('vac') \gx -[ RECORD 1 ]------+--------- table_len | 70623232 tuple_count | 500000 tuple_len | 64500000 tuple_percent | 91.33 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 416328 free_percent | 0.59 ``` Функция читает всю таблицу и выводит статистику по распределению места в файлах. Поле `tuple_percent` показывает процент места, занятого полезными данными (версиями строк). Его значение неизбежно меньше 100 % из-за накладных расходов на служебную информацию внутри страницы, но тем не менее в этом примере оно довольно велико. Для индекса выводится другая информация, но поле `avg_leaf_density` имеет тот же смысл: процент полезной информации (в листовых страницах). ```text {label="psql" hl_lines="11"} => SELECT * FROM pgstatindex('vac_s') \gx -[ RECORD 1 ]------+---------- version | 4 tree_level | 3 index_size | 114302976 root_block_no | 2825 internal_pages | 376 leaf_pages | 13576 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 53.83 leaf_fragmentation | 10.59 ``` Использованные функции расширения `pgstattuple` читают таблицу или индекс целиком, чтобы получить точную информацию. В случае большого объекта это может оказаться слишком затратно, поэтому в расширении есть функция `pgstattuple_approx`, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры. Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу[^internal-ru-173-1]. Вот какой размер занимают таблица и индекс: ```text {label="psql"} => SELECT pg_size_pretty(pg_table_size('vac')) AS table_size, pg_size_pretty(pg_indexes_size('vac')) AS index_size; table_size | index_size ------------+------------ 67 MB | 109 MB (1 row) ``` Теперь удалим 90 % всех строк: ```text {label="psql"} => DELETE FROM vac WHERE id % 10 != 0; DELETE 450000 ``` После обычной очистки размер файлов не меняется, поскольку нет пустых «хвостовых» страниц: ```text {label="psql"} => VACUUM vac; => SELECT pg_size_pretty(pg_table_size('vac')) AS table_size, pg_size_pretty(pg_indexes_size('vac')) AS index_size; table_size | index_size ------------+------------ 67 MB | 109 MB (1 row) ``` Но плотность информации уменьшилась примерно в десять раз: ```text {label="psql"} => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s; tuple_percent | avg_leaf_density ---------------+------------------ 9.13 | 6.66 (1 row) ``` Сейчас таблица и индекс размещаются в файлах со следующими именами: ```text {label="psql"} => SELECT pg_relation_filepath('vac') AS vac_filepath, pg_relation_filepath('vac_s') AS vac_s_filepath \gx -[ RECORD 1 ]--+----------------- vac_filepath | base/16391/16526 vac_s_filepath | base/16391/16527 ``` Теперь проверим, что получится после полной очистки. ```text {label="psql"} => VACUUM FULL vac; ``` Пока команда работает, ход ее выполнения можно отслеживать в представлении `pg_stat_progress_cluster` (похожем на представление для обычной очистки `pg_stat_progress_vacuum`): (v. 12) ```text {label="psql"} => SELECT * FROM pg_stat_progress_cluster \gx -[ RECORD 1 ]-------+------------------ pid | 53523 datid | 16391 datname | internals relid | 16486 command | VACUUM FULL phase | seq scanning heap cluster_index_relid | 0 heap_tuples_scanned | 43088 heap_tuples_written | 43088 heap_blks_total | 8621 heap_blks_scanned | 7429 index_rebuild_count | 0 ``` ```sql VACUUM ``` Этапы выполнения полной очистки[^internal-ru-175-1], конечно, отличаются от этапов обычной очистки. После полной очистки файлы поменялись на новые: ```text {label="psql"} => SELECT pg_relation_filepath('vac') AS vac_filepath, pg_relation_filepath('vac_s') AS vac_s_filepath \gx -[ RECORD 1 ]--+----------------- vac_filepath | base/16391/16538 vac_s_filepath | base/16391/16541 ``` Размер таблицы и индекса существенно уменьшился: ```text {label="psql"} => SELECT pg_size_pretty(pg_table_size('vac')) AS table_size, pg_size_pretty(pg_indexes_size('vac')) AS index_size; table_size | index_size ------------+------------ 6904 kB | 6504 kB (1 row) ``` Плотность информации, соответственно, увеличилась, причем в индексе она увеличилась даже по сравнению с первоначальной. Заново создать индекс (B-дерево) по имеющимся данным обычно выгоднее, чем вставлять данные строка за строкой в уже имеющийся индекс: ```text {label="psql"} => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s; tuple_percent | avg_leaf_density ---------------+------------------ 91.23 | 91.03 (1 row) ``` ### Заморозка {#internal-ru-subsection-star-134} При перестроении таблицы версии строк замораживаются, поскольку это ничего не стоит по сравнению с остальным объемом работы: ```text {label="psql" hl_lines="4 5 6 7 8"} => SELECT * FROM heap_page('vac',0,0) LIMIT 5; ctid | state | xmin | xmin_age | xmax -------+--------+-------+----------+------ (0,1) | normal | 892 f | 4 | 0 a (0,2) | normal | 892 f | 4 | 0 a (0,3) | normal | 892 f | 4 | 0 a (0,4) | normal | 892 f | 4 | 0 a (0,5) | normal | 892 f | 4 | 0 a (5 rows) ``` Однако страницы не отмечаются в карте видимости и заморозки, и в заголовке страницы не проставляется признак видимости (как это происходит при выполнении команды `COPY` с параметром `FREEZE`): (*с.* [161](/ru/07-autovacuum/#internal-ru-page-162)) ```text {label="psql"} => SELECT * FROM pg_visibility_map('vac',0); all_visible | all_frozen -------------+------------ f | f (1 row) => SELECT flags & 4 > 0 all_visible FROM page_header(get_raw_page('vac',0)); all_visible ------------- f (1 row) ``` И только после выполнения команды `VACUUM` (или срабатывания автоочистки) ситуация исправляется: ```text {label="psql"} => VACUUM vac; => SELECT * FROM pg_visibility_map('vac',0); all_visible | all_frozen -------------+------------ t | t (1 row) => SELECT flags & 4 > 0 AS all_visible FROM page_header(get_raw_page('vac',0)); all_visible ------------- t (1 row) ``` Фактически это означает, что такая страница будет перезаписана еще раз — даже если при перестроении все версии строк табличной страницы находились за горизонтом базы данных. ## 8.2. Другие способы перестроения {#internal-ru-section-8-2} ### Аналоги полной очистки {#internal-ru-subsection-star-136} Помимо полной очистки есть еще несколько команд, которые перестраивают таблицы и индексы полностью. Все они монопольно блокируют работу с таблицей, все они удаляют старые файлы данных и создают новые. Команда `CLUSTER` во всем аналогична `VACUUM FULL`, но дополнительно упорядочивает версии строк в файлах в соответствии с одним из имеющихся индексов. Это дает планировщику возможность в некоторых случаях более эффективно использовать индексный доступ. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться. (*с.* [409](/ru/19-index-access-methods/#internal-ru-page-410)) (*с.* [415](/ru/20-index-scans/#internal-ru-page-416)) С точки зрения кода `VACUUM FULL` — просто частный случай `CLUSTER`, не требующий переупорядочивания строк[^internal-ru-177-1]. Команда `REINDEX` перестраивает индекс или несколько индексов[^internal-ru-178-1]. `VACUUM FULL` и `CLUSTER` фактически используют механизм этой команды, когда завершают работу с таблицей и доходят до индексов. Команда `TRUNCATE` [^internal-ru-178-2] удаляет все табличные строки и логически соответствует команде `DELETE` без условия `WHERE`. Но `DELETE` только помечает версии строк как удаленные, что требует дальнейшей очистки. `TRUNCATE` же просто создает новый чистый файл — как правило, это намного быстрее. (*с.* [87](/ru/03-pages-and-tuples/#internal-ru-page-88)) ### Перестроение без долгих блокировок {#internal-ru-subsection-star-138} Полная очистка не предполагает регулярного использования, так как полностью блокирует всякий доступ к таблице (включая и выполнение запросов) на все время своей работы. Для высокодоступных систем это обычно неприемлемо. (*с.* [261](/ru/12-relation-level-locks/#internal-ru-page-262)) Существует несколько расширений (например, `pg_repack` [^internal-ru-178-3]), позволяющих перестроить таблицу и индексы практически без прерывания обслуживания. Исключительная блокировка таблицы все равно требуется, но лишь в начале и в конце работы, и на короткое время. Достигается это более сложной реализацией: пока идет перестроение, триггер сохраняет изменения данных исходной таблицы, а затем утилита применяет их к новой таблице и подменяет исходную в системном каталоге. Интересное решение предлагает утилита `pgcompacttable`[^internal-ru-178-4]. Идея состоит в многократном фиктивном (не меняющем данные) обновлении строк таблицы, в результате которого актуальные версии строк постепенно перемещаются внутри файла ближе к его началу. Между сериями обновлений запускается очистка, которая удаляет неактуальные версии и постепенно усекает файл. Такой подход отнимает существенно больше времени и ресурсов, но не требует дополнительного места для перестроения таблицы и не создает пиковых нагрузок. Кратковременные исключительные блокировки при усечении таблицы возникают и в этом случае, но они обрабатываются очисткой достаточно мягко. (*с.* [133](/ru/06-vacuum-and-freezing/#internal-ru-page-134)) ## 8.3. Профилактика {#internal-ru-section-8-3} ### Читающие запросы {#internal-ru-subsection-star-140} Одной из причин раздувания файлов является совмещение долго выполняющихся транзакций, удерживающих горизонт базы, с активным обновлением данных. (*с.* [106](/ru/04-snapshots/#internal-ru-page-107)) Сами по себе долгие (читающие) транзакции не вызывают никаких проблем. Поэтому обычное решение — разнести нагрузку на разные машины: OLAP-транзакции выполнять на реплике, а быстрые OLTP-транзакции оставить на основном сервере. Конечно, это усложняет и удорожает систему, но может оказаться необходимым. Бывают ситуации, когда долгие транзакции вызваны не необходимостью, а ошибками в приложении или драйвере. Если проблему не удается решить цивилизованным путем, у администратора в распоряжении есть пара параметров: *idle\_in\_transaction\_session\_timeout*, который определяет максимальное время жизни бездействующей транзакции, и *transaction\_timeout*, который ограничивает общую продолжительность транзакции. По прошествии указанного времени (если оно не равно нулю) транзакция прерывается. (*по умолчанию:* `0ms`) (*по умолчанию:* `0ms`) (v. 17) ### Обновление данных {#internal-ru-subsection-star-142} Вторая причина раздувания состоит в одномоментном изменении большого числа строк. Обновление всех строк таблицы может привести к увеличению количества версий в два раза, а очистка не успеет вмешаться. Самоочистка может сгладить проблему, но и только. Добавим к таблице столбец `processed`, в котором будем отмечать обработанные строки: ```text {label="psql"} => ALTER TABLE vac ADD processed boolean DEFAULT false; => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 6936 kB (1 row) ``` После обновления всех строк размер таблицы увеличивается примерно вдвое: ```text {label="psql"} => UPDATE vac SET processed = true; UPDATE 50000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 14 MB (1 row) ``` Решение состоит в уменьшении объема изменений, выполняемых в одной транзакции, и разнесении изменений во времени, чтобы очистка успела удалить ненужные версии строк и освободить место под новые версии внутри уже существующих страниц. Если считать, что обновление каждой строки в принципе может быть зафиксировано независимо, то в качестве шаблона можно использовать запрос, выделяющий в таблице пакет строк определенного размера: ```sql SELECT идентификатор FROM таблица WHERE фильтрация уже обработанных строк LIMIT размер пакета FOR UPDATE SKIP LOCKED ``` В этом фрагменте кода выбирается и сразу блокируется набор строк не более заданного размера. При этом строки, уже заблокированные другими транзакциями, пропускаются — они попадут в другой пакет в следующий раз. Это довольно универсальный и удобный способ, позволяющий легко менять размер пакета и выполнять повторную обработку в случае каких-либо сбоев. Сбросим признак обработки и выполним полную очистку, чтобы вернуть размер к исходному: (*с.* [285](/ru/13-row-level-locks/#internal-ru-page-286)) ```text {label="psql"} => UPDATE vac SET processed = false; => VACUUM FULL vac; ``` После обновления первого пакета размер таблицы немного увеличивается: ```text {label="psql"} => 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); UPDATE 1000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 7064 kB (1 row) ``` Но больше размер практически не растет, поскольку новые версии строк добавляются на место вычищенных версий: ```text {label="psql"} => VACUUM vac; => 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); UPDATE 1000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 7072 kB (1 row) ``` [^internal-ru-172-1]: [postgrespro.ru/docs/postgresql/18/routine-vacuuming#VACUUM-FOR-SPACE-RECOVERY](). [^internal-ru-172-2]: [backend/commands/cluster.c](). [^internal-ru-173-1]: [wiki.postgresql.org/wiki/Show\_database\_bloat](). [^internal-ru-175-1]: [postgrespro.ru/docs/postgresql/18/progress-reporting#CLUSTER-PHASES](). [^internal-ru-177-1]: [backend/commands/cluster.c](). [^internal-ru-178-1]: [backend/commands/indexcmds.c](). [^internal-ru-178-2]: [backend/commands/tablecmds.c](), функция ExecuteTruncate. [^internal-ru-178-3]: [github.com/reorg/pg\_repack](). [^internal-ru-178-4]: [github.com/dataegret/pgcompacttable]().