Як створити індекс на великій таблиці, не зупинивши запис?

Звичайний CREATE INDEX у PostgreSQL бере блокування, яке забороняє запис у таблицю на весь час побудови. На таблиці в сотні мільйонів рядків це хвилини чи години простою для INSERT/UPDATE.

CREATE INDEX CONCURRENTLY будує індекс без блокування запису:

CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at);

Ціна й підводні камені:

• Працює довше: два проходи по таблиці й очікування завершення всіх транзакцій, що вже йшли.
• Не можна в транзакції. У Laravel-міграції для цього потрібно вимкнути транзакцію міграції (public $withinTransaction = false;).
• Якщо побудова впала (наприклад, порушено унікальність), лишається індекс у стані INVALID. Він не використовується для читання, але сповільнює запис. Його треба знайти (pg_index.indisvalid = false), видалити DROP INDEX CONCURRENTLY і створити знову.
• Довга транзакція десь у системі змушує побудову чекати - перед операцією варто перевірити pg_stat_activity.

MySQL (InnoDB) з версії 5.6 будує більшість індексів online (ALGORITHM=INPLACE, LOCK=NONE), але на початку й наприкінці все одно потрібне коротке метаданих-блокування. Для великих таблиць часто використовують gh-ost чи pt-online-schema-change.

Загальне правило: зміни схеми великих таблиць ганяють окремо від деплою коду, з lock_timeout, щоб операція краще впала, ніж повісила всю базу в черзі за блокуванням.

Докладніше в документації: CREATE INDEX CONCURRENTLY