REINDEX

REINDEX — перестроить индексы

Синтаксис

REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] имя

Описание

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

  • Индекс был повреждён, его содержимое стало некорректным. Хотя в теории этого не должно случаться, на практике индексы могут испортиться из-за программных ошибок или аппаратных сбоев. В таких случаях REINDEX служит методом восстановления индекса.

  • Индекс стал «раздутым», то есть в нём оказалось много пустых или почти пустых страниц. Это может происходить с B-деревьями в PostgreSQL при определённых, достаточно редких сценариях использования. REINDEX даёт возможность сократить объём, занимаемый индексом, записывая новую версию индекса без «мёртвых» страниц. За подробностями обратитесь к Разделу 24.2.

  • Параметр хранения индекса (например, фактор заполнения) был изменён, и теперь требуется, чтобы это изменение вступило в силу в полной мере.

  • Если построить индекс в режиме CONCURRENTLY не удаётся, индекс остаётся в «нерабочем» состоянии. Такие индексы бесполезны, но их можно легко перестроить, воспользовавшись командой REINDEX. Однако заметьте, что перестраивать индекс в неблокирующем режиме может только команда REINDEX INDEX.

Параметры

INDEX

Перестраивает указанный индекс.

TABLE

Перестраивает все индексы в указанной таблице. Если у таблицы имеется дополнительная таблица «TOAST», она так же переиндексируется.

SCHEMA

Перестраивает все индексы в указанной схеме. Если таблица в этой схеме имеет вторичную таблицу «TOAST», она также будет переиндексирована. При этом обрабатываются и индексы в общих системных каталогах. Эту форму REINDEX нельзя выполнить в блоке транзакции.

DATABASE

Перестраивает все индексы в текущей базе данных. При этом обрабатываются также индексы в общих системных каталогах. Эту форму REINDEX нельзя выполнить в блоке транзакции.

SYSTEM

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

имя

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

CONCURRENTLY

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

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

VERBOSE

Выводит отчёт о прогрессе после переиндексации каждого индекса.

Замечания

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

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

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

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

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

Для перестраивания одного индекса или индексов таблицы необходимо быть владельцем этого индекса или таблицы. Для переиндексирования схемы или базы данных необходимо быть владельцем этой схемы или базы. Заметьте, что вследствие этого в некоторых случаях не только суперпользователи могут перестраивать индексы таблиц, принадлежащих другим пользователям. Однако из этих правил есть исключение — когда команду REINDEX DATABASE, REINDEX SCHEMA или REINDEX SYSTEM выполняет не суперпользователь, индексы общих каталогов будут пропускаться, если только данный каталог не принадлежит этому пользователю (как правило, это так). Разумеется, суперпользователи могут переиндексировать всё без ограничений.

Переиндексирование секционированных таблиц или секционированных индексов не поддерживается. Переиндексировать можно каждую секцию по отдельности.

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

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

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

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

  1. В каталог pg_index добавляется переходное определение индекса, которое затем заменит старое. Для предотвращения каких-либо изменений в схеме во время операции обрабатываемые индексы, а также связанные с ними таблицы защищаются блокировкой SHARE UPDATE EXCLUSIVE на уровне сеанса.

  2. Для каждого нового индекса выполняется первый проход, на котором строится индекс. Когда индекс построен, его флаг pg_index.indisready переходит в состояние «true», чтобы этот индекс был готов к добавлениям, и таким образом он становится видимым для других сеансов сразу после окончания построившей его транзакции. Это действие выполняется в отдельной транзакции для каждого индекса.

  3. Затем выполняется второй проход, на котором в индекс вносятся кортежи, добавленные в таблицу во время первого прохода. Это действие также выполняется в отдельной транзакции для каждого индекса.

  4. Все ограничения, ссылающиеся на индекс, переключаются на определение нового индекса, а также меняются имена индексов. В этот момент флаг pg_index.indisvalid нового индекса принимает значение «true», а старого — «false», и производится сброс кеша, в результате чего все сеансы, обращавшиеся к старому индексу, получают новую информацию.

  5. Флаг pg_index.indisready старого индекса сбрасывается в «false» во избежание добавления в него новых кортежей, как только завершатся текущие запросы, которые могли обращаться к этому индексу.

  6. Старые индексы удаляются. Блокировки SHARE UPDATE EXCLUSIVE уровня сеанса, установленные для индексов и таблиц, снимаются.

Если при перестроении индексов возникает проблема, например нарушение уникальности в уникальном индексе, REINDEX прерывается, но оставляет после себя «нерабочий» новый индекс в дополнение к уже существующему. Этот индекс будет игнорироваться запросами, так как он может быть неполным; тем не менее он будет обновляться при изменении данных, что повлечёт дополнительные издержки. Команда psql \d будет обозначать такой индекс как INVALID (нерабочий):

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

Если имя индекса с пометкой INVALID оканчивается на ccnew, это переходный индекс созданный при параллельной операции, и для исправления ситуации рекомендуется удалить его, выполнив DROP INDEX, а затем попытаться ещё раз выполнить REINDEX CONCURRENTLY. Если же имя нерабочего индекса оканчивается на ccold, значит, это исходный индекс, удалить который по какой-то причине не получилось. Такой индекс рекомендуется просто удалить, так как нужный индекс был перестроен успешно.

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

Как и любая длительная транзакция, операция REINDEX с таблицей может повлиять на возможность удаления кортежей параллельной операцией VACUUM с какой-либо другой таблицей.

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

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

Примеры

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

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;

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

Команда REINDEX отсутствует в стандарте SQL.