Оглавление

• Задача и бизнес‑выгода
• Где обычно ломается прод при миграциях
• Принципы безопасных миграций
• Стратегия expand/contract: пошагово с примерами
  • Шаг 1. Expand: добавить новую колонку (nullable)
  • Шаг 2. Мягкая синхронизация: триггер для новых/обновлённых строк
  • Шаг 3. Бэкфилл в фоне малыми порциями
  • Шаг 4. Обновить приложение: двойная запись и чтение из новой колонки
  • Шаг 5. Сделать ограничение NOT NULL безопасно
  • Шаг 6. Contract: удалить старую колонку и триггеры
• Типовые сценарии: рецепты
  • Добавить колонку с NOT NULL и DEFAULT — безопасно
  • Переименование колонки
  • Изменение типа
  • Индексы
• Онлайн‑DDL: PostgreSQL и MySQL
  • PostgreSQL
  • MySQL (InnoDB)
• Большой бэкфилл без нагрузки на прод
• Порядок релизов приложения и фичефлаг для чтений/записей
• План отката за минуты, а не часы
• Инструменты: Flyway/Liquibase, gh-ost, pt-osc, pg_repack
• Чек‑лист перед продом
• Метрики и наблюдаемость при миграциях
• Частые вопросы
• Вывод

Задача и бизнес‑выгода

Изменения в схеме БД — это не только работа разработчика, но и прямые деньги для бизнеса: каждый незапланированный простой — потерянные заказы и репутация. Цель статьи — дать пошаговую методику, как безопасно менять схему в PostgreSQL и MySQL без простоя и с предсказуемым временем выполнения. Поговорим о стратегии expand/contract (сначала расширяем схему, потом её «сворачиваем»), онлайн‑DDL, бэкфилле по кусочкам и быстром откате.

Где обычно ломается прод при миграциях

• Жёсткие изменения «за один шаг»: переименовали колонку — приложение больше не может читать/писать.
• Долгие блокировки: «ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT ...» переписывает таблицу и держит блокировки — запросы встают.
• Недостаточно мощности для бэкфилла: массовый UPDATE на миллионы строк «кладёт» реплики, растёт лаг репликации.
• Нечёткий план отката: скрипт отката отсутствует или небезопасен (например, удаление старых данных без резервной копии).

Принципы безопасных миграций

• Совместимость вперёд и назад: приложение и схема должны на время «жить» вместе в обеих версиях.
• Маленькие шаги: каждый шаг атомарен, обратим и измерим по времени.
• Без долгих блокировок: использовать онлайн‑DDL, асинхронную валидацию, индексы «параллельно».
• Наблюдаемость: метрики времени запросов, очередей, лаг репликации, ошибки записи.
• План отката до начала работ: зафиксировать команды и условия, когда останавливаемся.

Стратегия expand/contract: пошагово с примерами

Рассмотрим типовой кейс — «переименовать колонку login в username» в PostgreSQL без простоя.

Шаг 1. Expand: добавить новую колонку (nullable)

-- Миграция 001_expand_add_username.sql (PostgreSQL)
ALTER TABLE public.users ADD COLUMN username text;

-- Для будущего индекса — без блокировок записи
-- Важно: выполнять ВНЕ транзакционного блока
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_username ON public.users (username);

Пока что приложение продолжает читать/писать в login.

Шаг 2. Мягкая синхронизация: триггер для новых/обновлённых строк

Триггер подставит username из login, если приложение его ещё не пишет.

-- Миграция 002_expand_sync_trigger.sql (PostgreSQL)
CREATE OR REPLACE FUNCTION public.users_sync_username()
RETURNS trigger AS $$
BEGIN
  IF NEW.username IS NULL AND NEW.login IS NOT NULL THEN
    NEW.username := NEW.login;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_users_sync_username ON public.users;
CREATE TRIGGER trg_users_sync_username
BEFORE INSERT OR UPDATE OF login, username ON public.users
FOR EACH ROW EXECUTE FUNCTION public.users_sync_username();

Шаг 3. Бэкфилл в фоне малыми порциями

-- Миграция 003_backfill_username.sql (PostgreSQL)
DO $$
DECLARE
  rows_updated integer := 0;
BEGIN
  LOOP
    UPDATE public.users u
      SET username = u.login
      WHERE u.username IS NULL
      AND ctid IN (
        SELECT ctid FROM public.users WHERE username IS NULL LIMIT 1000
      );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.05); -- пауза для снижения нагрузки
  END LOOP;
END $$;

На больших объёмах имеет смысл запускать это ночью или через планировщик, контролируя метрики.

Шаг 4. Обновить приложение: двойная запись и чтение из новой колонки

• Записи: писать и в login, и в username (временная «двойная запись»).
• Чтения: переключить чтение на username через фичефлаг.

Пример: Node.js (pg) — запись обеих колонок.

// app/db/users.js
import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function createUser({ login, passwordHash }) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `INSERT INTO public.users(login, username, password_hash)
       VALUES ($1, $1, $2)`,
      [login, passwordHash]
    );
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

export async function updateLogin({ userId, newLogin }) {
  await pool.query(
    `UPDATE public.users SET login = $1, username = $1 WHERE id = $2`,
    [newLogin, userId]
  );
}

export async function getUserByLogin({ login, readFromNew = true }) {
  if (readFromNew) {
    const { rows } = await pool.query(
      'SELECT * FROM public.users WHERE username = $1',
      [login]
    );
    return rows[0] || null;
  }
  const { rows } = await pool.query('SELECT * FROM public.users WHERE login = $1', [login]);
  return rows[0] || null;
}

Включаем фичефлаг на чтение из username поэтапно (например, 5% → 25% → 100%).

Шаг 5. Сделать ограничение NOT NULL безопасно

В PostgreSQL безопаснее идти через проверяемое ограничение, затем валидировать и только потом ставить NOT NULL.

-- Миграция 004_contract_not_null.sql (PostgreSQL)
ALTER TABLE public.users
  ADD CONSTRAINT users_username_not_null CHECK (username IS NOT NULL) NOT VALID;

ALTER TABLE public.users VALIDATE CONSTRAINT users_username_not_null;

-- Опционально, после успешной валидации
ALTER TABLE public.users ALTER COLUMN username SET NOT NULL;

Шаг 6. Contract: удалить старую колонку и триггеры

Удаляем только когда уверены, что приложение 100% читает/пишет новую колонку.

-- Миграция 005_contract_drop_login.sql (PostgreSQL)
DROP TRIGGER IF EXISTS trg_users_sync_username ON public.users;
DROP FUNCTION IF EXISTS public.users_sync_username();

ALTER TABLE public.users DROP COLUMN login;
DROP INDEX IF EXISTS idx_users_login;

Типовые сценарии: рецепты

Добавить колонку с NOT NULL и DEFAULT — безопасно

Опасно: ALTER TABLE ... ADD COLUMN col TEXT NOT NULL DEFAULT 'x' может переписать таблицу.

Безопасный путь в PostgreSQL:

ALTER TABLE public.orders ADD COLUMN status text; -- nullable

-- Бэкфилл малыми порциями: см. шаблон выше

ALTER TABLE public.orders
  ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE public.orders VALIDATE CONSTRAINT orders_status_nn;
ALTER TABLE public.orders ALTER COLUMN status SET NOT NULL;

-- DEFAULT можно добавить отдельно (не переписывает существующие строки)
ALTER TABLE public.orders ALTER COLUMN status SET DEFAULT 'new';

В MySQL используйте ALGORITHM=INPLACE, LOCK=NONE, либо gh-ost/pt-online-schema-change (ниже).

Переименование колонки

• Вместо моментального RENAME — добавить новую, бэкфилл, двойная запись, потом удалить старую.
• RENAME безопасен только если вы уверены, что все клиенты переключены одновременно (редко).

Изменение типа

• Расширяющая совместимость (например, int → bigint): добавить новую колонку нужного типа, бэкфилл с приведением, двойная запись.
• Сужение типа требует явной валидации и возможной коррекции данных до миграции.

Индексы

• PostgreSQL: CREATE INDEX CONCURRENTLY и DROP INDEX CONCURRENTLY (вне транзакции).
• MySQL: ALGORITHM=INPLACE, LOCK=NONE (если поддерживается) или gh-ost/pt-osc.

Онлайн‑DDL: PostgreSQL и MySQL

PostgreSQL

• Индексы: CREATE INDEX CONCURRENTLY ... — без долгих блокировок записи. Важно: не в транзакции.
• NOT VALID constraints: позволяют отделить создание ограничения от валидации данных.
• ALTER TABLE ... SET NOT NULL — требует сканирования, но обычно кратко блокирует метаданные; добавляйте после валидации CHECK.

Пример миграции Flyway без транзакции:

-- V2024_04_01__create_idx_users_username.sql
-- flyway:executeInTransaction=false
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_username ON public.users (username);

MySQL (InnoDB)

• Для простых изменений используйте ALGORITHM=INPLACE, LOCK=NONE:

ALTER TABLE users
  ADD COLUMN username VARCHAR(255) NULL,
  ALGORITHM=INPLACE,
  LOCK=NONE;

• Для «тяжёлых» изменений — gh-ost или pt-online-schema-change. Пример gh-ost:

GH_USER="app"; GH_PASS="secret"; GH_HOST="db:3306"; \
  gh-ost \
    --host="db" \
    --port=3306 \
    --user="$GH_USER" \
    --password="$GH_PASS" \
    --database="app" \
    --table="users" \
    --alter="ADD COLUMN username VARCHAR(255) NULL" \
    --max-load=Threads_running=25 \
    --critical-load=Threads_running=100 \
    --chunk-size=1000 \
    --approve-renamed-columns \
    --cut-over=default \
    --exact-rowcount \
    --allow-on-master \
    --execute

pt-online-schema-change (Percona) аналогично создаёт «теневую» таблицу и копирует данные без простоя.

Большой бэкфилл без нагрузки на прод

Принципы:

• Идём порциями с паузами.
• Сортируем по первичному ключу (или используем ctid в PostgreSQL) — минимизируем блокировки.
• Ограничиваем параллелизм.
• Следим за лагом репликации и откатываемся при его росте.

PostgreSQL, бэкфилл с блокировкой строк «по чуть‑чуть»:

DO $$
DECLARE
  rows_updated integer := 0;
BEGIN
  LOOP
    WITH cte AS (
      SELECT ctid FROM public.orders WHERE new_col IS NULL LIMIT 2000
    )
    UPDATE public.orders o
      SET new_col = 'value'
      FROM cte
     WHERE o.ctid = cte.ctid;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

Для MySQL — батчевый UPDATE с LIMIT и паузами, контролируя Seconds_Behind_Master.

Порядок релизов приложения и фичефлаг для чтений/записей

• Релиз 1: приложение пишет и в старую, и в новую колонку. Читает из старой.
• Миграции expand + бэкфилл + индексы.
• Включение фичефлага: читаем из новой колонки (поэтапно).
• Валидация: сравнение значений (выборочная проверка), мониторинг ошибок.
• Релиз 2: приложение перестаёт писать в старую колонку.
• Миграции contract: удаление старой колонки и артефактов.

План отката за минуты, а не часы

• Если после переключения чтений выросли ошибки — возвращаем фичефлаг в исходное положение (чтение из старой колонки), продолжая двойную запись.
• Если проблема в миграции expand (индекс, ограничение) — откатываем только проблемный шаг (DROP INDEX CONCURRENTLY, DROP CONSTRAINT) — это быстро.
• Резервные копии и точка восстановления: для критичных изменений держим свежий snapshot и WAL/бинлоги.

Пример отката в PostgreSQL:

-- Отключаем чтение из новой колонки на уровне приложения (фичефлаг)
-- Удаляем проблемный индекс без блокировки
DROP INDEX CONCURRENTLY IF EXISTS idx_users_username;

-- Отменяем NOT NULL через падение ограничения
ALTER TABLE public.users DROP CONSTRAINT IF EXISTS users_username_not_null;

Инструменты: Flyway/Liquibase, gh-ost, pt-osc, pg_repack

• Flyway/Liquibase — управление версиями схемы, чёткий порядок миграций, запрет «ручных» правок.
• gh-ost/pt-online-schema-change — онлайн‑DDL в MySQL для тяжёлых изменений.
• pg_repack — перестройка таблиц и индексов без простоя (например, для сильной фрагментации).
• Планировщик (cron/Airflow) — запуск бэкфилла порциями вне пиков.

Чек‑лист перед продом

• Согласован ли план expand/contract по шагам и времени.
• Есть ли тест‑прогон миграций на копии прод‑данных (обезличенных).
• Пороговые значения метрик и человек «на кнопке» отката.
• Миграции с CONCURRENTLY помечены как «вне транзакции» в инструменте (Flyway/Liquibase).
• Бэкфилл — порционный, с паузами, с возможностью остановки.
• Реплики не отстают; есть запас по диску на индексы/временные объекты.
• Фичефлаг для переключения чтений включается постепенно.

Метрики и наблюдаемость при миграциях

• Время ответов p95/p99 на горячих эндпойнтах.
• Ошибки записи/чтения по сущностям (users/orders и т.д.).
• Нагрузка БД: CPU, IOPS, блокировки, долгие транзакции.
• Лаг репликации: pg_stat_replication.replay_lag, Seconds_Behind_Master.
• Прогресс бэкфилла: обработано N из M строк, скорость строк/сек.

Запрос для оценки блокировок в PostgreSQL:

SELECT pid, locktype, relation::regclass, mode, granted, query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT granted;

Лаг репликации в PostgreSQL:

SELECT
  application_name,
  state,
  pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)) AS replication_lag
FROM pg_stat_replication;

(В новых версиях используйте функции с WAL и LSN из вашей версии.)

Частые вопросы

• Можно ли сразу сделать RENAME? Можно, если все клиенты обновляются строго одновременно и вы уверены, что миграция микросервисов синхронна. На практике — почти никогда.
• А если данных мало? На малых объёмах допустимо упрощать, но держите план отката и следите за блокировками.
• Триггеры обязательны? Нет. Часто достаточно двойной записи на уровне приложения и бэкфилла. Триггеры удобны как «страховка» на период перехода.

Вывод

Безопасные миграции — это дисциплина небольших шагов, совместимости вперёд/назад и наблюдаемости. Стратегия expand/contract, онлайн‑DDL, порционный бэкфилл и готовый план отката позволяют менять схему без простоя и нервов. В итоге бизнес получает предсказуемые релизы, меньше инцидентов и стабильную скорость изменений.