Для обновления индекса нужно точно знать его имя и имя схемы данных. Таблица может содержать большое количество индексов, как по полным столбцам, так и по их частичным значениям (по диапазонам значений в столбце). Обновление индекса по сути является его полным перестроением. Можно сказать, что мы производим две операции:
- Удаляем существующий индекс совсем (удаляем только по готовности нового)
- Создаём новый индекс с тем же набором условий и именем как и был до этого.
Пример обновления:
REINDEX INDEX CONCURRENTLY scheme.index_name;
Мы используем слово REINDEX, чтобы сказать среде выполнения SQL кода, что мы хотим перестроить индекс.
После этого у нас есть несколько вариантов, что пере-индексировать:
- INDEX
- TABLE
- SCHEMA
- DATABASE
- SYSTEM
Нас интересует только перестроение одного конкретного индекса, поэтому мы выбираем слово INDEX, а не TABLE или другое.
Затем мы используем слово CONCURRENTLY, чтобы не блокировать таблицу от возможных вставок, удалений или обновлений записей. Так, мы в спокойном режиме перестроим индекс. Да, это дольше чем с блокировкой, но не вызовет проблем.
Общая идея перестроения индекса такова, что существующий индекс удалится только после полного созданного нового индекса. То есть, если что-то пойдёт не так во время переиндекса, то система откатится в обычное состояние к старому индексу так как он ещё не будет удалён к этому времени.
Зачем нужно обновлять (перестраивать / пересобирать) индекс в PostgreSQL?
Любому индексу нужно физическое хранение своей информации на диске. Любая активная база данных постоянно проводит множество преобразований данных в таблицах. То есть столбцы и записи постоянно меняются. Это значит, что в физических местах их хранения также происходят преобразования на диске.
Разработчики PostgreSQL ничего умнее чем «страницы» пока не придумали. Поэтому индексы также хранят информацию в страницах как и таблицы. То есть под капотом алгоритм «освежевания» страниц точно такой же для индексов, как и для таблиц.
Говоря простыми словами индекс не сильно заморачивается над оптимизацией свободного места и тупо «распухает». Так быстрее. Вы же не оглядываетесь назад, чтобы подсчитать свои шаги, когда куда-то идёте — так и тут.
Пример. Мы создали индекс для 1000 идентификаторов внешнего ключа. У нас это третья зависимая таблица в цепочке каскада. Работает каскадное удаление. Изначальный размер индекса 72 мегабайта. Индекс был создан за 1 минуту. Транзакций в базу не поступало.
После удаления около 1480000 записей (в том числе по каскаду) размер индекса не изменился.
Теперь делаем перестроение индекса и смотрим как поменяется его размер.
REINDEX INDEX CONCURRENTLY a.efim_index_t_all_dp_idd_k8747;
Время на перестроение индекса также заняло 1 минуту. Суммарно в таблице около 250 миллионов записей. Количество записей в пропорции не сильно изменилось, поэтому время практически тоже самое.
Самое главное то, что мы существенно уменьшаем размер индекса на диске. В нашем примере это уменьшение размера составило 15 процентов. С 71 до 61 мегабайта. Это очень существенно.
Информационные ссылки
Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org
Официальный сайт СУБД PostgreSQL — https://www.postgresql.org
Команды SQL — https://postgrespro.ru/docs/postgresql/15/sql-commands
Команда REINDEX — https://postgrespro.ru/docs/postgresql/15/sql-reindex
