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

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

PostgreSQL 18 · Издание 2026 года · Китайский перевод доступен для PostgreSQL 18

简体中文 · PG14English · PG14简体中文 · PG18Русский · PG18

8.1. Полная очистка

Необходимость

Обычная очистка освобождает больше места, чем самоочистка, но и она не всегда решает задачу полностью.

Если файлы таблицы или индекса по каким-то причинам сильно выросли в размерах, то обычная очистка освобождает место внутри существующих страниц, но число страниц в большинстве случаев не уменьшается. Единственная ситуация, при которой очистка возвращает место операционной системе, — образование полностью пустых страниц в самом конце файла, что происходит нечасто. (с. 133)

Излишний размер приводит к неприятным последствиям:

  • замедляется полное сканирование таблицы (или индекса);

  • может потребоваться больший буферный кеш (ведь кешируются страницы, а плотность полезной информации в них падает);

  • в B-дереве может появиться лишний уровень, который будет замедлять индексный доступ;

  • нерационально расходуется место на диске и в резервных копиях.

Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить полную очистку 1 командой VACUUM FULL. Она последовательно перестраивает сначала таблицу, а затем и все ее индексы с нуля, максимально компактно упаковывая данные (с учетом параметра fillfactor). В процессе перестроения объекта на диске хранятся и старые, и новые файлы2, поэтому для работы полной очистки может потребоваться довольно много свободного места. (с. 111)

Следует учитывать, что на все время перестроения таблица полностью блокируется и для записи, и для чтения.

Оценка плотности информации

Для иллюстрации вставим в таблицу некоторое количество строк, отключив автоматическую очистку:

=> 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:

=> 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 имеет тот же смысл: процент полезной информации (в листовых страницах).

=> 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, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры.

Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу3.

Вот какой размер занимают таблица и индекс:

=> 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 % всех строк:

=> DELETE FROM vac WHERE id % 10 != 0;
DELETE 450000

После обычной очистки размер файлов не меняется, поскольку нет пустых «хвостовых» страниц:

=> 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)

Но плотность информации уменьшилась примерно в десять раз:

=> 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)

Сейчас таблица и индекс размещаются в файлах со следующими именами:

=> 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

Теперь проверим, что получится после полной очистки.

=> VACUUM FULL vac;

Пока команда работает, ход ее выполнения можно отслеживать в представлении pg_stat_progress_cluster (похожем на представление для обычной очистки pg_stat_progress_vacuum): (v. 12)

=> 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
VACUUM

Этапы выполнения полной очистки4, конечно, отличаются от этапов обычной очистки.

После полной очистки файлы поменялись на новые:

=> 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

Размер таблицы и индекса существенно уменьшился:

=> 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-дерево) по имеющимся данным обычно выгоднее, чем вставлять данные строка за строкой в уже имеющийся индекс:

=> 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)

Заморозка

При перестроении таблицы версии строк замораживаются, поскольку это ничего не стоит по сравнению с остальным объемом работы:

=> 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)

=> 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 (или срабатывания автоочистки) ситуация исправляется:

=> 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. Другие способы перестроения

Аналоги полной очистки

Помимо полной очистки есть еще несколько команд, которые перестраивают таблицы и индексы полностью. Все они монопольно блокируют работу с таблицей, все они удаляют старые файлы данных и создают новые.

Команда CLUSTER во всем аналогична VACUUM FULL, но дополнительно упорядочивает версии строк в файлах в соответствии с одним из имеющихся индексов. Это дает планировщику возможность в некоторых случаях более эффективно использовать индексный доступ. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться. (с. 409) (с. 415)

С точки зрения кода VACUUM FULL — просто частный случай CLUSTER, не требующий переупорядочивания строк5.

Команда REINDEX перестраивает индекс или несколько индексов6. VACUUM FULL и CLUSTER фактически используют механизм этой команды, когда завершают работу с таблицей и доходят до индексов.

Команда TRUNCATE 7 удаляет все табличные строки и логически соответствует команде DELETE без условия WHERE. Но DELETE только помечает версии строк как удаленные, что требует дальнейшей очистки. TRUNCATE же просто создает новый чистый файл — как правило, это намного быстрее. (с. 87)

Перестроение без долгих блокировок

Полная очистка не предполагает регулярного использования, так как полностью блокирует всякий доступ к таблице (включая и выполнение запросов) на все время своей работы. Для высокодоступных систем это обычно неприемлемо. (с. 261)

Существует несколько расширений (например, pg_repack 8), позволяющих перестроить таблицу и индексы практически без прерывания обслуживания. Исключительная блокировка таблицы все равно требуется, но лишь в начале и в конце работы, и на короткое время. Достигается это более сложной реализацией: пока идет перестроение, триггер сохраняет изменения данных исходной таблицы, а затем утилита применяет их к новой таблице и подменяет исходную в системном каталоге.

Интересное решение предлагает утилита pgcompacttable9. Идея состоит в многократном фиктивном (не меняющем данные) обновлении строк таблицы, в результате которого актуальные версии строк постепенно перемещаются внутри файла ближе к его началу. Между сериями обновлений запускается очистка, которая удаляет неактуальные версии и постепенно усекает файл. Такой подход отнимает существенно больше времени и ресурсов, но не требует дополнительного места для перестроения таблицы и не создает пиковых нагрузок. Кратковременные исключительные блокировки при усечении таблицы возникают и в этом случае, но они обрабатываются очисткой достаточно мягко. (с. 133)

8.3. Профилактика

Читающие запросы

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

Сами по себе долгие (читающие) транзакции не вызывают никаких проблем. Поэтому обычное решение — разнести нагрузку на разные машины: OLAP-транзакции выполнять на реплике, а быстрые OLTP-транзакции оставить на основном сервере. Конечно, это усложняет и удорожает систему, но может оказаться необходимым.

Бывают ситуации, когда долгие транзакции вызваны не необходимостью, а ошибками в приложении или драйвере. Если проблему не удается решить цивилизованным путем, у администратора в распоряжении есть пара параметров: idle_in_transaction_session_timeout, который определяет максимальное время жизни бездействующей транзакции, и transaction_timeout, который ограничивает общую продолжительность транзакции. По прошествии указанного времени (если оно не равно нулю) транзакция прерывается. (по умолчанию: 0ms) (по умолчанию: 0ms) (v. 17)

Обновление данных

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

Добавим к таблице столбец processed, в котором будем отмечать обработанные строки:

=> ALTER TABLE vac ADD processed boolean DEFAULT false;
=> SELECT pg_size_pretty(pg_table_size('vac'));
 pg_size_pretty
----------------
 6936 kB
(1 row)

После обновления всех строк размер таблицы увеличивается примерно вдвое:

=> UPDATE vac SET processed = true;
UPDATE 50000
=> SELECT pg_size_pretty(pg_table_size('vac'));
 pg_size_pretty
----------------
 14 MB
(1 row)

Решение состоит в уменьшении объема изменений, выполняемых в одной транзакции, и разнесении изменений во времени, чтобы очистка успела удалить ненужные версии строк и освободить место под новые версии внутри уже существующих страниц. Если считать, что обновление каждой строки в принципе может быть зафиксировано независимо, то в качестве шаблона можно использовать запрос, выделяющий в таблице пакет строк определенного размера:

SELECT идентификатор
FROM таблица
WHERE фильтрация уже обработанных строк
LIMIT размер пакета
FOR UPDATE SKIP LOCKED

В этом фрагменте кода выбирается и сразу блокируется набор строк не более заданного размера. При этом строки, уже заблокированные другими транзакциями, пропускаются — они попадут в другой пакет в следующий раз. Это довольно универсальный и удобный способ, позволяющий легко менять размер пакета и выполнять повторную обработку в случае каких-либо сбоев. Сбросим признак обработки и выполним полную очистку, чтобы вернуть размер к исходному: (с. 285)

=> UPDATE vac SET processed = false;
=> VACUUM FULL 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
----------------
 7064 kB
(1 row)

Но больше размер практически не растет, поскольку новые версии строк добавляются на место вычищенных версий:

=> 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)

  1. postgrespro.ru/docs/postgresql/18/routine-vacuuming#VACUUM-FOR-SPACE-RECOVERY. ↩︎

  2. backend/commands/cluster.c. ↩︎

  3. wiki.postgresql.org/wiki/Show_database_bloat. ↩︎

  4. postgrespro.ru/docs/postgresql/18/progress-reporting#CLUSTER-PHASES. ↩︎

  5. backend/commands/cluster.c. ↩︎

  6. backend/commands/indexcmds.c. ↩︎

  7. backend/commands/tablecmds.c, функция ExecuteTruncate. ↩︎

  8. github.com/reorg/pg_repack. ↩︎

  9. github.com/dataegret/pgcompacttable. ↩︎

PostgreSQL 18 изнутри, © Текст, оформление, ООО «ППГ», 2026; © Издание, ДМК Пресс, 2026, под лицензией Original publication notice. См. авторство.