Объясните разницу между OLTP и OLAP системами. Как это влияет на проектирование хранилищ данных?

OLTP vs OLAP: Фундаментальные различия

OLTP (Online Transaction Processing) — это системы для обработки повседневных операций (транзакции), а OLAP (Online Analytical Processing) — это системы для анализа больших объёмов данных. Эти два подхода имеют принципиально разные цели и требуют разных архитектур.

Таблица сравнения

OLTP: Детали

Характеристики:

• Множество малых операций
• Высокая требовательность к ACID
• Нормализованная схема
• Быстрые запросы на чтение/запись

Пример:

-- Транзакция переводимов денег (OLTP)
BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Системы:

• PostgreSQL
• MySQL
• Oracle Database
• SQL Server
• MongoDB

OLAP: Детали

Характеристики:

• Малое количество сложных запросов
• CAP theorem: выбор между consistency и availability
• Денормализованная/звёздная схема
• Медленные но мощные запросы

Пример:

-- Аналитический запрос (OLAP)
SELECT
    DATE_TRUNC('month', order_date) as month,
    product_category,
    SUM(amount) as total_sales,
    AVG(amount) as avg_sale,
    COUNT(*) as transaction_count
FROM sales
WHERE year(order_date) = 2024
GROUP BY 1, 2
HAVING COUNT(*) > 100
ORDER BY month, total_sales DESC;

Системы:

• Snowflake
• BigQuery
• Redshift
• ClickHouse
• Presto

Проектирование OLTP-системы

-- Нормализованная схема (OLTP)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    order_date TIMESTAMP,
    total DECIMAL(10,2)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT,
    quantity INT,
    price DECIMAL(10,2)
);

-- Индекс на часто используемые поля
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

Оптимизации:

• Нормализация данных (3NF)
• Индексы на foreign keys
• Partitioning по ID
• Row-based storage

Проектирование OLAP-системы

-- Денормализованная/звёздная схема (OLAP)
CREATE TABLE fact_sales (
    sale_id INT,
    user_id INT,
    product_id INT,
    date_id INT,
    amount DECIMAL(10,2),
    quantity INT
);

CREATE TABLE dim_users (
    user_id INT,
    user_name VARCHAR(100),
    country VARCHAR(50),
    user_segment VARCHAR(50)
);

CREATE TABLE dim_products (
    product_id INT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    supplier_id INT
);

CREATE TABLE dim_date (
    date_id INT,
    date DATE,
    year INT,
    month INT,
    quarter INT,
    day_of_week VARCHAR(10)
);

-- Columnstore индекс для скорости
CREATE COLUMNSTORE INDEX idx_fact_sales_columns ON fact_sales;

Оптимизации:

• Денормализация (Star Schema / Snowflake Schema)
• Columnstore индексы
• Partitioning по дате
• Aggregation таблицы
• Materialized views

Гибридный подход: HTAP (Hybrid Transactional/Analytical Processing)

# Современные системы поддерживают оба подхода
from sqlalchemy import create_engine
import pandas as pd

# OLTP часть - быстрая транзакция
engine = create_engine('postgresql://localhost/mydb')
with engine.begin() as conn:
    conn.execute(
        "INSERT INTO users (name, email) VALUES (:name, :email)",
        {"name": "Alice", "email": "alice@example.com"}
    )

# OLAP часть - аналитический запрос
df = pd.read_sql(
    "SELECT * FROM sales_summary WHERE year = 2024",
    engine
)
print(df.groupby('category')['amount'].sum())

HTAP системы:

• PostgreSQL (с расширениями)
• Clickhouse
• TiDB
• Azure Synapse

Выбор архитектуры

Выбирай OLTP, если:

• Нужна высокая скорость обновления
• Много пользователей одновременно
• Нужна ACID гарантия
• Небольшой объём данных

Выбирай OLAP, если:

• Нужен анализ больших объёмов
• Данные обновляются редко (batch)
• Важна скорость запросов на чтение
• Нужна История данных

Типичная Data Engineering архитектура

OLTP (PostgreSQL) → ETL Pipeline → OLAP (Snowflake/BigQuery)
     ↓                                   ↓
  Клиент                           BI Tools (Tableau, Looker)

Вывод: OLTP и OLAP требуют диаметрально противоположных подходов к проектированию. Для достижения оптимальной производительности нужно использовать правильный инструмент для каждой задачи: OLTP для операционных систем и OLAP для аналитики. Современная data engineering часто использует оба подхода вместе в одной экосистеме.