Когда добавление индекса «кладет» ваше приложение

Ваша команда неделями ищет причину медленного запроса. Таблица users разрослась до миллионов строк, и поиск по email теперь занимает секунды вместо миллисекунд. Кто-то предлагает добавить индекс. Исправление кажется простым: одна команда CREATE INDEX — и проблема решена.

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

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

Что происходит при создании индекса

Когда вы выполняете CREATE INDEX idx_users_email ON users(email), база данных должна прочитать каждую строку в таблице, построить сбалансированное дерево и записать его на диск. В процессе база обычно блокирует таблицу, чтобы изменения данных не повредили структуру индекса.

Тип блокировки зависит от СУБД. В PostgreSQL обычный CREATE INDEX захватывает SHARE LOCK. Чтение остается возможным, но любые операции записи — вставки, обновления, удаления — вынуждены ждать. В MySQL с InnoDB поведение блокировок варьируется в зависимости от типа операции и версии движка.

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

Конкурентный вариант, о котором многие забывают

Большинство крупных СУБД позволяют строить индексы без блокировки записи. В PostgreSQL для этого есть CREATE INDEX CONCURRENTLY. MySQL поддерживает ALGORITHM=INPLACE и LOCK=NONE на совместимых версиях.

-- PostgreSQL: строит индекс без блокировки записи
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL: строит индекс без блокировки записи (InnoDB)
ALTER TABLE users ADD INDEX idx_users_email (email), ALGORITHM=INPLACE, LOCK=NONE;

Конкурентный подход занимает больше времени. CREATE INDEX CONCURRENTLY в PostgreSQL должен дождаться завершения текущих транзакций, затем выполнить дополнительные проходы, чтобы учесть изменения, произошедшие во время построения. Но компромисс очевиден: ваше приложение продолжает работать, пока строится индекс.

Ошибка в том, чтобы считать конкурентное создание индекса опциональной или продвинутой возможностью. Это должно быть стандартом для любой продакшен-миграции на живой таблице. Прописывайте это явно в скриптах миграций. Не полагайтесь на поведение по умолчанию.

Ограничения тоже блокируют

Внешние ключи и ограничения уникальности приносят свои сюрпризы с блокировками. Когда вы добавляете внешний ключ, база проверяет, что все существующие строки удовлетворяют отношению. Эта проверка читает всю таблицу и блокирует ее на время процесса.

В PostgreSQL есть прагматичное решение: NOT VALID. Это добавляет ограничение только для новых данных, пропуская проверку существующих строк. Затем вы можете выполнить VALIDATE CONSTRAINT отдельно, в часы низкой нагрузки.

-- Добавление внешнего ключа без проверки существующих данных
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Проверка позже, когда трафик низкий
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

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

Планируйте миграции индексов отдельно

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

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

Практический подход:

  • Изменения структуры таблиц (добавление колонки, изменение типа) выполняйте в одной миграции
  • Изменения индексов и ограничений — в отдельных миграциях
  • Планируйте миграции индексов на окна низкого трафика
  • Явно используйте конкурентные опции в каждом скрипте миграции
  • Проверяйте ограничения отдельно от их добавления

Настоящий вопрос

Когда индексы и ограничения уже на месте, нужно убедиться, что они не ломают работающее приложение. Индекс, меняющий планы запросов, может вызвать неожиданные сдвиги производительности. Внешний ключ, блокирующий каскадное удаление, может остановить фоновые задачи.

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

Чек-лист для миграций индексов и ограничений

  • Используйте конкурентное создание индексов (CREATE INDEX CONCURRENTLY или аналог) на любой таблице с активной записью
  • Добавляйте внешние ключи с NOT VALID, когда это возможно, проверяйте отдельно
  • Выносите миграции индексов в отдельные файлы от изменений структуры таблиц
  • Планируйте построение больших индексов на окна низкого трафика
  • Мониторьте длительность блокировок и производительность запросов во время и после миграции
  • Тестируйте миграцию на копии продакшен-данных перед запуском на живой базе

Итог

Индекс — это не просто инструмент производительности. Это изменение схемы, которое может заблокировать таблицу, поставить в очередь ваши записи и вызвать таймауты у пользователей. Относитесь к нему с той же осторожностью, что и к любому другому деплою на проде. Используйте конкурентные опции, разделяйте миграции и планируйте с запасом безопасности. Скорость запроса, которую вы получите, ничего не стоит, если сама миграция сломает приложение.