Проверка PostgreSQL-схемы перед первой миграцией

ИИ-конструктор может сгенерировать корректный PostgreSQL и при этом неверно понять базу данных. Синтаксис - простая часть. Опасны правдоподобные ошибки: необязательная связь становится обязательной, строка статуса получает неполное CHECK-ограничение, удаление каскадно затрагивает записи, которые должны сохраниться, или миграция пересоздает таблицу и незаметно теряет столбец.

Поэтому при проверке PostgreSQL-схемы нужно отдельно оценивать смысл, поведение миграции и восстановление. Я утверждаю выведенную схему, только когда она выдержала проверку на известном наборе данных, явных инвариантах, характерных запросах, анализе разрушительных изменений и репетиции восстановления. Пока хотя бы одного пункта нет, миграция остается предложением.

Первая миграция заслуживает такой проверки, даже если рабочая база пуста. Ранние ошибки схемы быстро закрепляются: от них начинают зависеть код приложения, начальные данные, отчеты и последующие миграции. Пятнадцать минут проверки перед первым запуском обычно обходятся дешевле, чем объяснение через полгода, почему два разных понятия хранятся в одном допускающем null текстовом столбце.

Выведенная схема - недоверенная спецификация

Считайте выведенную схему черновиком спецификации, а не исполнимой истиной. Конструктор видел запросы, примеры экранов, импортированные записи или сгенерированный код приложения. Он не видел всех бизнес-исключений, правил хранения, массовых импортов, исправлений службой поддержки и неудачных платежей, которые однажды окажутся в базе.

Сначала разделите три вопроса, которые команды часто смешивают. Корректность схемы отвечает, моделируют ли таблицы и ограничения предметную область. Безопасность миграции отвечает, сохраняют ли предлагаемые операции существующие данные и доступность базы во время работы. Готовность к восстановлению отвечает, сможете ли вы вернуться к известному состоянию после частичного или семантически неверного изменения. Успех в одном почти ничего не говорит о двух других.

CREATE TABLE может описывать нужную итоговую структуру, но прийти к ней небезопасными операциями. Допустим, конструктор меняет customer_name text на customer_id bigint. Итоговый внешний ключ может быть разумным, но миграция, которая удаляет столбец имени до сопоставления исторических имен с клиентами, уничтожает единственное доказательство для этого сопоставления. Проверка схемы одобряет пункт назначения, проверка миграции изучает путь.

Прочитайте предложенную модель вслух языком предметной области. Скажите: каждый счет относится ровно к одному юридическому клиенту, а не: invoices.customer_id ссылается на customers.id. Первое утверждение вызывает полезные возражения: черновик может появиться до выбора клиента, импортированный счет может ссылаться на архивного клиента, а в юридическом документе имя клиента на момент выпуска должно оставаться неизменным. SQL-термины способны скрыть эти разногласия.

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

Известные записи выявляют ошибочное сопоставление таблиц

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

До запуска DDL составьте матрицу сопоставления. Одна строка матрицы должна проводить каждое исходное понятие через предлагаемое назначение и фиксировать ожидаемое количество или значение. Для приложения заказов артефакт может выглядеть так:

Так вы поймаете ошибки сопоставления таблиц до того, как детали ограничений отвлекут внимание. ИИ-конструкторы часто нормализуют повторяющиеся объекты в отдельные таблицы, и это обычно разумно, но повторение не доказывает идентичность. Два адреса доставки с одинаковым текстом могут быть историческими снимками, а не ссылками на одну редактируемую строку адреса. При слиянии последующее изменение адреса перепишет историю.

Встречается и обратная ошибка. Конструктор может копировать поля клиента в каждый заказ, потому что экран показывает их рядом. Одни значения принадлежат клиенту, другие должны оставаться снимком заказа. В правильной схеме могут быть и customer_id, и поля выпущенного документа, например billing_name. Если назвать это дублированием и удалить одну сторону, вы потеряете либо актуальную идентичность, либо историческую правду.

Загрузите известный набор в одноразовую базу тем же путем импорта или начального заполнения, которым воспользуется приложение. Затем напишите проверки фактов, а не только количества строк:

SELECT
    (SELECT count(*) FROM orders WHERE external_id = 'ORDER-A') AS order_a,
    (SELECT count(*) FROM order_items i
       JOIN orders o ON o.id = i.order_id
      WHERE o.external_id = 'ORDER-A') AS order_a_items,
    (SELECT account_id IS NULL FROM orders
      WHERE external_id = 'ORDER-B') AS order_b_unassigned;

Ожидаемая форма успешного результата должна быть явной:

 order_a | order_a_items | order_b_unassigned
---------+---------------+---------------------
       1 |             2 | t

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

Ограничения должны выражать истины предметной области

Ограничение базы данных должно отклонять состояние, которое всегда недопустимо, независимо от экрана, API, импорта или скрипта исправления, записывающего строку. Если у правила есть исключения или оно зависит от изменчивых внешних фактов, попытка втиснуть его в простое ограничение часто приводит к блокировке работы или нечестным данным.

Первичные ключи идентифицируют строки, но не дают осмысленную бизнес-идентичность автоматически. Внутренний bigint ID может существовать рядом с уникальным номером заказа в рамках арендатора. Если бизнес утверждает, что номера заказов уникальны для каждого арендатора, правило выражает UNIQUE (tenant_id, order_number). Глобальное уникальное ограничение отклонит законные записи, а отсутствие ограничения допустит неоднозначность при повторах.

CHECK-ограничения подходят для неизменных фактов строки, например quantity > 0 или finished_at >= started_at. В руководстве PostgreSQL сказано, что база считает выражение CHECK неизменным на протяжении жизни ограничения. Поэтому CHECK, вызывающий функцию, чье поведение затем меняется, может оставить старые строки нарушающими видимое правило. Для неизменной истины используйте фиксированное выражение. Меняющуюся политику, например текущий список разрешенных значений, которым управляют администраторы, храните в связанной таблице или в логике приложения.

К ограничениям статусов, созданным генератором, нужно относиться с подозрением. Конструктор может изучить текущие примеры и выдать:

status text NOT NULL
    CHECK (status IN ('draft', 'active', 'closed'))

Это правильно, только если перечислены все устойчивые состояния. Спросите о неудачных, отмененных, приостановленных, импортированных и неизвестных старых записях. Если машина состояний еще меняется, таблица справочника делает добавления явными, но не заменяет проверку переходов. Строка, которой разрешено содержать closed, ничего не говорит о допустимости прямого перехода из draft в closed.

Используйте уникальность осознанно. PostgreSQL реализует уникальное ограничение уникальным B-tree индексом, но частичный уникальный индекс выражает другое правило. При мягком удалении уникальность часто нужна только среди активных строк:

CREATE UNIQUE INDEX users_tenant_email_live_uq
    ON users (tenant_id, lower(email))
    WHERE deleted_at IS NULL;

Это не то же самое, что UNIQUE (tenant_id, email, deleted_at). PostgreSQL обрабатывает NULL по своим правилам уникальности, а добавление времени удаления меняет обеспечиваемую идентичность. Проверяйте точные случаи дубликатов на подготовленных примерах, а не выводите поведение из списка столбцов.

Допустимость null - бизнес-решение

Делайте столбец NOT NULL, только когда предметная область требует значение для каждой законной строки и каждый путь записи может его передать. Дизайн экрана - слабое доказательство. Обязательное поле в текущей форме ничего не говорит об импортах, черновиках, системных строках или исторических записях.

Отдельно проверьте четыре состояния: источник не передал поле, источник явно передал null, источник передал пустое значение и источник передал осмысленное значение. JSON API, формы, CSV-импорт и PostgreSQL могут обрабатывать их по-разному. Если приложение сводит все четыре состояния к одному до вставки, проверка схемы должна показать это решение, а не делать вид, что его приняла база.

Значения по умолчанию требуют такого же внимания. Значение по умолчанию подставляется, когда INSERT не передает столбец: оно не исправляет явный NULL и не доказывает правдивость значения. country_code DEFAULT 'US' опасен, если страна может быть неизвестна. В строке появляется уверенная ложь, которой могут доверять отчеты и логика соответствия требованиям.

Распространенная сгенерированная миграция добавляет обязательный столбец одной командой:

ALTER TABLE customers
    ADD COLUMN account_type text NOT NULL DEFAULT 'standard';

Команда может выполниться, но каждый исторический клиент без доказательств станет стандартным. Безопаснее добавить допускающий null столбец, вывести значения из известных данных, измерить неразрешенные строки, запретить новые пропуски при записях приложения и лишь затем добавить NOT NULL, если это поддерживает предметная область. Если неизвестность остается допустимой, сохраните NULL и определите, как его показывают запросы и интерфейсы.

PostgreSQL дает полезное разделение для некоторых ограничений. CHECK или внешний ключ можно добавить как NOT VALID: при создании это не проверяет все существующие строки, а позднее ограничение проверяют через VALIDATE CONSTRAINT. Руководство описывает это как способ отложить первоначальное сканирование таблицы. Это не разрешение игнорировать старые нарушения: новые записи уже проверяются, а проверка все равно должна пройти до утверждения.

Перед ужесточением допустимости null выполните запрос распределения, который покажет реальные категории:

SELECT
    count(*) AS total,
    count(*) FILTER (WHERE account_type IS NULL) AS nulls,
    count(*) FILTER (WHERE account_type = '') AS empty_strings,
    count(*) FILTER (WHERE account_type NOT IN
        ('standard', 'partner', 'internal')) AS unexpected
FROM customers;

После миграции сгенерированное значение по умолчанию может сделать этот запрос чистым. Выполните его и до заполнения данных, сохранив результат. Иначе исчезнут доказательства, позволяющие отличить выведенные значения от выдуманных.

Индексы должны отвечать наблюдаемым путям доступа

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

Начните с запросов, которые реально отправляет сгенерированное приложение. Зафиксируйте столбцы фильтрации, границу арендатора, столбцы соединения, сортировку и ожидаемый размер результата. Для страницы недавних заказов форма запроса важнее диаграммы таблицы:

SELECT id, order_number, status, created_at
FROM orders
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Индекс только по tenant_id все еще может просмотреть много строк арендатора и отсортировать их. Индекс по (tenant_id, status, created_at DESC) точнее соответствует этому пути доступа. Порядок столбцов - не конкурс популярности: он следует условиям равенства, диапазонам, сортировке и избирательности реального запроса.

Запускайте EXPLAIN (ANALYZE, BUFFERS) на репрезентативных данных, но не считайте небольшой пример доказательством производительности. PostgreSQL может обоснованно предпочесть последовательное сканирование маленькой таблицы. Проверка должна подтвердить существование нужного индекса и то, что репетиция с размером, близким к рабочему, дает планировщику реалистичный выбор. Никогда не отключайте последовательное сканирование, чтобы искусственно получить сканирование индекса для утверждения.

Внешние ключи создают еще один типичный сюрприз: PostgreSQL индексирует первичные или уникальные столбцы, на которые ссылаются, но не создает автоматически индекс на дочерних столбцах, которые ссылаются. Поэтому при удалении или обновлении родителя база может сканировать дочернюю таблицу, чтобы проверить ссылки. Соединения от дочерней таблицы к родительской также могут нуждаться в этом индексе. Оценивайте каждую связь по ожидаемым чтениям и изменениям родителя.

Отклоняйте дублирующие и неиспользуемые индексы в первоначальном предложении. (tenant_id, status) может быть избыточен при наличии подходящего (tenant_id, status, created_at), хотя детали нагрузки могут изменить оценку. Сравнивайте определения, а не названия. ИИ-конструкторы часто создают один индекс на функцию и не замечают, что нескольким функциям нужны одинаковые ведущие столбцы.

Для существующих нагруженных баз помните: CREATE INDEX CONCURRENTLY нельзя запускать внутри блока транзакции, он требует больше работы и после ошибки может оставить недействительный индекс. Руководство PostgreSQL описывает эти эксплуатационные отличия. Фреймворку миграций, который оборачивает каждую миграцию транзакцией, нужны явное исключение и процедура очистки, а не надежда на замену одного ключевого слова.

Внешним ключам нужны владение и правила удаления

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

Рассмотрите projects.owner_user_id, invoices.customer_id и audit_events.actor_user_id. У проекта может смениться владелец. Счет может быть обязан сохраниться после закрытия аккаунта клиента. Событие аудита может хранить прежний идентификатор участника даже после удаления данных об учетной записи. Применить ON DELETE CASCADE ко всем трем только потому, что они ссылаются на users, означало бы зафиксировать разрушительную выдумку.

Используйте CASCADE, когда дочерняя запись не имеет смысла без родительской и удаление родителя действительно означает удаление всего агрегата. Позиции заказа часто подходят. Платежи, выданные документы, импорты, журналы и доказательства модерации обычно не подходят. Для них лучше подойдут запрет удаления, архивирование, управляемая анонимизация или допускающая null ссылка вместе с сохраненными полями снимка.

SET NULL тоже требует смысловой проверки. Он сохраняет дочернюю строку, но стирает прямую связь. Если сотрудникам позже потребуется объяснить, какой аккаунт создал отчет, null-ссылки может не хватить. Неидентифицирующий исторический токен или снимок может сохранить подотчетность без хранения всех персональных данных, но точный выбор хранения задает политика продукта, а не догадка ИИ.

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

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

После применения миграции к репетиционной базе проверьте каталог:

SELECT
    conname,
    contype,
    convalidated,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
ORDER BY conname;

Типичный результат выглядит так:

       conname        | contype | convalidated | definition
----------------------+---------+--------------+---------------------------------------
 orders_pkey          | p       | t            | PRIMARY KEY (id)
 orders_customer_fk   | f       | t            | FOREIGN KEY (customer_id) REFERENCES customers(id)
 orders_total_check   | c       | t            | CHECK ((total_cents >= 0))

Сравните возвращенные определения с утвержденными правилами владения. Успех миграции сам по себе не покажет отсутствующее действие, неожиданную отсрочку или непроверенное ограничение.

Разрушительные изменения скрываются в разумном SQL

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

Переименование и удаление различаются в работе, даже если итоговая схема выглядит одинаково. Если surname становится family_name, переименование точнее сохраняет данные и зависимости. Удаление старого столбца и добавление нового дают ту же диаграмму, но очищают каждое значение. Сгенерированные миграции часто выводят итоговое состояние, не понимая непрерывности.

Для изменений типов нужны примеры преобразования и случаи отклонения. Превращение текстовых идентификаторов в целые числа может убрать ведущие нули или отклонить смешанные идентификаторы. Уменьшение числовой точности может округлить значения. Преобразование временных меток требует явного допущения о часовом поясе. До изменения столбца проверьте фактическое выражение USING на минимальных, максимальных, null, неверных и исторически необычных значениях.

Требуйте письменного обоснования для операций: удаления таблицы или столбца, изменения типа с потерей данных, замены заполненного столбца, добавления CASCADE, установки NOT NULL после сгенерированного заполнения и перестроения уникальности с другими столбцами. Проверяйте также сырой SQL внутри сгенерированных функций или обратных вызовов миграций. Текстовый поиск - начальный фильтр, а не вся проверка.

Один сценарий сбоя повторяется постоянно. В известных данных есть контакты с необязательными компаниями, но пример экрана показывает только деловые контакты. Конструктор делает contacts.company_id NOT NULL и вставляет сгенерированную компанию с именем Unknown для несопоставленных строк. Миграция проходит, количества совпадают, все внешние ключи проверены. Но данные неверны: индивидуальные контакты теперь выглядят принадлежащими компании, отчеты группируют несвязанных людей, а удаление заглушки может каскадно затронуть реальные контакты.

Исправление не в еще одном значении по умолчанию. Восстановите исходное состояние, сделайте связь допускающей null, переносите только подтвержденные данными сопоставления и добавьте проверку, что несопоставленное множество равно известным индивидуальным контактам. Поэтому семантические примеры должны фиксировать ожидаемые связи, а не только количество строк.

Инструменты diff схемы полезны, но я против утверждения только по diff. Этот совет популярен, потому что diff компактен и его легко проверить. В качестве единственного барьера он неверен: diff показывает структурное изменение, но не происхождение заполненных значений, границы транзакций, блокировки или правдивость данных после миграции.

Репетиция должна доказать результат и поведение при сбое

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

Используйте эту последовательность репетиции как артефакт релиза:

1. Восстановите набор данных до изменения в изолированной базе и зафиксируйте количество строк и семантические проверки.
2. Сохраните текущую схему, примените точный артефакт миграции и сохраните весь вывод с длительностью и границами транзакций.
3. Запустите проверки каталога, сопоставлений, отклонения ограничениями и характерные запросы приложения.
4. Сравните важные значения с сохраненными ожиданиями, включая категории несопоставленных записей и null.
5. Выполните описанный способ восстановления, затем снова запустите проверки до миграции на восстановленной базе.

Сохраните дамп только схемы до и после:

pg_dump --schema-only --no-owner --no-privileges \
  --dbname "$DATABASE_URL" > schema.sql

Проверьте таблицы, последовательности, индексы, ограничения, функции, триггеры, расширения и права, относящиеся к приложению. Diff модели ORM может не включать объекты базы, которые приложение не моделирует, особенно триггеры, индексы выражений, частичные индексы и функции, установленные вручную.

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

BEGIN;

INSERT INTO order_items (order_id, quantity, unit_price_cents)
VALUES (1001, 0, 2500);

ROLLBACK;

Ожидаемый вывод должен называть нарушенное ограничение, например:

ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"
DETAIL:  Failing row contains (..., 0, 2500, ...).

Не сравнивайте весь текст ошибки во всех окружениях, потому что детали могут отличаться. В автоматизированных тестах проверяйте SQLSTATE или имя ограничения, а читаемый вывод сохраняйте для проверяющего.

Измерьте блокировки и длительность на наборе, достаточно большом, чтобы быть похожим на предполагаемое развертывание. Операция, мгновенная на пятидесяти строках, может блокировать записи при проверке миллионов. Для первой пустой рабочей базы немедленный риск ниже, но репетиция все равно проверяет импортированные начальные данные и создает базовую точку для будущих изменений.

Для восстановления недостаточно обратной миграции

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

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

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

Снимки и откат транзакции решают разные сбои. Транзакция может отменить команды, если миграция завершилась ошибкой до commit, при условии что каждая операция участвует в транзакции. Снимок может вернуть всю базу к более раннему состоянию, но при этом потерять законные записи, созданные после снимка. Ни один механизм сам не согласует эти записи.

Если остается неопределенность, предпочитайте аддитивные изменения. Добавьте новый столбец или таблицу, скопируйте данные по измеримым правилам, при необходимости некоторое время запускайте оба пути кода под контролем, а старую структуру удаляйте только после проверки. Такой подход расширения и сокращения требует дополнительной работы, но сохраняет доказательства. Часто дешевле оставить переименованный старый столбец на один релиз, чем восстанавливать его по журналам.

Заранее запишите условия запуска восстановления. Например: неудачная семантическая проверка, неожиданные несопоставленные записи, недействительное ограничение, превышение миграцией утвержденного окна блокировки или ошибки приложения из-за несовместимости версий. Оператор не должен придумывать решение, пока пользователи ждут.

Зафиксируйте момент, после которого восстановление старой базы требует и восстановления старого приложения. Новое приложение может зависеть от нового столбца, а старое может не принимать новое значение enum или записывать старую форму. Для восстановления базы и приложения нужны совместимые версии.

Для утверждения нужны доказательства, а не уверенность

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

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

Когда приложение создается в Koder.ai, используйте режим планирования, чтобы записать решения о схеме до разрешения миграции, экспортируйте исходный код для проверки и считайте снимки и откат инструментами восстановления, которые все равно нужно отрепетировать на известном наборе данных.

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

Итоговый запрос на утверждение должен быть скучным. Каждый известный факт дает один ожидаемый результат, каждое ограничение отклоняет задуманный контрпример, у каждой разрушительной операции есть причина, а восстановление повторяет проверки до миграции. Если для оправдания расхождения доказательствам нужно убедительное объяснение, остановите миграцию. PostgreSQL точно применит схему, включая части, в которых конструктор ошибся.