Реляционная модель, нормализация и SQL как язык

Реляционная модель — самая живучая абстракция в истории промышленного софта. Ей больше пятидесяти лет, её многократно хоронили (объектные БД в девяностых, NoSQL в десятых, «датафреймы вместо баз» сейчас), и каждый раз она возвращалась, потому что решает не техническую, а эпистемологическую задачу: как записать факты о мире так, чтобы из них можно было вывести ответы на вопросы, которых при записи ещё никто не задавал.

Эта статья — фундамент трека. Дальше мы будем разбирать конкретные движки (https://courses.digitable.life/post/databases/02-postgresql/, https://courses.digitable.life/post/databases/03-mysql-and-mariadb/), их индексы и планы (https://courses.digitable.life/post/databases/06-indexes-and-query-plans/) и альтернативные модели (https://courses.digitable.life/post/databases/09-nosql-landscape/). Но и MongoDB, и ClickHouse, и Cassandra проектируются в терминах, которые задал Кодд: ключ, зависимость, дублирование, аномалия. Не поняв реляционную модель, вы не сможете осознанно от неё отказаться — только случайно.

1. Задача, которую решал Кодд

До 1970 года данные хранили в иерархических (IMS от IBM) и сетевых (CODASYL) СУБД. Работали они так: программист явно проходил по указателям от записи к записи. Чтобы получить заказы клиента, вы открывали набор «клиенты», позиционировались на нужном, брали указатель на первый заказ и шли по цепочке. Это быстро и понятно — ровно до момента, когда бизнес просит новый отчёт.

Проблем было три, и они смертельные:

1. Программа знала физическое устройство хранилища. Изменили порядок полей, добавили индекс, переложили набор на другой том — переписывайте прикладной код.
2. Путь запроса был зашит в программу. «Заказы клиента» и «клиенты по заказу» — два разных куска кода, потому что указатели однонаправленные.
3. Новый вопрос требовал новой структуры. Отчёт, которого не было в исходном проекте, часто означал перестройку всей базы.

Эдгар Кодд, математик в исследовательском центре IBM в Сан-Хосе, предложил в статье «A Relational Model of Data for Large Shared Data Banks» (CACM, 1970) радикально другое: данные — это множества кортежей, а запрос — это выражение над множествами. Никаких указателей, никаких путей. Связь между сущностями существует не физически, а логически — как совпадение значений.

Ключевое понятие, которое из этого следует, — независимость данных (data independence). Приложение описывает что оно хочет получить; движок сам решает как. Между «что» и «как» встаёт оптимизатор — и именно он даёт реляционным СУБД их главное свойство: вы можете поменять физическое представление (добавить индекс, перепартиционировать таблицу, переписать движок хранения), не тронув ни строки прикладного кода.

Обратите внимание на последнюю строку: индустрия не отказалась от реляционной модели, а встроила в неё то, чего не хватало. PostgreSQL умеет JSONB и векторный поиск (https://courses.digitable.life/post/databases/15-vector-databases/), ClickHouse говорит на SQL, CockroachDB даёт распределённые ACID-транзакции (https://courses.digitable.life/post/databases/18-newsql-and-distributed/). Победил не конкретный движок — победил язык и модель.

2. Отношение: строгое определение и чем оно отличается от таблицы

Формально. Пусть заданы домены D₁, …, Dₙ — множества допустимых значений (целые числа, строки длины ≤ 255, даты). Отношение R — это подмножество декартова произведения D₁ × D₂ × … × Dₙ. Элемент отношения — кортеж. Схема отношения — набор именованных атрибутов с доменами: Orders(order_id: int, customer_id: int, total: numeric).

Из определения «отношение — это множество» следуют три свойства, которые важно проговорить, потому что SQL нарушает все три:

Кристофер Дейт в «SQL and Relational Theory» (O’Reilly, 3-е изд.) посвящает половину книги именно этому зазору: SQL — не реляционный язык, а язык, «вдохновлённый» реляционной моделью. Практический вывод простой и очень полезный: каждое расхождение — источник багов. Дубликаты дают неверные COUNT, отсутствие порядка ломает пагинацию, NULL ломает NOT IN. Мы разберём все три.

Ключи

• Суперключ — любой набор атрибутов, однозначно определяющий кортеж.
• Потенциальный (кандидатный) ключ — минимальный суперключ: удалите любой атрибут, и однозначность пропадёт.
• Первичный ключ — выбранный из кандидатных. Ничем не «лучше» остальных, кроме соглашения.
• Внешний ключ — атрибут(ы), значения которых должны существовать как ключ в другом отношении.

Здесь живёт вечный спор — естественный ключ против суррогатного:

Практический компромисс, который выдерживает продакшн: суррогатный первичный ключ + обязательный UNIQUE на бизнес-ключ. Без второй половины вы получите базу с тремя записями одного и того же клиента и разными customer_id — и никакая нормализация уже не поможет, потому что аномалия проникла на уровень идентичности.

CREATE TABLE customers (
    customer_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- суррогат для FK
    email        citext NOT NULL,
    city         text   NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT customers_email_uniq UNIQUE (email)                 -- бизнес-ключ обязателен
);

GENERATED ALWAYS AS IDENTITY — стандарт SQL:2003, в PostgreSQL доступен с 10-й версии и предпочтительнее устаревшего serial: он не позволяет случайно вставить своё значение и не оставляет «висячих» sequence при DROP COLUMN.

3. Реляционная алгебра — то, во что компилируется ваш SQL

Кодд дал не только модель данных, но и алгебру над ней. Это важно практически: оптимизатор любой СУБД переписывает ваш запрос как алгебраическое выражение и применяет к нему законы эквивалентности. Понимая алгебру, вы понимаете, почему одни переписывания запроса работают, а другие — нет.

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

Два закона, ради которых стоит держать алгебру в голове:

• Проталкивание предиката (predicate pushdown): σ(A ⋈ B) эквивалентно σ(A) ⋈ B, если предикат касается только A. Оптимизатор фильтрует до соединения, а не после — объём промежуточных данных падает на порядки. Именно поэтому WHERE по индексированной колонке одной таблицы ускоряет джойн трёх.
• Ассоциативность соединения: (A ⋈ B) ⋈ C = A ⋈ (B ⋈ C). Порядок в вашем запросе — не порядок выполнения. Оптимизатор перебирает планы (в PostgreSQL — динамическим программированием до join_collapse_limit, дальше — генетическим алгоритмом GEQO).

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

-- Клиенты, купившие ВСЕ товары из промо-набора (реляционное деление)
SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (                       -- нет такого промо-товара,
    SELECT 1 FROM promo_products p
    WHERE NOT EXISTS (                   -- ... которого этот клиент не покупал
        SELECT 1
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.order_id
        WHERE o.customer_id = c.customer_id
          AND oi.product_id = p.product_id
    )
);

Читается тяжело, поэтому на практике чаще пишут через агрегат — и это, как правило, быстрее, потому что даёт один проход с хеш-агрегацией вместо коррелированных подзапросов:

SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE oi.product_id IN (SELECT product_id FROM promo_products)
GROUP BY o.customer_id
HAVING COUNT(DISTINCT oi.product_id) = (SELECT COUNT(*) FROM promo_products);

4. Целостность: три уровня и почему её место в базе

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

1. Доменная — значение принадлежит домену. В SQL это тип + CHECK + ENUM/domain-типы.
2. Сущностная (entity integrity) — первичный ключ не NULL и уникален.
3. Ссылочная (referential integrity) — внешний ключ либо NULL, либо указывает на существующий кортеж.

Регулярный аргумент «валидируем в приложении, констрейнты замедляют» не выдерживает проверки практикой по трём причинам. Первая: приложений у базы обычно больше одного — бэкенд, воркеры, скрипты миграций, ручной psql дежурного в три часа ночи. Вторая: гонки. Проверка «нет ли уже такого email» с последующей вставкой без UNIQUE — классический TOCTOU, и под нагрузкой он всегда срабатывает. Третья: констрейнты — это информация для оптимизатора. NOT NULL позволяет ему выбросить проверки, UNIQUE — доказать, что джойн не размножит строки и заменить его на более дешёвый узел, CHECK при секционировании даёт partition pruning.

Цена честная и её надо знать: FK — это дополнительный поиск по индексу родителя при каждой вставке/обновлении дочерней строки (единицы микросекунд при прогретом кэше) плюс блокировка FOR KEY SHARE на родительской строке, которая при «горячем» родителе (все заказы ссылаются на одного контрагента) становится точкой конкуренции. И отдельная ловушка PostgreSQL: индекс на дочерней стороне FK не создаётся автоматически. Без него DELETE родителя приводит к seq scan по всей дочерней таблице.

CREATE TABLE orders (
    order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers ON DELETE RESTRICT,
    status       text   NOT NULL DEFAULT 'new',
    total_cents  bigint NOT NULL,
    placed_at    timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT orders_status_chk CHECK (status IN ('new','paid','shipped','cancelled')),
    CONSTRAINT orders_total_chk  CHECK (total_cents >= 0)
);

-- ОБЯЗАТЕЛЬНО: PostgreSQL не создаёт индекс под внешний ключ сам
CREATE INDEX orders_customer_idx ON orders (customer_id);

Заметьте total_cents bigint, а не money и не float. Деньги никогда не хранят в плавающей точке — 0.1 + 0.2 <> 0.3 в двоичном IEEE 754, и через месяц бухгалтерия принесёт расхождение в копейки на миллионе транзакций. Допустимые варианты: целые в минимальных единицах (bigint в копейках) или numeric(18,2). Тип money в PostgreSQL завязан на локаль сервера — избегайте.

Три часто забываемых инструмента целостности

-- 1. Частичный UNIQUE: только один активный адрес доставки на клиента
CREATE UNIQUE INDEX ON addresses (customer_id) WHERE is_default;

-- 2. EXCLUDE: брони переговорки не должны пересекаться по времени
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&);

-- 3. Генерируемая колонка: производный факт нельзя рассинхронизировать
ALTER TABLE order_items
    ADD COLUMN line_total_cents bigint
    GENERATED ALWAYS AS (qty * unit_price_cents) STORED;

EXCLUDE-ограничение — уникальная возможность PostgreSQL: обобщение UNIQUE на произвольный оператор. Задача «не допустить пересекающихся интервалов» без него решается либо блокировкой всей таблицы, либо (чаще) не решается вовсе, и в календаре появляются двойные брони.

5. Функциональные зависимости и аномалии

Теперь — сердце проектирования. Функциональная зависимость X → Y означает: если два кортежа совпадают по атрибутам X, они обязаны совпадать и по Y. Это утверждение о предметной области, а не о текущих данных. email → city верно, если бизнес утверждает «у клиента один город»; тот факт, что сегодня совпадений нет, ничего не доказывает.

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

На картинке — три классические аномалии, ради устранения которых всё и затевается:

• Аномалия обновления. Один факт (город Анны) хранится в N строках. Обновили не все — данные разошлись. В строке 1004 это уже произошло: «Кофемолка PRO» вместо «Кофемолка Pro» и цена 5100 вместо 4900.
• Аномалия вставки. Клиента без заказа записать некуда: первичный ключ требует order_id.
• Аномалия удаления. Удалили последний заказ Боба — потеряли самого Боба.

Общий принцип формулируется как афоризм Кента: «каждый атрибут зависит от ключа, от всего ключа и ни от чего, кроме ключа» (the key, the whole key, and nothing but the key) — это ровно 2НФ и 3НФ вместе. Источник афоризма — статья Уильяма Кента «A Simple Guide to Five Normal Forms in Relational Database Theory» (CACM, 1983), лучший короткий текст по теме на сегодня.

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

Нарушение 1НФ — самая частая и самая дорогая ошибка джуниора: tags text со значением 'sql,базы,индексы'. Поиск требует LIKE '%sql%' (никакой индекс не поможет), а 'postgresql' даст ложное совпадение. Лечение — таблица связей post_tags(post_id, tag_id) либо, если тегов мало и они не справочные, нативный массив с GIN-индексом (см. https://courses.digitable.life/post/databases/06-indexes-and-query-plans/).

Нарушение 2НФ на живом примере. Пусть order_items(order_id, product_id, qty, product_name). Ключ составной: (order_id, product_id). Но product_name зависит только от product_id — от части ключа. Результат: переименование товара требует обхода всех позиций всех заказов. Лечение — вынести в products.

Нарушение 3НФ: orders(order_id, customer_id, customer_city). Здесь order_id → customer_id → customer_city — транзитивная цепочка, город приехал в заказ «прицепом». Лечение — customers.

Важнейшая оговорка про 3НФ, которую пропускают в 90% пересказов: unit_price в order_items — это НЕ нарушение 3НФ. Цена товара в момент покупки и текущая цена в каталоге — разные факты о разных сущностях. Если не сохранить цену в позиции заказа, изменение прайса задним числом перепишет всю финансовую историю. Нормализация запрещает дублировать один факт, а не хранить разные факты, которые сегодня совпадают по значению.

BCNF и цена, которую за неё платят

Каноническая иллюстрация. Отношение Address(city, street, zip) с зависимостями {city, street} → zip и zip → city. Кандидатных ключей два: {city, street} и {zip, street}. Отношение в 3НФ (транзитивных зависимостей нет, city — часть ключа), но не в BCNF: zip → city имеет несуперключ слева.

Декомпозиция в BCNF даёт R1(zip, city) и R2(zip, street). Она без потерь (по значениям восстанавливается исходное отношение), но не сохраняет зависимость {city, street} → zip: проверить её теперь можно только соединением. То есть ради устранения редкой аномалии вы теряете возможность декларативно защитить бизнес-правило.

Отсюда индустриальное правило: алгоритм синтеза Бернштейна в 3НФ всегда даёт декомпозицию без потерь и с сохранением зависимостей; BCNF гарантирует только отсутствие потерь. Поэтому целевая форма OLTP-схем — 3НФ, а BCNF применяют точечно, когда аномалия реальна и дорога.

4НФ: не так экзотична, как кажется

Многозначная зависимость X ↠ Y — это «с каждым значением X связан независимый набор Y». Пример из жизни: преподаватель ведёт несколько курсов и владеет несколькими языками, и эти факты никак не связаны между собой. Таблица teacher(name, course, language) вынуждена хранить их декартовым произведением: 3 курса × 2 языка = 6 строк. Добавили третий язык — обязаны добавить три строки, иначе схема становится несогласованной. Лечение очевидно: teacher_courses и teacher_languages отдельно. Теорию ввёл Роналд Фейгин в 1977 году; практический признак — «таблица с тремя колонками, где строк ровно столько, сколько произведение размерностей».

6. Денормализация: когда и сколько это стоит

Нормализация оптимизирует запись и истину; денормализация — чтение. Это осознанный размен, а не «оптимизация».

Оцените порядок величин на конкретных числах. 50 млн строк заказов, каждая тащит customer_city varchar(40) вместо FK на bigint: ≈ 20 байт против 8 → примерно 600 МБ лишних данных в куче плюс раздувание всех индексов, где колонка участвует, плюс вытеснение полезных страниц из shared_buffers. Взамен вы экономите hash join с таблицей на 2 млн клиентов — в PostgreSQL это единицы-десятки миллисекунд при прогретом кэше. Меняйте только тогда, когда джойн измеренно является узким местом, а не когда «джойны медленные».

Легальные формы денормализации, у которых есть механизм поддержания согласованности:

-- 1. Материализованное представление: пересчёт управляемый, данные не врут молча
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', placed_at) AS day,
       count(*)                     AS orders_cnt,
       sum(total_cents)             AS revenue_cents
FROM orders
WHERE status <> 'cancelled'
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);            -- нужен для CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- без блокировки читателей

-- 2. Счётчик-агрегат, поддерживаемый триггером (когда нужен онлайн)
CREATE OR REPLACE FUNCTION bump_orders_counter() RETURNS trigger AS $$
BEGIN
    UPDATE customers
       SET orders_count = orders_count + 1
     WHERE customer_id = NEW.customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Про второй вариант надо знать правду: триггер-счётчик сериализует все вставки заказов одного клиента на строке customers. Для маркетплейса, где 40% заказов идут на одного крупного продавца, это готовая точка блокировки. Альтернатива — писать дельты в отдельную таблицу и схлопывать их фоновым процессом. Без плана поддержания согласованности денормализация — это просто ошибка, отложенная во времени.

7. Схема-пример: как выглядит спроектированная предметная область

Три решения в этой схеме заслуживают комментария. Первое: ORDER_ITEMS — таблица-связка со составным первичным ключом (order_id, product_id); суррогат здесь не нужен, потому что естественный ключ узкий и стабильный. Второе: unit_price_cents и price_cents живут в разных таблицах намеренно (см. выше про 3НФ). Третье: CATEGORIES ссылается сама на себя — это адъяцентный список, самое простое представление дерева, за которое платят рекурсивным обходом.

8. SQL как язык: декларативность и логический порядок

SQL родился как SEQUEL в System R (IBM, 1974) и был спроектирован как язык для непрограммистов — отсюда англоподобный синтаксис. Ключевая его особенность: вы описываете свойства результата, а не алгоритм. Один и тот же SELECT может выполниться как nested loop, hash join или merge join в зависимости от статистики — и это нормально.

Главный источник недоразумений — синтаксический порядок не равен логическому. Вы пишете SELECT первым, но вычисляется он почти последним. Отсюда и правило «нельзя использовать алиас из SELECT в WHERE»: на момент вычисления WHERE алиаса ещё не существует.

Из этой схемы прямо следуют четыре практических вывода, каждый из которых закрывает частый баг:

1. WHERE фильтрует строки, HAVING — группы. Условие, не зависящее от агрегата, всегда пишите в WHERE: тогда оно проталкивается до группировки и не заставляет движок агрегировать лишнее.
2. Оконные функции нельзя использовать в WHERE — они вычисляются позже. Нужен фильтр по row_number()? Оберните в CTE или подзапрос.
3. Алиасы SELECT недоступны в WHERE, но доступны в ORDER BY (шаг 10 после шага 7). Это не каприз грамматики, а прямое следствие порядка.
4. Без ORDER BY порядка нет. LIMIT 20 OFFSET 40 без сортировки — недетерминированная пагинация: одна и та же строка может встретиться на двух страницах и не встретиться ни на одной.

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

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

Две ловушки, встречающиеся в каждом втором ревью:

-- ЛОВУШКА 1: условие на правую таблицу в WHERE превращает LEFT JOIN в INNER
SELECT c.email, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid';          -- клиенты без заказов дают NULL → предикат UNKNOWN → строка выбрасывается

-- ПРАВИЛЬНО: условие переносим в ON, фильтр по левой таблице оставляем в WHERE
SELECT c.email, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
WHERE c.city = 'Казань';

-- ЛОВУШКА 2: агрегат по «размноженным» строкам
SELECT c.customer_id,
       count(o.order_id)            AS orders,   -- завышено в число позиций на заказ
       count(DISTINCT o.order_id)   AS orders_ok -- корректно
FROM customers c
LEFT JOIN orders o      ON o.customer_id = c.customer_id
LEFT JOIN order_items i ON i.order_id = o.order_id
GROUP BY c.customer_id;

NULL и трёхзначная логика

NULL — не значение, а маркер «значение неизвестно». Любое сравнение с ним даёт UNKNOWN, а WHERE пропускает только TRUE.

NOT IN с подзапросом, где может оказаться NULL, — самая дорогая из «тихих» ошибок SQL: запрос не падает, а возвращает пустой результат. Правило: вместо NOT IN (SELECT ...) всегда пишите NOT EXISTS — он корректен при NULL и обычно быстрее (антисоединение вместо материализации списка).

Инструменты, которые делают SQL полноценным языком

-- Рекурсивный CTE (SQL:1999): обход дерева категорий вниз с путём и глубиной
WITH RECURSIVE tree AS (
    SELECT category_id, parent_id, name,
           1 AS depth,
           name::text AS path
    FROM categories
    WHERE parent_id IS NULL                       -- якорь рекурсии

    UNION ALL

    SELECT c.category_id, c.parent_id, c.name,
           t.depth + 1,
           t.path || ' / ' || c.name
    FROM categories c
    JOIN tree t ON c.parent_id = t.category_id    -- рекурсивный шаг
    WHERE t.depth < 10                            -- страховка от циклов в данных
)
SELECT repeat('  ', depth - 1) || name AS tree_view, path
FROM tree
ORDER BY path;

Про страховку depth < 10 — не перестраховка. Самоссылающаяся таблица без CHECK на отсутствие циклов рано или поздно получит цикл (сотрудник — сам себе руководитель после неудачной миграции), и рекурсивный CTE будет крутиться, пока не съест диск под временные файлы.

-- Оконные функции (SQL:2003): аналитика без потери детализации
SELECT
    o.order_id,
    o.customer_id,
    o.total_cents,
    row_number() OVER w                                  AS order_seq,
    o.total_cents - lag(o.total_cents) OVER w            AS delta_prev,
    sum(o.total_cents) OVER (PARTITION BY o.customer_id) AS lifetime_value,
    avg(o.total_cents) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.placed_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )                                                    AS moving_avg_3
FROM orders o
WINDOW w AS (PARTITION BY o.customer_id ORDER BY o.placed_at);

Разница между GROUP BY и OVER фундаментальна: GROUP BY схлопывает строки, окно — добавляет колонку, сохраняя детализацию. Задача «вывести каждый заказ и рядом долю от суммы клиента» без окон решается самосоединением с агрегатом — вдвое дороже и втрое многословнее.

-- LATERAL: коррелированный подзапрос как источник строк. Топ-3 заказа каждого клиента
SELECT c.email, o.order_id, o.total_cents
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, total_cents
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY total_cents DESC
    LIMIT 3
) o
WHERE c.city = 'Казань';

LATERAL с LIMIT внутри — самый эффективный способ решить «top-N по группе», когда групп много, а N мал: при наличии индекса (customer_id, total_cents DESC) каждая итерация читает ровно три индексные записи вместо сортировки всей таблицы оконной функцией.

9. От декларации к плану: что делает оптимизатор

Между вашим SQL и диском стоит стоимостной оптимизатор. Он строит алгебраическое дерево, применяет эквивалентные преобразования, оценивает кардинальность по статистике (pg_statistic, обновляется ANALYZE) и выбирает дешевейший план. Читать планы — базовый навык; подробно — в https://courses.digitable.life/post/databases/06-indexes-and-query-plans/, здесь покажем связь с нормализацией.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.city, count(*) AS orders_cnt, sum(o.total_cents) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.placed_at >= date '2026-06-01'
GROUP BY c.city
ORDER BY revenue DESC
LIMIT 10;

Limit  (cost=48211.90..48211.93 rows=10 width=48) (actual time=312.441..312.444 rows=10 loops=1)
  ->  Sort  (cost=48211.90..48213.15 rows=498 width=48) (actual time=312.439..312.441 rows=10 loops=1)
        Sort Key: (sum(o.total_cents)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=48195.14..48201.37 rows=498 width=48) (actual time=312.201..312.318 rows=498 loops=1)
              Group Key: c.city
              Batches: 1  Memory Usage: 121kB
              ->  Hash Join  (cost=6410.00..44012.71 rows=418243 width=24) (actual time=41.882..248.114 rows=417 902 loops=1)
                    Hash Cond: (o.customer_id = c.customer_id)
                    ->  Index Scan using orders_placed_at_idx on orders o
                          (cost=0.43..31122.44 rows=418243 width=16) (actual time=0.038..117.402 rows=417 902 loops=1)
                          Index Cond: (placed_at >= '2026-06-01'::date)
                          Buffers: shared hit=12844 read=3901
                    ->  Hash  (cost=3910.00..3910.00 rows=200000 width=16) (actual time=41.402..41.403 rows=200000 loops=1)
                          Buckets: 262144  Batch: 1  Memory Usage: 11321kB
                          ->  Seq Scan on customers c  (cost=0.00..3910.00 rows=200000 width=16)
Planning Time: 0.412 ms
Execution Time: 312.588 ms

Что здесь читать:

• rows= в cost-оценке против rows= в actual. Расхождение более чем на порядок — сигнал устаревшей статистики или коррелированных предикатов. Здесь 418 243 против 417 902 — оценка отличная.
• Hash Join, а не Nested Loop. Оптимизатор решил, что дешевле построить хеш по всем 200 тыс. клиентов, чем 200 тыс. раз ходить в индекс. Это правильно при большом объёме внешней стороны.
• Buffers: shared hit=12844 read=3901. 24% чтений мимо кэша — при повторном прогоне число упадёт. BUFFERS всегда включайте: он показывает реальный объём работы, а не оценку.
• top-N heapsort вместо полной сортировки — оптимизация под LIMIT.

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

10. Реляционная модель против остальных: честное сравнение

Честный итог: реляционная модель проигрывает не по возможностям, а по стоимости масштабирования записи и по жёсткости схемы в среде, где требования меняются быстрее релизного цикла. В остальных случаях она выигрывает, и типичный современный стек — PostgreSQL как основное хранилище плюс специализированные системы под конкретные паттерны нагрузки (https://courses.digitable.life/post/databases/19-choosing-and-migrating/).

Отдельно — важная поправка к мифу «реляционка не умеет схему без схемы». Умеет: JSONB в PostgreSQL с GIN-индексами даёт документное хранение внутри ACID-транзакции. Разумная стратегия для многих продуктов: стабильное ядро (клиенты, заказы, платежи) — в нормализованных таблицах с констрейнтами; изменчивая периферия (настройки, атрибуты товара, события) — в JSONB.

Поддержка стандарта: чего ждать от разных СУБД

Строка про CHECK в MySQL — не придирка, а исторический источник аварий: до 8.0.16 сервер парсил и молча игнорировал ограничение. Схема выглядела защищённой, а данные не проверялись годами. Мораль: проверяйте, что декларация действительно работает, а не только принимается парсером.

11. Типичные ошибки проектирования

EAV (entity-attribute-value) — таблица (entity_id, attribute, value) в попытке «сделать схему гибкой». Убивает типизацию, целостность и планировщик: чтобы собрать одну сущность из 20 атрибутов, нужно 20 самосоединений, а оценки кардинальности становятся мусорными. В PostgreSQL правильная альтернатива — JSONB с GIN-индексом.

Списки в строке. '1,5,17' вместо таблицы связей. Никакой индекс не поможет, поиск подстроки даёт ложные совпадения, целостность отсутствует.

Отказ от FK «ради производительности». Экономия — микросекунды на вставке. Расплата — осиротевшие строки, которые обнаруживаются через год, когда восстановить связи уже нечем.

float для денег. См. выше. Только bigint в минимальных единицах или numeric.

timestamp without time zone для событий. Через полгода приедет пользователь из другого пояса или случится перевод часов, и отчёты разъедутся. Для моментов времени — timestamptz; для «календарной даты рождения» — date.

Отсутствие UNIQUE на бизнес-ключе при наличии суррогатного PK. База честно принимает трёх «одинаковых» клиентов.

N+1 запрос. ORM в цикле: один запрос на список и по одному на каждую связь. Тысяча заказов — тысяча запросов, каждый по сети. Лечение — JOIN, IN (...) пачкой или встроенный eager loading.

SELECT * в проде. Тащит колонки, которые не нужны (включая TOAST-поля на мегабайты), ломается при ALTER TABLE, запрещает index-only scan.

Отсутствие ANALYZE после массовой загрузки. Оптимизатор считает, что в таблице ноль строк, и выбирает nested loop на 50 миллионах записей.

Схема без миграционного инструмента. Отсутствие версионируемых миграций (Flyway, Liquibase, Alembic, goose) означает, что схема dev, stage и prod различаются, и никто не знает как.

12. Практика продакшена: как менять схему живой базы

Проектирование не заканчивается на первом релизе — 90% работы со схемой это её изменение под нагрузкой. Ключевое знание: какие операции берут ACCESS EXCLUSIVE и на сколько.

-- Всегда: ограничить время ожидания блокировки, чтобы миграция не встала в очередь
-- и не заблокировала за собой весь трафик к таблице
SET lock_timeout = '3s';

-- 1) Добавление колонки с DEFAULT: в PostgreSQL 11+ НЕ переписывает таблицу
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';

-- 2) Добавление CHECK на большую таблицу — в два шага, без долгой блокировки
ALTER TABLE orders ADD CONSTRAINT orders_channel_chk
    CHECK (channel IN ('web','app','partner')) NOT VALID;   -- мгновенно, только для новых строк
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_chk;  -- долго, но блокировка слабее (SHARE UPDATE EXCLUSIVE)

-- 3) Индекс на боевой таблице — только CONCURRENTLY (вне транзакции, может упасть в INVALID)
CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);

-- 4) NOT NULL без долгой блокировки (PostgreSQL 12+): через валидный CHECK
ALTER TABLE orders ADD CONSTRAINT orders_ch_nn CHECK (channel IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_ch_nn;
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;       -- использует существующий CHECK, полного скана нет

Три правила, выстраданные инцидентами:

1. Всегда lock_timeout. ALTER TABLE, ждущий ACCESS EXCLUSIVE, встаёт в очередь блокировок и блокирует за собой все последующие запросы, включая SELECT. Пятисекундная миграция кладёт сервис на полчаса.
2. Расширяющие изменения — раздельно от сужающих. Схема «добавили колонку → задеплоили код, пишущий в обе → бэкфилл → переключили чтение → удалили старую» (expand/contract) даёт откат на каждом шаге.
3. Проверяйте миграции на копии продакшн-объёма. ALTER на таблице в 10 тыс. строк мгновенен на любой машине; поведение на 500 млн строк принципиально другое.

Для тестирования самой схемы — не только запросов — работает подход «свойства данных как SQL-ассерты»:

-- Регулярный аудит целостности: должен всегда возвращать ноль строк
SELECT 'осиротевшие позиции заказа' AS check_name, count(*) AS violations
FROM order_items oi LEFT JOIN orders o USING (order_id)
WHERE o.order_id IS NULL
UNION ALL
SELECT 'сумма заказа не равна сумме позиций', count(*)
FROM orders o
JOIN (SELECT order_id, sum(qty * unit_price_cents) s FROM order_items GROUP BY 1) t USING (order_id)
WHERE o.total_cents <> t.s;

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

13. Мини-итог

• Реляционная модель — про независимость данных. Логическая связь по значениям вместо физических указателей — единственная причина, по которой запрос, которого не было в проекте, выполняется без переделки хранилища.
• Отношение ≠ таблица. SQL допускает дубликаты, порядок и NULL; каждое расхождение — известный класс багов.
• Нормализация — механика, а не вкусовщина. Выпишите функциональные зависимости, разложите так, чтобы каждая была представлена один раз. Целевая форма OLTP — 3НФ; BCNF точечно, помня о риске потери зависимостей.
• Разные факты, совпадающие по значению, — не дублирование. Цена в момент покупки остаётся в позиции заказа.
• Денормализация — размен чтения на согласованность. Легальна только с механизмом поддержания: matview, триггер с понятной ценой блокировок, фоновый пересчёт.
• Целостность живёт в базе. NOT NULL, UNIQUE, FK, CHECK, EXCLUDE — это и защита, и подсказки оптимизатору.
• SQL декларативен, но у него есть логический порядок. Знание порядка снимает половину вопросов о WHERE против HAVING, окнах и алиасах.
• NOT EXISTS вместо NOT IN. Всегда.

Источники

• E. F. Codd. A Relational Model of Data for Large Shared Data Banks. CACM 13(6), 1970 — оригинал, читается за час и стоит того.
• William Kent. A Simple Guide to Five Normal Forms in Relational Database Theory, CACM 26(2), 1983 — лучшее краткое изложение нормальных форм.
• C. J. Date. SQL and Relational Theory: How to Write Accurate SQL Code, O’Reilly — где именно SQL расходится с моделью и что с этим делать.
• Abraham Silberschatz, Henry Korth, S. Sudarshan. Database System Concepts — классический университетский учебник, доступны слайды и упражнения.
• PostgreSQL Documentation: DDL and Constraints — первоисточник по констрейнтам, включая EXCLUDE и NOT VALID.
• Markus Winand. Use The Index, Luke и modern-sql.com — индексы и современный SQL с честными таблицами поддержки по СУБД.
• Joe Celko. SQL for Smarties: Advanced SQL Programming — сборник нетривиальных приёмов, включая деление и обработку деревьев.

Что дальше

Модель и язык разобраны — пора смотреть, как конкретный движок реализует всё это на диске: страницы, версии строк, индексы, статистику и фоновые процессы.

PostgreSQL: возможности, индексы, MVCC, расширения, эксплуатация