ALTER TABLE: мышление миграций

Обед. Ты катишь релиз: две миграции, обе после ревью, на вид безобидны — ADD COLUMN ... DEFAULT 'active' и одна строка ALTER TABLE orders ALTER COLUMN amount TYPE numeric(12,2). Релиз уходит в 12:33.

Руслан (в чате, 12:41)

Кассы. Обе. 12:41.

Смотрю. Из нового — только релиз. Но там смена типа колонки, она же безобидная.

Павел

ALTER TYPE на orders. ACCESS EXCLUSIVE, переписывание всей таблицы. Кассы стоят за твоей миграцией. Убиваю.

Восемь минут — от релиза до kill — обе кассы не пробили ни одного заказа: таблица в десятки миллионов строк переписывалась под блокировкой. Вечером — твой первый постмортем, в графе «автор» — ты. Павел молча достаёт новый бумажный блокнот и записывает дату.

Ботыр

Помнишь, в первый день: «у нас так касса вставала»? Это было два раза. Видимо, придётся пересчитать.

Дмитрий

Счёт потом. Правило постмортема: виноват способ, не человек. Строка прошла ревью. Способ — переписывание горячей таблицы в обед.

В файле миграции обе строки выглядят одинаково. Как отличать?

Дмитрий

Не по синтаксису. Одни ALTER правят метаданные — это мгновенно. Другие переписывают таблицу под блокировкой. Учимся различать заранее.

Цель юнита — не «список безопасных ALTER наизусть», а рефлекс: перед миграцией спросить «это мгновенно или это переписывание/долгая блокировка?». Наблюдать цену будем через relfilenode — идентификатор физического файла таблицы. Он меняется только при полном переписывании; если ALTER лишь поправил метаданные, файл прежний. Плюс разберём двухфазное добавление констрейнта (NOT VALID → VALIDATE), которое отделяет быструю смену метаданных от долгого сканирования.

Мгновенно против переписывания

Часть ALTER — это правка системного каталога, без касания данных на диске. Классика — ADD COLUMN с константным DEFAULT: с PG11 новое значение хранится как метаданные таблицы, существующие строки не трогаются (relfilenode тот же). Мгновенно, независимо от размера таблицы.

Другая часть требует переписать каждую строку, потому что меняется физическое представление данных. ALTER COLUMN ... TYPE, меняющий тип (int → bigint, text → numeric), — почти всегда переписывание: Postgres создаёт новый файл, перегоняет туда все строки в новом формате и подменяет relfilenode. На большой таблице это долгая ACCESS EXCLUSIVE-блокировка — то самое, что положило кассу Brew.

⚠️ Важная оговорка: даже «мгновенный» ADD COLUMN на миг берёт ACCESS EXCLUSIVE. Если в этот момент по таблице идёт долгий запрос, миграция встанет в очередь за ним — а за миграцией встанут все остальные. Поэтому в проде миграции пускают с lock_timeout (см. «Заборчик»).

Двухфазный констрейнт: NOT VALID → VALIDATE

Добавить CHECK (или FOREIGN KEY) обычным способом — это просканировать всю таблицу, проверяя старые строки, под сильной блокировкой. PG умеет разбить это на две фазы:

ADD CONSTRAINT ... NOT VALID — мгновенно: констрейнт заводится и применяется к новым строкам, но старые не сканируются (convalidated = f). Короткая блокировка.
VALIDATE CONSTRAINT — отдельной командой досканировать старые строки. Берёт лишь SHARE UPDATE EXCLUSIVE — не блокирует чтение и запись, идёт в фоне.

Так «добавить правило» перестаёт быть стоп-краном: горячая фаза — мгновенная, а долгая проверка не держит приложение.

Очередь блокировок и цена ALTER

Главная ловушка миграции не в «переписывании», а в очереди блокировок: даже мгновенный ALTER на миг берёт ACCESS EXCLUSIVE, и если перед ним висит долгая транзакция — встаёт он, а за ним весь трафик:

plaintext

время →
T1  долгий SELECT по orders   ╞════════ держит ACCESS SHARE ════════╡
T2  миграция: ALTER ADD COLUMN      ╞···· ждёт ACCESS EXCLUSIVE ····╡══╡
T3  обычный INSERT в orders               ╞······ стоит за миграцией ······╡
                                          ▲
                      даже «мгновенный» ALTER встал в очередь за T1 —
                      и весь поток записи (T3) встал уже за ALTER

А вот как соотносятся сами операции по цене:

`ALTER`	Что происходит	Цена
`ADD COLUMN ... DEFAULT <константа>`	правка метаданных (с PG11)	мгновенно
`ADD CONSTRAINT ... NOT VALID`	метаданные; старые строки не сканируются	мгновенно (короткая блокировка)
`VALIDATE CONSTRAINT`	досканирует старые строки	фоном, `SHARE UPDATE EXCLUSIVE` (не блокирует запись)
`ALTER COLUMN ... TYPE` (смена представления)	переписывает каждую строку	долгая `ACCESS EXCLUSIVE`
`ADD COLUMN ... DEFAULT now()` (волатильный)	переписывает таблицу	долгая `ACCESS EXCLUSIVE`
`ALTER COLUMN ... SET NOT NULL` (существующая колонка)	сканирует таблицу, проверяя, что NULL нет	блокировка на время скана (PG12+ пропустит при валидном `CHECK (col IS NOT NULL)`)

Что показывает наш код

Урок — в demo.sql на лабораторном столе с 1000 строк (базовые таблицы не трогаем). Перед каждым ALTER снимаем relfilenode, после — сравниваем:

sql

SELECT pg_relation_filenode('alter_lab') AS fn \gset before1_
ALTER TABLE alter_lab ADD COLUMN status TEXT NOT NULL DEFAULT 'active';   -- метаданные
SELECT pg_relation_filenode('alter_lab') AS fn \gset after1_
SELECT (:before1_fn = :after1_fn) AS filenode_unchanged;                  -- t
 
ALTER TABLE alter_lab ALTER COLUMN n TYPE bigint;                         -- переписывание
-- ... сравнение → f
 
ALTER TABLE alter_lab ADD CONSTRAINT n_positive CHECK (n > 0) NOT VALID;  -- convalidated = f
ALTER TABLE alter_lab VALIDATE CONSTRAINT n_positive;                     -- convalidated = t

filenode_unchanged = t означает «тот же файл, таблица не переписана»; f — «новый файл, было переписывание». convalidated показывает фазу констрейнта.

Запуск

docker compose up -d
make lecture L=02-schema-and-constraints/02-06-alter-table-migration-mindset T=db-reset
make lecture L=02-schema-and-constraints/02-06-alter-table-migration-mindset

Вывод:

plaintext

== 1) ADD COLUMN с константным DEFAULT — мгновенно (только метаданные) ==
 filenode_unchanged 
--------------------
 t
 
 
== 2) ALTER COLUMN ... TYPE int -> bigint — таблица ПЕРЕПИСана (новый relfilenode) ==
 filenode_unchanged 
--------------------
 f
 
 
== 3) ADD CONSTRAINT CHECK ... NOT VALID — мгновенно (старые строки не сканируются) ==
 validated_after_not_valid 
---------------------------
 f
 
 
== 4) VALIDATE CONSTRAINT — отдельный шаг (не блокирует запись) ==
 validated_after_validate 
--------------------------
 t

ADD COLUMN ... DEFAULT 'active' оставил relfilenode прежним (t) — мгновенная смена метаданных. ALTER COLUMN n TYPE bigint файл сменил (f) — таблицу переписали целиком (на проде это и есть та самая блокировка). CHECK ... NOT VALID завёлся непроверенным (convalidated = f), а VALIDATE отдельным шагом досканировал и пометил его проверенным (t) — горячая часть мгновенна, долгая не держит запись.

Заборчик

Что мы упростили: relfilenode — хороший индикатор «переписали / нет», но не вся картина, и дальше начинается территория, которую держит твой DBA:

Блокировка важнее переписывания. Даже мгновенный ADD COLUMN берёт ACCESS EXCLUSIVE, и если перед ним висит долгая транзакция, очередь блокировок положит запись на ровном месте — поэтому миграции пускают с коротким lock_timeout и ретраями, а не «насухо».
Большое переписывание делают не в лоб. ALTER TYPE на горячей таблице в проде разбивают на шаги: добавляют новую колонку, бэкфилят данные пачками в фоне, потом атомарно подменяют — либо используют онлайн-инструменты (pg_repack, оркестраторы вроде Reshape).
Нюансы версий. ADD COLUMN с волатильным дефолтом (now(), функция) уже переписывает таблицу. А NOT NULL зависит от того, как он появился: ADD COLUMN ... NOT NULL с константным дефолтом мгновенен (новая колонка и так без NULL — это и показывает наше демо), а ALTER COLUMN ... SET NOT NULL на существующей колонке сканирует таблицу, проверяя старые строки (PG12+ пропустит скан, если есть валидный CHECK (col IS NOT NULL)).

Граница курса: оркестрация zero-downtime-миграций — это уже ближе к DBA/DevOps; твоя задача — узнавать опасный ALTER в ревью миграции и не катить переписывание горячей таблицы в рабочее время.

Что забрать с собой

relfilenode меняется только при полном переписывании таблицы — это наблюдаемый индикатор «мгновенно vs переписывание».
ADD COLUMN с константным DEFAULT — мгновенно (метаданные); ALTER COLUMN ... TYPE со сменой представления — переписывание под ACCESS EXCLUSIVE.
ADD CONSTRAINT ... NOT VALID (мгновенно) + VALIDATE CONSTRAINT (фоном, SHARE UPDATE EXCLUSIVE) — двухфазное добавление правила без стоп-крана.
Даже мгновенный ALTER берёт ACCESS EXCLUSIVE на миг → в проде нужен lock_timeout; большое переписывание делают через new-column + batched backfill + swap.
Рефлекс перед миграцией: «это правит метаданные или переписывает таблицу/держит долгую блокировку?».

На этом модуль 02 «Схема, DDL и ограничения» закрыт: IDENTITY/DEFAULT, NOT NULL/PK и выбор ключа, внешние ключи, UNIQUE/CHECK, генерируемые столбцы/домены и мышление миграций. Постмортем твоего инцидента дочитывает Ботыр — и задерживается у стола.

Ботыр

Не расстраивайся: самый дорогой наш инцидент — вообще одна строка. UPDATE без WHERE. Знаю. Я автор.

Чем эта строка обошлась Brew и какая дисциплина спасает от таких строк — с этого начнётся модуль 03 «CRUD-беглость»: уверенные INSERT ... RETURNING, SELECT с пагинацией, безопасные UPDATE/DELETE, upsert и трезвая семантика NULL.