Миграции схемы PostgreSQL без простоя: что ALTER перепишет, очередь блокировок и expand/contract

Миграция ALTER TABLE ... ADD COLUMN выполняется за 2 мс, а потом сайт одиннадцать секунд не отвечает: команде нужна блокировка ACCESS EXCLUSIVE, она встала за долгим запросом, и все новые запросы выстроились за ней. Это два разных вопроса: сколько команда держит блокировку, когда её получила (зависит от того, перепишет ли она таблицу), и сколько она ждёт, пока её получит (зависит от чужих запросов). Какие формы ALTER TABLE переписывают файл, видно по смене relfilenode; как устроена очередь, разобрано в статье про блокировки. Здесь обе половины сведены к правилам миграции и к схеме expand/contract для переименований. MySQL с ALGORITHM = INSTANT измерен в статье про online DDL InnoDB, где gh-ost только назван.

Опыт 1: что берёт и что переписывает каждая команда

Таблица на 3 млн строк, 165 МБ. Каждая команда выполняется в транзакции, из pg_locks читаются режимы собственного процесса на таблице, а pg_relation_filenode до и после показывает, появился ли новый файл. Затем ROLLBACK:

docker run -d --name dba-schema-migrations-zero-downtime -e POSTGRES_PASSWORD=pg postgres:18
P() { docker exec -i dba-schema-migrations-zero-downtime psql -U postgres -Atq -v ON_ERROR_STOP=1 "$@"; }
P -c "CREATE TABLE o_ref (id int PRIMARY KEY)" -c "INSERT INTO o_ref SELECT g FROM generate_series(1,100) g" \
  -c "CREATE TABLE o (id int PRIMARY KEY, ref int, status varchar(20) NOT NULL DEFAULT 'new', note text, v int)" \
  -c "INSERT INTO o SELECT g, 1 + g % 100, 'new', 'n' || g, g FROM generate_series(1, 3000000) g" -c "VACUUM ANALYZE o"
run() {
  P <<SQL | tr '\n' ' '
BEGIN;
SELECT 'o'::regclass::oid AS oid, pg_relation_filenode('o') AS f0, clock_timestamp() AS t0 \gset
$1;
SELECT string_agg(mode, ',') FROM pg_locks WHERE locktype = 'relation' AND relation = :oid AND pid = pg_backend_pid();
SELECT pg_relation_filenode(:oid) <> :f0 AS rewritten, round(extract(epoch FROM clock_timestamp() - :'t0') * 1000) AS ms;
ROLLBACK;
SQL
  echo
}
run "ALTER TABLE o ADD COLUMN c2 int DEFAULT 0"

Цикл run по командам из таблицы (ограничения NOT VALID для строк VALIDATE создаются заранее) дал такой вывод (режимы с самым сильным AccessExclusiveLock; в строках с перезаписью рядом виден ShareLock от перестройки индексов, он слабее и ничего не меняет):

Миллисекунды — один прогон на этой машине, режимы и перезапись от неё не зависят. Три группы видны сразу. Метаданные: единицы миллисекунд при любом числе строк. Скан без перезаписи (SET NOT NULL, CHECK, внешний ключ): время растёт с таблицей, и всё это время держится блокировка. Перезапись: DEFAULT с изменчивой функцией, расширение типа int до bigint, сужение varchar и text в varchar(n) — секунды на 165 МБ. Расширение varchar и varchar в text перезаписи не потребовали. Режим у почти всех строк один и тот же, поэтому «быстрая команда» опасна не временем, а очередью (опыт 2).

Два исключения, которые кладут миграцию на безопасные рельсы. Ограничения с NOT VALID добавляются за 1 мс, а VALIDATE CONSTRAINT читает таблицу под ShareUpdateExclusive, который совместим с чтением и записью. А SET NOT NULL после проверенного CHECK (v IS NOT NULL) проверку пропускает:

ALTER TABLE o ADD CONSTRAINT o_nn CHECK (v IS NOT NULL) NOT VALID;
ALTER TABLE o VALIDATE CONSTRAINT o_nn;
ALTER TABLE o ALTER COLUMN v SET NOT NULL;

вывод
VALIDATE CONSTRAINT (86 мс на скан):  ShareUpdateExclusiveLock
SET NOT NULL после него:              AccessExclusiveLock, 2 мс вместо 91

Индекс без блокировки записи строит CREATE INDEX CONCURRENTLY: он держит на таблице ShareUpdateExclusiveLock, и pg_locks другой сессии застал этот замок в 6 замерах из 8 (устройство и ловушки — в статье про индексы без простоя).

Что в коде решает про перезапись

Условие в ATRewriteTables: перезапись нужна, только если какой-то столбец надо пересчитать или меняется сохраняемость или метод доступа. Поэтому SET UNLOGGED в таблице выше перезаписывает, а DROP COLUMN нет:

		/*
		 * We only need to rewrite the table if at least one column needs to
		 * be recomputed, or we are changing its persistence or access method.
		 *
		 * There are two reasons for requiring a rewrite when changing
		 * persistence: on one hand, we need to ensure that the buffers
		 * belonging to each of the two relations are marked with or without
		 * BM_PERMANENT properly.  On the other hand, since rewriting creates
		 * and assigns a new relfilenumber, we automatically create or drop an
		 * init fork for the relation as appropriate.
		 */
		if (tab->rewrite > 0 && tab->relkind != RELKIND_SEQUENCE)

tablecmds.c, ATRewriteTables, строки 5883–5894: если tab->rewrite пуст, таблица остаётся прежним файлом, даже когда команда её сканирует. Флаг rewrite выставляет разбор подкоманд, например в ATExecAddColumn. Для ADD COLUMN ... DEFAULT решение принимает проверка на изменчивость выражения:

			if (rel->rd_rel->relkind == RELKIND_RELATION &&
				!colDef->generated &&
				!has_domain_constraints &&
				!contain_volatile_functions((Node *) defval))
			{
...
				/*
				 * Failed to use missing mode.  We have to do a table rewrite
				 * to install the value --- unless it's a virtual generated
				 * column.
				 */
				if (colDef->generated != ATTRIBUTE_GENERATED_VIRTUAL)
					tab->rewrite |= AT_REWRITE_DEFAULT_VAL;

Строки 7543–7547 и 7571–7577. Неизменяемый DEFAULT вычисляется один раз и кладётся в каталог (StoreAttrMissingVal, строка 7562 между двумя цитатами), поэтому DEFAULT 0 в опыте занял 2 мс. Если в выражении есть изменчивая функция (clock_timestamp(), gen_random_uuid()), каждая строка получает своё значение, и файл переписывается: 1655 и 3131 мс. Подкоманды одного ALTER TABLE раскладываются по проходам AT_PASS_* (строки 148–164): сначала DROP, потом смена типа, потом ADD COLUMN, затем ограничения и индексы.

Опыт 2: ALTER за долгим SELECT под нагрузкой

Теперь вторая половина вопроса. База pgbench -i -s 100 (10 млн строк в pgbench_accounts, 1503 МБ), нагрузка pgbench -S -c 16 -j 4 -T 24 -P 1 postgres: 16 клиентов, каждый запрос читает pgbench_accounts. С третьей секунды второй сеанс держит читателя SELECT pg_sleep(14) FROM pgbench_accounts LIMIT 1, с пятой третий запускает ALTER TABLE pgbench_accounts ADD COLUMN note text без lock_timeout. Сам ADD COLUMN без значения не пишет в строки, как показала таблица выше. Раз в секунду четвёртый сеанс считает ожидающих:

SELECT count(*) FILTER (WHERE wait_event_type = 'Lock') AS lock_waiters FROM pg_stat_activity;

вывод
tps по секундам pgbench:  5 с: 215724;  6 с: 3754;  7–17 с: 0;  18 с: 187995
lock_waiters: 0 до 5-й секунды, 17 с 5-й по 16-ю, 0 с 17-й
ALTER TABLE: Time: 12012.426 ms (00:12.012)

В секундах 7–17 прогресс pgbench показал 0,0 tps: 11 секунд подряд ни один запрос не прошёл. lock_waiters равен 17 — 16 клиентов pgbench и сам ALTER; читатель ушёл примерно на 17-й секунде, и очередь расползлась сразу. Читатели pgbench совместимы с читателем pg_sleep, но стоят за ACCESS EXCLUSIVE, как в опыте с очередью. Команда, которая на 3 млн строк заняла 2 мс (опыт 1), здесь отстояла 12 секунд в очереди и остановила чтение на 11.

Тот же сценарий, но миграция идёт циклом: SET lock_timeout = '2s', при ошибке пауза секунда и новая попытка.

n=0; until P -v ON_ERROR_STOP=1 -c "SET lock_timeout = '2s'" -c "ALTER TABLE pgbench_accounts ADD COLUMN note text"; do
  n=$((n+1)); echo "attempt $n failed at t=$(( $(date +%s) - S ))"; sleep 1
done; echo "attempt $((n+1)) ok at t=$(( $(date +%s) - S ))"

вывод
ERROR:  canceling statement due to lock timeout
attempt 1 failed at t=7
attempt 2 failed at t=10
attempt 3 failed at t=13
attempt 4 failed at t=16
attempt 5 ok at t=17
tps по секундам 5..19:  291544  902  0  295366  22687  0  268085  42229  0  245679  66555  0  213976  287253  283238

Каждая попытка снимала ALTER из очереди через 2 секунды, и очередь уходила вместе с ним. Нулевой tps остался в 4 секундах (7, 10, 13 и 16-я) вместо 11, ещё четыре секунды были частичными (902, 22 687, 42 229 и 66 555 tps при обычных 250–290 тысячах); пятая попытка прошла, как только читатель закончился. Один прогон каждого варианта; средний tps за 24 секунды 116 195 против 183 689 — это разница в потерянных секундах. Цена повтора — до 2 секунд задержки читателей на каждую попытку; если читатель живёт дольше, цикл надо останавливать по числу попыток.

Опыт 3: переименование через expand/contract

Прямое переименование занимает 1–2 мс (опыт 1), но ломает запущенное приложение: приложение и база не обновляются одномоментно.

ALTER TABLE pgbench_accounts RENAME COLUMN abalance TO balance;
SELECT abalance FROM pgbench_accounts WHERE aid = 1;

вывод
ERROR:  column "abalance" does not exist
LINE 1: SELECT abalance FROM pgbench_accounts WHERE aid = 1
HINT:  Perhaps you meant to reference the column "pgbench_accounts.balance".

Вместо переименования столбцы живут рядом. Шаг 1 (expand): новый столбец и триггер, который копирует в него значение при каждой записи; обе команды идут с lock_timeout, потому что CREATE TRIGGER берёт ShareRowExclusive, который тоже конфликтует с записью и встаёт в очередь:

\timing on
SET lock_timeout = '2s';
ALTER TABLE pgbench_accounts ADD COLUMN balance int;
CREATE FUNCTION sync_balance() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.balance := NEW.abalance; RETURN NEW; END $$;
CREATE TRIGGER sync_balance BEFORE INSERT OR UPDATE ON pgbench_accounts FOR EACH ROW EXECUTE FUNCTION sync_balance();

вывод
ALTER TABLE ADD COLUMN: Time: 2.205 ms
CREATE TRIGGER:         Time: 0.674 ms   (в pg_locks: ShareRowExclusiveLock)

Шаг 2 (backfill): старые строки догоняют батчами, каждый в своей транзакции, чтобы блокировки строк снимались после каждой сотни тысяч:

UPDATE pgbench_accounts SET balance = abalance WHERE aid > 0 AND aid <= 100000;
UPDATE pgbench_accounts SET balance = abalance WHERE aid > 100000 AND aid <= 200000;
-- ... и так до 500000

вывод
пять батчей по 100 000 строк: 353.536, 367.963, 364.649, 358.215, 371.990 мс
журнал: 242 MB, таблица 1281 MB -> 1345 MB, заполнено balance: 500000 строк

Измерено на 500 тысячах строк из 10 млн; по этому вынести срок для всей таблицы нельзя, а размер видно: каждая обновлённая строка оставила мёртвую версию, и таблица выросла на 64 МБ: VACUUM сделает это место пригодным для новых строк, а вернуть его диску может только перепаковка. Шаг 3: релиз приложения, которое читает balance. Шаг 4 (contract), когда старая версия ушла:

DROP TRIGGER sync_balance ON pgbench_accounts;
ALTER TABLE pgbench_accounts DROP COLUMN abalance;

вывод
DROP TRIGGER: Time: 1.310 ms
DROP COLUMN:  Time: 1.107 ms, relfilenode тот же, размер 1345 MB

DROP COLUMN только помечает столбец удалённым в каталоге, место он не возвращает. Стенд убирается командой docker rm -f -v dba-schema-migrations-zero-downtime.

Что из этого следует

• Любая миграция с ACCESS EXCLUSIVE идёт с SET lock_timeout и повтором, как в опыте 2: 11 секунд нулевого tps превратились в 4 коротких провала по 2 секунды. Большинство строк таблицы из опыта 1 берут этот режим, включая RENAME и SET DEFAULT.
• Мгновенны только операции над каталогом: ADD COLUMN без DEFAULT или с неизменяемым, SET DEFAULT, DROP COLUMN, расширение varchar. Всё, что перезаписывает файл (изменчивый DEFAULT, int в bigint, сужение varchar), на большой таблице делается сменой столбца по схеме expand/contract, а не ALTER ... TYPE.
• Проверку делай в два шага: ADD CONSTRAINT ... NOT VALID, затем VALIDATE CONSTRAINT под ShareUpdateExclusive; SET NOT NULL ставь после проверенного CHECK: 2 мс вместо 91. Индексы строй CREATE INDEX CONCURRENTLY.
• Переименование и смена типа — это минимум три шага: expand, переключение приложения, contract. Прямой RENAME ломает старую версию приложения на первом же запросе.
• Backfill батчами оставляет мёртвые версии строк: заложи VACUUM и место на диске, а длительность измеряй на копии, а не выводи из чужих чисел.

Чек-лист

1. Для каждой команды миграции проверь на копии таблицы режим по pg_locks и смену pg_relation_filenode: имя команды ничего не гарантирует.
2. Любой ALTER с AccessExclusive запускай с SET lock_timeout и циклом повтора с пределом попыток; перед запуском посмотри долгие запросы в pg_stat_activity.
3. Ограничения и NOT NULL ставь через NOT VALID и VALIDATE CONSTRAINT.
4. Переименование и смену типа проводи через новый столбец, триггер и батчи; старый столбец удаляй после релиза, который его не читает.
5. Убери стенд командой docker rm -f -v dba-schema-migrations-zero-downtime.