OLTP и OLAP: отличия режимов работы с базами данных

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

Хорошо эту разницу видно на примере интернет-магазина. Пока покупатели оформляют заказы, система постоянно проверяет остатки, записывает платежи и меняет статусы. При этом несколько пользователей могут одновременно претендовать на один и тот же товар, поэтому изменения важно выполнять не только быстро, но и согласованно.

У бизнеса возникают совсем другие вопросы: как изменилась выручка за два года, какие категории растут быстрее, в каких регионах чаще возвращают товары. Чтобы ответить на них, нужно обработать большую часть накопленной истории.

Первый тип нагрузки называют OLTP, второй — OLAP. Разберемся, чем они отличаются и почему это влияет не только на запросы, но и на устройство СУБД и серверной инфраструктуры.

Что такое OLTP и OLAP

OLTP и OLAP правильнее воспринимать не как два вида баз данных, а как два разных способа работы с данными. СУБД может выполнять запросы обоих типов, но ее архитектура обычно лучше приспособлена к определенной нагрузке.

OLTP: много коротких транзакций

OLTP — Online Transaction Processing, или оперативная обработка транзакций. Такой режим характерен для систем, где пользователи и сервисы постоянно создают и изменяют конкретные объекты: заказы, платежи, остатки и бронирования.

Вернемся к примеру с последней единицей товара. Упрощенно резервирование и создание заказа можно представить так:

      BEGIN;

WITH reserved AS (
    UPDATE products
    SET stock = stock - 1
    WHERE id = 1001
      AND stock > 0
    RETURNING id
)
INSERT INTO orders (user_id, product_id, status)
SELECT 42, id, 'new'
FROM reserved
RETURNING id; 

COMMIT;

В этом примере UPDATE уменьшает остаток только в том случае, если товар еще доступен. Если изменение произошло, RETURNING передает идентификатор товара дальше и создается заказ. Если другой покупатель уже успел забрать последнюю единицу, строка не будет обновлена — а значит, новый заказ тоже не появится.

В реальном приложении операций будет больше: к заказу добавятся позиции, платеж и другие связанные данные. Но суть OLTP остается той же: система должна выполнять много небольших операций, а связанные изменения — оставаться согласованными даже при конкурентной работе пользователей. Поэтому для OLTP важны транзакции и свойства ACID — атомарность, согласованность, изолированность и долговечность изменений.

OLAP: анализ больших объемов данных

OLAP — Online Analytical Processing, или аналитическая обработка данных. Здесь вопрос ставится иначе: нас интересует уже не конкретный заказ, а закономерности во множестве накопленных записей.

Допустим, нужно узнать, как по месяцам менялась чистая выручка разных категорий в регионах:

      SELECT
    toStartOfMonth(sale_date) AS month,
    region,
    category,
    count(DISTINCT order_id) AS orders,
    sum(amount - refund) AS revenue
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY
    month,
    region,
    category;

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

Самих запросов такого типа может быть гораздо меньше, чем коротких операций в OLTP-системе. Зато один из них способен обработать сотни миллионов строк.

Дальше рассмотрим эти режимы на примере двух СУБД:

• PostgreSQL — универсальная реляционная СУБД, которую часто используют как основную базу транзакционных приложений;
• ClickHouse — колоночная СУБД, спроектированная прежде всего для аналитической обработки больших объемов данных.

Это не означает, что PostgreSQL умеет только OLTP, а ClickHouse — только OLAP. Разница в том, под какой характер работы оптимизирована их архитектура.

OLTP и OLAP: в чем разница

Если посмотреть только на SQL, различие может показаться небольшим: и там и там мы читаем таблицы, фильтруем строки и выполняем вычисления. Но оно становится заметнее, если учитывать, сколько данных затрагивает один запрос и что он с ними делает. Сравним детальнее:

Что сравниваем	OLTP	OLAP
Объем данных одной операции	Обычно небольшой	Обычно большой
Изменение данных	Частое	Значительно реже
Хранение	Часто строковое	Часто колоночное
Модель данных	Часто нормализованная, без дублирования данных	Может быть подготовленной под анализ
Приоритет	Низкая задержка операции	Высокая скорость обработки большого объема данных

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

Аналитический запрос работает с теми же бизнес-данными совсем иначе. Для отчета могут понадобиться только дата, регион, категория и сумма, зато сразу из миллионов продаж. Если читать каждую запись целиком, вместе с полезными значениями придется загружать множество полей, которые не участвуют в расчете. Поэтому аналитическим СУБД выгодно хранить значения по колонкам и читать только необходимые столбцы.

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

Различаются и цели оптимизации. Для OLTP важна задержка отдельной операции, а для OLAP — скорость, с которой система может обработать большой объем данных.

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

Как OLTP и OLAP работают вместе

Разделять транзакционную и аналитическую нагрузку не всегда имеет смысл. Пока аналитических запросов немного, одна СУБД может одновременно обслуживать приложение и редкие отчеты.

По мере роста истории и усложнения аналитики эти нагрузки начинают конкурировать за ресурсы. Например, во время распродажи PostgreSQL принимает новые заказы, пока параллельно выполняется запрос, который читает большой объем истории, группирует данные и соединяет таблицы. Обе задачи используют одни и те же CPU, память и диски, которые в первую очередь нужны пользовательским операциям.

Поэтому OLTP- и OLAP-нагрузку часто разделяют:

PostgreSQL остается основной базой приложения: здесь создаются заказы и меняются остатки. Затем необходимые данные передаются в ClickHouse, где их можно анализировать, не создавая дополнительную нагрузку на транзакционный контур.

При этом данные необязательно копировать один в один. В транзакционной базе сведения о покупке могут находиться, например, в таблицах orders, products и payments. Для аналитики их можно заранее собрать в одну таблицу sales:

      sale_date | order_id | region | category | amount | refund

Так отчету не приходится каждый раз заново собирать нужные сведения из нескольких таблиц.

Для передачи данных между системами используется несколько подходов.

• ETL (Extract, Transform, Load): данные извлекают из источников, преобразуют и загружают в аналитическое хранилище.
• ELT (Extract, Load, Transform): исходные данные сначала загружают в аналитическое хранилище, а затем преобразуют средствами этого хранилища.
• CDC (Change Data Capture): изменения в исходной системе фиксируют по мере их возникновения и передают в другие системы. При необходимости данные преобразуют отдельно.

Выбор подхода зависит от того, насколько свежими должны быть данные. Ежедневному финансовому отчету может быть достаточно обновления раз в несколько часов, а оперативному дашборду — каждую минуту или почти в реальном времени.

При этом приложение обычно не записывает один заказ синхронно сразу в обе базы. Если ClickHouse временно недоступен, покупатель все равно должен иметь возможность оформить заказ. Поэтому сначала фиксируют бизнес-критичную транзакцию в OLTP-системе, а аналитические данные доставляют отдельно.

PostgreSQL и ClickHouse на одном примере

На примере PostgreSQL и ClickHouse хорошо видно, как разные профили нагрузки отражаются на архитектуре СУБД.

Почему PostgreSQL хорошо подходит для OLTP

Чтобы пользователь увидел страницу заказа, приложению нужно быстро найти запись по идентификатору и получить ее актуальное состояние. После подтверждения платежа эту же запись потребуется изменить.

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

Но одного быстрого поиска для OLTP недостаточно. Пока один процесс изменяет заказ, другие могут читать его или работать со связанными данными. PostgreSQL использует MVCC — Multiversion Concurrency Control, многоверсионное управление конкурентным доступом. Благодаря этому запросы чтения видят согласованный снимок данных и обычно не блокируют параллельную запись. Если несколько транзакций одновременно изменяют одну и ту же строку, возникает конфликт, который разрешают механизмы блокировок и изоляции.

Еще одна задача возникает после успешного изменения. Если база сообщила приложению, что транзакция завершена, подтвержденные данные не должны пропасть после сбоя. Для этого PostgreSQL использует WAL — Write-Ahead Log, журнал предварительной записи. Сведения об изменениях сначала фиксируются в журнале, который затем можно использовать при восстановлении базы.

Получается, что индексы помогают быстро находить отдельные записи, MVCC — работать с ними конкурентно, а WAL — надежно фиксировать изменения. Вместе эти механизмы хорошо соответствуют характеру OLTP-нагрузки: множество клиентов постоянно читают и изменяют небольшие наборы данных.

При этом PostgreSQL не ограничен только транзакционными запросами. В нем можно строить отчеты, использовать JOIN, GROUP BY, оконные функции и другие инструменты аналитики.

Почему ClickHouse хорошо подходит для OLAP

Теперь посмотрим на тот же заказ глазами аналитика. Сам по себе условный заказ № 12501 его почти не интересует. Нужно узнать, например, как за два года менялись продажи разных категорий по регионам и какую долю выручки пришлось вернуть покупателям.

Здесь уже приходится работать не с одной строкой, а с большим массивом истории. И устройство ClickHouse помогает сократить объем работы сразу на нескольких этапах.

Во-первых, данные в ClickHouse хранятся по колонкам. Если отчету нужны только дата, регион, категория, сумма и возврат, остальные поля читать не требуется.

Во-вторых, ClickHouse старается не читать диапазоны данных, которые заведомо не подходят под условия запроса. Например, если таблица содержит продажи за пять лет, а отчету нужен только последний год, правильно организованные данные позволяют пропустить часть истории.

В-третьих, значения обрабатываются блоками, а вычисления можно распределять между несколькими потоками. Это полезно для запросов, где одну и ту же фильтрацию или агрегацию нужно выполнить над миллионами строк.

Наконец, колоночные данные хорошо сжимаются. Для аналитики это важно не только для экономии места: чем меньше данных приходится физически прочитать с диска, тем быстрее выполняется большой запрос.

ClickHouse не стремится любой ценой ускорить поиск одной строки, а старается уменьшить объем лишнего чтения и быстрее обработать большой поток данных. В этом и проявляется его ориентация на OLAP.

Это не означает, что ClickHouse нельзя использовать для точечного чтения или изменения данных. Такие операции поддерживаются, но архитектурный приоритет системы другой — массовая загрузка и последующий анализ больших объемов информации.

Поэтому сравнивать PostgreSQL и ClickHouse по отдельным возможностям не совсем корректно. Обе СУБД умеют читать и изменять данные, но оптимизированы под разные профили нагрузки.

Что выбрать: OLTP, OLAP или оба подхода

Подходящая технология зависит прежде всего от профиля нагрузки: какие запросы выполняются чаще, какие объемы данных они затрагивают и какие операции критичны для приложения.

Сценарий	Что обычно подходит
Приложение в основном создает и изменяет заказы, платежи, пользователей и другие объекты	OLTP
Основная задача — анализировать большую историю, логи, события или метрики	OLAP
Приложению нужны транзакции, а бизнесу — тяжелые отчеты и аналитика	OLTP + OLAP
Аналитике нужны практически сразу самые свежие транзакционные данные	Стоит рассмотреть HTAP

Таблица дает общий ориентир, но один тяжелый отчет не означает, что проекту нужна отдельная OLAP-система. Сначала стоит проверить сам запрос, индексы, структуру таблиц и план выполнения.

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

HTAP: когда OLTP и OLAP нужны одновременно

Разделение OLTP и OLAP хорошо работает, пока аналитика может получать данные с некоторой задержкой. Но бывают задачи, где большой массив истории нужно анализировать практически сразу после новой транзакции.

Например, при проверке платежа антифрод-система должна сохранить операцию и сразу сопоставить ее с предыдущей активностью клиента: обычными суммами, географией платежей, устройствами и недавними операциями. Здесь одновременно нужны свойства OLTP и OLAP — быстро обработать новую транзакцию и выполнить аналитику по накопленным данным.

Для таких сценариев используют HTAP — Hybrid Transactional/Analytical Processing, или гибридную транзакционно-аналитическую обработку. HTAP-системы объединяют оба режима работы в одной архитектуре, хотя внутри могут использовать разные компоненты для транзакций и аналитики.

Один из наглядных примеров HTAP — TiDB. В этой СУБД строковый движок TiKV обслуживает транзакционную нагрузку, а колоночный TiFlash — аналитическую. Оба работают в рамках одной архитектуры.

HTAP можно реализовать и с помощью отдельных OLTP- и OLAP-систем, связанных между собой репликацией или CDC с небольшой задержкой. В таком случае PostgreSQL и ClickHouse остаются самостоятельными СУБД, но вместе образуют архитектуру, в которой транзакционная и аналитическая обработка работают с минимальной задержкой передачи данных.

HTAP имеет смысл прежде всего тогда, когда аналитическим запросам нужны данные, изменившиеся буквально только что. Если допустима задержка в минуты или часы, отдельные OLTP- и OLAP-контуры обычно проще масштабировать и эксплуатировать.

Как подобрать инфраструктуру для OLTP, OLAP и HTAP

При выборе серверного железа характер нагрузки задает разные приоритеты:

• для OLTP важны низкая задержка отдельных операций, производительность ядра и быстрый случайный ввод-вывод;
• для OLAP — параллельные вычисления, достаточный объем RAM и высокая скорость чтения больших массивов данных;
• для HTAP к этим требованиям добавляется изоляция ресурсов, чтобы аналитика не ухудшала работу транзакций.

Но это только ориентир: реальная конфигурация зависит от запросов, объема данных и количества одновременных операций.

В общих чертах приоритеты выглядят так:

Профиль	CPU	Память и диски	Что контролировать
OLTP	Высокая производительность ядра и достаточно ядер для параллельных запросов	RAM для активных данных и индексов; низкая задержка и высокий IOPS	Задержку транзакций
OLAP	Много производительных ядер	RAM для агрегаций и JOIN; высокая скорость чтения	Время запросов и объем обрабатываемых данных
HTAP	Ресурсы для обоих профилей с возможностью изоляции	Запас RAM и I/O для одновременной нагрузки	Влияние аналитики на транзакции

Инфраструктура для OLTP

В OLTP пользователь ждет завершения конкретной операции — например, оформления заказа или подтверждения платежа. Поэтому одна из главных характеристик такой системы — стабильно низкая задержка транзакций.

Короткие OLTP-запросы обычно не получают большой пользы от распараллеливания одного запроса между множеством CPU, поэтому на их задержку заметно влияет производительность отдельного ядра. Дополнительные ядра нужны прежде всего, чтобы одновременно обслуживать больше независимых запросов.

Оперативная память позволяет чаще обслуживать обращения к активным данным и индексам без чтения с диска. Для дисковой подсистемы важны низкая задержка и высокий IOPS — количество операций ввода-вывода в секунду. Это связано в том числе с частыми точечными чтениями и записью WAL при фиксации транзакций.

Инфраструктура для OLAP

Для OLAP, наоборот, обычно важнее количество производительных ядер. Большой запрос по сотням миллионов строк можно разбить на части и выполнять несколькими потоками, поэтому аналитическая СУБД способна использовать параллелизм внутри одного запроса. Производительность каждого ядра тоже влияет на результат, но OLAP чаще получает заметную пользу от увеличения их числа.

Память здесь нужна не только как кэш. Большие JOIN, GROUP BY и сортировки создают промежуточные данные, которые желательно обрабатывать в RAM, а не переносить на диск.

Меняется и характер работы с накопителем. Если ClickHouse читает большой диапазон истории, важна уже не только задержка отдельной операции, но и throughput — объем данных, который диск способен передать за секунду. Например, если процессор может обработать данные быстрее, чем накопитель успевает их читать, добавление CPU почти не ускорит запрос: узким местом остается диск.

В распределенном OLAP-кластере похожая ситуация может возникнуть и с сетью, если узлы активно обмениваются данными и промежуточными результатами.

Инфраструктура для HTAP

HTAP объединяет оба типа нагрузки, поэтому появляется дополнительная проблема: аналитический запрос не должен занять CPU, память или I/O настолько, чтобы заметно увеличивать задержку транзакций.

Для этого транзакционные и аналитические ресурсы стараются изолировать. В некоторых HTAP-системах разные движки можно размещать на отдельных группах узлов: одна часть кластера обслуживает OLTP, другая — тяжелую аналитику.

Главное при проверке HTAP — тестировать не OLTP и OLAP по отдельности, а их совместную работу. Система может отлично справляться с каждым профилем отдельно, но испытывать нехватку ресурсов, когда транзакции и аналитика выполняются одновременно.

Поэтому при нагрузочном тестировании полезно сравнивать задержку транзакций до запуска аналитики и во время ее работы. Если задержка резко растет, нужно искать общий ограничивающий ресурс — CPU, память, диски или сеть — и усиливать изоляцию либо масштабировать соответствующую часть системы.

Как построить OLTP- и OLAP-контуры в Selectel

Разделить транзакционную и аналитическую нагрузку можно с помощью облачных баз данных Selectel. Вместо самостоятельного развертывания и обслуживания двух СУБД команда получает готовые управляемые кластеры, а значительную часть инфраструктурных задач берет на себя облачный сервис. В частности, для managed-баз доступны мониторинг, резервное копирование и инструменты управления кластерами.

Для архитектуры из нашего примера можно использовать два независимых контура:

Для транзакционной части подойдет облако для PostgreSQL: управляемый сервис для работы с СУБД позволяет обслуживать заказы, платежи, остатки и другие операции, чувствительные к задержке. Состояние кластера и отдельных баз можно отслеживать с помощью  встроенного мониторинга, а метрики — экспортировать в формате Prometheus.

Аналитический контур можно построить на облаке для ClickHouse. Для групп нод можно выбирать количество vCPU, объем RAM и размер диска, а после создания кластера — изменять конфигурацию по мере роста нагрузки. Такое решение позволяет масштабировать OLAP-контур независимо от транзакционного: рост числа отчетов не требует одновременно увеличивать ресурсы PostgreSQL, и наоборот. Подробнее о доступных вариантах можно посмотреть в документации по конфигурациям ClickHouse.

Заключение

OLTP и OLAP решают разные задачи: первый режим рассчитан на множество коротких транзакций, второй — на анализ больших объемов накопленных данных. По мере роста проекта эти нагрузки можно разделить между самостоятельными контурами или, если аналитике нужны максимально свежие данные, объединить в HTAP-архитектуре.