ADD COLUMN: мгновенно в PG 11+ против перезаписи в старом Postgres

Команда деплоит ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending' на таблице с 50 млн строк. На Postgres 10 база зависает на 15 минут. На Postgres 11+ тот же запрос выполняется за миллисекунды. Одинаковый SQL, принципиально разный результат.

Как Postgres 11 сделал ADD COLUMN мгновенным

До PG 11 ALTER TABLE t ADD COLUMN c TEXT DEFAULT 'x' перезаписывал каждую строку, материализуя значение по умолчанию. На таблице с 100 млн строк это означало минуты удержания AccessExclusiveLock — каждый читатель и писатель блокировался на всё время.

Начиная с PG 11, если дефолт не является волатильным (строковый литерал, число, булево или стабильное выражение), Postgres хранит его в системном каталоге pg_attribute:

• pg_attribute.atthasmissing = true
• pg_attribute.attmissingval = 'x'

Существующие строки сохраняют исходное физическое хранилище — в них ничего не пишется. При SELECT старой строки Postgres читает attmissingval и синтезирует значение во время чтения. Новые строки после миграции хранят колонку обычным образом. Время удержания блокировки падает с минут до миллисекунд.

Волатильные дефолты всё равно вызывают перезапись

DEFAULT clock_timestamp(), DEFAULT random(), DEFAULT gen_random_uuid() — любая волатильная функция — не может храниться как единое пропущенное значение, потому что каждая строка нуждается в разном результате. Postgres должен обойти каждую существующую строку, вызвать функцию и записать результат — полная перезапись таблицы под AccessExclusiveLock.

Решение: добавить колонку без дефолта (мгновенно), затем выполнить бэкфил существующих строк в пакетах через UPDATE, затем установить дефолт для будущих вставок через ALTER TABLE t ALTER COLUMN c SET DEFAULT gen_random_uuid().

Безопасное добавление NOT NULL на большой таблице

ADD COLUMN c TEXT NOT NULL DEFAULT 'x' нормально работает на PG 11+ с константным дефолтом — мгновенно, без перезаписи. Опасность возникает при добавлении NOT NULL к существующей nullable-колонке, в которой могут быть NULL:

-- НЕПРАВИЛЬНО на большой таблице: сканирует каждую строку под AccessExclusiveLock
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

Безопасный многошаговый паттерн (подробно разобран в уроке 04):

1. Добавить колонку без NOT NULL: мгновенно.
2. Бэкфил существующих NULL небольшими пакетами.
3. Добавить CHECK (col IS NOT NULL) NOT VALID — применяется только для новых строк, без сканирования таблицы.
4. VALIDATE CONSTRAINT — сканирует под более лёгкой блокировкой, которая не блокирует записи.
5. ALTER COLUMN SET NOT NULL — быстро, так как constraint уже доказал валидность.