Изменить схему базы данных и не уронить прод — это отдельная наука. Разберём, почему обычные ALTER TABLE опасны на живом трафике, и как правильно накатывать изменения.
Почему ALTER TABLE блокирует всё
Представьте, что вы хотите добавить колонку в таблицу с десятками миллионов строк. Пишете ALTER TABLE orders ADD COLUMN priority integer NOT NULL DEFAULT 0 — и прод встаёт на несколько минут.
Причина: PostgreSQL для большинства изменений схемы берёт блокировку ACCESS EXCLUSIVE. Эта блокировка — самая жёсткая: она не пускает никого — ни SELECT, ни INSERT, ни UPDATE. И что ещё хуже, она встаёт в очередь за всеми текущими запросами. Если в момент миграции идёт долгий отчёт, ALTER TABLE ждёт его, а за ним копится очередь всех новых запросов.
Поэтому первое правило: каждая миграция начинается с SET LOCAL lock_timeout = '3s'. Если блокировку не удаётся получить за 3 секунды — миграция падает с ошибкой вместо того, чтобы подвесить прод.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN priority integer;
COMMIT;
Breaking change и N-1 совместимость
Не все изменения схемы одинаково опасны.
Безопасные изменения (можно накатывать без специальных мер):
ADD COLUMN ... NULL— колонка без ограниченияNOT NULLADD COLUMN ... NOT NULL DEFAULT 'x'(PostgreSQL 11+, постоянное значение)CREATE TABLEADD CONSTRAINT ... NOT VALID
Опасные изменения (требуют особого подхода):
DROP COLUMN,RENAME COLUMN- Изменение типа колонки (
ALTER TYPE) ADD COLUMN NOT NULLбез значения по умолчанию на большой таблице- Удаление значения из enum-типа
Опасные изменения ещё называют breaking change — они могут сломать уже работающий код. Причём проблема не только в блокировке, но и во времени деплоя. Типичный деплой: сначала накатывается миграция, потом запускается новая версия приложения. Между ними есть окно, где старая версия кода работает с новой схемой. Если схема изменилась несовместимо — старый код упадёт.
Это правило называют N-1 совместимостью: миграция должна быть совместима с предыдущей версией кода.
Expand-Contract: три релиза вместо одного
Чтобы безопасно сделать опасное изменение, его разбивают на несколько релизов. Этот паттерн называется Expand-Contract (расширить — мигрировать — свернуть).
Релиз 1 — расширение: добавить новую структуру (колонку, таблицу)
код пишет в старое место, опционально и в новое
Релиз 2 — миграция: перенести данные из старого в новое (backfill)
код пишет в оба места, читает из старого
Релиз 3 — чтение нового: код читает из нового, пишет в оба
Релиз 4 — свёртывание: удалить старое, убрать из кода
Каждый релиз может быть откачен независимо. Если что-то пошло не так в релизе 2 — можно вернуться к релизу 1 без потери данных.
Как добавить NOT NULL колонку
PostgreSQL 11 и новее умеют добавлять колонку с константным значением по умолчанию мгновенно — значение хранится «виртуально» в метаданных, а не пишется в каждую строку:
-- Мгновенно даже на 100M строк
ALTER TABLE orders ADD COLUMN priority integer NOT NULL DEFAULT 0;
Но если значение по умолчанию динамическое (например, NOW() или gen_random_uuid()) — PostgreSQL не может его сохранить виртуально и пойдёт переписывать каждую строку. Тут нужен expand-contract:
- Добавить колонку без
NOT NULL - Заполнить существующие строки порциями (см. раздел про backfill)
- Установить ограничение безопасным способом:
-- Шаг 3a: добавить CHECK-ограничение, не проверяя старые строки
ALTER TABLE orders ADD CONSTRAINT ck_orders_priority_not_null
CHECK (priority IS NOT NULL) NOT VALID;
-- Шаг 3b: проверить существующие строки (не блокирует запись)
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_priority_not_null;
-- Шаг 3c: теперь SET NOT NULL дёшев — PG знает, что NULL нет
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;
-- Шаг 3d: убрать временный CHECK
ALTER TABLE orders DROP CONSTRAINT ck_orders_priority_not_null;
CHECK NOT VALID выполняется мгновенно. VALIDATE берёт мягкую блокировку SHARE UPDATE EXCLUSIVE, которая не мешает обычным запросам.
Как переименовать колонку
RENAME COLUMN — нельзя сделать в один релиз. Старый код сразу сломается, потому что будет обращаться к несуществующей колонке.
Безопасная последовательность:
- Добавить новую колонку (
ADD COLUMN new_name <тип>) - Код начинает писать в обе колонки
- Перенести существующие данные порциями
- Код начинает читать из новой колонки
- Код перестаёт писать в старую
- Удалить старую колонку (
DROP COLUMN old_name)
Для read-only сценариев есть более простой вариант — view-обёртка:
ALTER TABLE orders RENAME COLUMN old_name TO new_name;
CREATE VIEW orders_v AS SELECT *, new_name AS old_name FROM orders;
Старый код читает из view и видит старое имя, новый код работает напрямую с таблицей.
Как изменить тип колонки
ALTER COLUMN ... SET DATA TYPE с приведением типов переписывает всю таблицу — получается то же, что ALTER TABLE на миллионах строк. Блокировка ACCESS EXCLUSIVE на долгое время.
Исключения, которые работают мгновенно (без переписи):
varchar→text(расширение без приведения)varchar(50)→varchar(100)(только расширение)
Для всего остального (например, integer → bigint) нужен expand-contract: добавить теневую колонку нового типа, заполнить данными, переключить код.
Как добавить внешний ключ
Обычный ADD FOREIGN KEY блокирует обе таблицы и проверяет все существующие строки. На больших таблицах — долго.
Безопасный способ — двухшаговый:
-- Шаг 1: создать FK, не проверяя существующие строки (мгновенно)
ALTER TABLE order_item
ADD CONSTRAINT fk_order_item_order_id
FOREIGN KEY (order_id) REFERENCES orders(id)
NOT VALID;
-- Шаг 2: проверить существующие строки (не блокирует запись)
ALTER TABLE order_item VALIDATE CONSTRAINT fk_order_item_order_id;
NOT VALID означает: новые строки будут проверяться сразу, старые — при VALIDATE.
Как добавить индекс
CREATE INDEX без дополнительных ключевых слов берёт блокировку SHARE, которая не пускает INSERT/UPDATE/DELETE. На большой таблице — несколько минут без записи.
Решение — CREATE INDEX CONCURRENTLY. Он строит индекс в несколько проходов без блокировки:
CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);
DROP INDEX тоже берёт жёсткую блокировку — используйте DROP INDEX CONCURRENTLY.
Если CREATE INDEX CONCURRENTLY был прерван, он оставляет INVALID индекс. Его нужно удалить и создать заново:
DROP INDEX CONCURRENTLY IF EXISTS ix_orders_status;
CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);
Важная деталь для инструментов миграций (Liquibase, Flyway): CONCURRENTLY нельзя выполнить внутри транзакции. Changesets с индексами должны иметь runInTransaction="false":
<changeSet id="20260507-add-status-index" runInTransaction="false">
<sql>CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);</sql>
<rollback>DROP INDEX CONCURRENTLY IF EXISTS ix_orders_status;</rollback>
</changeSet>
Как удалить значение из enum
В PostgreSQL нет команды REMOVE VALUE FROM ENUM. Нативно удалить значение нельзя.
Единственный способ — создать новый тип без ненужного значения и переключить колонку на него:
CREATE TYPE order_status_v2 AS ENUM ('NEW', 'PAID', 'SHIPPED')(без удаляемого значения)ADD COLUMN status_v2 order_status_v2 NULL- Перенести данные порциями:
UPDATE orders SET status_v2 = status::text::order_status_v2 - Переключить код на чтение и запись в
status_v2 DROP COLUMN status,RENAME COLUMN status_v2 TO status,DROP TYPE order_status
Добавить значение (ADD VALUE) — мгновенно (PostgreSQL 12+), но нельзя использовать новое значение в той же транзакции. Это должен быть отдельный changeset.
Перенос данных порциями
Большой UPDATE в одной транзакции — плохая идея: транзакция держит блокировки на всё время выполнения, копит WAL и мешает autovacuum.
Правильный подход — обновлять по небольшим частям с коммитом между ними:
DO $$
DECLARE rows_updated integer := 1;
BEGIN
WHILE rows_updated > 0 LOOP
UPDATE orders SET priority = 0
WHERE id IN (
SELECT id FROM orders
WHERE priority IS NULL
LIMIT 10000
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
COMMIT;
PERFORM pg_sleep(0.1);
END LOOP;
END $$;
Для очень больших таблиц SQL-пакет в миграции всё равно занимает одно соединение и держит деплой. Надёжнее — фоновое задание в коде приложения: оно работает независимо от деплоя, его можно остановить и перезапустить. Используйте FOR UPDATE SKIP LOCKED, чтобы не конфликтовать с пользовательскими запросами:
@Component
public class BackfillPriorityJob {
private final JdbcTemplate jdbc;
public BackfillPriorityJob(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
@Scheduled(cron = "0 * * * * *")
public void backfillPriority() {
int updated;
do {
updated = jdbc.update("""
UPDATE orders SET priority = 0
WHERE id IN (
SELECT id FROM orders
WHERE priority IS NULL
LIMIT 10000
FOR UPDATE SKIP LOCKED
)
""");
} while (updated == 10000);
}
}
Откат миграций почти не работает
Секция <rollback> в Liquibase или Flyway создаёт иллюзию безопасности. На практике откатить миграцию на продуктивной базе почти невозможно:
- Если удалена колонка — данные потеряны
- Если нарушена целостность данных — откат не восстановит состояние
- Одна миграция в цепочке expand-contract откатиться не может без разрушения всей цепочки
Реальные варианты при проблеме:
- Forward-fix — написать новую миграцию, которая исправляет ситуацию
- Восстановление из резервной копии — если данные потеряны
Именно поэтому expand-contract так важен: каждый шаг безопасен сам по себе и не ломает предыдущее состояние.
squawk — линтер для миграций
squawk — инструмент статического анализа SQL-миграций. Он находит опасные паттерны до того, как они попадут на прод:
ADD COLUMNсDEFAULTна PostgreSQL ниже 11CREATE INDEXбезCONCURRENTLYADD FOREIGN KEYбезNOT VALIDALTER TYPEс переписью таблицы- переименования колонок и таблиц
squawk db/changelog/v0042.sql
Squawk стоит добавить в pre-commit хук и в CI — так опасные миграции не пройдут ревью незамеченными.
Коротко
ALTER TABLEберётACCESS EXCLUSIVE— блокирует всё. На большой таблице это минуты без сервиса.- Начинайте каждую миграцию с
SET LOCAL lock_timeout = '3s'— лучше упасть быстро, чем зависнуть надолго. - N-1 совместимость: миграция должна работать и со старым кодом.
- Expand-Contract — способ сделать опасное изменение за 3–4 релиза без остановки сервиса.
NOT NULLна существующей колонке — черезCHECK NOT VALID+VALIDATE+SET NOT NULL.ADD FOREIGN KEY— черезNOT VALID+VALIDATEотдельно.- Индексы — всегда
CONCURRENTLYсrunInTransaction="false". - Удалить значение enum нативно нельзя — нужен новый тип и теневая колонка.
- Большой
UPDATE— порциями по 10K строк сSKIP LOCKED, лучше фоновым заданием. - Откат миграции на проде не работает — проектируйте forward-fix.
Что почитать дальше
- Индексы PostgreSQL — типы индексов, GIN, BRIN, частичные индексы.
- Блокировки PostgreSQL — как работает
ACCESS EXCLUSIVEи другие уровни блокировок. - Типы данных PostgreSQL — enum, jsonb, uuid и когда что выбирать.