Для чего использовать CONCURRENTLY при добавлении индекса

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

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

Как отвечать

Начните с блокировок PostgreSQL:

• обычный CREATE INDEX: берёт на таблицу блокировку SHARE. Чтение идёт, а INSERT, UPDATE и DELETE ждут до конца построения. На таблице в десятки миллионов строк это минуты простоя записи;
• CREATE INDEX CONCURRENTLY: берёт SHARE UPDATE EXCLUSIVE, которая запись не блокирует. Цена: таблица сканируется дважды, а команда ждёт завершения транзакций, начатых раньше, поэтому работает заметно дольше и сильнее грузит диск;
• транзакции: внутри транзакционного блока команду выполнить нельзя. Во Flyway её держат в отдельном скрипте (смешивать с обычными командами без настройки mixed не получится), в Liquibase у changeSet ставят runInTransaction="false";
• сбой: если построение прервалось, например из-за дубля в уникальном индексе или взаимоблокировки, остаётся индекс в состоянии INVALID. Запросы его не используют, но при записи он обновляется. Его удаляют через DROP INDEX CONCURRENTLY и строят заново или делают REINDEX INDEX CONCURRENTLY (есть с PostgreSQL 12).

Для уникальности на живой таблице есть приём: построить уникальный индекс с CONCURRENTLY, а затем превратить его в ограничение через ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX.

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

Говорят, что обычный CREATE INDEX блокирует таблицу целиком. Чтение он пропускает, останавливает только запись.

Не проверяют результат. Упавший CONCURRENTLY оставляет невалидный индекс, который замедляет запись и ничего не ускоряет. Найти такие можно по pg_index.indisvalid = false.

Запускают построение, когда в базе висит долгая транзакция, например забытая сессия в состоянии idle in transaction. Команда будет ждать её сколько угодно долго, поэтому перед запуском смотрят pg_stat_activity и ставят разумный lock_timeout.