Как безопасно менять структуру базы

Команды CREATE, ALTER и DROP меняют структуру базы: создают, изменяют и удаляют схемы, таблицы, столбцы и индексы. Эту часть SQL называют DDL — языком описания данных (Data Definition Language).

Файл с последовательностью таких изменений называется миграцией. В пустой учебной базе миграция обычно выполняется мгновенно. В рабочей базе с миллионами строк та же команда может надолго заблокировать запросы, перечитать таблицу или записать большой объём WAL.

#Первая таблица

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id bigint NOT NULL,
    external_id text NOT NULL,
    amount numeric(14, 2) NOT NULL CHECK (amount >= 0),
    issued_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    amount_minor bigint GENERATED ALWAYS AS ((amount * 100)::bigint) STORED,
    UNIQUE (org_id, external_id)
);

DEFAULT задаёт значение, если клиент его не передал. clock_timestamp() возвращает текущее время. Столбец amount_minor вычисляет сумму в минимальных единицах и хранит результат; вручную записать другое значение в него нельзя.

Схема billing разделяет имена объектов, но сама по себе не отделяет данные разных организаций и не выдаёт права. За это отвечают ограничения и роли.

#Почему изменение структуры может мешать запросам

Блокировка (lock) временно ограничивает другие действия над объектом. Например, ALTER TABLE часто требует блокировку ACCESS EXCLUSIVE, которая несовместима почти со всеми обращениями к таблице.

Даже быстрая команда может долго ждать старую транзакцию. Пока она ждёт, следующие запросы тоже способны выстроиться в очередь. Перед изменением полезно посмотреть активные сеансы и ограничить время ожидания:

SELECT pid, usename, state, xact_start, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

SET lock_timeout = '2s';
SET statement_timeout = '15min';

lock_timeout отменяет команду, если она слишком долго ждёт блокировку. statement_timeout ограничивает всё время выполнения. Конкретные значения зависят от нагрузки; смысл ограничений — остановиться раньше, чем очередь запросов станет опасной.

#Изменение в несколько совместимых шагов

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

Расширение (expand) добавляет новое необязательное поле. Старый код продолжает работать.
Заполнение (backfill) переносит данные небольшими порциями.
Проверка (validate) убеждается, что старые и новые строки подходят под правило.
Сужение договора (contract) делает поле обязательным после обновления всех приложений.

-- 1. Расширение
ALTER TABLE app_users ADD COLUMN normalized_email text;

-- 2. Одна возобновляемая порция заполнения
UPDATE app_users
SET normalized_email = lower(btrim(email))
WHERE normalized_email IS NULL
  AND id >= 1 AND id < 10001;

-- 3. Новые строки уже проверяются, старые проверим отдельно
ALTER TABLE app_users
ADD CONSTRAINT app_users_normalized_email_present
CHECK (normalized_email IS NOT NULL) NOT VALID;

ALTER TABLE app_users
VALIDATE CONSTRAINT app_users_normalized_email_present;

-- 4. Поле становится обязательным
ALTER TABLE app_users ALTER COLUMN normalized_email SET NOT NULL;

Порция должна быть небольшой и повторяемой. Если процесс упал после нескольких порций, следующий запуск снова найдёт строки с normalized_email IS NULL и продолжит. Один огромный UPDATE создаёт долгую транзакцию, много старых версий строк и большой объём WAL.

Во время заполнения следят за задержкой запросов, местом на диске, объёмом WAL, отставанием реплики и работой VACUUM.

#Уникальный индекс без долгой блокировки записи

Перед новой уникальностью сначала найдите повторы. Затем индекс большой рабочей таблицы можно строить в конкурентном режиме:

CREATE UNIQUE INDEX CONCURRENTLY app_users_org_email_norm_idx
ON app_users (org_id, normalized_email);

CONCURRENTLY позволяет обычным изменениям продолжаться, но индекс строится дольше и делает несколько проходов. Команду нельзя выполнять внутри блока BEGIN ... COMMIT.

После ошибки может остаться недействительный индекс. Проверить его состояние можно так:

SELECT i.indisready, i.indisvalid
FROM pg_index AS i
WHERE i.indexrelid = 'app_users_org_email_norm_idx'::regclass;

indisvalid = false означает, что планировщик не может считать индекс готовым для обычных запросов. Его причину выясняют, затем индекс контролируемо удаляют или строят заново.

#Когда PostgreSQL перечитывает всю таблицу

Сканирование (scan) — чтение строк таблицы. Переписывание таблицы (table rewrite) — создание нового физического представления всех её строк. Переписывание заметно дороже: требует времени, диска и WAL.

PostgreSQL 18 умеет быстро добавить столбец с постоянным значением по умолчанию без немедленного переписывания каждой строки:

ALTER TABLE invoices
ADD COLUMN source text NOT NULL DEFAULT 'legacy';

Но это не правило для любого ALTER TABLE. Изменение типа или выражение, которое даёт разный результат при каждом вызове, может потребовать переписывания. Опасную операцию проверяют на копии той же версии PostgreSQL и сопоставимого размера.

#Удаление и переименование

DROP ... CASCADE удаляет объект и все зависящие от него объекты. В учебной схеме это удобно, а в рабочей базе может снести неожиданное представление или функцию. Сначала получите список зависимостей и переключите пользователей.

Переименование тоже не гарантирует совместимость. PostgreSQL обновит известные ему зависимости, но строки SQL в приложениях, настройки ORM и внешние отчёты могут сохранить старое имя. Для долгого перехода безопаснее некоторое время поддерживать старый и новый договор одновременно.

#План запуска миграции

До запуска запишите:

на какой копии и версии PostgreSQL проверена команда;
какую блокировку она берёт и будет ли читать или переписывать таблицу;
совместимы ли старая и новая версии приложения с промежуточным состоянием;
при какой задержке, очереди блокировок или нехватке диска нужно остановиться;
какой шаг отменяется через ROLLBACK, а какой требует новой миграции;
как продолжить после сбоя.

После запуска проверьте состояние ограничений и индексов, число незаполненных строк, ошибки приложения и скорость важных запросов.

#Практика

В лаборатории 06 вы добавите столбец, заполните его, проведёте ограничение через NOT VALID и VALIDATE, а затем сделаете поле обязательным.

Подробнее: определение данных, ALTER TABLE, CREATE INDEX.