
Простой при миграциях — это прямые потери: заказ не оформился, платёж не прошёл, клиент ушёл. Ночные окна замедляют вывод фич и выгорают команды. Хорошая новость: большинство изменений схемы в PostgreSQL можно делать онлайн — без пауз для пользователей и без нервов для разработчиков и админов.
Цель статьи — дать практику: как проектировать изменения, чем опасны разные DDL, какие есть безопасные приёмы, и показать готовые SQL/скрипты, которые можно использовать прямо сегодня.
Этот подход позволяет менять схему и код постепенно, сохраняя совместимость:
Важно: порядок релиза — сначала DB‑expand, потом код, затем DB‑cleanup.
От версии PostgreSQL зависит многое (11+ стал добрее к добавлению колонок с дефолтом).
Относительно безопасно (мгновенно или почти мгновенно):
Рискованные операции (могут блокировать/переписывать):
Ниже — конкретные сценарии. Для наглядности используем тестовую таблицу заказов.
-- Пример схемы для экспериментов
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()
);
Задача: добавить колонку 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. Особенности:
-- Правильно
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);
Принципы:
Пример простого бэкфилла на 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()
Перед запуском миграции:
Во время и после:
Проверка активных запросов и блокировок:
-- Долгие запросы
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
Так вы перестанете «покупать» новые фичи ночными окнами и рисками, а команда начнёт выкатывать чаще и спокойнее — без сюрпризов для пользователей и бизнеса.