Сегодня расскажу про опцию CONCURRENTLY при построении индекса.
Идея проста. Обычный CREATE INDEX блокирует изменения в таблице, возможно только чтение данных. Пока индекс строится, запросы на запись подвисают. Блокировка может длиться от нескольких минут до часа. Вряд ли пользователям это понравится.
Опция CONCURRENTLY решает эту проблему и не блокирует обновления, с таблицей можно продолжать работать. Выглядит опция так:
CREATE INDEX CONCURRENTLY idx ON t(f);
Если всё так круто, почему опция не выбрана по умолчанию?
Потому что появляются новые проблемы.
Обычный индекс один раз проходит по таблице, которая не меняется, поэтому успех неизбежен.
Если индекс строится во время активной работы с базой — привет гонки, параллельные транзакции и вся классика многопоточных проблем. В итоге
▫️ Индекс строится гораздо дольше. Скан таблицы происходит 2 раза, индекс постоянно ждёт завершения транзакций и борется с внутренними противоречиями
▫️ Индекс может не получиться и остаться в статусе invalid. В таких случаях надо снести неполучившийся индекс и начать построение заново. Возможно не один раз💔
Второй важный момент, который касается неблокирующих индексов — партиционированные таблицы.
Если добавить обычный индекс для "основной" таблицы, для текущих и будущих(!) партиций индекс создаётся автоматически. Очень удобно
Но для CONCURRENTLY индекса такая схема не работает. Чтобы создать неблокирующие индексы для партиций придётся делать так:
▫️ Создать индекс на "основную" таблицу
▫️ Создать неблокирующий индекс для каждой партиции
▫️ Присоединить эти индексы к "основному"
Примерно так:
CREATE INDEX idx ON ONLY t(f);
CREATE INDEX CONCURRENTLY p1_idx ON p1_t(f);
ALTER INDEX idx ATTACH PARTITION p1_idx;
В общем, при создании индекса для большой таблицы придётся делать выбор:
🪑 CREATE INDEX и заблокировать работу с таблицей на десятки минут
🪑 CREATE INDEX CONCURRENTLY и выполнить кучу дополнительной работы, но меньше затронуть пользователя
Универсального решения нет, выбираем стул в зависимости от ситуации:)