Что происходит с запросами на вставку, обновление и чтение во время накатывания индекса

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

Понимаете ли вы, что построение индекса это не фоновая магия, а операция с блокировками. Для серверного разработчика это ещё и вопрос про миграции: как накатить индекс на живой базе и не уронить приложение по таймаутам.

Как отвечать

• В PostgreSQL обычный CREATE INDEX берёт на таблице разделяемую блокировку: чтение работает как обычно, а INSERT, UPDATE и DELETE ждут до конца построения. На большой таблице это минуты недоступной записи.
• CREATE INDEX CONCURRENTLY запись не блокирует, но платит за это: два прохода по таблице, ожидание завершения старых транзакций, запрет на запуск внутри блока транзакции. Если построение сорвалось, остаётся невалидный индекс, его нужно удалить и повторить попытку.
• В MySQL с InnoDB добавление вторичного индекса идёт онлайн: изменения разрешены и копятся в журнале, который применяется в конце. Короткая блокировка метаданных всё равно нужна на входе и выходе, и долгая транзакция способна её подвесить, собрав за собой очередь.
• Пока индекс строится, запись в любом случае дороже обычной: поддерживать надо и таблицу, и растущую структуру, а сортировка занимает память и место на диске.
• Со стороны приложения: DDL не в часы пика, lock_timeout с повторами, отдельное подключение под миграцию. Сам по себе инструмент миграций ничего не делает безопасным, его шаг просто ждёт блокировку.

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

Говорят, что блокируются чтения. В PostgreSQL обычный CREATE INDEX не мешает SELECT, страдает именно запись. Обратная ошибка встречается не реже: считают CONCURRENTLY бесплатным и запускают его на боевой базе в пик нагрузки.

Второе: приплетают уровень изоляции, будто READ COMMITTED разрешает читать во время построения. Уровень изоляции определяет видимость версий строк и к блокировкам на уровне объекта отношения не имеет.

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