Приведи примеры спроектированной базы данных с денормализацией

Примеры денормализованных баз данных

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

Пример 1: Интернет-магазин с анализом продаж

Нормализованная схема (плохо для аналитики):

-- Множество таблиц, много JOIN'ов
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    customer_id UUID REFERENCES customers(id),
    created_at TIMESTAMP
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY,
    order_id UUID REFERENCES orders(id),
    product_id UUID REFERENCES products(id),
    quantity INT,
    price_per_unit DECIMAL
);

CREATE TABLE products (
    id UUID PRIMARY KEY,
    name VARCHAR,
    category_id UUID REFERENCES categories(id),
    supplier_id UUID REFERENCES suppliers(id)
);

CREATE TABLE categories (id UUID PRIMARY KEY, name VARCHAR);
CREATE TABLE customers (id UUID PRIMARY KEY, city VARCHAR, country VARCHAR);

-- Запрос с 5 JOIN'ами (медленно!)
SELECT 
    o.created_at,
    c.city,
    cat.name as category,
    p.name,
    oi.quantity,
    oi.price_per_unit
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
JOIN customers c ON o.customer_id = c.id;

Денормализованная схема (хорошо для аналитики):

-- Одна таблица фактов с pre-aggregated данными
CREATE TABLE fct_order_items (
    order_item_id UUID PRIMARY KEY,
    order_id UUID,
    order_date DATE,
    order_timestamp TIMESTAMP,
    
    -- Денормализованные данные из customer
    customer_id UUID,
    customer_city VARCHAR,
    customer_country VARCHAR,
    
    -- Денормализованные данные из product
    product_id UUID,
    product_name VARCHAR,
    product_category VARCHAR,
    supplier_id UUID,
    
    -- Меры
    quantity INT,
    unit_price DECIMAL,
    total_amount DECIMAL,
    
    -- Для быстрого фильтра
    created_date DATE,
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Создаём индексы по часто используемым полям
CREATE INDEX idx_fct_order_date ON fct_order_items(order_date);
CREATE INDEX idx_fct_category ON fct_order_items(product_category);
CREATE INDEX idx_fct_country ON fct_order_items(customer_country);

-- Теперь запрос без JOIN'ов
SELECT 
    order_date,
    customer_city,
    product_category,
    product_name,
    SUM(quantity) as total_qty,
    AVG(unit_price) as avg_price,
    SUM(total_amount) as revenue
FROM fct_order_items
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2, 3, 4;

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

• Запросы выполняются в 10-100x быстрее (no joins)
• Проще писать аналитические запросы
• Лучше масштабируемость для больших объёмов

Минусы:

• Дублирование данных (customer_city может измениться)
• Обновление сложнее (нужно синхронизировать)
• Необходимо управлять целостностью данных

Пример 2: Real-time аналитика в SaaS

Вызов: Dashboards должны показывать метрики в реальном времени (5+ млн событий в день).

Решение с денормализацией:

-- Исходные таблицы (нормализованные)
CREATE TABLE events (
    event_id UUID PRIMARY KEY,
    event_type VARCHAR,  -- 'page_view', 'click', 'signup'
    user_id UUID,
    session_id UUID,
    timestamp TIMESTAMP,
    metadata JSONB
);

CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    plan VARCHAR,     -- 'free', 'pro', 'enterprise'
    company_id UUID,
    created_at TIMESTAMP
);

-- Денормализованная таблица для быстрых запросов
CREATE TABLE events_denormalized (
    event_id UUID PRIMARY KEY,
    event_type VARCHAR,
    
    -- Денормализованные пользовательские данные
    user_id UUID,
    user_plan VARCHAR,
    company_id UUID,
    user_created_date DATE,
    
    session_id UUID,
    timestamp TIMESTAMP,
    event_date DATE,
    event_hour DATE,  -- для GROUP BY по часам
    
    -- Денормализованные метаданные (частые поля)
    page_url VARCHAR,
    referrer VARCHAR,
    device_type VARCHAR,
    country VARCHAR,
    
    metadata JSONB
);

-- Мат. представление для ещё большей скорости
CREATE MATERIALIZED VIEW mv_events_hourly AS
SELECT 
    event_date,
    event_hour,
    event_type,
    user_plan,
    country,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users,
    COUNT(DISTINCT session_id) as unique_sessions
FROM events_denormalized
GROUP BY 1, 2, 3, 4, 5;

-- Refresh каждый час
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_events_hourly;

-- Супер быстрый запрос для dashboards
SELECT 
    event_type,
    user_plan,
    SUM(event_count) as total_events,
    SUM(unique_users) as active_users
FROM mv_events_hourly
WHERE event_hour BETWEEN now() - interval '24 hours' AND now()
GROUP BY 1, 2
ORDER BY total_events DESC;

Пример 3: Data warehouse для больших объёмов

Архитектура:

OLTP (нормализованные)
     ↓
ELT pipeline
     ↓
Data warehouse (денормализованные)
     ↓
Agg tables / Materialized Views
     ↓
BI tools / Reports

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

-- Таблица фактов: широкая, одна большая таблица
CREATE TABLE warehouse.fct_sales (
    -- IDs
    sale_id BIGINT,
    order_id BIGINT,
    customer_id BIGINT,
    product_id BIGINT,
    store_id BIGINT,
    salesperson_id BIGINT,
    
    -- Денормализованные атрибуты
    sale_date DATE,
    sale_year INT,
    sale_month INT,
    sale_quarter INT,
    sale_week INT,
    
    customer_segment VARCHAR,      -- Денормализовано
    customer_region VARCHAR,        -- Денормализовано
    
    product_category VARCHAR,       -- Денормализовано
    product_subcategory VARCHAR,    -- Денормализовано
    product_brand VARCHAR,          -- Денормализовано
    
    store_name VARCHAR,             -- Денормализовано
    store_country VARCHAR,          -- Денормализовано
    
    -- Меры
    quantity INT,
    unit_price DECIMAL(10, 2),
    discount_percent DECIMAL(5, 2),
    tax_amount DECIMAL(10, 2),
    sale_amount DECIMAL(12, 2),
    cost_amount DECIMAL(12, 2),
    profit_amount DECIMAL(12, 2)
);

-- Практически no joins нужны
SELECT 
    sale_year,
    sale_month,
    customer_segment,
    product_category,
    COUNT(*) as transaction_count,
    SUM(quantity) as total_units,
    SUM(sale_amount) as revenue,
    SUM(profit_amount) as profit,
    SUM(profit_amount) / SUM(sale_amount) as profit_margin
FROM warehouse.fct_sales
WHERE sale_year >= 2024
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) > 100
ORDER BY revenue DESC;

Пример 4: Логирование и мониторинг

-- Нормализованное логирование (проблема: много JOIN'ов)
CREATE TABLE logs (id, service_id, level, message, created_at);
CREATE TABLE services (id, name, version);
CREATE TABLE environments (id, name);

-- Денормализованное логирование (решение)
CREATE TABLE logs_denormalized (
    log_id UUID PRIMARY KEY,
    timestamp TIMESTAMP,
    log_date DATE,
    log_hour DATE,
    
    service_name VARCHAR,        -- вместо service_id
    service_version VARCHAR,     -- вместо join
    environment VARCHAR,         -- вместо join
    
    log_level VARCHAR,           -- ERROR, WARN, INFO
    message TEXT,
    
    -- Быстрые индексы
    error_code VARCHAR,
    stack_trace TEXT,
    user_id UUID,
    request_id UUID,
    response_time_ms INT,
    
    INDEX idx_timestamp (timestamp),
    INDEX idx_service_level (service_name, log_level),
    INDEX idx_date_hour (log_date, log_hour)
);

-- Поиск ошибок (быстро!)
SELECT 
    service_name,
    COUNT(*) as error_count,
    AVG(response_time_ms) as avg_response_time
FROM logs_denormalized
WHERE log_date >= CURRENT_DATE - INTERVAL '7 days'
  AND log_level = 'ERROR'
GROUP BY service_name
ORDER BY error_count DESC;

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

✅ Используй, если:

• Нужна высокая скорость чтения (analytics, reports)
• Данные часто читаются, редко пишутся
• Объём данных большой (миллиарды строк)
• Нормализованные запросы содержат много JOIN'ов
• Одно значение, часто используемое в фильтре (category, country)

❌ Избегай, если:

• Много писем и обновлений (нормализация лучше)
• Мало данных (не нужна оптимизация)
• Значение часто изменяется (несинхронизированность)
• OLTP система (используй нормализацию)

Best practices

1. Начни с нормализацией, потом денормализуй при необходимости
2. Используй ETL для управления денормализованными таблицами
3. Мониторь данные на актуальность и целостность
4. Документируй какие данные денормализованы и почему
5. Кэшируй мат. представления, если обновление дорого

Заключение

Денормализация — мощный инструмент для performance, но с ответственностью. Используй с умом в read-heavy сценариях (analytics, reporting), но будь осторожен с write-heavy системами.