Когда нужен OLAP: ClickHouse против PostgreSQL для аналитики

«Просто добавь ещё один индекс» работает ровно до того момента, пока запрос считает сумму и группировку по десяткам миллионов строк — тут PostgreSQL честно уходит в полный скан, и обычный B-tree уже не меняет природу полного агрегатного скана. Аналитика — это другой класс нагрузки, и под неё есть другой класс баз. ClickHouse — одна из них: колоночная, векторная, заточенная под «просканировать много, вернуть агрегат». Но у этой скорости есть цена, и заплатить её готов далеко не каждый сценарий.

Это первая, обзорная статья серии «ClickHouse и аналитические БД». Дальше будут MergeTree и клиенты на Go/Javaготовится, с 30 июля, бенчмарк драйверовготовится, с 4 августа, материализованные представления и real-time агрегацииготовится, с 5 августа, распределённый кластер, эксплуатация, S3-тиринг и карта выбора между ClickHouse, TimescaleDB и DuckDB. Здесь — критерий: нужен ли вам OLAP вообще, и на каких данных это видно, а не декларируется.

Все числа ниже — из живого стенда when-olap: один и тот же детерминированный датасет в 20 000 000 строк, загруженный одинаково в ClickHouse и PostgreSQL, один и тот же аналитический запрос, один и тот же id для точечных операций. Стенд целиком — в публичном репозитории digital-cookbook, каталог clickhouse/when-olap.

В статье

• OLTP против OLAP
• Колоночное хранение и сжатие
• Векторное исполнение
• Когда PostgreSQL уже не тянет
• Где ClickHouse не подходит
• Как решить, нужен ли он

OLTP против OLAP

OLTP (online transactional processing) и OLAP (online analytical processing) — это не два вида баз данных, а два класса нагрузки, которые предъявляют хранилищу противоположные требования.

OLTP — короткие транзакции: прочитать заказ по id, списать остаток на складе, вставить одну строку в таблицу сессий. Запросов много, каждый лёгкий, конкурентность высокая, а консистентность нужна прямо сейчас — деньги на счету не могут «немного подождать» согласованности. Классический профиль PostgreSQL: B-tree индексы для точечного доступа, MVCC для конкурентных транзакций, WAL для durability.

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

Одна СУБД редко хороша сразу в обоих профилях — не потому, что кто-то не постарался, а потому что оптимизации хранения и исполнения тянут в разные стороны. Индекс, ускоряющий точечный SELECT WHERE id = ?, ничем не помогает GROUP BY по всей таблице. Формат хранения, удобный для «прочитать/изменить одну строку», плохо подходит для «прочитать одну колонку из ста миллионов строк». Дальше — почему это так на уровне физического хранения.

Колоночное хранение и сжатие

PostgreSQL — классический row-store: значения одной строки лежат на диске рядом друг с другом. Страница таблицы — это набор целых строк, и даже если запросу нужна одна колонка из семи, движку всё равно приходится поднять с диска всю строку целиком, потому что физически колонки не разделены.

ClickHouse — column-store: значения одной колонки лежат подряд, отдельно от остальных колонок. Запрос GROUP BY country, sum(revenue) в таком хранилище читает с диска только country и revenue — остальные пять колонок таблицы demo.events (event_time, user_id, event_type, url, duration_ms) он не трогает вообще.

flowchart TB
  subgraph ROW["Строковое хранение (row-store, PostgreSQL heap)"]
    direction TB
    R0["страница диска"]
    R1["строка 1: event_time | user_id | event_type | url | duration_ms | country | revenue"]
    R2["строка 2: event_time | user_id | event_type | url | duration_ms | country | revenue"]
    R3["строка 3: event_time | user_id | event_type | url | duration_ms | country | revenue"]
    R0 --> R1 --> R2 --> R3
  end

  subgraph COL["Колоночное хранение (column-store, ClickHouse MergeTree)"]
    direction TB
    C0["part на диске"]
    CT["колонка event_time: t1, t2, t3, ..."]
    CU["колонка user_id: u1, u2, u3, ..."]
    CC["колонка country: c1, c2, c3, ..."]
    CR["колонка revenue: r1, r2, r3, ..."]
    C0 --> CT
    C0 --> CU
    C0 --> CC
    C0 --> CR
  end

  Q1["GROUP BY country, sum(revenue)"] -->|читает всю строку целиком,
даже ненужные колонки| ROW
  Q2["тот же агрегат"] -->|читает только country и revenue,
остальные колонки не трогает| COL

  style ROW fill:#f9f3e3,stroke:#8b7355
  style COL fill:#c9e4c5,stroke:#5b8a5e
  style Q1 fill:#f4d9c6,stroke:#c67a4a
  style Q2 fill:#f4d9c6,stroke:#c67a4a

У колоночного хранения есть прямое следствие для сжатия: соседние значения одной колонки однотипны и часто похожи друг на друга. Колонка country — короткий список повторяющихся строк (в датасете 20 стран, для этого в схеме используется LowCardinality(String) — фактически словарное кодирование), колонка event_time — монотонно растущие временные метки, для которых хорошо работает дельта-кодирование. ClickHouse по умолчанию дожимает результат LZ4. Таблица DDL, на которой построены все числа ниже:

CREATE TABLE demo.events
(
    id          UInt64,
    event_time  DateTime,
    user_id     UInt64,
    event_type  LowCardinality(String),
    url         String,
    duration_ms UInt32,
    country     LowCardinality(String),
    revenue     Decimal(10,2)
)
ENGINE = MergeTree
ORDER BY (event_time, user_id)
PARTITION BY toYYYYMM(event_time)
SETTINGS index_granularity = 8192;

-- PostgreSQL: та же схема данных, PRIMARY KEY(id) + btree на event_time/country
CREATE TABLE events (
    id          BIGINT PRIMARY KEY,
    event_time  TIMESTAMP NOT NULL,
    user_id     BIGINT NOT NULL,
    event_type  TEXT NOT NULL,
    url         TEXT NOT NULL,
    duration_ms INT NOT NULL,
    country     TEXT NOT NULL,
    revenue     NUMERIC(10,2) NOT NULL
);
CREATE INDEX idx_events_event_time ON events (event_time);
CREATE INDEX idx_events_country ON events (country);

На 20 000 000 строк, загруженных в обе СУБД одинаковым батчевым способом (ClickHouse — PrepareBatch по миллиону строк, PostgreSQL — COPY через pgx, не построчный INSERT), результат на диске (число детерминировано, не зависит от хоста):

• ClickHouse: 464,20 MiB, 15 активных parts.
• PostgreSQL: 2,54 GiB (1,64 GiB данные таблицы + 921,10 MiB индексы).
• Коэффициент сжатия PG/CH — 5,6×: ClickHouse занимает в 5,6 раза меньше места на том же объёме данных.

Разница объясняется не только компрессией — PostgreSQL здесь ещё и хранит два btree-индекса поверх данных, а ClickHouse обходится без единого вторичного индекса. Но и без индексов row-хранилище с MVCC (заголовки строк, версии кортежей) на однотипных данных проигрывает колоночному сжатию в разы.

Плата за это устройство хранения — на другом полюсе задач, и до неё дойдёт очередь ниже: точечное чтение или изменение одной «строки» в column-store физически дороже, чем в row-store, потому что строки как таковой на диске не существует — есть N обращений к N колонкам.

Векторное исполнение

Классический построчный движок запросов (модель Volcano) обрабатывает данные по одной записи за раз: каждый оператор плана запрашивает у нижнего оператора одну строку, что-то с ней делает, отдаёт выше. Это простая и универсальная модель, но с высокими накладными расходами на строку — виртуальные вызовы, промахи кэша процессора, отсутствие эффекта от SIMD-инструкций.

ClickHouse исполняет запрос векторно: операторы работают не со строками, а с блоками — несколько тысяч значений одной колонки за раз, как единый непрерывный массив в памяти. Это даёт то, чего построчная модель принципиально не может: данные одного типа лежат подряд, процессор эффективно использует кэш и может применить SIMD-инструкции к целому блоку значений одной операцией, а не N отдельными вызовами. Ускорение достаётся именно аналитическим сканам — там, где одна и та же операция (сравнение, сложение, агрегация) применяется к миллионам однотипных значений подряд. К точечному SELECT по одному ключу векторизация ничего не добавляет: там нечего векторизовать, это один элемент, а не поток.

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

Когда PostgreSQL уже не тянет

Симптом узнаваем: агрегатный запрос, который ещё вчера отрабатывал за сотни миллисекунд на тестовых данных, на проде занимает секунды или минуты, EXPLAIN показывает Seq Scan/Parallel Seq Scan по всей таблице, а добавление очередного индекса не помогает вообще — просто занимает лишнее место на диске.

Живой пример на том же датасете и той же схеме: аналитический агрегат GROUP BY country, toDate(event_time) с count() и sum(revenue) по всем 20 000 000 строкам.

-- ClickHouse
SELECT country, toDate(event_time) AS d, count() AS cnt, sum(revenue) AS rev
FROM demo.events
GROUP BY country, d
ORDER BY country, d;

-- PostgreSQL
SELECT country, event_time::date AS d, count(*) AS cnt, sum(revenue) AS rev
FROM events
GROUP BY country, d
ORDER BY country, d;

Результат одного характерного прогона (медиана из пяти замеров на каждой стороне). Host-зависимы здесь и абсолютное время, и кратность — воспроизводить стоит направление, а не конкретное число (см. оговорку под графиком):

ClickHouse проходит агрегат за медианные 99,9 мс (разброс по пяти прогонам — 92,3–121,6 мс), PostgreSQL — за 4,27 с. Важная деталь именно про индексы: медиана PG с индексами на event_time/country (4,27 с) и без них (4,36 с) отличаются в пределах шума. Индексы этому запросу не помогают в принципе — GROUP BY по всем строкам всё равно требует полного скана, потому что запрос не отбирает узкое подмножество по условию WHERE, индексу нечего сузить. План PostgreSQL — Seq Scan/Parallel Seq Scan по всей таблице; план ClickHouse — один линейный проход ReadFromMergeTree только по трём нужным колонкам (country, event_time, revenue), без единого обращения к индексу.

Насчёт «в 42,7 раза» стоит сразу снять завышенное ожидание: эта кратность — не константа. Повторный полный прогон того же стенда на другой машине дал 128,1 мс против 1,71 с, то есть 13,4× — направление то же, но множитель втрое меньше. Он зависит от числа ядер и их загрузки, объёма страничного кэша, скорости диска, настроек параллелизма обеих СУБД. Поэтому переносить в свои оценки стоит не «42×», а качественную границу: полный агрегат по десяткам миллионов строк у row-store занимает секунды, у колоночного движка — десятые доли секунды, и никакой индекс эту разницу не закрывает, потому что упирается она в объём поднятых с диска данных, а не в поиск.

Это и есть точка, в которой партиционирование, ещё один индекс или материализованное представление в PostgreSQL перестают быть решением, а превращаются в обходной манёвр вокруг фундаментального ограничения row-store на полных сканах больших объёмов. Граница, за которой стоит присмотреться к выделенному движку хранения — не только колоночному ClickHouse, но и другим специализированным структурам, — разобрана отдельно в статье про устройство LSM-деревьев и B-деревьевСкоро.

Где ClickHouse не подходит

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

Точечный SELECT по ключу, не входящему в ORDER BY. В demo.events ORDER BY (event_time, user_id), а суррогатный id в него не входит. Разреженный первичный индекс MergeTree работает по гранулам (index_granularity = 8192) и умеет пропускать гранулы только по колонкам из ORDER BY — по id пропускать нечего, и ClickHouse вынужден читать колонку id почти по всей таблице.

// PostgreSQL: Index Scan using events_pkey — доли миллисекунды
row := pool.QueryRow(ctx, "SELECT country FROM events WHERE id = $1", id)

// ClickHouse: id не в ORDER BY (event_time, user_id) — сканирует гранулы
row := conn.QueryRow(ctx, fmt.Sprintf(
    "SELECT country FROM demo.events WHERE id = %d LIMIT 1", id))

Результат: PostgreSQL — 433,913 мкс (Index Scan using events_pkey). ClickHouse — 7,742567 мс, при этом реально прочитал 2 490 368 строк из 20 000 000 (12,5% таблицы, по гранулам) — на этом конкретном запросе ClickHouse медленнее PostgreSQL примерно в 18 раз. Не потому, что ClickHouse «медленный» — а потому, что это запрос, для которого он не спроектирован: точечный поиск по неотсортированному ключу.

Точечный UPDATE/DELETE — главный анти-паттерн ClickHouse. В PostgreSQL изменение строки по PK — обычная синхронная операция: UPDATE ... WHERE id = $1 занимает 2,925796 мс, DELETE ... WHERE id = $2 — 439,192 мкс, оба применяются сразу и полностью. У ClickHouse нет операции «поправить одну строку на месте» — есть мутация (ALTER TABLE ... UPDATE/DELETE), и она устроена принципиально иначе: клиент получает ответ почти мгновенно, но это ответ о постановке в очередь, а не о применении.

// ALTER возвращается почти сразу — это НЕ время применения мутации
conn.Exec(ctx, fmt.Sprintf("ALTER TABLE demo.events UPDATE revenue = 0 WHERE id = %d", id))

// реальная стоимость — время до is_done=1 в system.mutations,
// мутация переписывает целые parts, а не правит строку
for {
    conn.QueryRow(ctx, `SELECT is_done FROM system.mutations
        WHERE database = 'demo' AND table = 'events' AND mutation_id = ?`, mutationID).Scan(&isDone)
    if isDone == 1 {
        break
    }
    time.Sleep(200 * time.Millisecond)
}

На стенде: ALTER ... UPDATE отправляется за 6,48824 мс, но реально завершается только через 1,023825123 с (parts_to_do на момент отправки — 15 parts, которые нужно переписать целиком). ALTER ... DELETE — отправка за 5,693265 мс, завершение за 820,630404 мс. Итог: мутация ClickHouse дороже эквивалентной операции PostgreSQL в 350× (UPDATE) и 1870× (DELETE) — потому что ClickHouse не редактирует строку внутри part’а, а пересобирает part целиком. Число строк после мутаций совпало на обеих сторонах — 19 999 999 — сами операции применились корректно, вопрос исключительно в цене.

Отсюда и остальные ограничения того же происхождения: у ClickHouse нет привычных ACID-транзакций между таблицами и полноценных внешних ключей — модель заточена под append и большие батчи, а не под согласованные многошаговые изменения. Вставка по одной строке за раз убивает производительность так же, как мутации — по той же причине (много мелких parts, дорогие фоновые merge — подробнее в статье про MergeTree и клиенты на Go/Javaготовится, с 30 июля). И если приложению нужна строгая сиюминутная консистентность — «прочитал сразу после того, как записал, и увидел ровно то, что записал, с блокировками при конкуренции» — это профиль OLTP-базы, а не ClickHouse.

Как решить, нужен ли он

Практический чек-лист, без магии:

• Объём и характер запросов. Десятки миллионов строк и агрегаты по большей части таблицы — кандидат на OLAP. Единицы миллионов строк и точечный доступ по ключу — почти наверняка хватит PostgreSQL, возможно с партиционированием.
• Доля записи и её паттерн. Частые одиночные INSERT/UPDATE по одной строке — сигнал против ClickHouse. Батчевая загрузка (тысячи-миллионы строк за раз, редко) — ровно то, под что он спроектирован.
• Требования к консистентности. Нужны транзакции между таблицами, немедленная видимость после записи, внешние ключи — это OLTP-профиль. Приемлема eventual-консистентность аналитики, отстающей на секунды-минуты от сырых событий — ClickHouse подходит.
• Что уже пробовали в PostgreSQL. Партиционирование, материализованные представления, columnar-расширения (pg_columnar-подобные) отодвигают границу, но не убирают её — если EXPLAIN всё ещё показывает полный скан на растущем объёме, это симптом, что нагрузка переросла row-store, а не что PostgreSQL настроен неправильно.

Часто ответ — не «или-или», а «аналитика рядом с OLTP»: горячие транзакционные данные остаются в PostgreSQL, а поток событий реплицируется или дублируется в ClickHouse для отчётов и дашбордов, не нагружая транзакционную базу тяжёлыми GROUP BY. Именно этот сценарий — что происходит с данными после того, как они попали в ClickHouse: как устроен MergeTree изнутри, что такое разреженный первичный индекс и гранулы, как вставлять данные батчами из Go и Java, не наступая на антипаттерн одиночных INSERT — тема следующей статьи, «MergeTree из Go и Java»готовится, с 30 июля. Общая карта альтернатив хранения и обработки данных, если нужен более широкий взгляд, чем одна серия про ClickHouse — в хабе «Карта данных»готовится, с 22 сентября.

Версии в стендах: ClickHouse 26.6.1.1193, PostgreSQL 16.14, clickhouse-go v2.47.0.