Базы данных/Средний ~ 70 мин

Проектирование схем и нормализация

Схема базы данных - это не техническая формальность после написания Go-кода. Это модель предметной области, контракт между сервисами и набор инвариантов, которые переживут несколько версий приложения.

Хорошая схема:

выражает бизнес-сущности и связи между ними;
запрещает невозможные состояния;
поддерживает основные запросы без мучений;
допускает эволюцию через миграции;
не заставляет backend держать всю целостность "на честном слове".

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

ER-модель и ограничения

От предметной области к ER-модели

ER-модель описывает сущности, атрибуты и связи.

User 1 ─── * Order 1 ─── * OrderItem * ─── 1 Product
                |
                * Payment

Пример для учебной платформы:

Сущность	Что хранит
`users`	Аккаунты студентов, менторов и админов
`courses`	Курсы
`lessons`	Уроки внутри курса
`enrollments`	Запись пользователя на курс
`submissions`	Отправленные решения задач
`reviews`	Проверки и комментарии ментора

Сначала полезно писать модель словами:

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

Потом это превращается в таблицы, ключи и constraints.

Иллюстративная схема для RateDesk

Для RateDesk полезно сразу разделять разные типы фактов. "Курс" в разговоре может означать сырое значение от провайдера, выбранную сервисом ставку, правило расчета, заявку пользователя или audit-запись. Если смешать это в одной таблице, production-проблемы появятся быстро: невозможно объяснить расчет, трудно переиграть импорт, отчеты расходятся с текущим состоянием.

Упрощенная write model может выглядеть так:

rate_providers(id, code, title, enabled)
currency_pairs(id, base_currency, quote_currency)
provider_rate_quotes(id, provider_id, pair_id, bid, ask, received_at, provider_trace_id)
rate_rules(id, pair_id, priority, spread_bps, valid_from, valid_to, enabled)
rate_decisions(id, pair_id, quote_id, rule_id, final_rate, decided_at)
rate_decision_audit(id, decision_id, actor, reason, created_at)

Минимальный DDL-фрагмент:

CREATE TABLE currency_pairs (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    base_currency  char(3) NOT NULL,
    quote_currency char(3) NOT NULL,

    CHECK (base_currency <> quote_currency),
    UNIQUE (base_currency, quote_currency)
);

CREATE TABLE provider_rate_quotes (
    id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_id       bigint NOT NULL REFERENCES rate_providers(id),
    pair_id           bigint NOT NULL REFERENCES currency_pairs(id),
    bid               numeric(20, 8) NOT NULL CHECK (bid > 0),
    ask               numeric(20, 8) NOT NULL CHECK (ask > 0),
    received_at       timestamptz NOT NULL,
    provider_trace_id text NOT NULL,

    CHECK (ask >= bid),
    UNIQUE (provider_id, provider_trace_id)
);

CREATE TABLE rate_decisions (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pair_id     bigint NOT NULL REFERENCES currency_pairs(id),
    quote_id    bigint NOT NULL REFERENCES provider_rate_quotes(id),
    rule_id     bigint NOT NULL REFERENCES rate_rules(id),
    final_rate  numeric(20, 8) NOT NULL CHECK (final_rate > 0),
    decided_at  timestamptz NOT NULL DEFAULT now()
);

Что здесь важно именно как теория проектирования:

provider_rate_quotes хранит входящий факт от провайдера и не должна перетираться "текущим курсом";
rate_decisions хранит результат выбора и ссылку на quote/rule, чтобы расчет можно было объяснить после инцидента;
UNIQUE (provider_id, provider_trace_id) делает импорт идемпотентным при retry;
CHECK (ask >= bid) ловит невозможную рыночную форму до попадания в доменную логику;
денежные и курсовые значения не хранятся в float, потому что округление становится частью финансового бага;
если правило меняется во времени, valid_from/valid_to и audit важнее, чем обновление одной "активной" строки без истории.

Это не единственно правильная схема. На практике могут появиться tenancy, версии правил, manual override, outbox, read model для dashboard и retention policy для старых quote'ов. Но базовая эвристика остается: отдельный факт - отдельная таблица или явно названный snapshot.

Cardinality

Cardinality описывает, сколько объектов одной сущности может быть связано с объектом другой сущности.

Связь	Пример	Реализация
One-to-one	`user` - `user_profile`	FK с `UNIQUE` или PK на FK
One-to-many	`course` - `lessons`	FK на стороне many
Many-to-many	`users` - `courses`	Таблица связей

One-to-many:

CREATE TABLE lessons (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_id bigint NOT NULL REFERENCES courses(id),
    title     text NOT NULL,
    position  integer NOT NULL CHECK (position > 0),

    UNIQUE (course_id, position)
);

UNIQUE (course_id, position) говорит: внутри одного курса позиция урока уникальна. Но разные курсы могут иметь урок с position = 1.

Many-to-many:

CREATE TABLE enrollments (
    user_id   bigint NOT NULL REFERENCES users(id),
    course_id bigint NOT NULL REFERENCES courses(id),
    status    text NOT NULL CHECK (status IN ('active', 'paused', 'completed')),
    joined_at timestamptz NOT NULL DEFAULT now(),

    PRIMARY KEY (user_id, course_id)
);

Таблица связей почти всегда со временем получает собственные атрибуты: роль, статус, дату вступления, источник, прогресс. Поэтому даже если кажется, что связь "просто many-to-many", проектируйте ее как полноценный факт.

Primary, foreign, unique, check

Четыре constraint'а дают большую часть целостности.

CREATE TABLE submissions (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users(id),
    lesson_id   bigint NOT NULL REFERENCES lessons(id),
    status      text NOT NULL CHECK (status IN ('draft', 'submitted', 'accepted', 'rejected')),
    attempt_no  integer NOT NULL CHECK (attempt_no > 0),
    code        text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),

    UNIQUE (user_id, lesson_id, attempt_no)
);

Что здесь защищено:

submission не существует без пользователя и урока;
status не может быть произвольной строкой;
номер попытки положительный;
у пользователя не может быть двух попыток с одинаковым номером для одного урока.

Если это оставить только в Go-коде, любое место, которое пишет в таблицу в обход нужного service-метода, сможет нарушить модель.

Surrogate vs natural keys

Natural key берется из предметной области: email, номер паспорта, ISO-код страны, slug курса. Surrogate key создается системой: bigint identity, uuid, ulid.

Критерий	Natural key	Surrogate key
Смысл для бизнеса	Есть	Нет
Может измениться	Иногда да	Обычно нет
Размер индекса	Может быть большим	Обычно компактный
Удобство ссылок	Зависит от типа	Высокое
Риск неверной уникальности	Выше	Ниже

Практическое правило:

primary key часто делайте surrogate;
бизнес-уникальность фиксируйте отдельным UNIQUE;
не используйте изменяемый атрибут как primary key;
не храните только surrogate key, если есть важная domain uniqueness.

CREATE TABLE courses (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug        text NOT NULL UNIQUE,
    title       text NOT NULL,
    published   boolean NOT NULL DEFAULT false
);

Здесь id удобен для ссылок, а slug защищает публичный URL от дублей.

Индексы концептуально

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

CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

Такой индекс помогает запросу:

SELECT *
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

Ключевая идея composite index: порядок колонок важен. Индекс (user_id, created_at) хорошо работает, когда запрос ограничивает user_id, а потом сортирует или фильтрует по created_at. Он не так же полезен для запроса только по created_at.

Частые типы:

Индекс	Для чего
B-tree	Равенство, диапазоны, `ORDER BY`, `UNIQUE`
Hash	Равенство, используется реже
GIN	Массивы, JSONB, full-text search
GiST	Геоданные, ranges, специальные операторы
Partial index	Индекс только по части строк
Expression index	Индекс по выражению

Partial index:

CREATE INDEX idx_orders_unpaid
ON orders (created_at)
WHERE status = 'draft';

Expression index:

CREATE UNIQUE INDEX idx_users_email_lower
ON users (lower(email));

Теперь User@Example.com и user@example.com конфликтуют, если бизнес считает email case-insensitive.

Индекс не надо добавлять "на всякий случай". Каждый индекс:

обновляется при INSERT, UPDATE, DELETE;
занимает место;
может сбивать оптимизатор, если статистика плохая;
усложняет миграции.

Обычно индекс появляется из конкретного запроса и проверяется через EXPLAIN.

Нормализация

Зачем она нужна

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

Плохая таблица:

order_id	user_email	user_name	product_name	product_price	qty
1	a@example.com	Anna	Go Basics	5000	1
1	a@example.com	Anna	SQL Pro	7000	1
2	a@example.com	Anna	Go Basics	5000	2

Проблемы:

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

Нормализованная модель:

users(id, email, name)
products(id, name, current_price_cents)
orders(id, user_id, created_at)
order_items(order_id, product_id, price_cents, qty)

Важно: order_items.price_cents может намеренно дублировать цену продукта. Это не ошибка, если это цена на момент покупки. Данные нужно нормализовать по смыслу факта, а не механически.

Functional dependencies

Functional dependency: если значение A определяет значение B, пишут A -> B.

Пример:

user_id -> email, name
product_id -> product_name, current_price
order_id -> user_id, created_at
(order_id, product_id) -> qty, price_at_purchase

Если в таблице есть order_id, user_id, user_email, и order_id -> user_id, а user_id -> user_email, то user_email зависит от order_id транзитивно. Это сигнал, что email пользователя в заказе может быть лишним дублированием.

Но есть важная оговорка: иногда "дублирование" фиксирует исторический факт.

Поле	Нормализовать?	Почему
`orders.user_email` для текущего email	Обычно нет	Email можно взять через `users`
`orders.contact_email_at_checkout`	Возможно да	Это исторический email на момент заказа
`order_items.product_name_snapshot`	Возможно да	Название в чеке должно остаться прежним
`order_items.current_product_name`	Нет	Это не факт строки заказа

Нормальные формы

1NF

Первая нормальная форма: значения атомарны, нет повторяющихся групп колонок.

Плохо:

user_id	phones
1	`+7999,+7888`

Лучше:

CREATE TABLE user_phones (
    user_id bigint NOT NULL REFERENCES users(id),
    phone   text NOT NULL,
    PRIMARY KEY (user_id, phone)
);

Не путайте 1NF с запретом JSON вообще. JSONB может быть разумным для настроек, внешних payload'ов или редко фильтруемых данных. Но если по элементу нужно часто искать, соединять, валидировать и ссылаться внешними ключами, это кандидат на отдельную таблицу.

2NF

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

Плохо:

order_id	product_id	product_name	qty
1	10	Go Basics	1

Ключ (order_id, product_id), но product_name зависит только от product_id. Значит, название продукта лучше хранить в products.

3NF

Третья нормальная форма: нет транзитивных зависимостей неключевых атрибутов от ключа.

Плохо:

user_id	city_id	city_name
1	77	Moscow

user_id -> city_id, city_id -> city_name, значит city_name транзитивно зависит от user_id.

Лучше:

users(id, city_id)
cities(id, name)

BCNF

Boyce-Codd Normal Form строже 3NF: для любой нетривиальной functional dependency X -> Y X должен быть superkey.

Пример проблемы:

student	course	instructor
Anna	Go	Ivan
Boris	Go	Ivan

Если правило такое: каждый instructor ведет только один course, и каждый course может иметь несколько instructors, то instructor -> course. Но instructor не superkey этой таблицы. Значит, модель может хранить противоречия.

4NF

Четвертая нормальная форма борется с независимыми многозначными зависимостями.

Плохо:

course	mentor	language
Go Backend	Ivan	Russian
Go Backend	Ivan	English
Go Backend	Maria	Russian
Go Backend	Maria	English

Если mentors и languages независимы, их нужно хранить отдельно:

course_mentors(course_id, mentor_id)
course_languages(course_id, language)

5NF

Пятая нормальная форма связана с join dependencies: таблицу можно разложить на меньшие таблицы без потери информации, и все зависимости выражаются через ключи. На практике в backend-собеседованиях редко просят формально доказать 5NF. Важно понимать идею: некоторые многосвязные факты лучше хранить несколькими отношениями, если исходная таблица создает искусственные комбинации.

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

Аномалия обновления

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

Аномалия вставки

Если продукт хранится только внутри заказа, нельзя создать продукт без заказа.

Аномалия удаления

Если удалили последний заказ с продуктом, потеряли информацию о продукте.

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

Денормализация и read/write модели

Денормализация

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

Примеры:

Денормализованное поле	Зачем
`courses.lessons_count`	Быстро показывать список курсов
`orders.total_cents`	Не суммировать строки заказа каждый раз
`order_items.product_name_snapshot`	Историческая точность чека
`user_progress.completed_lessons_count`	Быстрый dashboard

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

Источник истины: order_items
Денормализованное поле: orders.total_cents
Обновление: в одной транзакции с изменением order_items
Проверка: периодический consistency job

Опасный вариант - когда непонятно, кто главный:

orders.total_cents
order_items sum
payment.amount_cents
invoice.total_cents

Все "примерно одно и то же", но расходятся при сбое.

Read model и write model

Write model оптимизирована под корректные изменения. Read model оптимизирована под чтение.

Для записи:

users
courses
lessons
submissions
reviews

Для чтения dashboard:

student_course_progress
  user_id
  course_id
  completed_lessons
  accepted_submissions
  last_activity_at

Read model можно строить:

SQL view;
materialized view;
отдельной таблицей, обновляемой транзакционно;
фоновой задачей;
через event-driven pipeline.

Чем дальше read model от источника истины, тем больше нужно думать про задержку, повторы, idempotency и восстановление.

Паттерны и миграции

Паттерны проектирования схем

Lookup table вместо свободной строки

Если список значений управляется бизнесом и расширяется, отдельная таблица лучше CHECK.

CREATE TABLE lesson_statuses (
    code text PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE lessons (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL REFERENCES lesson_statuses(code)
);

Если список маленький и меняется только с релизом, CHECK может быть проще.

Audit columns

created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
created_by bigint REFERENCES users(id),
updated_by bigint REFERENCES users(id)

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

Soft delete

deleted_at timestamptz

Плюсы:

можно восстановить данные;
проще аудит;
ссылки не ломаются.

Минусы:

каждый запрос должен учитывать deleted_at IS NULL;
unique constraints усложняются;
таблицы растут;
очистка данных становится отдельной задачей.

Partial unique index для soft delete:

CREATE UNIQUE INDEX users_email_active_unique
ON users (lower(email))
WHERE deleted_at IS NULL;

Status history

Если важно знать историю статусов, одной колонки мало.

CREATE TABLE order_status_events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   bigint NOT NULL REFERENCES orders(id),
    old_status text,
    new_status text NOT NULL,
    changed_at timestamptz NOT NULL DEFAULT now(),
    changed_by bigint REFERENCES users(id)
);

orders.status хранит текущее состояние для быстрых запросов, order_status_events хранит историю.

Outbox

Если нужно изменить БД и надежно отправить событие, событие сначала пишут в outbox в той же транзакции.

CREATE TABLE outbox_events (
    id            uuid PRIMARY KEY,
    aggregate_id  text NOT NULL,
    event_type    text NOT NULL,
    payload       jsonb NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now(),
    published_at  timestamptz
);

Фоновый publisher читает неопубликованные события и отправляет их в Kafka, Redis Streams или другой broker. Это снижает риск "данные сохранили, событие потеряли".

Миграции

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

Хорошая миграция:

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

Expand-contract

Для production часто используют подход expand-contract:

Добавить новую nullable-колонку или таблицу.
Выпустить приложение, которое пишет и старое, и новое.
Backfill существующих данных.
Переключить чтение на новое поле.
Добавить NOT NULL, UNIQUE, FK или удалить старое поле.

Пример:

-- 1. Быстрое расширение схемы.
ALTER TABLE users ADD COLUMN display_name text;

-- 2. Backfill отдельной задачей или батчами.
UPDATE users
SET display_name = name
WHERE display_name IS NULL;

-- 3. После проверки.
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

На больших таблицах backfill одним UPDATE может создать долгую транзакцию, раздуть WAL и заблокировать важные операции. Лучше делать батчами.

Constraint без долгой блокировки

В PostgreSQL для некоторых сценариев удобно добавлять constraint как NOT VALID, а потом валидировать отдельно:

ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fk
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders
VALIDATE CONSTRAINT orders_user_id_fk;

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

Миграционная безопасность: backfill, rollback, validation

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

Перед backfill полезно явно зафиксировать:

Вопрос	Зачем
Какой source of truth для старых данных?	Чтобы не заполнить новое поле приблизительным значением
Можно ли запускать backfill повторно?	Retry не должен создавать дубли или менять уже проверенные строки
Какой размер батча и пауза между батчами?	Чтобы не раздуть WAL и не забить autovacuum/replication
Какие запросы проверяют прогресс?	Нужны понятные метрики `remaining`, `updated`, `failed`
Что делает старая версия приложения?	Expand-contract требует совместимости между релизами
Как откатиться?	Иногда rollback - это не `DROP COLUMN`, а возврат чтения на старое поле

Пример безопасного направления мысли:

-- 1. Добавляем поле без жесткого ограничения.
ALTER TABLE rate_decisions ADD COLUMN rule_version integer;

-- 2. Новая версия приложения начинает писать rule_version.
-- 3. Backfill идет батчами и может быть перезапущен.
UPDATE rate_decisions
SET rule_version = 1
WHERE rule_version IS NULL
  AND id >= $1
  AND id < $2;

-- 4. Проверяем, что дыр больше нет.
SELECT count(*) AS missing_rule_version
FROM rate_decisions
WHERE rule_version IS NULL;

-- 5. Только после проверки усиливаем constraint.
ALTER TABLE rate_decisions
ALTER COLUMN rule_version SET NOT NULL;

Rollback-план здесь может быть таким: если новая версия приложения работает плохо, старая версия продолжает читать прежние поля, а rule_version остается безвредным расширением схемы. Удаление колонки можно отложить на отдельную contract-миграцию после стабилизации.

Reviewer в PR с миграцией обычно смотрит не только SQL-файл, но и порядок релизов:

новая схема совместима со старым кодом?
старые данные валидируются до добавления NOT NULL/FK/UNIQUE?
backfill батчевый и идемпотентный?
есть ли запрос проверки результата?
понятен ли rollback без потери данных?
не появится ли долгий lock на горячей таблице?
обновлены ли read/write paths так, чтобы не было расхождения source of truth?

Как проектировать схему practically

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

Нормализация уменьшает дублирование и защищает от аномалий изменения данных.
Surrogate primary key удобен как техническая ссылка, natural key часто фиксируют через UNIQUE.
Денормализация допустима, если известен source of truth и механизм синхронизации.
Индекс проектируют под запрос, а не "на всякий случай".
Миграции больших таблиц требуют expand-contract, backfill батчами и проверки locks.

Практика

Возьмите сущности учебной платформы и выпишите functional dependencies: курс, модуль, урок, пользователь, прогресс.
Найдите пример нарушения 1NF/2NF/3NF и разложите таблицу.
Добавьте read model для dashboard прогресса и опишите, откуда она обновляется.
Спланируйте безопасную миграцию добавления обязательного display_name.

Интерактивная практика

Quiz+10 XP

Что лучше всего описывает безопасную денормализацию?

Скопировать данные в новую таблицу и забыть source of truth
Удалить constraints, чтобы writes стали быстрее
Явно назвать source of truth и способ синхронизации read model
Всегда хранить всё в JSONB, чтобы не думать о схеме

Predict+15 XP

Что выведет этот код?

WITH tables(name, source_of_truth) AS (
    VALUES
        ('courses', true),
        ('course_progress_dashboard', false)
)
SELECT CASE
    WHEN source_of_truth THEN 'normalized'
    ELSE 'read-model'
END AS role
FROM tables
ORDER BY source_of_truth DESC;

Полезные источники

PostgreSQL Documentation: Constraints
PostgreSQL Documentation: Indexes
PostgreSQL Documentation: Modifying Tables

normalizationERkeysconstraintsdenormalizationmigrations