0Pricing
SQL Academy · Урок

Параллельное создание индексов (CREATE INDEX CONCURRENTLY)

Создавайте индексы без блокировки записи с помощью CREATE INDEX CONCURRENTLY и восстанавливайтесь после прерванного создания

«Параллельное создание индексов (CREATE INDEX CONCURRENTLY)» — бесплатный урок SQL Academy на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Проблема с CREATE INDEX

Обычная команда CREATE INDEX получает блокировку SHARE — она блокирует запись (но не чтение) на всё время построения индекса. Для таблицы с миллиардом строк это означает остановку записи на несколько минут.

CONCURRENTLY: решение

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

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Как это работает

Выполняются два сканирования таблицы:

  1. Первый проход: построение индекса по текущим строкам
  2. Второй проход: обработка строк, изменённых во время первого прохода

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

Нельзя выполнять внутри транзакции

CONCURRENTLY выполняет COMMIT дважды, поэтому эту операцию нельзя помещать внутрь явного блока BEGIN/COMMIT:

BEGIN;
CREATE INDEX CONCURRENTLY ...;  -- ERROR
COMMIT;

Медленно, но без остановки работы

CONCURRENTLY выполняется дольше обычной команды CREATE INDEX (два сканирования и ожидание блокировок). Преимущество — отсутствие простоя.

Восстановление после сбоя

Если CONCURRENTLY завершается с ошибкой (из-за прерывания или системной ошибки), индекс остаётся в состоянии INVALID:

SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;

-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;

REINDEX CONCURRENTLY

Перестроение без блокировки, начиная с PG 12:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;

DROP INDEX CONCURRENTLY

Также не блокирует работу:

DROP INDEX CONCURRENTLY old_idx;

Уникальные индексы

Индексы UNIQUE также можно строить с помощью CONCURRENTLY. Это удобно для добавления ограничений UNIQUE без остановки работы:

CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;

Индексы внешних ключей

Стандартный подход при добавлении FK в большую таблицу:

-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);

-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Стоимость параллельного построения индекса

Два полных сканирования таблицы и ведение служебных данных. Это потребляет ресурсы CPU и IO. По возможности выполняйте операцию в часы низкой нагрузки.

Мониторинг процесса

Начиная с PG 12, ход выполнения доступен в представлении pg_stat_progress_create_index:

SELECT phase,
       blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

Итоги

CONCURRENTLY — удобный для рабочей среды способ управления индексами.

  • CREATE/DROP/REINDEX CONCURRENTLY не блокируют запись
  • Их нельзя выполнять внутри транзакции
  • Сбои оставляют индексы в состоянии INVALID — удаляйте их
  • Отслеживайте процесс с помощью pg_stat_progress_create_index

Быстрая проверка

Почему CREATE INDEX CONCURRENTLY нельзя выполнять внутри явного блока BEGIN/COMMIT?

Часто задаваемые вопросы

Урок «Параллельное создание индексов (CREATE INDEX CONCURRENTLY)» бесплатный?

Да — полный текст урока «Параллельное создание индексов (CREATE INDEX CONCURRENTLY)» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Параллельное создание индексов (CREATE INDEX CONCURRENTLY)»?

Создавайте индексы без блокировки записи с помощью CREATE INDEX CONCURRENTLY и восстанавливайтесь после прерванного создания Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Параллельное создание индексов (CREATE INDEX CONCURRENTLY)»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Миграции без остановки: почему ALTER TABLE блокирует таблицы
  2. Параллельное создание индексов (CREATE INDEX CONCURRENTLY)
  3. Переименование столбцов без остановки
  4. Инструменты: Flyway, Liquibase, Sqitch
← Назад к SQL Academy