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

Проектирование схемы базы данных — это не выбор между «правильно» и «быстро». Это постоянный компромисс между целостностью данных, стоимостью изменений и производительностью запросов. Нормализация помогает удерживать модель в форме, где изменения предсказуемы, а аномалии обновлений минимальны. Денормализация, в свою очередь, иногда превращает медленные запросы в быстрые за счёт устранения джойнов и сокращения чтений.

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

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

Что именно мы оптимизируем: целостность, изменения или чтение

Схема БД отвечает на три вопроса:

1. Как гарантировать корректность данных?
   Нормализация (вплоть до 3NF/BCNF на практике) снижает риск аномалий:

   • аномалия вставки,
   • аномалия обновления,
   • аномалия удаления.

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

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

Важный нюанс: денормализация — не «ускоритель джойнов» сам по себе. Часто узкое место не в джойнах, а в:

• плохих индексах,
• неверных фильтрах,
• неудачном порядке операций (например, сортировка без индекса),
• слишком больших выборках,
• лишних round-trip’ах,
• N+1 запросах на уровне приложения.

Поэтому правильный путь — сначала зафиксировать рабочие запросы и измерить их планы (EXPLAIN/EXPLAIN ANALYZE), а затем принимать решение по схеме.

Базовый кейс: каталог с товарами, категориями и ценами

Рассмотрим типичную витрину: список товаров с категорией и текущей ценой. В реальном мире цены меняются, категории могут обновляться, а товаров много.

Вариант 1: нормализованная схема (3NF-подход)

Таблицы:

• products — товары
• categories — категории
• product_categories — связь многие-ко-многим (если нужно)
• prices — история цен или текущие цены (как модель выберете)

Пример (упрощённо):

CREATE TABLE categories (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

CREATE TABLE product_categories (
  product_id BIGINT NOT NULL REFERENCES products(id),
  category_id BIGINT NOT NULL REFERENCES categories(id),
  PRIMARY KEY (product_id, category_id)
);

CREATE TABLE prices (
  product_id BIGINT NOT NULL REFERENCES products(id),
  currency CHAR(3) NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  price NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (product_id, currency, valid_from)
);

Запрос для карточки товара или списка:

• текущая цена — выбирать максимальный valid_from (или строку с флагом “current”).
• категория — через связь.

На практике для списка чаще всего нужно что-то вроде: товар + одна основная категория + текущая цена в USD.

Пример запроса:

SELECT
  p.id,
  p.name,
  c.name AS category_name,
  pr.price
FROM products p
JOIN product_categories pc ON pc.product_id = p.id
JOIN categories c ON c.id = pc.category_id
JOIN LATERAL (
  SELECT price
  FROM prices
  WHERE product_id = p.id
    AND currency = 'USD'
  ORDER BY valid_from DESC
  LIMIT 1
) pr ON TRUE
WHERE c.id = 42
ORDER BY p.id
LIMIT 50;

Плюсы:

• Цена хранится как отдельная сущность, можно вести историю.
• Категории — отдельно, обновления корректны.
• Логику «текущей цены» легко менять.

Минусы:

• Для витрины будет больше джойнов.
• Если это горячая страница с высокой частотой, нагрузка на prices и product_categories может стать заметной.

Индексы при нормализации

Схема сама по себе не гарантирует скорость. Для запроса выше критичны индексы:

CREATE INDEX ON product_categories (category_id, product_id);
CREATE INDEX ON prices (product_id, currency, valid_from DESC);

Обратите внимание: направление порядка (DESC) важно в некоторых СУБД (например, PostgreSQL) для поддержки оптимизации под ORDER BY ... DESC LIMIT 1.

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

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

Вариант 2: денормализация под чтение (витринная таблица)

Сделаем отдельную таблицу product_catalog_view (часто это называют materialized view, но можно и обычную таблицу). Идея: держать денормализованные поля, которые нужны в типовых запросах.

CREATE TABLE product_catalog_view (
  product_id BIGINT PRIMARY KEY,
  product_name TEXT NOT NULL,
  category_id BIGINT NOT NULL,
  category_name TEXT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  current_price NUMERIC(12,2) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT now()
);

Теперь запрос для списка будет проще:

SELECT
  product_id,
  product_name,
  category_name,
  current_price
FROM product_catalog_view
WHERE category_id = 42
ORDER BY product_id
LIMIT 50;

Что выиграли:

• Убрали джойны на чтении.
• Простой индекс под фильтрацию и порядок.

Индексы:

CREATE INDEX ON product_catalog_view (category_id, product_id);

Но где тут риск?

Риск в том, что денормализованные данные могут рассинхронизироваться:

• цена обновилась в prices, но не отразилась в product_catalog_view;
• категория переименована — а category_name остался старым;
• товар переместили в другую основную категорию — category_id не обновился.

Поэтому денормализация всегда требует механизма синхронизации:

• триггеры,
• фоновые задачи/ETL,
• событийная пересборка,
• обновление по расписанию (если допустима задержка).

Например, если допустима задержка в минуту (иногда да), можно обновлять витрину фоновым процессом. Если нужна строгая консистентность — триггеры или транзакционный подход.

Подводные камни: «запихнём всё в одну таблицу» почти всегда плохая идея

Денормализация оправдана, когда вы:

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

Если же сделать «одну мегатаблицу» с ценой, историей, категориями, статусами и т.д., вы получите:

• рост размера строки и индексов,
• больше времени на вставки/обновления (потому что нужно обновлять массу полей),
• больше шансов на рассинхронизацию при сложной логике,
• искажённые планы запросов: оптимизатор может выбрать не тот путь из-за статистики.

Важно: денормализация не означает «всё дублировать». Чаще это означает «дублировать только то, что действительно требуется для горячих чтений».

Кейc 2: пользователи, подписки и скидки — пример с агрегатами

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

• количество активных подписок,
• сумму скидок,
• ближайшую дату окончания подписки,
• возможно — флаг “имеет ли право на скидку”.

Нормализация: события и статусы

Модель:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE subscriptions (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status TEXT NOT NULL, -- 'active', 'canceled', ...
  ends_at TIMESTAMP NOT NULL,
  started_at TIMESTAMP NOT NULL
);

CREATE TABLE discounts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  type TEXT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  valid_until TIMESTAMP NOT NULL
);

Запрос: вывести пользователей и агрегаты.

SELECT
  u.id,
  COUNT(s.id) AS active_subscriptions,
  COALESCE(SUM(d.amount), 0) AS discounts_sum,
  MIN(s.ends_at) AS nearest_end
FROM users u
LEFT JOIN subscriptions s
  ON s.user_id = u.id
 AND s.status = 'active'
LEFT JOIN discounts d
  ON d.user_id = u.id
 AND d.valid_until >= now()
GROUP BY u.id
ORDER BY nearest_end NULLS LAST
LIMIT 50;

Почему это может стать медленным:

• COUNT, SUM, MIN на больших таблицах;
• большие группировки;
• соединения по нескольким таблицам.

Индексы здесь обязательны:

CREATE INDEX ON subscriptions (user_id, status, ends_at);
CREATE INDEX ON discounts (user_id, valid_until);

Но даже с индексами агрегаты могут оставаться дорогими под большой трафик.

Денормализация: витринная таблица агрегатов

Решение: хранить агрегированные значения в отдельной таблице, обновляя её по событию или периодически.

CREATE TABLE user_stats (
  user_id BIGINT PRIMARY KEY REFERENCES users(id),
  active_subscriptions INT NOT NULL DEFAULT 0,
  discounts_sum NUMERIC(12,2) NOT NULL DEFAULT 0,
  nearest_end TIMESTAMP NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT now()
);

Теперь запрос:

SELECT
  user_id,
  active_subscriptions,
  discounts_sum,
  nearest_end
FROM user_stats
ORDER BY nearest_end NULLS LAST
LIMIT 50;

Индексы:

CREATE INDEX ON user_stats (nearest_end);

Компромисс:

• чтение быстрое;
• обновления сложнее: при каждом изменении подписок/скидок нужно пересчитывать статистику (полностью или частично).

Если пересчёт дешёвый — всё отлично. Если события частые, лучше смотреть на:

• батчевую пересборку,
• триггеры, которые ставят задачу в очередь,
• incremental update (частичный пересчёт) вместо полного.

Как оценивать влияние на индексы и планы запросов

Схема — это не только таблицы и связи. Скорость почти всегда приходит из правильного сочетания:

1. Фильтры WHERE (что ограничивает набор)
2. Сортировки ORDER BY
3. Джойны (как соединяются строки)
4. Проекции (какие столбцы реально нужны)
5. Планировщик (что выберет оптимизатор)

Практика: сначала измерить, потом менять схему

1. Напишите запросы, которые реально выполняются чаще всего (и под нагрузкой).
2. Сделайте EXPLAIN (ANALYZE, BUFFERS) (или аналог в вашей СУБД).
3. Смотрите:

• какие узлы реально «дороги»,
• где происходят Seq Scan вместо Index Scan,
• растёт ли количество буферов/строк,
• используется ли нужная сортировка/лимитирование через индекс.

Пример: что меняется при денормализации

• В нормализованной схеме запрос часто делает несколько джойнов + сортировку.
• В денормализованной витрине запрос превращается в «одну таблицу + фильтр + сортировка», и оптимизатор получает шанс эффективно использовать индекс (category_id, product_id) или (nearest_end).

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

Критерии: когда нормализовать обязательно, а когда можно денормализовать

Ниже — практичные правила, которые помогают не делать лишнего.

Нормализовать, если…

1. Есть сложные сущности с независимой жизнью
   Цена, статусы подписки, история изменений — отдельные домены.

2. Нужна корректность при изменениях
   Когда консистентность критична, лучше один источник истины.

3. Есть множественные зависимости и многие-ко-многим
   product_categories, roles, tags — всё это редко стоит дублировать напрямую.

4. Обновления происходят часто и логика сложная
   Денормализация увеличит объём работы при каждом изменении.

Денормализовать, если…

1. Есть чёткий набор горячих чтений
   Например, каталоги, ленты, страницы профиля с небольшим временем ответа.

2. Функционально можно терпеть задержку
   Если витрина обновляется раз в минуту — вы выиграете, а бизнес не страдает.

3. Есть агрегаты, вычисление которых дорого
   Денормализация агрегатов часто оправдана.

4. Можно контролировать поддержание консистентности
   Например, триггеры/события/фоновые job’ы.

Типичные ошибки при денормализации

1) Денормализовать “ради индекса”, не проверив планы

Иногда кажется: «уберём джойн — будет быстрее». Но если в исходной схеме индексы хорошие, джойн мог выполняться эффективно. В итоге вы переплачиваете поддержкой витрины без прироста.

2) Дублировать слишком много данных

Если в витрине лежит «всё про всё», то индексы становятся тяжелее, обновления дольше, а иногда возрастает общая нагрузка.

3) Игнорировать частоту обновлений

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

• lazy-refresh,
• кэшах на уровне приложения/CDN,
• частичных пересчётах,
• компромиссной модели хранения.

4) Не задать правила консистентности

Нужно заранее определить:

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

Когда компромисс выглядит как “нормализация + витрины + ограничения”

У зрелых систем обычно такая архитектура:

1. Нормализованное ядро — для корректности и истории.
2. Витрины для чтения — денормализованные таблицы/материализованные представления.
3. Ограничения и контроль данных:
   • уникальные ключи,
   • внешние ключи (где возможно),
   • валидность времени,
   • механизмы обновления витрин.
4. Набор индексов строго под реальные запросы.

Если вы выбираете витрину, полезно документировать:

• откуда берутся поля,
• какие события её обновляют,
• какова типичная задержка,
• и как проверяется консистентность.

Мини-гайд: как действовать при проектировании “в реальности”

1. Соберите список основных запросов
   10–20 запросов, которые реально поддерживают бизнес-страницы.

2. Сформулируйте требования к консистентности
   Что должно быть строго верным, а что может быть «почти верно»?

3. Начните с нормализованной модели
   Это обычно проще проверить, проще тестировать и проще расширять.

4. Постройте индексы под запросы Не под таблицы “вообще”, а под WHERE/ORDER BY конкретных запросов.

5. Сравните планы до/после Если есть узкое место — смотрите EXPLAIN. Денормализация должна отвечать на конкретную проблему.

6. Если денормализуете — делайте это управляемо Выделяйте витрину, определяйте источник истины, продумывайте обновление и сбои.

Практический вывод: нормализация — фундамент, денормализация — инструмент

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

Главная метрика — не «сколько джойнов в запросе», а:

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

Если с SQL и базовыми принципами построения запросов у