Как обновлять индекс в большой таблице без блокировки и недоступности сервиса?

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

Знаете ли вы, какие блокировки берёт DDL в вашей СУБД и как провести миграцию на живой таблице. Для Java-разработчика следом почти всегда спрашивают, как сделать это через Flyway или Liquibase, которые по умолчанию выполняют миграцию в транзакции.

Как отвечать

Сначала уточните: индекс сам обновляется при каждой записи, вопрос обычно про создание нового, перестройку или замену существующего. Дальше по СУБД. В PostgreSQL:

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

В MySQL InnoDB вторичный индекс строится онлайн с ALGORITHM=INPLACE, LOCK=NONE, для тяжёлых изменений схемы берут gh-ost или pt-online-schema-change. В Oracle есть CREATE INDEX ... ONLINE.

Со стороны приложения: долгую миграцию не запускают на старте сервиса, иначе поды не пройдут readiness-проверку. Её выносят в отдельный job, а код выкатывают так, чтобы он работал и со старым, и с новым индексом.

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

Кладут CREATE INDEX CONCURRENTLY в миграцию, которая выполняется внутри транзакции, и получают cannot run inside a transaction block уже на проде.

Предлагают построить индекс на реплике и переключиться на неё. В физической репликации PostgreSQL реплика доступна только на чтение, создать там индекс нельзя.

Не проверяют результат. После сбоя индекс в INVALID висит незаметно: замедляет запись и не ускоряет ни одного запроса.