Какие варианты накатывания индексов существуют без ухудшения пользовательского опыта

Что проверяют

Знаете ли вы, что обычный CREATE INDEX на большой таблице блокирует запись, и умеете ли выкатывать индекс на живую систему. Следом обычно спрашивают, как это сделать через Flyway или Liquibase и что будет, если создание упадёт посередине.

Как отвечать

Ответ зависит от СУБД, начните с той, что у вас в проекте.

PostgreSQL:

• CREATE INDEX CONCURRENTLY: не блокирует INSERT, UPDATE, DELETE, но идёт дольше, делает два прохода по таблице и ждёт завершения ранее начатых транзакций;
• вне транзакции: CONCURRENTLY нельзя выполнить в транзакционном блоке. В Liquibase у changeSet ставят runInTransaction="false", во Flyway такой оператор держат в отдельной миграции, смешивать его с транзакционными он по умолчанию не даёт;
• упавшее создание: оставляет индекс INVALID, он замедляет запись и не помогает чтению. Лечится DROP INDEX CONCURRENTLY и повтором;
• lock_timeout: чтобы операция не встала в очередь за долгой транзакцией и не собрала за собой остальные запросы;
• перестроение: REINDEX CONCURRENTLY есть с PostgreSQL 12, прогресс виден в pg_stat_progress_create_index.

MySQL (InnoDB): вторичный индекс добавляется как online DDL, ALGORITHM=INPLACE, LOCK=NONE явно требует не блокировать запись. Для тяжёлых изменений берут pt-online-schema-change или gh-ost.

Общие приёмы: часы низкой нагрузки, проверка на копии прод-данных, контроль отставания реплик, ведь построение индекса пишет много WAL или binlog. ddl-auto=update в Hibernate для прода не годится, индексы едут только миграциями.

Где спотыкаются

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

Повторяют упавшую миграцию с IF NOT EXISTS. Индекс INVALID уже существует, команда молча проходит, а план запроса не меняется.

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