Распространённые ошибки при проектировании реляционных баз данных

Большинство ошибок в реляционных схемах сводятся к одной: доверять приложению то, что должна гарантировать база данных. Внешний ключ, который вы «обрабатываете на уровне сервиса», правило уникальности, которое вы проверяете через SELECT перед INSERT, поле статуса, которое валидирует ORM, но не сам столбец — каждое из них представляет собой ограничение, вынесенное из движка, созданного для его соблюдения, в код, который выполняется лишь тогда, когда вы не забываете его вызвать. Результат — данные, которые схема никогда бы не допустила: осиротевшие строки, дублирующиеся аккаунты, отрицательные цены, временны́е метки, которые никто не может интерпретировать. В этом руководстве рассматриваются типичные ошибки проектирования, которые допускают разработчики приложений в Postgres и MySQL, объясняется, почему каждая из них приводит к проблемам, и предлагается решение — с небольшим фрагментом кода «до и после» для каждого случая. Примеры используют синтаксис современного Postgres 18 там, где это важно, однако принципы применимы к любому реляционному движку.

Корень большинства ошибок проектирования реляционных БД: правила в коде приложения, а не в схеме

Наиболее критичная ошибка — соблюдение правил работы с данными исключительно в коде приложения. Ограничение в сервисном слое защищает ровно один путь выполнения кода; ограничение в схеме защищает каждый путь — ваш API, фоновые задачи, ручную сессию psql, неудачную миграцию данных и следующий сервис, который кто-то напишет поверх той же базы данных. Когда правило существует только в приложении, первый же процесс записи, обходящий его, необратимо повреждает таблицу.

В этом и заключается ловушка современных ORM. Такие инструменты, как Prisma и Drizzle, с лёгкостью моделируют связь в коде приложения, не создавая при этом внешнего ключа на уровне базы данных, а @default или Zod-схема воспринимается как валидация. Но это не так. ORM, позволяющий объявить связь без внешнего ключа на уровне базы данных, не экономит ваши усилия — он откладывает ошибку целостности данных до продакшена, где её обнаружит не тестовый набор, а session replay, потому что сбой не порождает stack trace. Повреждённые данные проявляются иначе: отзыв, привязанный к несуществующему пользователю, или задублированный заказ — запутанный экран, который пользователь видит своими глазами.

Решение — опустить правило туда, где его нельзя обойти. Например, check-ограничения PostgreSQL отклоняют некорректные данные независимо от того, какой клиент их записал: чтобы гарантировать положительные цены на товары, можно использовать ограничение CHECK (price > 0) в определении таблицы.

-- До: ничто не препятствует отрицательному платежу или NULL-значению email
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint,                 -- нет FK: потенциальные осиротевшие строки
  amount  numeric,                -- нет CHECK: отрицательные значения разрешены
  email   text                    -- нет NOT NULL, нет UNIQUE
);

-- После: движок гарантирует соблюдение инвариантов
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  amount  numeric NOT NULL CHECK (amount > 0),
  email   text NOT NULL UNIQUE
);

Внешний ключ — это не налог на производительность, а единственное, что стоит между вами и осиротевшими строками, которые отображаются как отзыв несуществующего пользователя. Начиная с Postgres 18 вы также можете поэтапно добавлять ограничения на нагруженных таблицах: ALTER TABLE теперь поддерживает атрибут NOT VALID для ограничений NOT NULL, позволяя добавить ограничение без немедленного полного сканирования таблицы и отложить его валидацию с использованием более слабой блокировки.

Отказ от нормализации — или её избыточное применение

Нормализация — это практика хранения каждого факта ровно один раз. Первая нормальная форма (1НФ) означает отсутствие повторяющихся групп и многозначных полей; третья нормальная форма (3НФ) означает, что каждый неключевой столбец зависит только от ключа и ни от чего другого. Для прикладных схем 3НФ является разумным значением по умолчанию — см. документацию Postgres по определению данных для ознакомления с механикой. Два классических нарушения — это значения, разделённые запятыми, в одном столбце и повторяющиеся столбцы вида payment_1 … payment_12.

Хранение значений в одном столбце через запятую — это денормализация, о которой вы пожалеете при первом же WHERE … LIKE '%,42,%'; решение — дочерняя таблица или таблица связей, а не более хитрый строковый запрос.

-- До: теги как строка с разделителями, номера телефонов как столбцы
CREATE TABLE users (
  id     bigint PRIMARY KEY,
  tags   text,           -- 'admin,beta,vip' — не поддаётся запросам, без ограничений
  phone1 text, phone2 text, phone3 text   -- повторяющаяся группа, которая рано или поздно заканчивается
);

-- После: таблица связей для тегов, дочерняя таблица для телефонов
CREATE TABLE user_tags (
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  tag     text   NOT NULL,
  PRIMARY KEY (user_id, tag)
);
CREATE TABLE user_phones (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  phone   text   NOT NULL
);

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

Использование бизнес-полей в качестве первичных ключей

Не используйте бизнес-поля, такие как email, username или company_name, в качестве первичного ключа; применяйте суррогатный ключ и добавляйте отдельное ограничение UNIQUE на натуральное значение, чтобы переименование или смена адреса превращались в UPDATE, а не в миграцию. Бизнес-значения меняются, и когда меняется первичный ключ, каждый ссылающийся на него внешний ключ должен каскадно обновиться — значение, которое никогда не должно было быть идентификатором, становится причиной миграции на уровне всей схемы.

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

CREATE TABLE companies (
  id   uuid PRIMARY KEY DEFAULT uuidv7(),   -- стабильный суррогатный идентификатор
  name text NOT NULL UNIQUE                 -- натуральный ключ по-прежнему соблюдается
);

UUID или bigint: делаем выбор

Начиная с Postgres 18 расширение для временно́-упорядоченных идентификаторов больше не нужно: Postgres 18 добавляет функцию генерации UUID uuidv7(), значения которой поддерживают временну́ю сортировку, а также псевдоним uuidv4() для явной генерации UUID версии 4. UUIDv7 — определённый в RFC 9562 (май 2024 года, который заменяет RFC 4122) — содержит временно́й префикс, поэтому новые строки добавляются в правую часть индекса, а не рассеиваются по нему, как случайные UUID v4. Обратите внимание, что для более старых версий Postgres функция gen_random_uuid() (v4) входит в ядро начиная с версии 13; расширение pgcrypto больше не требуется.

Используйте bigint когда идентификаторы остаются внутри одной базы данных, и uuidv7() когда идентификаторы генерируются на стороне клиента или в нескольких сервисах.

Отсутствие продуманной стратегии индексирования

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

Ловушка специфична для Postgres: он автоматически индексирует ссылаемую (родительскую) сторону внешнего ключа, но не ссылающийся (дочерний) столбец. Поскольку индексирование ссылающихся столбцов не всегда необходимо и существует множество вариантов индексирования, объявление ограничения внешнего ключа не создаёт индекс на ссылающихся столбцах автоматически. (InnoDB в MySQL создаёт его автоматически — поэтому эта проблема специфична для Postgres.)

-- payments.user_id является FK, но не индексирован: каждое соединение и каждый
-- DELETE в таблице users выполняет последовательное сканирование таблицы payments
CREATE INDEX ON payments (user_id);

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

Одна таблица для множества задач

Единственная таблица, вынужденная моделировать множество предметных областей — обобщённая таблица «сущность–атрибут–значение» (EAV), или одна таблица notifications, хранящая оповещения, события аудита и системные журналы — жертвует ясностью схемы ради мнимой гибкости. Вы теряете типобезопасность, не можете применять значимые ограничения, и каждый запрос превращается в запутанный клубок фильтров и самосоединений. Используйте отдельные таблицы для каждой предметной области, чтобы у каждой были свои столбцы, типы и внешние ключи.

-- До: один мешок для всего, типизированный как text, без реальных ограничений
CREATE TABLE entity_attributes (
  entity_id bigint, attr_name text, attr_value text
);

-- После: настоящие таблицы с реальными столбцами и ограничениями
CREATE TABLE user_alerts  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                           user_id bigint NOT NULL REFERENCES users(id),
                           level text NOT NULL, body text);
CREATE TABLE audit_events (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                           actor_id bigint REFERENCES users(id),
                           action text NOT NULL,
                           occurred_at timestamptz NOT NULL DEFAULT now());

Если вам действительно нужны гибкие атрибуты, используйте типизированный столбец jsonb в реальной таблице — а не строково-типизированную EAV, которая сводит на нет все ограничения, предлагаемые движком.

Непоследовательное именование

Выберите одно соглашение об именовании и применяйте его везде. Для Postgres и большинства ORM это означает:

• snake_case (неэкранированные идентификаторы приводятся к нижнему регистру);
• единственное или множественное число для имён таблиц — выбрать один вариант и придерживаться его;
• created_at / updated_at для временны́х меток;
• никаких метаданных в именах (tbl_users, col_varchar_address);
• никаких пробелов и кавычек;
• никаких зарезервированных слов, таких как user, order или group, в качестве непосредственных идентификаторов.

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

Отношение к схеме как к чему-то окончательному

Схема — это живой код, а не одноразовый артефакт. Управляйте каждым изменением через версионированные, проверяемые и обратимые миграции — с помощью инструмента миграций вашего ORM (Prisma Migrate, Drizzle Kit) или отдельного средства запуска — чтобы история схемы воспроизводилась в любом окружении.

История также диктует подход к удалению. Предпочитайте мягкое удаление (столбец deleted_at timestamptz) жёсткому DELETE в тех случаях, когда другие строки ссылаются на запись: жёсткое удаление либо оставляет дочерние записи осиротевшими, либо при ON DELETE CASCADE незаметно уничтожает историю, которую впоследствии потребуется восстановить.

ALTER TABLE orders ADD COLUMN deleted_at timestamptz;
-- "Активные заказы" становятся фильтром, а не деструктивной операцией
CREATE VIEW active_orders AS SELECT * FROM orders WHERE deleted_at IS NULL;

Хранение дат и времени без часовых поясов

Храните временны́е метки как timestamptz, а не timestamp — столбец без часового пояса молча записывает то, что сервер считал «текущим временем», и эта неоднозначность становится неустранимой, как только ваши серверы оказываются в разных регионах. timestamptz хранит абсолютный момент времени (нормализованный к UTC) и отображает его в часовом поясе сессии; timestamp хранит показание часов без привязки к часовому поясу. См. документацию Postgres по типам даты/времени. Для расписаний и бронирований Postgres 18 также добавил временны́е ограничения: поддерживаются непересекающиеся ограничения PRIMARY KEY, UNIQUE и внешние ключи, задаваемые с помощью WITHOUT OVERLAPS и PERIOD. (Временны́е внешние ключи не поддерживают каскадные действия ON DELETE/ON UPDATE, поэтому не рассчитывайте на каскады в этом случае.)

Отсутствие документации и тестов схемы

Недокументированная и непротестированная схема — это ошибка, которая накапливается незаметно. Классический симптом — «магические» значения: status_code от 1 до 3, который ломается в тот день, когда кто-то добавляет 4, и при этом нет никаких записей о том, что означает каждое число. Конкретные меры:

• документируйте назначение прямо в базе данных с помощью COMMENT ON COLUMN;
• храните ER-диаграмму в репозитории;
• тестируйте миграции так же, как тестируете код — применяйте, откатывайте и проверяйте граничные и некорректные входные данные.

COMMENT ON COLUMN orders.status IS
  'enum: pending|paid|shipped|cancelled — см. app/orders/status.ts';

Ограничение CHECK (status IN (...)) в сочетании с комментарием превращает хрупкое целое число в самодокументируемый и самоконтролируемый столбец.

Заключение

Каждая из рассмотренных ошибок сводится к одному и тому же решению: сделать базу данных — а не приложение и не ORM — авторитетом в вопросе того, какие данные являются допустимыми. Добавляйте внешний ключ, NOT NULL, CHECK и UNIQUE до того, как напишете запрос, который рассчитывает на их наличие. Начните с таблицы, в которую чаще всего производится запись: перечислите инварианты, которые вы сейчас соблюдаете в коде, и перенесите каждый из них в схему, где его нельзя будет обойти.

Часто задаваемые вопросы

Когда следует использовать UUID в качестве первичного ключа вместо identity-столбца типа bigint?

Используйте bigint identity, когда идентификаторы генерируются внутри одной базы данных и никогда не создаются клиентами: он занимает 8 байт, индексируется последовательно и является наиболее быстрым вариантом. Используйте UUID, в частности uuidv7() в Postgres 18, когда идентификаторы генерируются на стороне клиента или в нескольких сервисах и требуется глобальная уникальность. UUIDv7 сохраняет близкую к последовательной локальность индекса, поскольку содержит временно́й префикс, в отличие от случайного UUIDv4.

Создаёт ли объявление внешнего ключа в ORM ограничение на уровне базы данных?

Не обязательно. Такие инструменты, как Prisma и Drizzle, могут моделировать связь в коде приложения, не создавая ограничения FOREIGN KEY на уровне базы данных, — в результате связь существует только в понимании ORM, но не в движке. Без ограничения на уровне базы данных фоновая задача, ручной запрос или другой сервис могут создать осиротевшие строки, которые ORM никогда не предвидел. Убедитесь, что ваша миграция действительно генерирует конструкцию REFERENCES, а не полагайтесь только на объявление связи.

В чём разница между timestamp и timestamptz в PostgreSQL?

timestamptz хранит абсолютный момент времени, нормализованный к UTC, и отображает его в часовом поясе сессии, тогда как timestamp хранит показание часов без привязки к часовому поясу. Обычный timestamp молча записывает то, что сервер считал локальным временем, и эта неоднозначность становится неустранимой, как только серверы оказываются в разных регионах. Используйте timestamptz для дат и времени в приложении, чтобы значение всегда соответствовало одному однозначному моменту времени независимо от того, где оно считывается.

Нужно ли по-прежнему расширение pgcrypto или uuid-ossp для генерации UUID в Postgres?

Нет. Функция gen_random_uuid(), генерирующая UUID версии 4, входит в ядро PostgreSQL начиная с версии 13, поэтому никакое расширение не требуется. В документации PG18 по pgcrypto собственная функция gen_random_uuid() этого расширения помечена как устаревшая, поскольку она вызывает одноимённую функцию ядра. Начиная с Postgres 18 вы также получаете встроенные функции uuidv7() для временно́-упорядоченных идентификаторов и псевдоним uuidv4() — без установки каких-либо расширений.