Для обновления индекса нужно точно знать его имя и имя схемы данных. Таблица может содержать большое количество индексов, как по полным столбцам, так и по их частичным значениям (по диапазонам значений в столбце). Обновление индекса по сути является его полным перестроением. Можно сказать, что мы производим две операции:
Пример обновления:
REINDEX INDEX CONCURRENTLY scheme.index_name;
Мы используем слово REINDEX, чтобы сказать среде выполнения SQL кода, что мы хотим перестроить индекс.
После этого у нас есть несколько вариантов, что пере-индексировать:
Нас интересует только перестроение одного конкретного индекса, поэтому мы выбираем слово INDEX, а не TABLE или другое.
Затем мы используем слово CONCURRENTLY, чтобы не блокировать таблицу от возможных вставок, удалений или обновлений записей. Так, мы в спокойном режиме перестроим индекс. Да, это дольше чем с блокировкой, но не вызовет проблем.
Общая идея перестроения индекса такова, что существующий индекс удалится только после полного созданного нового индекса. То есть, если что-то пойдёт не так во время переиндекса, то система откатится в обычное состояние к старому индексу так как он ещё не будет удалён к этому времени.
Любому индексу нужно физическое хранение своей информации на диске. Любая активная база данных постоянно проводит множество преобразований данных в таблицах. То есть столбцы и записи постоянно меняются. Это значит, что в физических местах их хранения также происходят преобразования на диске.
Разработчики 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
При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…
-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…
Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…
Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…
У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…
Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…