Den här artikeln finns för närvarande på ryska — en engelsk översättning är på gång.
Обычный CREATE INDEX берёт блокировку, которая запрещает запись в таблицу на всё время сборки. На крошечной таблице это незаметно, но на горячей таблице с миллионами строк такой INSERT/UPDATE-фриз на минуты — это инцидент. CREATE INDEX CONCURRENTLY строит индекс, не блокируя запись, и это рабочая лошадка zero-downtime миграций.
Что блокирует обычный CREATE INDEX
Обычный CREATE INDEX берёт на таблице блокировку SHARE: чтение продолжается, а вот любая запись встаёт в очередь до конца сборки.
CREATE INDEX idx_orders_user_id ON orders (user_id);
На таблице orders в десятки миллионов строк сборка займёт минуты, и всё это время приложение не сможет вставлять заказы. Вариант с CONCURRENTLY снимает именно эту проблему:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
Postgres делает два прохода по таблице и ждёт «зависшие» транзакции, поэтому строится индекс медленнее и нагружает диск сильнее — но запись при этом не стоит. Синтаксис тот же, что и обычно, включая составные индексы:
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status);
Почему нельзя внутри транзакции
CREATE INDEX CONCURRENTLY нельзя запускать внутри блока транзакции. Команда сама управляет несколькими внутренними транзакциями и ждёт чужие, поэтому BEGIN ... COMMIT вокруг неё — ошибка:
BEGIN;
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
COMMIT;
Практический вывод для миграций: эту команду нужно гонять вне транзакции. Многие инструменты миграций по умолчанию оборачивают каждый шаг в транзакцию — для шага с CONCURRENTLY это нужно отключать (disable_ddl_transaction! в Rails, atomic = False в Django, отдельный non-transactional шаг в других).
- Один индекс — один отдельный шаг миграции.
- Не смешивайте
CONCURRENTLY с другим DDL в том же шаге.
- Закладывайте время: на больших таблицах это десятки минут.
INVALID-индекс после сбоя
Самая коварная часть. Если сборка с CONCURRENTLY падает (дубликаты для UNIQUE, отмена, обрыв соединения), индекс не исчезает — он остаётся в каталоге помеченным как INVALID. Планировщик его не использует, но запись он замедляет, занимает место и блокирует повторное создание под тем же именем.
Найти такие индексы:
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;
Полезно знать и про мониторинг прогресса живой сборки:
SELECT phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;
Чинится INVALID-индекс просто — его надо удалить и пересоздать:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
Грабли: не пытайтесь «починить» INVALID-индекс через REINDEX без оглядки. До Postgres 12 REINDEX обычного INVALID-индекса берёт тяжёлую блокировку; начиная с 12 есть REINDEX INDEX CONCURRENTLY, который как раз и предназначен для пересборки без простоя.
DROP INDEX CONCURRENTLY и идемпотентность
Удаление индекса тоже берёт блокировку: обычный DROP INDEX на горячей таблице заморозит и чтение, и запись на момент удаления. DROP INDEX CONCURRENTLY снимает блокировку так же, как и создание:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;
У него те же ограничения: нельзя в транзакции и нельзя удалить сразу несколько индексов одной командой. Зато IF EXISTS делает шаг идемпотентным — повторный прогон не упадёт.
Безопасный паттерн «пересоздать индекс без простоя» целиком:
DROP INDEX CONCURRENTLY IF EXISTS idx_employees_dept;
CREATE INDEX CONCURRENTLY idx_employees_dept ON employees (dept, salary);
Чем отличаются другие СУБД
- MySQL/InnoDB: понятия
CONCURRENTLY нет, но онлайн-DDL встроен — CREATE INDEX ... ALGORITHM=INPLACE, LOCK=NONE (или ALTER TABLE ... ADD INDEX) обычно строит индекс, разрешая запись. Движок сам решает, доступен ли LOCK=NONE, и упадёт с ошибкой, если нет.
- ClickHouse: вторичные skip-индексы добавляются через
ALTER TABLE ... ADD INDEX — операция лёгкая, но к уже существующим данным индекс применяется только после MATERIALIZE INDEX, который и делает фоновую работу.
Для PostgreSQL правило простое: на проде любую сборку и удаление индекса делайте через CONCURRENTLY, отдельным non-transactional шагом, и всегда проверяйте pg_index.indisvalid после миграции.
Ещё одно рабочее правило: сначала оцените длительность на копии или реплике и договоритесь об окне повышенной нагрузки. Конкурентная сборка не останавливает запись, но всё равно читает таблицу, пишет индекс и может заметно поднять latency.
Обычный
CREATE INDEXберёт блокировку, которая запрещает запись в таблицу на всё время сборки. На крошечной таблице это незаметно, но на горячей таблице с миллионами строк такойINSERT/UPDATE-фриз на минуты — это инцидент.CREATE INDEX CONCURRENTLYстроит индекс, не блокируя запись, и это рабочая лошадка zero-downtime миграций.Что блокирует обычный CREATE INDEX
Обычный
CREATE INDEXберёт на таблице блокировкуSHARE: чтение продолжается, а вот любая запись встаёт в очередь до конца сборки.-- locks out writes to orders until the index is built CREATE INDEX idx_orders_user_id ON orders (user_id);На таблице
ordersв десятки миллионов строк сборка займёт минуты, и всё это время приложение не сможет вставлять заказы. Вариант сCONCURRENTLYснимает именно эту проблему:CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);Postgres делает два прохода по таблице и ждёт «зависшие» транзакции, поэтому строится индекс медленнее и нагружает диск сильнее — но запись при этом не стоит. Синтаксис тот же, что и обычно, включая составные индексы:
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders (user_id, status);Почему нельзя внутри транзакции
CREATE INDEX CONCURRENTLYнельзя запускать внутри блока транзакции. Команда сама управляет несколькими внутренними транзакциями и ждёт чужие, поэтомуBEGIN ... COMMITвокруг неё — ошибка:BEGIN; CREATE INDEX CONCURRENTLY idx_users_email ON users (email); -- ERROR COMMIT;Практический вывод для миграций: эту команду нужно гонять вне транзакции. Многие инструменты миграций по умолчанию оборачивают каждый шаг в транзакцию — для шага с
CONCURRENTLYэто нужно отключать (disable_ddl_transaction!в Rails,atomic = Falseв Django, отдельный non-transactional шаг в других).CONCURRENTLYс другим DDL в том же шаге.INVALID-индекс после сбоя
Самая коварная часть. Если сборка с
CONCURRENTLYпадает (дубликаты дляUNIQUE, отмена, обрыв соединения), индекс не исчезает — он остаётся в каталоге помеченным какINVALID. Планировщик его не использует, но запись он замедляет, занимает место и блокирует повторное создание под тем же именем.Найти такие индексы:
SELECT indexrelid::regclass AS index_name FROM pg_index WHERE indisvalid = false;Полезно знать и про мониторинг прогресса живой сборки:
SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index;Чинится INVALID-индекс просто — его надо удалить и пересоздать:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id; CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);DROP INDEX CONCURRENTLY и идемпотентность
Удаление индекса тоже берёт блокировку: обычный
DROP INDEXна горячей таблице заморозит и чтение, и запись на момент удаления.DROP INDEX CONCURRENTLYснимает блокировку так же, как и создание:DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;У него те же ограничения: нельзя в транзакции и нельзя удалить сразу несколько индексов одной командой. Зато
IF EXISTSделает шаг идемпотентным — повторный прогон не упадёт.Безопасный паттерн «пересоздать индекс без простоя» целиком:
DROP INDEX CONCURRENTLY IF EXISTS idx_employees_dept; CREATE INDEX CONCURRENTLY idx_employees_dept ON employees (dept, salary);Чем отличаются другие СУБД
CONCURRENTLYнет, но онлайн-DDL встроен —CREATE INDEX ... ALGORITHM=INPLACE, LOCK=NONE(илиALTER TABLE ... ADD INDEX) обычно строит индекс, разрешая запись. Движок сам решает, доступен лиLOCK=NONE, и упадёт с ошибкой, если нет.ALTER TABLE ... ADD INDEX— операция лёгкая, но к уже существующим данным индекс применяется только послеMATERIALIZE INDEX, который и делает фоновую работу.Для PostgreSQL правило простое: на проде любую сборку и удаление индекса делайте через
CONCURRENTLY, отдельным non-transactional шагом, и всегда проверяйтеpg_index.indisvalidпосле миграции.Ещё одно рабочее правило: сначала оцените длительность на копии или реплике и договоритесь об окне повышенной нагрузки. Конкурентная сборка не останавливает запись, но всё равно читает таблицу, пишет индекс и может заметно поднять latency.