Когда на таблицу с миллионами строк и непрерывным потоком запросов нужно добавить индекс, а простой сервиса недопустим, на помощь приходит CREATE INDEX CONCURRENTLY. Но эта команда - не волшебная палочка. За «неблокирующую» работу приходится платить дополнительной нагрузкой и вниманием к деталям. Разбираемся, как строить индексы в production без блокировок и головной боли.
Почему обычный CREATE INDEX - это боль
Блокировка записи и как она убивает production
Обычный CREATE INDEX берёт блокировку на уровне таблицы, которая запрещает любые INSERT, UPDATE и DELETE до завершения операции. Если таблица активно пишется, все запросы на изменение повисают в ожидании. Приложение начинает тормозить, коннекты к базе копятся, и в итоге сервис может лечь. Для базы с десятками гигабайт построение индекса без CONCURRENTLY может занять десятки минут - всё это время запись будет заблокирована.
Типичный сценарий: миграция, которая положила сервис
Представьте: ночной деплой, миграция добавляет индекс на ключевую таблицу заказов. Обычный CREATE INDEX стартует, и на пике нагрузки запись замирает. Клиенты не могут оформить заказ, support-чаты разрываются, а дежурный инженер в спешке откатывает миграцию. Такой сценарий легко воспроизводится в любой высоконагруженной системе, если не использовать конкурентное построение.
Как работает CREATE INDEX CONCURRENTLY
Два сканирования таблицы вместо одного
Чтобы обойти блокировку записи, PostgreSQL делает два полных прохода по таблице. Первое сканирование строит индекс по данным, которые уже были в таблице на момент старта. Второй проход подхватывает все изменения, накопившиеся за время первого сканирования. Отсюда и главный компромисс: конкурентное построение всегда выполняется дольше обычного, потому что таблица читается дважды.
Ожидание завершения всех незавершённых транзакций
Перед вторым сканированием процесс ждёт, пока не завершатся все транзакции, которые могли видеть таблицу в старом состоянии. Если в системе висит долгая транзакция или забытая prepared transaction, построение индекса будет стоять и ждать, пока они не закончатся. Никакой ошибки не будет - просто операция затянется на неопределённое время.
Режим блокировки SHARE UPDATE EXCLUSIVE - что остаётся заблокированным
CREATE INDEX CONCURRENTLY удерживает блокировку уровня SHARE UPDATE EXCLUSIVE. Она не мешает обычным чтениям и изменениям данных (SELECT, INSERT, UPDATE, DELETE), но блокирует попытки выполнить параллельно другие DDL-операции на этой же таблице и не даёт запустить VACUUM (кроме обычного, который сам может отступить). Иными словами, два конкурентных построения индекса на одной таблице одновременно не запустятся - они встанут в очередь.
Параллелизм: только первое сканирование может быть параллельным
PostgreSQL умеет использовать несколько рабочих процессов для ускорения построения индекса, но в режиме CONCURRENTLY параллельным будет только первое сканирование. Второй проход всегда выполняется одним процессом. Это ещё одна причина, почему конкурентное создание требует больше времени.
Подводные камни и как их избежать
Invalid-индекс после сбоя: причины и что делать
Если во время конкурентного построения что-то пошло не так - например, сессию принудительно убили или возникла ошибка - индекс может остаться в состоянии invalid. Такой индекс висит в pg_index с indisvalid = false, не используется планировщиком, но продолжает потреблять ресурсы при любых изменениях данных. Единственный верный способ исправить ситуацию - удалить этот индекс и создать его заново (начиная с PostgreSQL 12 можно также использовать REINDEX CONCURRENTLY).
Зависание на долгих транзакциях и prepared transactions
Самый частый сюрприз в production - «индекс создаётся уже час, хотя данных всего 10 ГБ». Причина почти всегда в том, что какая-то транзакция открыта очень давно, и CREATE INDEX CONCURRENTLY ждёт её завершения. Особенно коварны забытые prepared transactions: они могут висеть днями и неделями, и без явного ROLLBACK PREPARED или COMMIT PREPARED индекс никогда не достроится. Перед запуском всегда проверяйте pg_stat_activity и pg_prepared_xacts.
Рост нагрузки на диск и CPU во время построения
Конкурентное построение не снижает требования к ресурсам - наоборот, из-за двух проходов и временного хранения изменений нагрузка на CPU и дисковую подсистему может быть даже выше, чем при обычном создании. Если таблица активно пишется, второй проход может читать заметный объём новых данных. Планируйте запуск на время с запасом по ресурсам и помните, что блокировки записи нет, но замедление общих операций вполне возможно.
Особенности работы с репликацией
С точки зрения репликации CREATE INDEX CONCURRENTLY - это обычный DDL. Все WAL-записи, необходимые для воспроизведения индекса, передаются на реплику. На стороне реплики индекс строится в неконкурентном режиме, но поскольку реплика обычно работает только на чтение, это не вызывает проблем. Главное - убедиться, что на реплике хватает ресурсов и она не отстанет по WAL-лагу на время построения индекса.
REINDEX CONCURRENTLY и немного истории
Появление CONCURRENTLY в PostgreSQL 8.2
Поддержка CREATE INDEX CONCURRENTLY появилась в версии 8.2. Это стало спасением для администраторов, которым нужно было добавлять индексы на горячие таблицы без остановки сервиса. До этого приходилось либо рисковать длительной блокировкой, либо строить индекс на копии таблицы и переименовывать объекты.
REINDEX CONCURRENTLY в PostgreSQL 12 - перестроение без боли
В PostgreSQL 12 аналогичный подход добавили для перестроения индексов: команда REINDEX CONCURRENTLY. Теперь можно не только создать новый индекс без блокировки записи, но и перестроить старый, например, чтобы избавиться от разбухания или поменять параметры хранения. Это особенно удобно в сочетании с обновлением версии базы данных, когда старые индексы нужно перестроить из-за изменения формата.
Когда CREATE, а когда REINDEX
Если индекса ещё нет - только CREATE INDEX CONCURRENTLY. Если индекс уже существует, но стал «раздутым» или нужно сменить tablespace, а блокировать запись нельзя - выбирайте REINDEX CONCURRENTLY. При этом помните, что REINDEX CONCURRENTLY требует, чтобы исходный индекс был валидным и usable.
Практические рецепты для production
Проверяем окружение перед запуском: долгие транзакции, prepared xacts
Перед тем как выполнить CREATE INDEX CONCURRENTLY, обязательно:
- Найдите все долгие транзакции:
SELECT pid, now() - xact_start AS age FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start < now() - interval '1 minute'; - Проверьте prepared transactions:
SELECT * FROM pg_prepared_xacts; - Убедитесь, что нет активных DDL-операций на этой же таблице (например, другой
CREATE INDEX CONCURRENTLY).
Если обнаружите висящие prepared transactions, договоритесь с их владельцами о закрытии или принудительно завершите их, если это безопасно.
Мониторим процесс и состояние индекса
Пока команда выполняется, полезно следить за её прогрессом через pg_stat_progress_create_index (с PostgreSQL 12). Также можно запросить состояние индекса сразу после завершения:
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'имя_таблицы'::regclass;
Если indisvalid = false, индекс не будет использоваться, и его нужно пересоздать.
Настройка maintenance_work_mem и других параметров
Увеличьте maintenance_work_mem перед запуском, чтобы ускорить сортировку при построении. Для производственного сервера с достаточным объёмом памяти можно установить значение в несколько гигабайт, но не стоит забирать всю свободную память - помните, что параллельные воркеры тоже могут потреблять ресурсы. Разумный ориентир - 10–25% от доступной оперативной памяти, но не более 1 ГБ на каждое выполняемое построение.
Что делать, если индекс всё равно стал invalid
Если по окончании команды вы видите invalid, не паникуйте. Удалите проблемный индекс через DROP INDEX CONCURRENTLY (PostgreSQL 9.2+) и создайте его заново. Начиная с 12 версии можно просто выполнить REINDEX INDEX CONCURRENTLY имя_индекса. Не оставляйте invalid-индекс надолго - он будет замедлять все операции изменения данных.
Антипаттерн: CONCURRENTLY на временных таблицах
Временные таблицы видны только в рамках одного сеанса, поэтому другие сессии и так не могут с ними работать. Использовать CONCURRENTLY для временных таблиц бессмысленно: вы получите только лишнее ожидание и два сканирования без какой-либо практической пользы. Обычный CREATE INDEX здесь полностью безопасен и работает быстрее.
FAQ: короткие ответы на частые вопросы
Блокирует ли CREATE INDEX CONCURRENTLY чтение?
Нет, SELECT и любые другие операции чтения продолжают работать без ограничений. Блокировка SHARE UPDATE EXCLUSIVE не конфликтует с ACCESS SHARE, который берут читающие запросы.
Можно ли отменить команду без последствий?
Да, можно прервать сессию через pg_cancel_backend или pg_terminate_backend. Однако в этом случае индекс, скорее всего, останется в состоянии invalid. Его потом придётся удалить или пересоздать.
Как узнать, что создание индекса действительно завершилось?
После завершения команды проверьте pg_index.indisvalid для созданного индекса. Если значение равно true, индекс готов к использованию. Также можно попробовать выполнить EXPLAIN на типичный запрос - планировщик должен начать его использовать.
Почему индекс висит в состоянии «не готов»?
Это и есть состояние invalid. Оно возникает, когда конкурентное построение не завершилось успешно. Индекс есть, но не готов к использованию, и его нужно перестроить.
Влияет ли CONCURRENTLY на выполнение других DDL-операций?
Да, SHARE UPDATE EXCLUSIVE блокирует попытки выполнить параллельно другие DDL-команды на этой же таблице (например, ALTER TABLE, DROP INDEX, ещё один CREATE INDEX CONCURRENTLY). Но обычные запросы на чтение и запись данных не блокируются.
Нужен ли CONCURRENTLY для временных таблиц?
Нет. Временные таблицы и так видны только в рамках одного сеанса, поэтому обычный CREATE INDEX для них не блокирует другие сессии. Использование CONCURRENTLY только замедлит процесс без всякой пользы.
Итог: строим индексы без страха
CREATE INDEX CONCURRENTLY - проверенный инструмент для обслуживания production-баз, когда нельзя допустить блокировку записи. Главное - помнить о его особенностях: два сканирования, ожидание завершения транзакций, вероятность появления invalid-индекса при сбоях. Проверяйте окружение перед запуском, мониторьте прогресс, не оставляйте invalid-индексы, и ваши миграции пройдут без происшествий. А с появлением REINDEX CONCURRENTLY в PostgreSQL 12 обслуживание индексов стало ещё безопаснее - теперь и перестраивать их можно без остановки сервиса.
.svg.webp){kind=logo}
