В чём разница между OLAP и OLTP системами?

OLAP vs OLTP: различия и применение

OLTP (Online Transaction Processing) и OLAP (Online Analytical Processing) — это два противоположных подхода к организации баз данных, оптимизированных под разные типы операций.

OLTP — операционные системы

OLTP оптимизирован для быстрых транзакций с небольшим объёмом данных. Это системы, которые обслуживают повседневную работу приложений.

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

• Объём операции: маленький (несколько строк за раз)
• Частота: высокая (тысячи операций в секунду)
• Тип запроса: INSERT, UPDATE, DELETE, SELECT с одной строкой
• Структура: нормализованная (много таблиц, связи)
• Скорость ответа: миллисекунды (важна скорость)
• Хранение: текущее состояние данных

Примеры OLTP:

• Банковская система: переводы денег, снятие, внесение
• E-commerce: заказы, платежи, инвентарь
• CRM: создание контактов, обновление статусов
• Социальные сети: публикация постов, лайки, комментарии

Пример запроса OLTP:

-- Пример: обновить статус заказа
BEGIN TRANSACTION;
  UPDATE orders SET status = 'shipped' WHERE order_id = 12345;
  INSERT INTO order_history (order_id, status, timestamp) 
    VALUES (12345, 'shipped', NOW());
COMMIT;

OLAP — аналитические системы

OLAP оптимизирован для сложного анализа больших объёмов данных. Это системы для бизнес-аналитики и отчётности.

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

• Объём операции: большой (миллионы строк за раз)
• Частота: низкая (несколько запросов в час)
• Тип запроса: SELECT с агрегированием, многомерный анализ
• Структура: денормализованная (звёзды, снежинки, кубы)
• Скорость ответа: может быть медленнее (сложность важнее скорости)
• Хранение: историческая информация, факты и измерения

Примеры OLAP:

• Data warehouse: анализ продаж по регионам, товарам, периодам
• BI платформы: дашборды, отчёты, KPI
• Аналитика: когортный анализ, funnel анализ
• Прогнозирование: тренды, сезонность, прогнозы

Пример запроса OLAP:

-- Пример: продажи по регионам и месяцам
SELECT 
    DATE_TRUNC('month', order_date) as month,
    region,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(DISTINCT customer_id) as unique_customers,
    AVG(order_value) as avg_order
FROM sales_fact
WHERE year = 2024
GROUP BY DATE_TRUNC('month', order_date), region, product_category
ORDER BY month, revenue DESC;

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

Архитектура: OLTP → ETL → OLAP

В больших компаниях используется комбинация обеих:

┌─────────────────────────────────────────────────────────────┐
│  OLTP системы (операционные базы)                           │
│  • PostgreSQL (заказы)                                      │
│  • MySQL (пользователи)                                     │
│  • Oracle (финансы)                                         │
└─────────────────┬───────────────────────────────────────────┘
                  │
          ┌───────▼────────┐
          │  ETL Pipeline  │  (Extraction → Transform → Load)
          │  • Apache Airflow
          │  • dbt
          │  • Talend
          └───────┬────────┘
                  │
┌─────────────────▼───────────────────────────────────────────┐
│  OLAP системы (хранилища данных)                            │
│  • Snowflake (облачное хранилище)                           │
│  • ClickHouse (аналитика)                                   │
│  • BigQuery (Google Cloud)                                  │
└─────────────────────────────────────────────────────────────┘
                  │
      ┌───────────┴──────────────┐
      │                          │
  ┌───▼────┐              ┌─────▼──────┐
  │ BI Tools│              │  Analytics │
  │(Tableau)│              │  (Python)  │
  └────────┘              └────────────┘

Конкретные примеры

OLTP: Банк

-- Быстрое снятие со счёта (OLTP)
BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'ACC123';
  INSERT INTO transactions (account_id, amount, type, timestamp) 
    VALUES ('ACC123', -1000, 'withdrawal', NOW());
COMMIT;  -- Важна скорость!

OLAP: Анализ тренда

-- Анализ расходов по категориям (OLAP)
SELECT 
    DATE_TRUNC('week', transaction_date) as week,
    category,
    COUNT(*) as num_transactions,
    SUM(amount) as total_amount,
    AVG(amount) as avg_transaction
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY DATE_TRUNC('week', transaction_date), category
ORDER BY week, total_amount DESC;  -- Может работать минуты

Ключевые отличия в практике

OLTP оптимизирует:

• Скорость записи и быстрых чтений
• Консистентность данных (ACID транзакции)
• Блокировки и конфликты

OLAP оптимизирует:

• Скорость чтения больших объёмов
• Сжатие данных
• Параллелизм запросов
• Аггрегирование

Итог

OLTP — это «сердце» приложения, обеспечивающее текущие операции. OLAP — это «мозг», который анализирует накопленные данные. Data Analyst работает в основном с OLAP системами, извлекая insights из больших объёмов данных, в то время как разработчики приложений работают с OLTP для обеспечения функционала.