CREATE INDEX CONCURRENTLY: как создать индекс в PostgreSQL без остановки записей

Индексы сильно ускоряют запросы. Но на живом проекте у них есть неприятная сторона: индекс нужно не только придумать, но ещё и безопасно создать.

На маленькой таблице всё просто:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

Команда выполнилась за долю секунды — и все довольны.

Но если таблица большая, например orders на десятки миллионов строк, обычное создание индекса может стать проблемой. PostgreSQL будет строить индекс, а в это время запись в таблицу окажется заблокирована.

То есть приложение может продолжать читать данные, но INSERT, UPDATE и DELETE будут ждать.

Для горячей таблицы это уже не «просто миграция», а потенциальный инцидент: пользователи оформляют заказы, приложение пытается писать в базу, а база отвечает: «подождите, я тут индекс строю».

Чтобы этого избежать, в PostgreSQL есть специальный вариант:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

Он создаёт индекс так, чтобы не останавливать запись в таблицу.

Разберёмся, как это работает, чем отличается от обычного CREATE INDEX и какие грабли обязательно нужно знать.

Зачем вообще нужен CREATE INDEX CONCURRENTLY

Представим интернет-магазин.

Есть таблица заказов orders. В ней миллионы строк. Приложение часто ищет заказы конкретного пользователя:

SELECT *
FROM orders
WHERE user_id = 42;

Если индекса по user_id нет, PostgreSQL может читать много лишних строк. Поэтому мы хотим добавить индекс:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

С точки зрения SQL всё выглядит правильно.

Но на проде у такого запроса есть важный побочный эффект: пока PostgreSQL строит обычный индекс, он берёт блокировку, которая мешает изменять таблицу.

Проще говоря:

• SELECT обычно продолжит работать;
• INSERT, UPDATE, DELETE будут ждать окончания создания индекса.

Если таблица маленькая, вы можете даже не заметить эту блокировку.

Но если таблица большая и активно используется, несколько минут ожидания для записи могут превратиться в очередь запросов, рост latency, ошибки на уровне приложения и злые сообщения в чатах.

Поэтому на живых таблицах в PostgreSQL часто используют:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

Слово CONCURRENTLY здесь означает:

«Создай индекс конкурентно, не запрещая другим транзакциям писать в таблицу».

Чем обычный CREATE INDEX отличается от CREATE INDEX CONCURRENTLY

Обычный вариант:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

быстрее и проще для самой базы, но на время построения он блокирует запись в таблицу.

Конкурентный вариант:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

строится осторожнее.

PostgreSQL не может просто один раз пробежать по таблице и сказать: «готово». Пока индекс строится, в таблицу продолжают прилетать новые строки, обновления и удаления. База должна учесть и старые данные, и те изменения, которые произошли во время создания индекса.

Поэтому CREATE INDEX CONCURRENTLY обычно работает дольше, чем обычный CREATE INDEX.

Зато главное преимущество такое:

приложение может продолжать писать в таблицу во время создания индекса.

Это классический компромисс:

Вариант	Быстрее строится	Не блокирует запись
CREATE INDEX	да	нет
CREATE INDEX CONCURRENTLY	медленнее	да

Для локальной разработки или маленьких служебных таблиц обычный CREATE INDEX часто нормален.

Для продовой таблицы, куда постоянно идут записи, безопаснее использовать CREATE INDEX CONCURRENTLY.

Простой пример

Было:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

Стало безопаснее для продакшена:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

Составной индекс создаётся точно так же:

CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders(user_id, status);

Индекс по выражению тоже можно создавать конкурентно:

CREATE INDEX CONCURRENTLY idx_users_lower_email
ON users(LOWER(email));

Уникальный индекс — тоже:

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users(email);

Но с уникальными индексами нужно быть особенно внимательным: если в таблице уже есть дубликаты, создание такого индекса упадёт.

Почему CREATE INDEX CONCURRENTLY нельзя запускать внутри транзакции

Одна из самых частых ошибок:

BEGIN;

CREATE INDEX CONCURRENTLY idx_users_email
ON users(email);

COMMIT;

Так нельзя.

PostgreSQL вернёт ошибку, потому что CREATE INDEX CONCURRENTLY нельзя выполнять внутри явного блока транзакции.

Почему?

Потому что эта команда сама внутри себя проходит несколько этапов и управляет транзакциями особым образом. Ей нужно:

• начать создание индекса;
• пройти по таблице;
• дождаться транзакций, которые могли видеть старое состояние данных;
• проверить изменения, которые произошли во время построения;
• корректно завершить создание индекса.

Если обернуть всё это в ваш BEGIN ... COMMIT, PostgreSQL не сможет выполнить команду правильно.

Поэтому правило простое:

CREATE INDEX CONCURRENTLY запускается отдельной командой, вне явной транзакции.

Важный момент для миграций

Многие инструменты миграций по умолчанию оборачивают миграцию в транзакцию.

Это удобно для обычных изменений: если что-то пошло не так, база откатила всё назад.

Но для CREATE INDEX CONCURRENTLY такое поведение мешает.

Поэтому миграцию с конкурентным созданием индекса обычно нужно делать как отдельный non-transactional шаг.

Например:

• в Rails используют disable_ddl_transaction!;
• в Django ставят atomic = False;
• в других инструментах миграций ищут настройку, которая отключает транзакцию для конкретного шага.

Плохая идея — смешать всё в одной миграции:

ALTER TABLE users ADD COLUMN last_login_at timestamptz;

CREATE INDEX CONCURRENTLY idx_users_last_login_at
ON users(last_login_at);

ALTER TABLE users ADD COLUMN source text;

Лучше вынести индекс отдельно:

CREATE INDEX CONCURRENTLY idx_users_last_login_at
ON users(last_login_at);

Хорошее правило:

один конкурентный индекс — один отдельный шаг миграции.

Так проще запускать, проще откатывать и проще понимать, что именно пошло не так.

Что происходит, если создание индекса упало

У CREATE INDEX CONCURRENTLY есть один неприятный нюанс.

Если обычный CREATE INDEX упал, он обычно просто откатился — и всё.

А вот если падает CREATE INDEX CONCURRENTLY, индекс может остаться в базе в состоянии INVALID.

Например, такое может произойти, если:

• вы создавали UNIQUE-индекс, но в таблице нашлись дубликаты;
• команду отменили вручную;
• соединение оборвалось;
• база столкнулась с ошибкой во время построения;
• миграцию прервали по таймауту.

Индекс при этом может остаться в системном каталоге PostgreSQL, но будет помечен как невалидный.

Что это значит?

Такой индекс:

• не используется планировщиком для ускорения запросов;
• занимает место на диске;
• может добавлять накладные расходы при изменении таблицы;
• мешает создать новый индекс с таким же именем.

То есть он вроде бы есть, но пользы от него нет.

Как найти INVALID-индексы

Проверить невалидные индексы можно так:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;

Если запрос вернул строки, значит в базе есть индексы, которые PostgreSQL не считает валидными.

Для более подробного вывода можно добавить таблицу:

SELECT
    i.indexrelid::regclass AS index_name,
    i.indrelid::regclass AS table_name
FROM pg_index i
WHERE i.indisvalid = false;

Так сразу видно, к какой таблице относится проблемный индекс.

После миграций с CREATE INDEX CONCURRENTLY такую проверку полезно выполнять отдельно, особенно если миграция шла долго или завершалась с ошибками.

Как починить 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 CONCURRENTLY IF EXISTS idx_orders_user_id;

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

IF EXISTS делает команду удобнее для повторного запуска. Если индекса уже нет, команда не упадёт.

DROP INDEX CONCURRENTLY: удаление индекса без лишней боли

Создать индекс безопасно — это только половина дела.

Иногда индекс нужно удалить:

• он больше не используется;
• он дублирует другой индекс;
• он создан не на те колонки;
• он замедляет запись;
• он занимает слишком много места.

Обычное удаление:

DROP INDEX idx_orders_user_status;

может взять нежелательные блокировки.

Поэтому на проде часто используют:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

У DROP INDEX CONCURRENTLY есть ограничения:

• его тоже нельзя запускать внутри транзакции;
• одной командой нельзя удалить сразу несколько индексов;
• он должен выполняться отдельным шагом миграции.

То есть вот так лучше не делать:

BEGIN;

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

COMMIT;

Правильно — отдельной командой:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

Как безопасно пересоздать индекс

Допустим, у нас был индекс:

CREATE INDEX idx_employees_dept
ON employees(dept);

А потом мы поняли, что чаще ищем сотрудников по отделу и зарплате:

SELECT *
FROM employees
WHERE dept = 'QA'
ORDER BY salary DESC;

И хотим заменить индекс на составной:

CREATE INDEX idx_employees_dept_salary
ON employees(dept, salary);

Безопасный продовый вариант может выглядеть так:

CREATE INDEX CONCURRENTLY idx_employees_dept_salary
ON employees(dept, salary);

После этого нужно убедиться, что новый индекс действительно используется, и только потом удалить старый:

DROP INDEX CONCURRENTLY IF EXISTS idx_employees_dept;

То есть порядок такой:

1. Сначала создаём новый индекс.
2. Проверяем, что он валиден.
3. Проверяем, что запросы могут его использовать.
4. Удаляем старый индекс.

Не всегда стоит сначала удалять старый индекс. Если старый индекс ещё нужен приложению, вы можете временно ухудшить производительность запросов.

Как посмотреть прогресс создания индекса

Когда индекс строится долго, хочется понять: он вообще работает или завис?

В PostgreSQL есть представление pg_stat_progress_create_index.

Пример запроса:

SELECT
    phase,
    blocks_done,
    blocks_total
FROM pg_stat_progress_create_index;

Он покажет текущую фазу создания индекса и прогресс по блокам.

Более удобный вариант с процентом:

SELECT
    phase,
    blocks_done,
    blocks_total,
    ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 2) AS progress_percent
FROM pg_stat_progress_create_index;

Если индекс большой, такой запрос помогает понять, что база действительно работает, а не просто «молчит».

CREATE INDEX CONCURRENTLY не бесплатный

Важно не впасть в другую крайность.

CREATE INDEX CONCURRENTLY не блокирует запись так жёстко, как обычный CREATE INDEX, но это не значит, что его можно бездумно запускать в любое время.

Во время создания индекса PostgreSQL всё равно:

• читает большую таблицу;
• пишет новый индекс на диск;
• создаёт дополнительную нагрузку на CPU и I/O;
• может увеличивать нагрузку на репликацию;
• может повышать latency запросов.

То есть пользователи смогут продолжать работать, но база может стать заметно более загруженной.

Поэтому для больших таблиц лучше:

• запускать создание индекса в период низкой нагрузки;
• заранее оценивать время на копии, staging или реплике;
• следить за нагрузкой на диск и CPU;
• мониторить прогресс;
• после завершения проверять, что индекс валиден.

CONCURRENTLY — это не магическая кнопка «сделать бесплатно». Это способ избежать простоя записи ценой более долгой и аккуратной операции.

Что насчёт UNIQUE-индексов

CREATE UNIQUE INDEX CONCURRENTLY тоже существует:

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users(email);

Но перед созданием уникального индекса лучше заранее проверить данные.

Например, если хотим сделать email уникальным:

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Если запрос вернул строки, значит в таблице есть дубликаты. Уникальный индекс создать не получится, пока вы не решите, что с ними делать.

Для nullable-колонок тоже нужно понимать желаемое поведение. В PostgreSQL обычный уникальный индекс позволяет несколько строк с NULL, потому что NULL считается неизвестным значением, а не обычным равным значением.

Например:

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users(email);

не запрещает несколько строк, где email IS NULL.

Если вам нужна особая логика для NULL, это нужно проектировать отдельно.

Частые ошибки

Ошибка 1. Запустить CONCURRENTLY внутри транзакции

Плохо:

BEGIN;

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

COMMIT;

Правильно:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

Ошибка 2. Создать индекс и не проверить, что он валиден

После долгой миграции полезно выполнить:

SELECT
    i.indexrelid::regclass AS index_name,
    i.indrelid::regclass AS table_name
FROM pg_index i
WHERE i.indisvalid = false;

Если индекс невалидный, его нужно удалить и пересоздать.

Ошибка 3. Создавать индекс на проде в час пик

Даже CONCURRENTLY создаёт нагрузку.

Лучше не запускать тяжёлые индексы в момент, когда у вас максимальное количество пользователей, заказов, оплат или фоновых задач.

Ошибка 4. Сразу удалять старый индекс перед созданием нового

Плохой порядок:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;

CREATE INDEX CONCURRENTLY idx_orders_user_id_status
ON orders(user_id, status);

Иногда так можно, но часто безопаснее сначала создать новый индекс, проверить его, а потом удалить старый.

Лучше:

CREATE INDEX CONCURRENTLY idx_orders_user_id_status
ON orders(user_id, status);

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;

Как это отличается от MySQL и ClickHouse

В PostgreSQL для безопасного создания индекса на живой таблице используется явное слово CONCURRENTLY.

В MySQL/InnoDB подход другой. Там онлайн-DDL встроен в механизм ALTER TABLE. Часто индекс добавляют так:

ALTER TABLE orders
ADD INDEX idx_orders_user_id(user_id),
ALGORITHM=INPLACE,
LOCK=NONE;

Но важно понимать: MySQL сам решает, может ли выполнить конкретную операцию без блокировок. Если выбранный режим невозможен, команда может завершиться ошибкой или потребовать другой алгоритм.

В ClickHouse вторичные skip-индексы добавляют через ALTER TABLE ... ADD INDEX. Но для уже существующих данных часто нужен отдельный шаг материализации:

ALTER TABLE events
ADD INDEX idx_user_id user_id TYPE minmax GRANULARITY 4;

А потом:

ALTER TABLE events
MATERIALIZE INDEX idx_user_id;

То есть в разных СУБД идея похожая — добавить индекс без боли для работающего приложения, — но синтаксис и поведение отличаются.

Для PostgreSQL главное слово, которое нужно запомнить, — CONCURRENTLY.

Практическая шпаргалка

Создать обычный индекс на продовой таблице безопаснее так:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

Создать составной индекс:

CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders(user_id, status);

Создать уникальный индекс:

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users(email);

Удалить индекс:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

Посмотреть прогресс создания:

SELECT
    phase,
    blocks_done,
    blocks_total,
    ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 2) AS progress_percent
FROM pg_stat_progress_create_index;

Найти невалидные индексы:

SELECT
    i.indexrelid::regclass AS index_name,
    i.indrelid::regclass AS table_name
FROM pg_index i
WHERE i.indisvalid = false;

Удалить и пересоздать проблемный индекс:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);

Главное правило

На локальной базе или маленькой таблице обычный CREATE INDEX обычно не страшен.

Но на проде, особенно на таблице, куда постоянно идут записи, безопаснее использовать CREATE INDEX CONCURRENTLY.

Он строится дольше и создаёт дополнительную нагрузку, зато не останавливает INSERT, UPDATE и DELETE.

Запомните простое правило:

Если таблица живая и в неё пишет приложение, индекс в PostgreSQL лучше создавать через CONCURRENTLY.

И перед тем как запускать такую миграцию, проверьте четыре вещи:

1. Команда не находится внутри транзакции.
2. Индекс создаётся отдельным шагом миграции.
3. Вы готовы к повышенной нагрузке на время построения.
4. После выполнения вы проверите, что индекс не остался в состоянии INVALID.

Так индекс станет не только полезным для запросов, но и безопасным для продакшена.