Схемы: OLTP vs OLAP

Два мира

OLTP (Online Transaction Processing):

• Много коротких транзакций.
• Точки: 1-2 строки на запрос.
• Read/write mix.
• Миллисекунды.
• Нормализованные схемы.

OLAP (Online Analytical Processing):

• Мало длинных запросов.
• Сканы миллионов строк.
• Read-heavy.
• Секунды-минуты.
• Денормализованные схемы.

Правило:

• Клиентский сайт — OLTP.
• Аналитика, отчёты — OLAP.
• Обычно два хранилища.

Пример: интернет-магазин

OLTP:

-- Быстро, точечно
SELECT * FROM orders WHERE id = 12345;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (...);

OLAP:

-- Тяжело, много данных
SELECT
    DATE_TRUNC('month', created_at) AS month,
    category_id,
    SUM(total) AS revenue,
    COUNT(DISTINCT user_id) AS buyers
FROM orders
JOIN order_items USING (id)
JOIN products USING (product_id)
WHERE created_at >= '2024-01-01'
GROUP BY month, category_id
ORDER BY month, revenue DESC;

Что важно:

• OLTP — индексы, быстрые точечные запросы.
• OLAP — колоночное хранение, партиции, денормализация.

Аналитическое хранилище

DWH (Data Warehouse) — отдельная БД для аналитики.

ETL / ELT:

• Extract — вытянуть из OLTP.
• Transform — очистить, преобразовать.
• Load — залить в DWH.

Что даёт:

• Не мешает OLTP-нагрузке.
• Готовые к анализу данные.
• История без потери деталей.

Инструменты: Airflow, dbt, Dagster, Pentaho.

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

Компоненты:

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

Пример:

dim_date
   |
fact_sales ─ dim_product
   |
   |── dim_customer
   |
   |── dim_store

Fact-таблица:

CREATE TABLE fact_sales (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    date_id INTEGER NOT NULL REFERENCES dim_date(id),
    product_id BIGINT NOT NULL REFERENCES dim_product(id),
    customer_id BIGINT NOT NULL REFERENCES dim_customer(id),
    store_id INTEGER NOT NULL REFERENCES dim_store(id),

    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    discount NUMERIC(10, 2) NOT NULL DEFAULT 0,
    amount NUMERIC(12, 2) NOT NULL   -- quantity * unit_price - discount
);

Измерение:

CREATE TABLE dim_product (
    id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    subcategory TEXT,
    brand TEXT,
    supplier TEXT
);

CREATE TABLE dim_date (
    id INTEGER PRIMARY KEY,
    date DATE NOT NULL UNIQUE,
    year INTEGER NOT NULL,
    quarter INTEGER NOT NULL,
    month INTEGER NOT NULL,
    month_name TEXT NOT NULL,
    week INTEGER NOT NULL,
    day INTEGER NOT NULL,
    weekday INTEGER NOT NULL
);

Что даёт:

• Денормализованные измерения → нет JOIN.
• Быстрые агрегаты.
• Понятные отчёты.

Запрос:

SELECT
    d.year,
    d.month,
    p.category,
    SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.id = f.date_id
JOIN dim_product p ON p.id = f.product_id
WHERE d.year = 2025
GROUP BY d.year, d.month, p.category;

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

Отличие: измерения нормализованы дальше.

Пример:

dim_product → dim_category → dim_department

Что даёт: меньше дублей.

Что стоит: больше JOIN, медленнее.

Правило: для OLAP — star, не snowflake.

Columnar storage

OLTP — row-based: строка целиком на диске.

OLAP — column-based: колонка целиком.

Что даёт:

• Сжатие — одинаковые значения рядом.
• Быстрые агрегаты — SUM(amount) читает только одну колонку.
• Меньше I/O — не читаем ненужные колонки.

СУБД:

• ClickHouse — популярный columnar.
• DuckDB — встроенный.
• BigQuery, Redshift, Snowflake — облачные.

Что стоит: медленные UPDATE / DELETE (переписывает колонку).

Правило: OLAP — columnar. OLTP — row-based.

Partitioning

Партиционирование — разделение таблицы на части.

PostgreSQL (declarative):

CREATE TABLE orders (
    id BIGINT,
    created_at DATE NOT NULL,
    ...
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Что даёт:

• Запросы по диапазону читают одну партицию.
• DROP PARTITION вместо DELETE.
• Индексы на партицию — меньше.

MySQL:

ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);

Правило: партиционируй, когда таблица > 10-100 GB или > 100M строк.

Star vs Snowflake vs Fact Constellation

Star:

• Один факт, много измерений.
• Денормализованные измерения.
• Просто, быстро.

Snowflake:

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

Fact Constellation (Galaxy):

• Несколько фактов, общие измерения.
• Например: продажи + возвраты.

Правило: начинай со star. Snowflake — если дублирование критично.

Slowly Changing Dimensions (SCD)

Проблема: у клиента изменился город. В отчётах за прошлый год — какой город?

Типы SCD:

Type 1 — перезапись:

UPDATE dim_customer SET city = 'Питер' WHERE id = 5;

Плюс: просто. Минус: история теряется.

Type 2 — новая строка:

| id | cust_id | city      | valid_from | valid_to   | is_current |
|----|---------|-----------|------------|------------|------------|
| 1  | 5       | Москва    | 2024-01-01 | 2025-06-01 | false      |
| 2  | 5       | Питер     | 2025-06-01 | NULL       | true       |

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

Type 3 — доп. колонка:

| id | city_current | city_previous |

Редко. Только для 1 предыдущего значения.

Правило: для аналитики — Type 2.

Data Lake vs DWH

Data Lake:

• Сырые данные (CSV, JSON, parquet).
• S3, HDFS.
• Schema-on-read.
• Гибко, дёшево.

DWH:

• Структурированные данные.
• Schema-on-write.
• Дороже, но быстрее.

Lakehouse:

• Гибрид: Delta Lake, Iceberg, Hudi.
• ACID + schema evolution.
• Тренд последних лет.

Real-time analytics

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

Решение:

• Streaming ETL — Kafka → ClickHouse.
• Change Data Capture (CDC) — Debezium.
• Lambda architecture — batch + stream.

Что даёт: данные с задержкой минуты, не часы.

Выбор хранилища

Правило:

• 1M строк — PostgreSQL справится.
• 100M — партиции + индексы или ClickHouse.
• 1B+ — специализированное.

Пример: разделение OLTP и OLAP

OLTP (PostgreSQL):

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    total NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

CDC в ClickHouse:

# Debezium
connector: postgres
host: postgres
whitelist: public.orders

OLAP (ClickHouse):

CREATE TABLE orders_analytics (
    id UInt64,
    user_id UInt64,
    status LowCardinality(String),
    total Decimal(12, 2),
    created_at DateTime
) ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (user_id, created_at);

Запросы для отчётов — в ClickHouse. OLTP не тормозит.

Частые ошибки

1. Аналитика на OLTP-БД:

Тяжёлый отчёт блокирует сайт

Решение: отдельное хранилище.

2. Star-схема для OLTP:

Дубли, сложно поддерживать

Решение: нормализация для OLTP.

3. OLAP без партиций:

Сканы всей таблицы

Решение: партиционирование по дате.

4. Нет SCD Type 2:

История изменения атрибутов теряется

Решение: Type 2 для аналитики.

5. Snowflake вместо star:

Много JOIN, медленно

Решение: star.

6. Row-based для аналитики:

SUM читает все колонки

Решение: columnar (ClickHouse, DuckDB).

7. DWH без ETL:

Ручные выгрузки

Решение: dbt, Airflow.

Практика

1. Определите свою нагрузку: OLTP или OLAP?

2. Если OLTP — нормализуйте, добавьте индексы.

3. Если OLAP — сделайте star-схему.

4. Настройте партиционирование по дате для больших таблиц.

5. Для аналитики попробуйте ClickHouse или materialized view.

6. Настройте SCD Type 2 для измерения dim_customer.

Итог

• OLTP — короткие транзакции, нормализация.
• OLAP — тяжёлые запросы, денормализация.
• Star schema — для OLAP.
• Columnar storage — быстрое чтение.
• Partitioning — по дате.
• SCD Type 2 — история измерений.
• DWH + ETL — отдельно от OLTP.
• Real-time — CDC + ClickHouse.

Дальше — проектирование связей.