OLAP: почему колоночные хранилища быстрее для аналитики

Прямой ответ

OLAP (online analytical processing) — класс задач, где читают много строк, но мало колонок, и агрегируют их: отчёты, дашборды, когортный анализ. OLTP, наоборот, обслуживает короткие точечные операции «одна строка за раз». Колоночные хранилища созданы под первый сценарий и выигрывают за счёт четырёх механизмов.

1. Колоночное хранение. Данные каждой колонки лежат подряд, поэтому запрос SELECT sum(total) FROM orders WHERE date = ... читает только нужные колонки, а не всю строку целиком. На таблицах из десятков колонок выигрыш — на порядок.
2. Сжатие. Однородные значения в колонке сжимаются эффективно (LZ4, ZSTD, дельта-кодирование, dictionary encoding). Меньше байт на диске — меньше чтения с диска, а это главный ограничитель аналитики.
3. Векторизованное исполнение. Обработка идёт блоками значений, а не построчно, что использует SIMD-инструкции и кэш процессора.
4. Индексы пропуска и сортировка. Первичный ключ задаёт физический порядок данных; по нему движок отбрасывает целые блоки (granules в ClickHouse, zone maps в других системах), не читая их вовсе.

Что из этого следует на практике:

• Порядок колонок в первичном ключе критичен. Фильтр по колонке, стоящей в ключе после «неотфильтрованной», не даёт пропуска блоков.
• SELECT * в аналитике — антипаттерн. Он сводит на нет преимущество колоночности и увеличивает объём чтения.
• Высококардинальные JOIN дороже агрегаций. Колоночные СУБД оптимизированы под сканирование и агрегацию; тяжёлые соединения часто выгоднее предварительно денормализовать.
• Обновления и удаления дороги. Многие колоночные движки реализуют мутации асинхронно и пакетно — поэтому аналитический слой обычно заполняют append-only потоком.

Практика: сравнение на конкретном запросе

Задача: сумма продаж по регионам за день.

-- ClickHouse: MergeTree с первичным ключом (date, region)
SELECT region, sum(total) AS revenue, uniqExact(user_id) AS users
FROM orders
WHERE date = '2026-10-20'
GROUP BY region;

-- PostgreSQL: та же задача на строковой таблице
SELECT region, sum(total) AS revenue, count(DISTINCT user_id) AS users
FROM orders
WHERE created_at >= '2026-10-20' AND created_at < '2026-10-21'
GROUP BY region;

Разница в поведении:

• ClickHouse читает только колонки region, total, user_id, отбрасывает блоки вне диапазона даты и агрегирует векторизованно.
• PostgreSQL идёт по индексу на created_at, затем выполняет heap fetch для каждой строки — читает всю страницу целиком. Для точечных запросов это эффективно, для сканирования миллионов строк — нет. Ускорить строковый вариант помогает покрывающий индекс и материализованное представление.

Практические рекомендации:

1. Проектируйте ключ под фильтры. В ClickHouse первичный ключ (date, region) обслуживает фильтр по дате и региону; ключ (region, date) — наоборот. Проверить эффективность можно по read_rows в system.query_log.
2. Денормализуйте разумно. Широкая таблица с заранее подтянутыми атрибутами обычно быстрее, чем соединение трёх справочников на каждый запрос.
3. Используйте предзаполненные агрегаты. Projection/материализованные представления хранят готовые суммы по популярным срезам; запрос к ним читает в сотни раз меньше данных.
4. Следите за cardinality. uniqExact точнее, но дороже uniq; для дашбордов часто достаточно приближённой оценки. Как интерпретировать распределения и перцентили — в статье про гистограммы метрик.

Практический сценарий: дашборд открывается 40 секунд

Контекст: отчёт по продажам на ClickHouse, таблица 2 млрд строк, запрос с фильтром по менеджеру и дате.

1. Диагностика. В system.query_log видим: read_rows = 1.8e9, то есть прочитана почти вся таблица. Первичный ключ — (date), а фильтр по менеджеру идёт вторым условием и не даёт пропуска блоков внутри дня.
2. Проверка гипотезы. EXPLAIN indexes=1 показывает, что отсев по первичному ключу оставляет миллиард строк: за день данных слишком много.
3. Исправление. Добавляем проекцию с порядком (manager_id, date) — под конкретный срез отчёта. Для редких запросов остаётся основной порядок. Альтернатива — skip index по manager_id, если селективность высокая.
4. Проверка. После OPTIMIZE ... FINAL на тестовой партиции запрос читает 3 млн строк вместо 1.8 млрд и выполняется 0.8 секунды.
5. Закрепление. Фиксируем правило в документации слоя данных: новые отчёты проектируются от списка фильтров, а не от «удобной» схемы. Для подготовки данных используем Spark или SQL-трансформации.

Результат: время ответа сократилось с 40 с до 0.8 с без добавления железа. Вывод: в OLAP производительность определяется тем, сколько данных удалось не прочитать, а это вопрос порядка ключа и предзаполненных агрегатов.

FAQ

Можно ли использовать PostgreSQL как OLAP-хранилище? Частично да: покрывающие индексы, материализованные представления, партиционирование и параллельное сканирование позволяют обслуживать аналитику средних объёмов. Ограничение — строковое хранение и высокая стоимость сканирования миллиардов строк; на таких объёмах специализированное хранилище выигрывает заметно.

Что такое zone maps / granules? Это метаданные о минимумах и максимумах значений в блоке данных. Если фильтр не пересекает диапазон блока, блок пропускается без чтения. Эффективность зависит от корреляции значения с физическим порядком — поэтому данные часто сортируют по дате.

Почему в аналитике избегают частых UPDATE? Колоночные форматы оптимизированы под последовательную запись и пакетное чтение. Точечное обновление требует перезаписи блока (или помеченной мутации), что ломает паттерн доступа. Поэтому изменения обычно приходят как новые версии строк в append-поток — например, через CDC.

Когда выбирать ClickHouse, а когда облачный warehouse? ClickHouse подходит для больших объёмов с предсказуемой нагрузкой и желанием контролировать стоимость инфраструктуры. Облачные warehouse удобнее при переменной нагрузке, разделении команд и требовании минимальной эксплуатации. Критерий — не «что быстрее», а соотношение стоимости эксплуатации и требований к задержке.

Источники: ClickHouse docs (why fast), PostgreSQL docs (TOAST/storage), Wikipedia (OLAP) — ссылки в шапке.