• Зачем это бизнесу
• Что чаще всего ломает продакшен при миграциях
• Базовые принципы: расширяй — используй — сжимай (expand/contract)
• Какие изменения в PostgreSQL безопасны, а какие — нет
• Пошаговые рецепты без простоя: колонки, индексы, типы, переименования
• Бэкфиллы: как заполнять данные частями и не положить базу
• Приложение и трафик: двусовместимость кода и порядок релиза
• Репликация и бэкапы: как не уронить лаг и восстановление
• Чек‑листы: таймауты, мониторинг, защита от ошибок
• Полезные команды и готовые скрипты

Зачем это бизнесу

Простой при миграциях — это прямые потери: заказ не оформился, платёж не прошёл, клиент ушёл. Ночные окна замедляют вывод фич и выгорают команды. Хорошая новость: большинство изменений схемы в PostgreSQL можно делать онлайн — без пауз для пользователей и без нервов для разработчиков и админов.

Цель статьи — дать практику: как проектировать изменения, чем опасны разные DDL, какие есть безопасные приёмы, и показать готовые SQL/скрипты, которые можно использовать прямо сегодня.

Что чаще всего ломает продакшен при миграциях

• Долгие блокировки при DDL. Команды могут брать блокировку всей таблицы и держать её до завершения, стопоря записи и чтения.
• Переписывание таблицы. Некоторые ALTER TABLE перезаписывают весь объём данных — гигабайты, часы, огромный WAL и лаг на репликах.
• Тяжёлые индексы. Создание больших индексов без CONCURRENTLY блокирует таблицу и забивает диски.
• Долгие транзакции. Фоновые задачи/скрипты держат транзакции, из‑за чего VACUUM не чистит мусор, а миграции ждут вечность.
• Неподготовленный код. Релиз, который «не знает» о новой схеме, падает при первом запросе.

Базовые принципы: расширяй — используй — сжимай (expand/contract)

Этот подход позволяет менять схему и код постепенно, сохраняя совместимость:

1. Expand — добавить всё необходимое для новой логики (новые поля, индексы, таблицы), ничего не ломая.
2. Use — обновить приложение так, чтобы оно работало и со старой, и с новой схемой. По возможности — вести двойную запись/чтение.
3. Contract — когда вся нагрузка стабильно работает на новой схеме, удалить старые артефакты.

Важно: порядок релиза — сначала DB‑expand, потом код, затем DB‑cleanup.

Какие изменения в PostgreSQL безопасны, а какие — нет

От версии PostgreSQL зависит многое (11+ стал добрее к добавлению колонок с дефолтом).

Относительно безопасно (мгновенно или почти мгновенно):

• CREATE TABLE — новая таблица никого не блокирует.
• ALTER TABLE ADD COLUMN (NULLABLE) — добавление nullable‑колонки быстро.
• ALTER TABLE ADD COLUMN ... DEFAULT ... на PostgreSQL 11+ с неизменяемым значением (например, константа) — без переписывания таблицы.
• CREATE INDEX CONCURRENTLY — создание индекса без блокировки записи (но дольше по времени).
• ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT — позволяет проверять данные без долгих блокировок записи.
• REINDEX CONCURRENTLY (12+) — перестроение индекса без стопа.

Рискованные операции (могут блокировать/переписывать):

• ALTER TABLE RENAME COLUMN / TYPE — могут бить совместимость. Часто лучше через «добавить новое — скопировать — переключиться — удалить старое».
• ALTER TABLE SET NOT NULL — если нет соответствующего валидированного CHECK, может сканировать всю таблицу.
• CREATE INDEX (без CONCURRENTLY) — блокирует записи и чтения целиком.
• Большие UPDATE без батчинга — засоряют WAL, вызывают лаг реплик и раздувают таблицу.

Пошаговые рецепты без простоя

Ниже — конкретные сценарии. Для наглядности используем тестовую таблицу заказов.

-- Пример схемы для экспериментов
CREATE TABLE IF NOT EXISTS orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  email        TEXT NOT NULL,
  total_cents  INTEGER NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Добавление новой колонки с данными и последующим NOT NULL

Задача: добавить колонку email_domain, заполнить её, а потом потребовать NOT NULL без простоя.

-- 0) Рекомендуемые таймауты для сессии миграции
SET lock_timeout = '2s';
SET statement_timeout = '30min';
SET idle_in_transaction_session_timeout = '1min';

-- 1) Расширяем схему: добавляем колонку, ещё без ограничений
ALTER TABLE orders ADD COLUMN email_domain TEXT; -- быстро и без сканов

-- 2) Создаём индекс под будущие запросы (без блокировки записи)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_email_domain ON orders (email_domain);

Теперь бэкфилл — аккуратно, батчами. Вариант 1: чистый SQL‑батч с SKIP LOCKED.

-- Обновляем по 1000 строк за раз, пока есть NULL
DO $$
DECLARE
  _updated BIGINT := 0;
BEGIN
  LOOP
    WITH cte AS (
      SELECT id
      FROM orders
      WHERE email_domain IS NULL
      ORDER BY id
      LIMIT 1000
      FOR UPDATE SKIP LOCKED
    )
    UPDATE orders o
    SET email_domain = substring(o.email from '@(.+)$')
    FROM cte
    WHERE o.id = cte.id;

    GET DIAGNOSTICS _updated = ROW_COUNT;
    EXIT WHEN _updated = 0;

    PERFORM pg_sleep(0.1); -- чуть дышим, не давим на диск/реплики
  END LOOP;
END$$;

После бэкфилла добавим ограничение «не NULL», но без долгой блокировки:

-- 3) Сначала добавляем CHECK без валидации, чтобы не держать долгие блокировки
ALTER TABLE orders
  ADD CONSTRAINT orders_email_domain_nn CHECK (email_domain IS NOT NULL) NOT VALID;

-- 4) Валидируем ограничение отдельно: это читает таблицу, но не стопорит запись
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_domain_nn;

-- 5) Переводим колонку в NOT NULL — быстро, так как уже есть валидированный CHECK
ALTER TABLE orders ALTER COLUMN email_domain SET NOT NULL;

Переименование колонки без поломки кода

Нужно переименовать email в customer_email. Прямой RENAME может сломать код. Лучше сделать так:

-- Expand: добавляем новую колонку
ALTER TABLE orders ADD COLUMN customer_email TEXT;

-- Временный триггер для двойной записи (если есть прямые записи в таблицу)
CREATE OR REPLACE FUNCTION orders_email_dual_write() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.customer_email IS NULL AND NEW.email IS NOT NULL THEN
    NEW.customer_email := NEW.email;
  END IF;
  RETURN NEW;
END;$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_orders_dual_write ON orders;
CREATE TRIGGER trg_orders_dual_write
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_email_dual_write();

-- Бэкфилл (возможно, батчами, как выше)
UPDATE orders SET customer_email = email WHERE customer_email IS NULL;

-- Далее релизим код, который читает customer_email, пишет только туда
-- После стабилизации: Contract
ALTER TABLE orders DROP COLUMN email;
DROP TRIGGER IF EXISTS trg_orders_dual_write ON orders;

Изменение типа колонки без простоя

Меняем total_cents (INTEGER) на BIGINT. Прямой ALTER TYPE может подержать блокировку. Надёжнее — через новую колонку:

-- 1) Добавляем новую колонку
ALTER TABLE orders ADD COLUMN total_cents_big BIGINT;

-- 2) Бэкфилл батчами (или одним UPDATE, если данные небольшие)
UPDATE orders SET total_cents_big = total_cents WHERE total_cents_big IS NULL;

-- 3) Индексы/ограничения на новую колонку (по необходимости)
-- CREATE INDEX CONCURRENTLY ...

-- 4) Релизим код, который читает/пишет total_cents_big

-- 5) Меняем имена, если нужно сохранить старое название
ALTER TABLE orders RENAME COLUMN total_cents TO total_cents_old;
ALTER TABLE orders RENAME COLUMN total_cents_big TO total_cents;

-- 6) Удаляем старую колонку после стабилизации
ALTER TABLE orders DROP COLUMN total_cents_old;

Индексы без простоя и откаты

Создаём индекс только CONCURRENTLY. Особенности:

• Нельзя в транзакционном блоке с другими командами.
• Если упало — индекс останется INVALID; его нужно удалить и создать заново.

-- Правильно
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at ON orders (created_at);

-- Проверить статус
SELECT indexrelid::regclass AS index,
       indisvalid,
       indisready
FROM pg_index
WHERE indrelid = 'orders'::regclass;

-- Если невалидный — удаляем и создаём заново
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_created_at;
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

Бэкфиллы: как заполнять данные частями и не положить базу

Принципы:

• Малые батчи (500–5000 строк) + паузы 50–200 мс.
• ORDER BY первичным ключом — предсказуемые чтения.
• SKIP LOCKED для совместной работы с онлайновыми транзакциями.
• Следить за lag на репликах и замедлять бэкфилл при росте лагов.

Пример простого бэкфилла на Python (psycopg2) с регулировкой нагрузки:

import time
import psycopg2

DSN = "host=localhost dbname=app user=app password=secret connect_timeout=3"
BATCH = 1000
SLEEP = 0.1

def backfill_email_domain():
    with psycopg2.connect(DSN) as conn:
        conn.autocommit = True
        with conn.cursor() as cur:
            cur.execute("SET lock_timeout = '2s';")
            cur.execute("SET statement_timeout = '10min';")
            while True:
                cur.execute(
                    """
                    WITH cte AS (
                      SELECT id
                      FROM orders
                      WHERE email_domain IS NULL
                      ORDER BY id
                      LIMIT %s
                      FOR UPDATE SKIP LOCKED
                    )
                    UPDATE orders o
                    SET email_domain = substring(o.email from '@(.+)$')
                    FROM cte
                    WHERE o.id = cte.id
                    RETURNING o.id;
                    """,
                    (BATCH,)
                )
                updated = cur.rowcount
                if updated == 0:
                    break
                time.sleep(SLEEP)

if __name__ == "__main__":
    backfill_email_domain()

Приложение и трафик: двусовместимость кода и порядок релиза

• Порядок релиза: 1) DB‑expand, 2) код, совместимый со старым и новым, 3) DB‑cleanup.
• Двойная запись и обратная совместимость: при переименованиях/смене типа временно пишите в оба поля и читайте новое, пока не убедитесь, что все клиенты обновлены.
• Гладкая перезагрузка: грамотно настроенные readiness/liveness‑пробы, корректное закрытие соединений, короткие транзакции.
• Инструменты миграций: используйте Flyway/Liquibase/Goose и т.п. Ставьте таймауты команд, запрещайте транзакционные блоки для CREATE INDEX CONCURRENTLY.

Репликация и бэкапы: как не уронить лаг и восстановление

• Следите за pg_stat_replication — не допускайте большого лага. При росте лага замедляйте бэкфиллы.
• Учтите объём WAL. Большие UPDATE и индексы порождают много журналов — планируйте место на диске.
• Делайте бэкап перед рискованными миграциями. Для критичных изменений можно применять логическую репликацию или «теневую» таблицу с переключением.

Чек‑листы: таймауты, мониторинг, защита от ошибок

Перед запуском миграции:

• Установите lock_timeout/statement_timeout для сессии миграции.
• Проверьте, нет ли длинных транзакций: фоновые задачи, забытые psql.
• План отката: как вернуться на старую схему, если код уже выкатили? Часто — через временную двойную запись.
• Включите мониторинг: ошибки в логах, рост задержек, лаг реплик, размер WAL.
• Прогоните миграцию на копии продакшн‑данных (staging) — оцените время и нагрузку.

Во время и после:

• Логику бэкфилла держите перезапускаемой и идемпотентной.
• Следите за задержками запросов и ошибками в APM/логах.
• Чистите только после стабилизации: удаление столбцов/таблиц — последним шагом.

Полезные команды и готовые скрипты

Проверка активных запросов и блокировок:

-- Долгие запросы
SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 20;

-- Кто кого блокирует
SELECT bl.pid AS blocked_pid,
       ka.query AS blocking_query,
       now() - ka.query_start AS blocking_for,
       kl.pid AS blocking_pid,
       a.query AS blocked_query,
       now() - a.query_start AS blocked_for
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.DATABASE IS NOT DISTINCT FROM bl.DATABASE
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.page IS NOT DISTINCT FROM bl.page
  AND kl.tuple IS NOT DISTINCT FROM bl.tuple
  AND kl.virtualxid IS NOT DISTINCT FROM bl.virtualxid
  AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND kl.classid IS NOT DISTINCT FROM bl.classid
  AND kl.objid IS NOT DISTINCT FROM bl.objid
  AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid
  AND kl.pid <> bl.pid
JOIN pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

Безопасный шаблон миграционной сессии psql с таймаутами:

#!/usr/bin/env bash
set -euo pipefail
export PGHOST=localhost
export PGUSER=app
export PGDATABASE=app

psql <<'SQL'
SET lock_timeout = '2s';
SET statement_timeout = '30min';
SET idle_in_transaction_session_timeout = '1min';

-- Пример: безопасное добавление индекса
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer ON orders (customer_id);
SQL

Резюме

• Большинство миграций в PostgreSQL можно делать онлайн.
• Проектируйте изменения по схеме expand/use/contract.
• Используйте CONCURRENTLY, NOT VALID + VALIDATE, батчинги для бэкфиллов.
• Следите за таймаутами, длинными транзакциями и лагом реплик.
• Держите код двусовместимым на время перехода и убирайте старое только после стабилизации.

Так вы перестанете «покупать» новые фичи ночными окнами и рисками, а команда начнёт выкатывать чаще и спокойнее — без сюрпризов для пользователей и бизнеса.