CREATE INDEX CONCURRENTLY

В прошлых пяти юнитах мы выбирали правильный индекс. Остался вопрос, который встаёт уже на проде: как этот индекс добавить к таблице, в которую прямо сейчас идёт запись. В Brew решили докинуть индекс на горячую таблицу заказов в обеденный пик — обычным CREATE INDEX. Команда взяла на таблице блокировку SHARE, и все INSERT/UPDATE/DELETE встали в очередь до конца сборки. Касса не принимала заказы, пока строился индекс. Само построение было «правильным» — но способ оказался разрушительным.

Цель юнита — CREATE INDEX CONCURRENTLY: как построить индекс, не блокируя запись в таблицу на время сборки, и какой ценой это даётся. Это закрывающий юнит модуля про индексы — здесь не про «какой индекс», а про «как его выкатить безопасно».

Блокировки обычного CREATE INDEX и CONCURRENTLY

Обычный CREATE INDEX берёт на таблице блокировку SHARE. Она пропускает читателей, но конфликтует с записью: любой INSERT/UPDATE/DELETE ждёт, пока индекс достроится. На маленькой таблице это миллисекунды и никто не замечает; на таблице в миллионы строк сборка идёт секунды-минуты, и всё это время запись стоит.

CREATE INDEX CONCURRENTLY берёт более слабую блокировку — SHARE UPDATE EXCLUSIVE, которая не конфликтует с записью. Постгрес строит индекс в два прохода по таблице, дожидаясь между ними завершения старых транзакций, и всё это время INSERT/UPDATE/DELETE идут как обычно. Именно так индекс добавляют к живой таблице, не останавливая приложение.

Цена CONCURRENTLY

Слабая блокировка не бесплатна — у CONCURRENTLY свои правила:

Нельзя внутри транзакции. CREATE INDEX CONCURRENTLY управляет своими транзакциями сам (два прохода + ожидание), поэтому в явном BEGIN ... COMMIT он падает с ошибкой SQLSTATE 25001 («cannot run inside a transaction block»). Обычный CREATE INDEX — транзакционный, его можно положить в миграцию рядом с другими DDL; CONCURRENTLY — нет.
Дольше и тяжелее. Два прохода по таблице вместо одного — суммарно медленнее и больше работы, чем у обычного построения. Мы меняем общее время на отсутствие блокировки.
Может оставить битый индекс. Если конкурентная сборка сорвётся (конфликт, отмена, нарушение уникальности на втором проходе), в каталоге останется невалидный индекс с indisvalid = false. Он не используется планировщиком и его нельзя «достроить» — только DROP INDEX (тоже лучше CONCURRENTLY) и пересоздать.

⚠️ Битые индексы надо уметь находить. Запрос SELECT … FROM pg_index WHERE NOT indisvalid показывает все невалидные индексы — это первое, что смотрят, если CONCURRENTLY где-то сорвался. В норме их ноль.

Это та же очередь блокировок, что в 02-06 клала кассу на горячем ALTER — только теперь очередь создаёт построение индекса. Разница между двумя командами — ровно в том, конфликтует ли взятая блокировка с записью:

plaintext

Обычный CREATE INDEX — берёт SHARE (конфликтует с записью):
 
  строит индекс:  [=========================]→ готово
  запись:         [····· ждёт в очереди ·····]→ идёт только после сборки
                   └─ касса стоит всё время построения ─┘
 
CREATE INDEX CONCURRENTLY — берёт SHARE UPDATE EXCLUSIVE (не конфликтует):
 
  строит индекс:  [ проход 1 ]→[ ждёт старые tx ]→[ проход 2 ]→ готово
  запись:         [=== идёт как обычно, без очереди ===========]→
                   └─ касса принимает заказы всё время построения ─┘

	`CREATE INDEX`	`CREATE INDEX CONCURRENTLY`
Блокировка	`SHARE` — запись стоит	`SHARE UPDATE EXCLUSIVE` — запись идёт
В транзакции	можно (транзакционный)	нельзя → `SQLSTATE 25001`
Проходов по таблице	один	два + ожидание старых транзакций
Если сорвётся	откат, индекса нет	остаётся невалидный (`indisvalid = false`)
Когда брать	маленькая/холодная таблица, DDL в миграции	горячая таблица в проде

Что показывает наш код

demo.sql (цель run) детерминированно проверяет правила CONCURRENTLY на лабораторном столе cic_lab:

sql

-- 1) обычный CREATE INDEX — можно внутри транзакции
BEGIN; CREATE INDEX cic_lab_plain_idx ON cic_lab (payload); COMMIT;
-- 2) CONCURRENTLY внутри транзакции → ошибка 25001
BEGIN; CREATE INDEX CONCURRENTLY cic_lab_conc_idx ON cic_lab (payload); ROLLBACK;
-- 3) CONCURRENTLY вне транзакции → успех, indisvalid = t
CREATE INDEX CONCURRENTLY cic_lab_conc_idx ON cic_lab (payload);
-- 4) поиск битых индексов → 0
SELECT count(*) FROM pg_index WHERE NOT indisvalid;

А живую незаблокированность записи показывают session-a.sql / session-b.sql: сессия A строит CONCURRENTLY-индекс на таблице в 3 млн строк, а сессия B в это время делает INSERT — и он проходит сразу, не дожидаясь конца сборки (с обычным CREATE INDEX тот же INSERT встал бы в очередь).

Запуск

Детерминированное демо правил:

docker compose up -d
make lecture L=06-indexing-and-explain/06-06-create-index-concurrently

Вывод (stdout; текст ошибки шага 2 уходит в stderr):

plaintext

== 1) обычный CREATE INDEX можно внутри транзакции (он транзакционный) ==
                  result                   
-------------------------------------------
 обычный индекс собран внутри BEGIN/COMMIT
 
 
== 2) CREATE INDEX CONCURRENTLY ВНУТРИ транзакции запрещён (ошибка в stderr) ==
SQLSTATE = 25001 (cannot run inside a transaction block)
 
== 3) CREATE INDEX CONCURRENTLY ВНЕ транзакции — успех, индекс валиден ==
      index       | indisvalid 
------------------+------------
 cic_lab_conc_idx | t
 
 
== 4) проверка на битые индексы (сорванный CONCURRENTLY оставляет indisvalid=false) ==
 invalid_indexes 
-----------------
               0

Обычный CREATE INDEX спокойно прошёл внутри BEGIN/COMMIT; CONCURRENTLY там же упал с 25001; вне транзакции он построился и индекс валиден (indisvalid = t); битых индексов нет.

Живой сценарий двух сессий (интерактивный):

# терминал 1:
make lecture L=06-indexing-and-explain/06-06-create-index-concurrently T=session-a
# затем БЫСТРО в терминале 2, пока A строит индекс:
make lecture L=06-indexing-and-explain/06-06-create-index-concurrently T=session-b
# в конце:
make lecture L=06-indexing-and-explain/06-06-create-index-concurrently T=db-reset

Сессия B вставляет строку и получает INSERT 0 1 во время сборки индекса в A — запись не заблокирована. (Порядок зависит от тайминга: на быстрой машине сборка 3 млн строк занимает ~секунду, поэтому в терминал 2 надо переключиться сразу — это known caveat двусессионных демо, как и в 05-03.)

Заборчик

CONCURRENTLY снимает главную боль — блокировку записи, — но безопасная выкатка индекса в проде на этом не заканчивается, и дальше начинается территория твоего DBA/релиз-инженера:

CONCURRENTLY не мгновенен. На старте он всё же берёт короткую блокировку и ждёт завершения всех текущих транзакций по таблице — одна зависшая долгая транзакция отложит начало сборки, поэтому миграции пускают с lock_timeout и ретраями.
Нельзя в транзакции → нужен особый шаг миграции. Раз CONCURRENTLY не положить в общий транзакционный блок, миграционные инструменты должны уметь гонять такие шаги отдельно (многие фреймворки требуют явной пометки).
После сорванной сборки нужно прибрать невалидный индекс (DROP INDEX CONCURRENTLY + пересоздать) — это операционная процедура.
У CONCURRENTLY есть родственники для других операций без простоя (REINDEX CONCURRENTLY для раздутого индекса, DROP INDEX CONCURRENTLY) — их выбор и расписание относятся к сопровождению кластера.

Граница курса: твоя задача — знать, что индекс на горячую таблицу добавляют через CONCURRENTLY, а не обычным CREATE INDEX, и помечать такие миграции соответствующе; оркестрация zero-downtime-выкаток — за её пределами.

Что забрать с собой

Обычный CREATE INDEX берёт SHARE-блокировку и останавливает запись в таблицу на время сборки — на горячей таблице это простой.
CREATE INDEX CONCURRENTLY строит индекс слабой блокировкой (SHARE UPDATE EXCLUSIVE), не мешая INSERT/UPDATE/DELETE.
Цена: нельзя внутри транзакции (SQLSTATE 25001), дольше (два прохода), сорванная сборка оставляет невалидный индекс (indisvalid = false).
Битые индексы ищут запросом … WHERE NOT indisvalid; чинят через DROP INDEX CONCURRENTLY + пересоздание.
CONCURRENTLY не мгновенен: ждёт текущих транзакций на старте → в проде с lock_timeout.

На этом модуль 06 «Индексы и производительность через EXPLAIN» закрыт: чтение EXPLAIN ANALYZE (с buffers, по умолчанию в PG18), порядок столбцов в составном индексе и skip-scan, non-sargable условия и индекс по выражению, частичные/покрывающие/уникальные индексы, GIN для jsonb/массивов и безопасная выкатка через CONCURRENTLY. Дальше — модуль 07 «JSONB, массивы и поиск в БД»: доступ и containment в jsonb, когда jsonb не нужен, SQL/JSON path, массивы против junction-таблицы, полнотекстовый поиск и нечёткий поиск через pg_trgm.