Денормализация: когда и зачем

Что такое денормализация

Денормализация — намеренное нарушение нормальных форм для повышения производительности чтения.

Что даёт:

• Меньше JOIN.
• Быстрее запросы.
• Проще код.

Что стоит:

• Дубли данных.
• Риск рассинхрона.
• Сложнее обновления.
• Больше места.

Правило: сначала нормализуй, потом денормализуй по факту узкого места.

Признаки, что нужна денормализация

• JOIN 5+ таблиц на каждый запрос.
• Тяжёлые агрегаты (SUM, COUNT) в горячем пути.
• Медленные запросы даже с индексами.
• Высокая нагрузка на чтение (100:1 read/write).
• Материализованные представления пересчитывать дорого.
• Отчёты тянут много данных.

Правило: сначала пробуем индексы, кэш, оптимизацию. Денормализация — потом.

Виды денормализации

1. Дублирование полей.

2. Счётчики и агрегаты.

3. Материализованные представления.

4. Снежинка и звезда (OLAP).

5. JSON-поля для гибкости.

Дублирование полей

Пример: цена на момент покупки.

Нормализовано:

orders: id, user_id
order_items: order_id, product_id, quantity
products: id, price

Проблема: цена меняется. Если считать SUM(quantity * products.price), то после изменения цены история покупок переписывается.

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

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    price_at_purchase NUMERIC(10, 2) NOT NULL,   -- snapshot
    PRIMARY KEY (order_id, product_id)
);

Что даёт: цена фиксируется на момент покупки. Изменение товара не влияет на историю.

Когда: любая транзакционная история (цены, адреса, курсы, ставки).

Пример: имя автора в посте.

Нормализовано:

posts: id, author_id, body
authors: id, name

Проблема: если автор сменил имя — все посты показывают новое. Для блога — норм. Для документов (счета, накладные) — нет.

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

CREATE TABLE invoices (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    customer_name TEXT NOT NULL,      -- snapshot
    customer_address TEXT NOT NULL,   -- snapshot
    ...
);

Правило: для документов — снимок. Для динамических сущностей — ссылка.

Счётчики

Пример: количество постов у пользователя.

Нормализовано:

SELECT COUNT(*) FROM posts WHERE user_id = 5;

Проблема: на 1M постов у активных пользователей — тяжело.

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

ALTER TABLE users ADD COLUMN posts_count INTEGER NOT NULL DEFAULT 0;

Обновление:

-- Через триггер
CREATE FUNCTION posts_count_update() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET posts_count = posts_count + 1 WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET posts_count = posts_count - 1 WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_count_trigger
AFTER INSERT OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION posts_count_update();

Что даёт: SELECT posts_count FROM users — мгновенно.

⚠️ При массовых операциях триггеры тормозят. Иногда лучше пересчитывать периодически.

Пример: сумма заказа.

ALTER TABLE orders ADD COLUMN items_total NUMERIC(12, 2) NOT NULL DEFAULT 0;

Что даёт: не нужно считать SUM(quantity * price) при каждом показе заказа.

Материализованные представления

Идея: сохранить результат запроса как таблицу.

PostgreSQL:

CREATE MATERIALIZED VIEW user_stats AS
SELECT
    u.id,
    u.email,
    COUNT(o.id) AS orders_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

CREATE INDEX idx_user_stats_total ON user_stats (total_spent DESC);

Обновление:

REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;   -- без блокировки

По расписанию:

Schedule::call(fn () => DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats'))
    ->everyFiveMinutes();

Что даёт:

• Быстрое чтение агрегатов.
• Индексы на view.
• SQL-запросы как к обычной таблице.

⚠️ Данные устаревают. CONCURRENTLY требует уникального индекса.

MySQL: нет материализованных представлений. Альтернативы:

• Обычная таблица + триггеры / cron.
• CREATE TABLE ... AS SELECT — периодически.

OLAP: звезда и снежинка

Для аналитики нормализация вредна — нужны быстрые агрегаты.

Схема «звезда» (Star Schema):

        dim_date
           |
fact_sales ─ dim_product
           |
        dim_customer

Факт — таблица событий (продажи). Измерения — справочники (дата, продукт, клиент).

Пример:

CREATE TABLE fact_sales (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    date_id INTEGER NOT NULL,
    product_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    amount NUMERIC(12, 2) NOT NULL
);

CREATE TABLE dim_date (
    id INTEGER PRIMARY KEY,
    date DATE NOT NULL,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    day INTEGER NOT NULL,
    weekday INTEGER NOT NULL
);

Что даёт: быстрые агрегаты SUM(amount) GROUP BY year, month.

Схема «снежинка» (Snowflake):

• Измерения нормализованы дальше.
• Меньше дублей, но больше JOIN.

Правило:

• Star — быстрее, денормализовано.
• Snowflake — компактнее, больше JOIN.
• Для OLAP — star.

JSON для гибкости

Когда:

• Атрибуты различаются между записями.
• Внешние ответы API.
• Метаданные.

Пример:

ALTER TABLE products ADD COLUMN attributes JSONB NOT NULL DEFAULT '{}'::jsonb;

Что даёт: не надо ALTER TABLE для каждого нового поля.

⚠️ Не для бизнес-логики. FK не сделать, индексы слабые.

Правило: обязательные поля — колонки, опциональные — JSONB.

Резюме: когда что

Как поддерживать консистентность

4 способа:

1. Триггеры.

CREATE TRIGGER update_counter ...

Плюс: мгновенно. Минус: скрытая логика, тяжело отлаживать.

2. В приложении.

DB::transaction(function () use ($post) {
    $post->save();
    $post->user->increment('posts_count');
});

Плюс: явно. Минус: легко забыть.

3. По расписанию.

Schedule::call(function () {
    DB::statement('
        UPDATE users u SET posts_count = (
            SELECT COUNT(*) FROM posts WHERE user_id = u.id
        )
    ');
})->hourly();

Плюс: просто. Минус: данные отстают.

4. Через очередь.

RecalculatePostCount::dispatch($user_id);

Плюс: асинхронно. Минус: задержка, сложнее.

Опасности денормализации

1. Рассинхрон.

Счётчик обновился, а основная таблица — нет (или наоборот).

Решение: триггеры или транзакции.

2. Двойное обновление.

Счётчик инкрементится дважды из-за retry.

Решение: идемпотентность.

3. Забыли обновить.

Добавили новый путь записи — забыли про денормализованное поле.

Решение: единая точка обновления + тесты.

4. Backup и restore.

При восстановлении из partial backup счётчики могут быть неактуальны.

Решение: пересчёт после восстановления.

Практика

1. Найдите в своём проекте медленный запрос с JOIN.

2. Проверьте: можно ли решить индексами?

3. Если нет — определите, что можно денормализовать:

• Snapshot-поле?
• Счётчик?
• Materialized view?

4. Реализуйте:

ALTER TABLE orders ADD COLUMN items_total NUMERIC(12, 2) NOT NULL DEFAULT 0;

CREATE FUNCTION update_order_total() RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders SET items_total = (
        SELECT COALESCE(SUM(quantity * price), 0)
        FROM order_items
        WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE id = COALESCE(NEW.order_id, OLD.order_id);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_total_trigger
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

5. Проверьте, что при изменении позиции пересчитывается total.

Итог

• Денормализация — только когда реально нужно.
• Snapshot-поля — для истории.
• Счётчики — через триггер или cron.
• Materialized view — для агрегатов.
• Star schema — для OLAP.
• JSONB — для гибких атрибутов.
• Консистентность — триггеры / приложение / cron / очередь.
• Тесты обязательны.

Дальше — схемы: OLTP vs OLAP.