Переиндексация данных

Павел Семёнов

Contents

Определение необходимости переиндексации
Способы перестроения индексов
Обновление статистики индексов после переиндексации

В Greengage DB переиндексация — это операция, при которой существующие индексы пересоздаются заново на основе текущих данных таблицы. Функционально это то же самое, что удаление индекса и его повторное создание с нуля, но с отличиями в использовании блокировок. Эти отличия описаны в разделе Комбинация DROP INDEX и CREATE INDEX.

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

Раздувание индекса

Как и таблицы, индексы со временем раздуваются при обновлении или удалении строк таблицы. Раздувание увеличивает размер индекса и может замедлять доступ к нему, так как его структура на диске становится менее эффективной.
Изменение параметра индекса fillfactor

При изменении параметра fillfactor для индекса требуется его перестроение, чтобы изменения вступили в силу. Без переиндексации существующие страницы индекса сохранят прежнее заполнение.
Повреждение индекса

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

Индексы системного каталога можно перестроить так же, как индексы пользовательских таблиц.

Блокировки и использование ресурсов при переиндексации

Так как переиндексация потребляет ресурсы системы и использует блокировки, рекомендуется избегать ее без необходимости. Чтобы узнать, когда переиндексация нужна, см. раздел Определение необходимости переиндексации.

При планировании операций переиндексации имейте в виду следующие факторы:

Переиндексация может блокировать таблицы системного каталога и параллельные запросы — поэтому ее лучше проводить в периоды низкой нагрузки или в запланированное время обслуживания.
Greengage DB создает полную копию индекса перед заменой старого, для чего требуется временное место на диске, примерно равное размеру индекса.
Операции переиндексации потребляют память ресурсной группы, назначенной инициатору, и ограничены ее лимитом на одновременные транзакции. Длительные операции могут заблокировать выполнение других транзакций в этой ресурсной группе.
Для больших распределенных таблиц перестроение индексов может создавать значительный сетевой трафик между сегментами и мастером.

Планируйте окна обслуживания с учетом этих факторов и контролируйте использование ресурсов во время операции.

Чтобы определить, нужно ли перестраивать индекс, учитывайте следующие факторы:

Замедление работы

Замедление одних и тех же запросов на тех же объемах данных может означать, что индексы, которые они используют, раздуты или повреждены. Используйте EXPLAIN или EXPLAIN ANALYZE, чтобы проверить, используются ли индексы при выполнении запросов. Подробнее см. в разделе Анализ использования индексов.
Рост размера индекса

Если размер индекса, который возвращает функция pg_relation_size(), растет непропорционально размеру таблицы, индекс, вероятно, раздут.
Значительные изменения данных

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

ПРИМЕЧАНИЕ

При массовых вставках или обновлениях часто быстрее удалить индексы изменяемой таблицы и создать их заново после операции. Подробнее см. в разделе Комбинация DROP INDEX и CREATE INDEX.

Greengage DB предоставляет несколько способов перестроить индексы:

Команды SQL REINDEX.
Утилита командной строки reindexdb.
Комбинация команд DROP INDEX и CREATE INDEX.

В некоторых случаях ручное удаление и повторное создание индексов быстрее, чем REINDEX. Это особенно актуально при массовых загрузках данных или крупных изменениях. Подробности см. в разделе Комбинация DROP INDEX и CREATE INDEX.

Для пересоздания индексов используйте подходящую форму команды REINDEX:

REINDEX INDEX — перестраивает указанный индекс.
```
REINDEX INDEX orders_total_idx;
```
REINDEX TABLE — перестраивает все индексы указанной таблицы.
```
REINDEX TABLE orders;
```
REINDEX DATABASE — перестраивает все индексы в указанной базе данных.
```
REINDEX DATABASE books_store;
```
REINDEX SYSTEM — перестраивает индексы системных каталогов в указанной базе данных, включая общие системные каталоги.
```
REINDEX SYSTEM books_store;
```

ПРИМЕЧАНИЕ

REINDEX DATABASE и REINDEX SYSTEM имеют следующие ограничения:

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

Утилита reindexdb позволяет перестраивать индексы из командной строки, без использования SQL-консоли. Подходит для автоматизированного обслуживания через задания cron, скрипты или инструменты оркестрации.

Чтобы пересоздать все индексы в базе данных, выполните:

$ reindexdb books_store

Полная форма включает имя параметра -d (--dbname):

$ reindexdb -d books_store

Чтобы перестроить конкретный индекс или все индексы таблицы, используйте параметры -i (--index) и -t (--table):

$ reindexdb -i orders_total_idx books_store
$ reindexdb -t orders books_store

ПРИМЕЧАНИЕ

При использовании полных названий параметров --dbname, --table и --index указывайте значения после знака равенства:

$ reindexdb --table=orders --dbname=books_store

Для перестроения нескольких индексов или индексов нескольких таблиц укажите каждое название в отдельном параметре:

$ reindexdb -i orders_total_idx -i orders_comment_idx books_store
$ reindexdb -i orders_total_idx -t customers books_store

Для переиндексации системных каталогов используйте параметр -s (--system):

$ reindexdb -s books_store

Параметр -a (--all) перестраивает индексы всех баз данных в кластере Greengage DB:

$ reindexdb --all

При запуске с -a/--all reindexdb подключается к базе данных postgres (или к template1, если postgres не существует) и собирает список баз данных кластера для реиндексации. Вы можете явно указать базу данных для подключения в этом случае с помощью опции --maintenance-db:

$ reindexdb --all --maintenance-db admin_database

Два параметра управляют выводом reindexdb:

-e (--echo) — выводит SQL-команды, которые выполняет утилита.
-q (--quiet) — отключает вывод.

Переиндексация с помощью REINDEX эквивалентна удалению индекса (DROP INDEX) и его повторному созданию (CREATE INDEX). Индекс можно перестроить следующим образом:

DROP INDEX orders_total_idx;
CREATE INDEX orders_total_idx ON orders (total);

Отличия между REINDEX и комбинацией DROP INDEX-CREATE INDEX:

Блокировки
- REINDEX устанавливает блокировку ACCESS EXCLUSIVE на индекс и блокировку SHARE UPDATE EXCLUSIVE на таблицу. Это блокирует использование индекса и запрещает запись в таблицу до завершения операции. Запросы, которые пытаются использовать индекс, блокируются на все время выполнения.
- DROP INDEX устанавливает блокировку ACCESS EXCLUSIVE на таблицу только на время удаления. После удаления индекса запросы, которые его использовали, переходят на последовательное сканирование или другие индексы. CREATE INDEX блокирует запись в таблицу, но позволяет читать данные.
  
  ПРИМЕЧАНИЕ
  
  В обоих случаях отсутствие индекса может замедлить выполнение некоторых запросов до его восстановления.
Возможности команд

REINDEX позволяет перестроить все индексы таблицы, схемы или базы данных одной командой, тогда как комбинация DROP INDEX и CREATE INDEX работает с одним индексом за раз.
Производительность при массовых изменениях данных

Удаление индексов перед крупной загрузкой данных и их последующее пересоздание часто быстрее, чем обновление индексов во время вставок или обновлений. При наличии индекса каждая вставляемая строка также добавляется в него, увеличивая нагрузку на диск и CPU.

Пример:
```
DROP INDEX orders_total_idx;
-- INSERT ...
CREATE INDEX orders_total_idx ON orders (total);
```

После выполнения REINDEX статистика индексов, в частности reltuples и relpages в системном каталоге pg_class, сбрасывается в ноль. Чтобы обновить статистику для планировщика запросов, выполните ANALYZE на таблицах, чьи индексы были перестроены:

ANALYZE orders;

Удаление устаревших строк с помощью VACUUM

Управление spill-файлами

Переиндексация данных

Определение необходимости переиндексации

Способы перестроения индексов

Команды REINDEX

Утилита reindexdb

Комбинация DROP INDEX и CREATE INDEX

Обновление статистики индексов после переиндексации