Как правильно накатывать индекс на существующую таблицу

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

Понимаете ли вы, что CREATE INDEX на живой таблице с миллионами строк это не мгновенная операция, а блокировка, которая может положить продакшен. Следом обычно спрашивают, как вы проведёте такое изменение через миграции EF Core.

Как отвечать

Обычное создание индекса блокирует запись на всё время построения. Способы этого избежать зависят от базы:

• SQL Server: CREATE INDEX ... WITH (ONLINE = ON) строит индекс, не останавливая чтение и запись, но доступно это только в Enterprise, Developer и Azure SQL. В начале и в конце операция всё равно берёт короткую блокировку на таблицу, и её может задержать долгая транзакция. RESUMABLE = ON позволяет поставить построение на паузу и продолжить;
• PostgreSQL: CREATE INDEX CONCURRENTLY не блокирует запись, но идёт дольше, дважды сканирует таблицу и не работает внутри транзакции. Если построение упало, остаётся индекс в состоянии INVALID: его удаляют и создают заново;
• EF Core: миграции по умолчанию идут в транзакции, поэтому для CONCURRENTLY пишут migrationBuilder.Sql(..., suppressTransaction: true) или используют IsCreatedConcurrently() у провайдера Npgsql. Для SQL Server есть IsCreatedOnline().

Порядок действий: проверьте по плану запроса, что индекс будет использоваться, прогоните создание на копии продовых данных и оцените время. Для уникального индекса заранее найдите дубли, иначе операция упадёт в конце. Катите в окно низкой нагрузки с lock_timeout в PostgreSQL или SET LOCK_TIMEOUT в SQL Server, чтобы миграция не встала в очередь за чужой блокировкой и не остановила остальные запросы.

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

Пишут обычный CREATE INDEX в миграции, которая применяется автоматически при старте приложения. На большой таблице это минуты без записи и таймауты у пользователей.

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

Механически запускают UPDATE STATISTICS после создания. В SQL Server статистика по новому индексу строится при его создании по всем строкам. В PostgreSQL ANALYZE нужен после индекса по выражению: статистики по самому выражению до этого у планировщика нет.