Что такое денормализация и когда её применять?

Денормализация: когда нарушать правила нормализации

Денормализация — это намеренное нарушение правил нормализации БД (1NF, 2NF, 3NF) путём добавления избыточных данных или объединения таблиц для улучшения производительности запросов.

Другими словами, мы жертвуем целостностью данных ради скорости.

Нормализованная vs Денормализованная схема

Нормализованная (классическая):

users
├─ user_id (PK)
├─ name
├─ city_id (FK)

cities
├─ city_id (PK)
├─ city_name
├─ country

orders
├─ order_id (PK)
├─ user_id (FK)
├─ amount

-- Нормализованный запрос (больше JOIN)
SELECT u.user_id, u.name, c.city_name, c.country, COUNT(o.order_id) as orders
FROM users u
JOIN cities c ON u.city_id = c.city_id
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name, c.city_name, c.country;

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

users
├─ user_id (PK)
├─ name
├─ city_id
├─ city_name  ← избыточные данные из cities
├─ country    ← избыточные данные из cities
├─ total_orders  ← денормализованное значение

orders
├─ order_id (PK)
├─ user_id
├─ amount
├─ user_name  ← дублирование имени пользователя
├─ city_name  ← дублирование названия города

-- Денормализованный запрос (меньше JOIN, быстрее)
SELECT user_id, name, city_name, country, total_orders
FROM users;

Плюсы и минусы денормализации

Плюсы:

• ✅ Меньше JOIN → быстрее SELECT
• ✅ Проще приложению (меньше логики)
• ✅ Лучше для OLAP (аналитика)
• ✅ Кэшировать вычисления

Минусы:

• ❌ Риск несогласованности данных (если забыл обновить дубль)
• ❌ Больше байт на диске (дублирование данных)
• ❌ Сложнее UPDATE/DELETE (нужно обновлять все дубли)
• ❌ Нарушение ACID принципов

Когда применять денормализацию

1. Аналитика и отчёты (OLAP)

Отчёты редко изменяют данные, только читают. Денормализация помогает:

-- Аналитическая таблица (денормализованная)
CREATE TABLE sales_denorm AS
SELECT
    o.order_id,
    o.order_date,
    u.user_id,
    u.name as user_name,
    u.email,
    u.city_id,
    c.city_name,
    c.country,
    p.product_id,
    p.name as product_name,
    p.category,
    oi.quantity,
    oi.price,
    (oi.quantity * oi.price) as total_amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN cities c ON u.city_id = c.city_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- Теперь отчёты очень быстро:
SELECT
    country,
    category,
    SUM(total_amount) as sales,
    COUNT(order_id) as orders
FROM sales_denorm
GROUP BY country, category;

2. Кэширование вычисленных значений

-- Нормализованная версия (медленная)
SELECT u.user_id, COUNT(o.order_id) as total_orders
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

-- Денормализованная версия (быстрая)
-- Добавляем колонку total_orders в таблицу users
ALTER TABLE users ADD COLUMN total_orders INTEGER DEFAULT 0;

-- Обновляем при каждом INSERT в orders:
UPDATE users SET total_orders = total_orders + 1 WHERE user_id = NEW.user_id;

-- Теперь просто:
SELECT user_id, total_orders FROM users;

3. Дополнительные колонки для быстрого доступа

-- Вместо калькулирования каждый раз
SELECT
    order_id,
    user_id,
    product_id,
    amount,
    CASE WHEN amount > 1000 THEN 'High' ELSE 'Low' END as price_level
FROM orders;

-- Денормализуем: добавляем price_level как колонку
ALTER TABLE orders ADD COLUMN price_level VARCHAR;
UPDATE orders SET price_level = CASE WHEN amount > 1000 THEN 'High' ELSE 'Low' END;

-- Теперь просто:
SELECT order_id, user_id, product_id, amount, price_level FROM orders;

4. Часто используемые данные из другой таблицы

-- Вместо JOIN всегда:
SELECT o.*, c.city_name FROM orders o JOIN cities c ON o.city_id = c.city_id;

-- Добавляем city_name в orders
ALTER TABLE orders ADD COLUMN city_name VARCHAR;

-- При INSERT/UPDATE в cities обновляем orders:
UPDATE orders SET city_name = NEW.city_name WHERE city_id = NEW.city_id;

-- Теперь просто:
SELECT * FROM orders;  -- city_name уже есть

Практический пример: Денормализованная таблица продаж

from sqlalchemy import create_engine, event, text
import pandas as pd

engine = create_engine("postgresql://user:pass@localhost/db")

# Создаём денормализованную таблицу для аналитики
def create_denormalized_sales():
    sql = """
    CREATE TABLE IF NOT EXISTS sales_analytics (
        sale_id BIGSERIAL PRIMARY KEY,
        order_id BIGINT,
        user_id BIGINT,
        user_name VARCHAR,
        user_email VARCHAR,
        city_name VARCHAR,
        country VARCHAR,
        product_id BIGINT,
        product_name VARCHAR,
        category VARCHAR,
        quantity INTEGER,
        unit_price DECIMAL(10,2),
        total_amount DECIMAL(12,2),
        order_date TIMESTAMP,
        processed_at TIMESTAMP DEFAULT NOW(),
        INDEX idx_user (user_id),
        INDEX idx_product (product_id),
        INDEX idx_date (order_date),
        INDEX idx_country (country)
    );
    """
    with engine.connect() as conn:
        conn.execute(text(sql))
        conn.commit()

# Материализованное представление для регулярного обновления
def refresh_denormalized_sales():
    sql = """
    INSERT INTO sales_analytics
    (
        order_id, user_id, user_name, user_email,
        city_name, country, product_id, product_name,
        category, quantity, unit_price, total_amount, order_date
    )
    SELECT
        o.id, u.id, u.name, u.email,
        c.name, c.country, p.id, p.name,
        p.category, oi.quantity, oi.price,
        (oi.quantity * oi.price), o.created_at
    FROM orders o
    JOIN users u ON o.user_id = u.id
    JOIN cities c ON u.city_id = c.id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    WHERE o.created_at > (
        SELECT COALESCE(MAX(order_date), '1970-01-01')
        FROM sales_analytics
    )
    """
    with engine.connect() as conn:
        conn.execute(text(sql))
        conn.commit()
        print("Денормализованная таблица обновлена")

# Запуск при запуске приложения
create_denormalized_sales()
refresh_denormalized_sales()

# Теперь отчёты очень быстрые
def get_sales_by_country():
    return pd.read_sql(
        "SELECT country, SUM(total_amount) as sales, COUNT(*) as orders FROM sales_analytics GROUP BY country",
        engine
    )

Стратегии управления денормализацией

1. Материализованное представление (Materialized View)

CREATE MATERIALIZED VIEW user_order_stats AS
SELECT
    u.user_id,
    u.name,
    COUNT(o.order_id) as total_orders,
    SUM(o.amount) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;

-- Обновляем периодически
REFRESH MATERIALIZED VIEW user_order_stats;

2. Trigger для автоматического обновления

-- При вставке заказа обновляем статистику
CREATE TRIGGER update_user_stats
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE users SET total_orders = total_orders + 1 WHERE user_id = NEW.user_id;
END;

3. Background job для периодического обновления

from celery import shared_task
from datetime import datetime

@shared_task
def refresh_denormalized_data():
    """Обновляем денормализованные данные каждый час"""
    engine = create_engine("postgresql://...")
    
    with engine.connect() as conn:
        # Очищаем и переполняем
        conn.execute(text("TRUNCATE TABLE sales_analytics"))
        conn.execute(text("""
            INSERT INTO sales_analytics
            SELECT o.*, u.name, c.city_name, p.product_name
            FROM orders o
            JOIN users u ON o.user_id = u.id
            JOIN cities c ON u.city_id = c.id
            JOIN products p ON o.product_id = p.id
        """))
        conn.commit()
    
    print(f"Денормализация завершена в {datetime.now()}")

Когда НЕ использовать денормализацию

❌ Аналитика в реальном времени (OLTP)
❌ Часто обновляемые данные
❌ Маленькие таблицы (несколько тысяч строк)
❌ Если нет проблем с производительностью
❌ Если целостность данных критична

Best Practices

1. Сначала нормализуй — начни с чистой схемы
2. Профилируй запросы — найди узкие места
3. Денормализуй только если нужно — не спеши
4. Документируй причину — почему именно эта денормализация
5. Автоматизируй обновления — triggers, jobs, views
6. Мониторь консистентность — проверяй на расхождения

Заключение

Денормализация — это баланс между скоростью и целостностью:

• Нужна для OLAP и аналитики (отчёты)
• Опасна для OLTP и транзакций (изменение данных)
• Всегда есть overhead на поддержку (обновление дублей)
• Используй только если действительно нужна