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

REINDEX

примечание

Эта страница переведена при помощи нейросети GigaChat.

REINDEX — перестроение индексов.

Синтаксис​

REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name
REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]

where option can be one of:

    CONCURRENTLY [ boolean ]
    TABLESPACE new_tablespace
    VERBOSE [ boolean ]

Описание​

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

  • Индекс поврежден и больше не содержит корректных данных. Хотя такое не должно происходить при нормальной работе, в реальности повреждение может быть вызвано ошибками в программном обеспечении или сбоями оборудования. В этом случае REINDEX позволяет восстановить индекс.
  • Индекс чрезмерно увеличился в размере, накопив множество пустых или частично заполненных страниц. Такая ситуация возможна, например, с B-tree индексами при определенных атипичных сценариях использования. REINDEX позволяет сократить занимаемое индексом пространство за счет создания новой версии индекса без мертвых страниц. Подробнее описано в разделе «Регулярная перестроение индексов».
  • После изменения параметров хранения индекса, например коэффициента заполнения, необходимо убедиться, что изменения применились полностью. Пересоздание индекса позволяет этого добиться.
  • При неудачной попытке создать индекс с использованием параметра CONCURRENTLY, он остается в состоянии «неактивный» и не может использоваться. Для его восстановления может применяться REINDEX, причем только команда REINDEX INDEX поддерживает параллельное пересоздание таких индексов.

Параметры​

INDEX
Перестраивает указанный индекс. Если индекс относится к партиционированной таблице, эта форма команды не может выполняться внутри блока транзакции.
TABLE
Перестраивает все индексы заданной таблицы. При наличии вспомогательной таблицы TOAST ее индексы также будут перестроены. Не допускается выполнение внутри блока транзакции, если речь идет о партиционированной таблице.
SCHEMA
Перестраивает все индексы указанной схемы. Индексы таблиц TOAST и общих системных каталогов также охватываются. Эта операция должна выполняться вне транзакционного блока.
DATABASE
Перестраивает все индексы текущей базы данных, кроме системных каталогов. Индексы системных каталогов не обрабатываются. Эта форма команды REINDEX не может выполняться внутри блока транзакций.
SYSTEM
Перестраивает индексы только системных каталогов текущей базы данных. Затрагиваются также общие системные каталоги. Индексы пользовательских таблиц не обрабатываются. Выполнение возможно только вне блока транзакции.
name
Задает имя объекта (индекса, таблицы или базы данных), для которого выполняется переиндексация. Для индексов и таблиц может быть указана схема. Варианты REINDEX DATABASE и REINDEX SYSTEM допускают переиндексацию только текущей базы данных, поэтому их параметр (необязательный) должен соответствовать имени текущей базы данных.
CONCURRENTLY
Позволяет перестроить индекс без блокировки операций вставки, обновления и удаления. В стандартном режиме записи (но не чтение) блокируются до завершения операции. Особенности использования этого параметра описаны в разделе «Неблокирующее перестроение индексов».

Для временных таблиц REINDEX всегда выполняется более простым, неблокирующим способом, так как они не могут использоваться никакими другими сеансами.

TABLESPACE
Указывает новое табличное пространство, в которое будут перенесены перестроенные индексы.
VERBOSE
Выводит сообщения о ходе выполнения переиндексации.
boolean
Управляет включением или отключением выбранных параметров. Для включения можно указать TRUE, ON или 1, для отключения — FALSE, OFF или 0. Если значение опущено, считается, что задано TRUE.
new_tablespace
Задает имя табличного пространства, куда следует перенести индексы при перестроении.

Примечания​

Если предполагается повреждение индекса в пользовательской таблице, его можно перестроить с помощью команд REINDEX INDEX или REINDEX TABLE, в зависимости от того, нужно ли восстановить один индекс или все индексы таблицы.

Ситуация усложняется при повреждении индексов системных таблиц. В этом случае важно исключить их использование сервером. Часто такие повреждения приводят к немедленным сбоям серверных процессов при запуске. Чтобы восстановить работоспособность, сервер следует запустить с параметром -P, отключающим использование индексов при работе с системным каталогом.

Один из надежных способов — остановить сервер и запустить его в однопользовательском режиме с параметром -P в командной строке. В этом режиме доступны команды REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE или REINDEX INDEX, выбор зависит от объема требуемого восстановления. Если есть сомнения, можно воспользоваться REINDEX SYSTEM, чтобы восстановить все индексы системных таблиц в базе данных. После завершения работ сессия однопользовательского режима завершается, и сервер запускается в обычном режиме. Дополнительные сведения приведены в разделе postgres.

Альтернативно параметр -P можно указать при запуске обычного клиентского соединения. Например, при использовании клиентов, основанных на libpq, переменная окружения PGOPTIONS может быть установлена в значение -P до запуска клиента. Несмотря на то, что этот метод позволяет избежать остановки сервера, целесообразно ограничить подключение других пользователей к проблемной базе данных до завершения восстановления.

Команда REINDEX по своей сути эквивалентна удалению и повторному созданию индекса, так как наполнение строится заново. Однако поведение с точки зрения блокировок отличается. REINDEX блокирует операции записи в таблицу, но не блокирует чтение. При этом накладывается блокировка ACCESS EXCLUSIVE на индекс, что запрещает попытки его использования в чтении. Поскольку планировщик запросов по умолчанию накладывает блокировку ACCESS SHARE на все индексы таблицы, команда REINDEX блокирует почти все запросы, за исключением ранее подготовленных, не использующих этот индекс. Для сравнения, DROP INDEX временно блокирует и чтение, и запись в таблицу, а последующий CREATE INDEX блокирует только запись. При этом, так как индекс отсутствует, чтение не обращается к нему, что предотвращает дополнительные блокировки, но может привести к более дорогим последовательным сканированиям.

Пока выполняется команда REINDEX, путь поиска search_path временно изменяется на pg_catalog, pg_temp.

Переиндексация отдельного индекса или таблицы требует наличия привилегии MAINTAIN для этой таблицы. Обратите внимание, что хотя переиндексация разделенного индекса или таблицы требует наличия привилегии MAINTAIN для разделенной таблицы, такие команды пропускают проверку привилегий при обработке отдельных секций. Переиндексация схемы или базы данных требует владения данной схемой или базой данных либо иметь привилегии роли pg_maintain. Следует особо отметить, что таким образом пользователи, не являющиеся суперпользователями, могут перестроить индексы таблиц, принадлежащих другим пользователям. Однако, как особое исключение, REINDEX DATABASE, REINDEX SCHEMA, а также REINDEX SYSTEM пропустят индексы общих каталогов, если у пользователя нет привилегии MAINTAIN на этот каталог.

Для партиционированных таблиц и индексов поддерживаются команды REINDEX TABLE и REINDEX INDEX. Каждая партиция обрабатывается в отдельной транзакции. Такие команды нельзя использовать внутри блока транзакций, если затрагиваются партиционированные объекты.

При использовании REINDEX с указанием TABLESPACE для партиционированных таблиц или индексов обновляются только табличные пространства листовых разделов. Сами корневые партиционированные объекты не изменяются, поэтому рекомендуется дополнительно выполнить ALTER TABLE ONLY, чтобы новые разделы наследовали новое табличное пространство. В случае сбоя некоторые индексы могут остаться в старом табличном пространстве; повторный запуск команды завершит перенос.

Если параметр TABLESPACE используется с REINDEX SCHEMA, REINDEX DATABASE или REINDEX SYSTEM, то системные таблицы не будут перемещены, а в журнале появится предупреждение WARNING. Индексы TOAST-таблиц при этом перестраиваются, но не переносятся в новое табличное пространство.

Неблокирующее перестроение индексов​

Переиндексация может негативно повлиять на работу базы данных. В типичном случае PostgreSQL блокирует индексируемую таблицу от записи, выполняя полное сканирование таблицы для перестроения индекса. Чтение остается доступным, но любые попытки вставки, обновления или удаления данных будут заблокированы до завершения операции. Это особенно критично для производственных систем: переиндексация крупных таблиц может занять часы, а даже на малых таблицах блокировки записи могут быть неприемлемыми.

Для снижения воздействия PostgreSQL поддерживает переиндексацию с минимальной блокировкой посредством использования параметра CONCURRENTLY в команде REINDEX. Этот режим требует два сканирования таблицы для каждого переиндексируемого объекта и ожидания завершения всех транзакций, которые могли бы использовать старый индекс. Хотя этот способ требует больше ресурсов и времени, он позволяет базе продолжать работу без простоев, что делает его предпочтительным для производственных систем. Разумеется, другие операции могут несколько замедлиться из-за дополнительной нагрузки на процессор, память и ввод/вывод, связанной с перестроением индекса.

Процесс REINDEX CONCURRENTLY выполняется поэтапно, каждый шаг — в отдельной транзакции. Если индексов несколько, каждый этап обрабатывает все индексы, прежде чем перейти к следующему:

  1. В pg_index создается временное определение нового индекса, которое будет использоваться для замены старого. Для таблиц и индексов устанавливается блокировка SHARE UPDATE EXCLUSIVE, чтобы запретить изменения схемы во время обработки.
  2. Выполняется первое сканирование таблицы для построения нового индекса. После завершения этого этапа индекс становится доступным для новых вставок (через установку параметра pg_index.indisready = true) и видим другим сессиям после завершения транзакции, которая выполнила построение. Этот шаг выполняется в отдельной транзакции для каждого индекса.
  3. Производится второе сканирование таблицы, чтобы добавить кортежи, появившиеся после первого сканирования.
  4. Все ограничения, связанные с индексом, перенаправляются на новое определение, происходит переименование, и параметр pg_index.indisvalid переключается на true для нового индекса, делая его активным. Одновременно старый индекс помечается как недействительный, и все сессии, его использующие, получают уведомление о недействительности.
  5. Старый индекс переводится в состояние pg_index.indisready = false, во избежание добавления в него новых кортежей, как только завершатся текущие запросы, которые могли обращаться к этому индексу.
  6. Старый индекс удаляется, а все временные блокировки снимаются.

Если при переиндексации возникает ошибка, например нарушение уникальности, операция завершится сбоем, а новый индекс останется в системе в состоянии INVALID. Такой индекс не используется для запросов, но продолжает потреблять ресурсы. Это можно проверить через \d, где индекс будет отображен, например:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Modifiers
--------+---------+-----------
 col    | integer |
Indexes:
    "idx" btree (col)
    "idx_ccnew" btree (col) INVALID

Индекс с суффиксом _ccnew является временным и должен быть удален вручную через DROP INDEX, после чего можно повторно запустить REINDEX CONCURRENTLY. Если же индекс имеет суффикс _ccold, это прежний индекс, не удаленный по ошибке — его тоже можно безопасно удалить.

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

Еще одно отличие заключается в том, что обычная команда REINDEX TABLE или REINDEX INDEX может быть выполнена внутри блока транзакции, но REINDEX CONCURRENTLY не может. Как и другие длительные транзакции, REINDEX может влиять на возможности удаления устаревших кортежей в других таблицах, если в это время выполняется VACUUM.

Команда REINDEX SYSTEM не поддерживает CONCURRENTLY, так как системные каталоги не допускают параллельной переиндексации. Также невозможно переиндексировать одновременно индексы, поддерживающие ограничения исключения — их нужно обрабатывать отдельно, без CONCURRENTLY.

Ход выполнения переиндексации можно отслеживать через представление pg_stat_progress_create_index. Подробности смотрите в разделе «Отчет о ходе выполнения CREATE INDEX».

Примеры​

Перестроение одного индекса:

REINDEX INDEX my_index;

Перестроение всех индексов таблицы my_table:

REINDEX TABLE my_table;

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

$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

Перестроение индексов таблицы, допускающее одновременные операции чтения и записи с затрагиваемыми в процессе переиндексации отношениями:

REINDEX TABLE CONCURRENTLY my_broken_table;

Совместимость​

В стандарте SQL нет команды REINDEX.

Смотрите также​

CREATE INDEX, DROP INDEX, reindexdb, «Отчет о ходе выполнения CREATE INDEX»