Почему CREATE INDEX CONCURRENTLY ждёт

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

— Макс, объясни. Я же делаю CREATE INDEX CONCURRENTLY, он не должен никого блокировать… а он висит уже второй час для маленькой таблицы!

Макс даже не повернул голову от монитора:

— Так он и не блокирует “как обычный” CREATE INDEX. В твоем случае он скорее всего ждет завершения какой-то транзакции. CONCURRENTLY — это не “вне законов физики”. Он строит индекс в несколько фаз и в ключевых точках обязан дождаться транзакций или снапшотов, которые ещё могут видеть старую картину данных. Поэтому какой-нибудь долгий SELECT (особенно в явной транзакции) легко превращается в шлагбаум для создания индекса.

Вася прищурился:

— Подожди… SELECT же не держит блокировку на таблицу!

— Не держит. Он просто держит твою надежду на быстрый релиз… Это не “жёсткая” блокировка таблицы, а ожидание, пока закончатся старые транзакции. В CONCURRENTLY это нормальный механизм корректности: база должна убедиться, что индекс не пропустит строки, которые кто-то ещё может видеть/менять в “старом мире”.

Макс вздохнул и сначала посмотрел фазу операции и текущую ожидаемую сессию:

SELECT
  progress.pid AS index_pid,
  progress.phase,
  progress.lockers_total,
  progress.lockers_done,
  progress.current_locker_pid,
  blocker.state AS blocker_state,
  now() - blocker.xact_start AS blocker_xact_age,
  blocker.application_name,
  blocker.query
FROM pg_stat_progress_create_index AS progress
LEFT JOIN pg_stat_activity AS blocker
  ON blocker.pid = progress.current_locker_pid
WHERE progress.command = 'CREATE INDEX CONCURRENTLY';

pg_blocking_pids() полезна для обычных конфликтов блокировок, но фазы ожидания старых транзакций и снимков удобнее начинать с pg_stat_progress_create_index. Не каждая фаза заполняет current_locker_pid, поэтому затем нужно сопоставить phase, возраст транзакций и wait_event самой индексирующей сессии.

Вася посмотрел на вывод, и выражение лица стало ещё грустнее:

— Блокировщик… это же мой SELECT из отчета, который я запустил с утра для проверки.

— Поздравляю, — усмехнулся Макс. — Ты стал жертвой самого сложного врага — себя.

Вася нахмурился:

— И что мне теперь делать? Убить сессию с CREATE INDEX?

— Сначала выясни, можно ли безопасно завершить отчёт и его транзакцию, — Макс откинулся в кресле. — Одного окончания SQL-оператора может быть недостаточно, если приложение оставит явную транзакцию открытой. Не убивай чужие сессии без владельца и оценки отката.

— Но это же неудобно! — возмутился Вася. — Я блокирую создание индекса! Может, лучше прибью сессию с индексом, а потом заново запущу?

Макс покачал головой:

— Вот этого как раз делать не стоит. Если оборвёшь CREATE INDEX CONCURRENTLY, postgres оставит индекс в списке, но пометит его как INVALID — неполный и ненадёжный. Такой индекс не будет использоваться при выполнении запросов, но продолжит занимать место на диске и создавать накладные расходы при вставках и обновлениях.

— То есть получится “мёртвый груз”? — уточнил Вася.

— Именно. После проверки pg_index.indisvalid такой индекс придётся либо удалить через DROP INDEX CONCURRENTLY, либо перестроить подходящим способом. Гораздо проще заранее контролировать долгие транзакции и дать операции завершиться, если ожидание приемлемо.

Вася задумался:

— Понял. А как этого можно избежать в будущем?

Макс пожал плечами:

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

Вася вздохнул:

— То есть CONCURRENTLY — это не волшебная кнопка “без последствий”.

— Ага, — усмехнулся Макс. — CONCURRENTLY — это как ремонт дороги без полного перекрытия движения. Машины едут, но приходится ждать, пока проедет встречная колонна.