OLTP и OLAP: почему одну базу нельзя использовать для всего

Прямой ответ

OLTP (online transaction processing) и OLAP (online analytical processing) — два разных профиля нагрузки, которые требуют разных систем хранения.

OLTP — обработка бизнес-операций: создание заказа, списание средств, изменение статуса. Характерные черты: много коротких операций, работа с одной-двумя строками за раз, высокая частота записи, строгие требования к согласованности и задержке.

OLAP — аналитические запросы: отчёты, агрегации, срезы по периодам. Характерные черты: мало запросов, но каждый читает миллионы строк, преобладает чтение, задержка измеряется секундами, согласованность может быть отложенной.

Сравнение:

Почему строчное хранение не подходит для аналитики: при агрегации по одной колонке из таблицы с тридцатью колонками система читает и отбрасывает 29/30 данных. Колоночное хранение располагает значения одной колонки подряд, что даёт два преимущества: читаются только нужные колонки и данные в колонке однотипны, поэтому сжимаются в разы лучше. Подробно — в статье про колоночные хранилища.

Обратное тоже верно: колоночная система плохо подходит для точечного обновления одной строки, потому что изменение требует перезаписи целого блока колонки.

-- OLTP: типичный запрос
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;

-- OLAP: типичный запрос
SELECT date_trunc('month', created_at) AS m, category,
       count(*) AS orders, sum(total) AS revenue
FROM orders JOIN order_items USING (order_id)
WHERE created_at >= now() - INTERVAL '2 years'
GROUP BY m, category ORDER BY m;

Практика: как разделить нагрузки

1. Не запускайте аналитику на основной базе. Один тяжёлый запрос вытесняет кэш, занимает соединения и замедляет все операции. Даже при наличии свободных ресурсов влияние заметно.

2. Минимальное решение — реплика для чтения. Аналитические запросы уходят на реплику. Это снимает нагрузку с основной базы, но не решает проблему строчного хранения: тяжёлые агрегации остаются медленными. О настройке — в статье про реплики для чтения.

3. Полноценное решение — отдельное хранилище. Данные переносятся в колоночную систему, оптимизированную под агрегации. Способы переноса:

• Пакетная выгрузка по расписанию. Просто, но данные устаревают на интервал выгрузки.
• CDC (change data capture) — поток изменений из журнала транзакций. Даёт задержку в секунды — см. CDC.
• Материализованные представления для небольших объёмов внутри той же СУБД — см. materialized view.

4. Денормализуйте при переносе. В хранилище соединения дороги, поэтому таблицы объединяют заранее: широкая таблица событий со всеми нужными атрибутами работает быстрее, чем нормализованная схема.

5. Разделяйте слои хранения. Сырой слой повторяет структуру источника, слой витрин содержит готовые агрегаты. О подходе — в статье про lakehouse.

Рекомендации:

1. Определите допустимую устаревость данных. Для операционных экранов нужны секунды, для ежедневных отчётов — часы. От этого зависит способ переноса.
2. Не копируйте OLTP-ограничения в хранилище. Внешние ключи и нормальные формы там не нужны и мешают загрузке.
3. Следите за объёмом переносимого. Копирование всей истории при каждом запуске — частая ошибка; используйте инкрементальную загрузку по метке изменения.
4. Проверяйте согласованность чисел. Разные системы должны давать одинаковые результаты по одним показателям; расхождение обычно связано с моментом среза.
5. Измеряйте стоимость запросов в хранилище. Колоночная система быстрее, но не мгновенно: запрос без ограничений по времени может прочитать годы данных.

Практический сценарий: отчёт «положил» основную базу

Контекст: маркетолог запустил отчёт за два года в рабочее время. Сервис оформления заказов начал отвечать с задержкой 8 секунд, часть запросов завершилась таймаутом.

1. Диагностика. Запрос выполнял соединение трёх таблиц с агрегацией по 41 млн строк на основной базе. Он вытеснил кэш страниц, и последующие точечные запросы начали читать с диска.
2. Быстрое действие. Запрос прервали, ограничения на одновременные тяжёлые запросы добавили на уровне пула соединений.
3. Системное решение. (а) Развернули ClickHouse для аналитики; (б) настроили перенос через CDC с задержкой около 10 секунд; (в) отчёты перевели на хранилище, доступ к основной базе для аналитических ролей закрыли; (г) в хранилище создали широкую таблицу событий, чтобы исключить соединения.
4. Результат. Тот же отчёт выполняется за 3,4 секунды вместо 22 минут и не влияет на основную базу. Задержка данных — около 10 секунд, что для задач маркетинга достаточно.
5. Профилактика. Ввели правило: доступ к основной базе имеют только сервисные роли, аналитика работает через хранилище; добавили алерт на запросы длительнее 30 секунд в OLTP-контуре.

Вывод: конфликт между операционной и аналитической нагрузкой неизбежен, если они работают на одной системе. Разделение контуров дешевле, чем постоянное устранение последствий.

FAQ

Можно ли использовать одну систему для обеих нагрузок? Для небольших объёмов — да: реплика для чтения и ограничения на запросы часто достаточны. Проблемы начинаются, когда аналитические запросы читают десятки миллионов строк или требуют сложных агрегаций.

Что такое HTAP? Системы, совмещающие транзакционную и аналитическую обработку, обычно за счёт хранения данных в двух форматах одновременно. Это снимает необходимость переноса, но усложняет эксплуатацию и накладывает ограничения на объёмы.

Насколько актуальными должны быть данные в хранилище? Определяется задачей. Операционные дашборды требуют секунд, финансовая отчётность — суток. Чем ниже требование к актуальности, тем проще и дешевле перенос.

Нужно ли дублировать все данные в хранилище? Нет. Переносят то, что используется в аналитике. Копирование всей базы увеличивает стоимость и время загрузки без пользы.

Источники: ClickHouse docs, PostgreSQL docs (resource settings), Wikipedia (OLAP) — ссылки в шапке.