Как добавить колонку в таблицу без блокировки при большом объеме данных?

Что проверяют

Знаете ли вы, какие блокировки берёт ALTER TABLE и почему даже мгновенная операция может положить сервис. Следом обычно просят сделать эту колонку NOT NULL с заполненными значениями.

Как отвечать

Возьмите PostgreSQL как самый частый случай у Python-команд:

• сама операция: ADD COLUMN всегда берёт ACCESS EXCLUSIVE, но без перезаписи таблицы это доли секунды. Колонка без DEFAULT меняет только метаданные, а с PostgreSQL 11 то же верно для неволатильного значения по умолчанию. Волатильный дефолт вроде clock_timestamp() перепишет таблицу;
• очередь блокировок: главный риск. Если на таблице висит долгая транзакция, ALTER ждёт её, а все новые запросы встают в очередь уже за ALTER. Поэтому ставят SET lock_timeout = '3s' и повторяют попытку;
• заполнение: пачками по несколько тысяч строк с коммитом после каждой, а не одним UPDATE. Так таблица не раздувается, WAL не копится, реплика не отстаёт;
• ограничение: ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID, затем VALIDATE CONSTRAINT, который не блокирует запись. После этого SET NOT NULL с PostgreSQL 12 опирается на проверенный CHECK и не сканирует таблицу;
• индекс: CREATE INDEX CONCURRENTLY вне транзакции. В Django это AddIndexConcurrently и atomic = False у миграции, в Alembic блок autocommit_block().

Для MySQL назовите ALGORITHM=INSTANT в InnoDB 8.0 и инструменты gh-ost и pt-online-schema-change, которые копируют таблицу в фоне.

Где спотыкаются

Упоминают ключевое слово ONLINE для PostgreSQL. Такого синтаксиса там нет, похожие опции есть в SQL Server и Oracle.

Считают, что партиционирование спасает от блокировки. ALTER на партиционированной таблице всё равно затронет все партиции.

Кладут добавление колонки, бэкфилл и индекс в одну миграцию. Django и Alembic на PostgreSQL по умолчанию оборачивают её в транзакцию, и эксклюзивная блокировка держится до конца бэкфилла.