ClickHouse в production: колоночная база данных для реальной аналитики

Три года назад мы пытались построить аналитическую систему на PostgreSQL. Запрос воронки конверсии по 80 миллионам событий выполнялся 40 секунд. Пользователи жаловались. Мы добавили индексы - стало 30 секунд. Добавили партиции по дате - 20 секунд. Добавили кеш - первый пользователь ждёт 20 секунд, остальные получают устаревшие данные. В итоге мы переехали на ClickHouse, и тот же запрос стал выполняться за 0.3 секунды. Вот что мы узнали за эти три года.

Почему колоночное хранение выигрывает

Когда PostgreSQL читает строку, он читает всю строку целиком - все столбцы. Для OLTP это правильно: SELECT * FROM users WHERE id = 42 нужны все данные о пользователе. Но для аналитики картина другая:

-- Типичный аналитический запрос
SELECT 
    date_trunc('day', event_time) as day,
    COUNT(*) as events,
    COUNT(DISTINCT user_id) as users,
    SUM(revenue) as total_revenue
FROM events
WHERE event_time >= '2026-01-01'
    AND event_type = 'purchase'
GROUP BY day
ORDER BY day;

Этот запрос читает только 4 столбца из таблицы, у которой может быть 30+ столбцов. PostgreSQL всё равно прочитает все 30 - потому что строки хранятся целиком. ClickHouse прочитает только нужные 4.

Реальные цифры с нашего production-кластера:

Метрика	PostgreSQL	ClickHouse
Запрос воронки, 100M строк	~25 сек	~280 мс
Дисковое чтение	14 GB	0.8 GB
Сжатие данных	1x (без сжатия)	~8x
RAM под запрос	4 GB	200 MB

Разница в дисковом чтении - вот где вся магия. ClickHouse читает в 17 раз меньше данных. Плюс колонки хорошо сжимаются: числа похожего диапазона, строки с повторяющимися значениями. Алгоритм LZ4 по умолчанию, ZSTD для лучшего сжатия за счёт скорости.

Семейство движков MergeTree

Выбор движка таблицы в ClickHouse - это архитектурное решение. Ошибиться здесь дороже, чем в PostgreSQL.

MergeTree - основа всего

CREATE TABLE events
(
    event_id     UUID,
    user_id      UInt64,
    session_id   String,
    event_type   LowCardinality(String),
    event_time   DateTime,
    properties   String,  -- JSON
    revenue      Decimal(18, 2),
    country      LowCardinality(String),
    platform     LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time)
SETTINGS index_granularity = 8192;

LowCardinality - это оптимизация для столбцов с небольшим числом уникальных значений (меньше ~10,000). ClickHouse будет хранить словарь и индексы, что ускоряет фильтрацию и GROUP BY примерно в 2-3 раза для таких полей.

ORDER BY vs PRIMARY KEY - критически важное различие

Это место, где мы сделали первую серьёзную ошибку. Многие думают, что ORDER BY в MergeTree - это то же самое, что PRIMARY KEY. Нет.

-- Можно написать так:
ENGINE = MergeTree()
ORDER BY (user_id, event_time)

-- А можно явно задать PRIMARY KEY отличным от ORDER BY:
ENGINE = MergeTree()
PRIMARY KEY (user_id)
ORDER BY (user_id, event_time)

ORDER BY определяет физический порядок данных на диске и полный ключ сортировки. PRIMARY KEY определяет, какая часть ключа попадает в разреженный индекс. По умолчанию PRIMARY KEY = ORDER BY.

Зачем разделять? Если у вас запросы вида WHERE user_id = 123, но вы хотите хранить данные отсортированными по (user_id, event_time) для эффективного сканирования диапазонов - PRIMARY KEY (user_id) позволит ClickHouse быстро найти нужный user_id по индексу, а потом отсканировать его события в порядке времени.

Правило: PRIMARY KEY должен быть префиксом ORDER BY. ClickHouse не выдаст ошибку, если это не так - просто проигнорирует настройку.

ReplicatedMergeTree - production без него не бывает

CREATE TABLE events ON CLUSTER my_cluster
(
    -- те же поля
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

{shard} и {replica} - макросы из конфига. ReplicatedMergeTree использует ZooKeeper (или ClickHouse Keeper) для координации. Репликация асинхронная - данные сначала пишутся на одну реплику, потом копируются на другие. Это важно: если вы сразу читаете с другой реплики, данных там может не быть.

SummingMergeTree - предагрегация на уровне движка

CREATE TABLE daily_stats
(
    date        Date,
    user_id     UInt64,
    page_views  UInt64,
    clicks      UInt64,
    revenue     Decimal(18, 2)
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id);

-- Пишем данные построчно
INSERT INTO daily_stats VALUES ('2026-04-17', 42, 10, 3, 99.50);
INSERT INTO daily_stats VALUES ('2026-04-17', 42, 5, 1, 49.75);

-- Со временем (при мерже) ClickHouse автоматически схлопнет их в:
-- ('2026-04-17', 42, 15, 4, 149.25)

Важный нюанс: мержи происходят в фоне, и нет гарантии когда. Поэтому в запросах всегда используйте SUM():

SELECT date, user_id, SUM(page_views), SUM(revenue)
FROM daily_stats
GROUP BY date, user_id;

AggregatingMergeTree - для сложных агрегатов

CREATE TABLE user_funnel_state
(
    date       Date,
    user_id    UInt64,
    step1_cnt  AggregateFunction(count, UInt8),
    step2_cnt  AggregateFunction(count, UInt8),
    revenue    AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, user_id);

Используется вместе с материализованными представлениями для инкрементальной агрегации.

Материализованные представления - инкрементальная агрегация

Это одна из самых мощных и при этом наименее понятых возможностей ClickHouse.

-- Исходная таблица событий
CREATE TABLE events_raw
(
    event_id   UUID DEFAULT generateUUIDv4(),
    user_id    UInt64,
    event_type LowCardinality(String),
    event_time DateTime,
    revenue    Decimal(18, 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- Таблица-назначение для агрегатов
CREATE TABLE events_hourly_agg
(
    hour       DateTime,
    event_type LowCardinality(String),
    user_count AggregateFunction(uniq, UInt64),
    event_cnt  AggregateFunction(count, UInt8),
    revenue    AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, event_type);

-- Материализованное представление - триггер на инсерты
CREATE MATERIALIZED VIEW events_hourly_mv
TO events_hourly_agg
AS SELECT
    toStartOfHour(event_time) as hour,
    event_type,
    uniqState(user_id)  as user_count,
    countState()        as event_cnt,
    sumState(revenue)   as revenue
FROM events_raw
GROUP BY hour, event_type;

-- Запрос к агрегированной таблице
SELECT
    hour,
    event_type,
    uniqMerge(user_count)  as unique_users,
    countMerge(event_cnt)  as total_events,
    sumMerge(revenue)      as total_revenue
FROM events_hourly_agg
GROUP BY hour, event_type
ORDER BY hour DESC;

Ключевое: материализованное представление срабатывает при каждом INSERT в исходную таблицу. Данные агрегируются инкрементально - вы не пересчитываете всё с нуля при каждом запросе.

Запрос воронки конверсии

Это то, из-за чего многие и переходят на ClickHouse:

-- Воронка: регистрация → просмотр товара → добавление в корзину → покупка
WITH funnel AS (
    SELECT
        user_id,
        windowFunnel(86400)(  -- окно 24 часа
            event_time,
            event_type = 'registration',
            event_type = 'view_product',
            event_type = 'add_to_cart',
            event_type = 'purchase'
        ) AS step
    FROM events
    WHERE event_time >= '2026-04-01'
        AND event_time < '2026-04-17'
    GROUP BY user_id
)
SELECT
    step,
    COUNT(*) AS users,
    ROUND(100.0 * COUNT(*) / MAX(COUNT(*)) OVER (), 2) AS pct
FROM funnel
WHERE step > 0
GROUP BY step
ORDER BY step;

На 100M строк этот запрос у нас выполняется за 0.8 секунды. В PostgreSQL аналогичный запрос через оконные функции - около 45 секунд.

Типичные ошибки при переносе из PostgreSQL

1. Нет UPSERT - только INSERT

В PostgreSQL вы привыкли к INSERT ... ON CONFLICT DO UPDATE. В ClickHouse нет первичных ключей в привычном смысле. Есть несколько подходов:

-- Вариант 1: ReplacingMergeTree
CREATE TABLE users_state
(
    user_id    UInt64,
    email      String,
    updated_at DateTime,
    is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

-- Пишем обновление как новую строку
INSERT INTO users_state VALUES (42, 'new@email.com', now(), 0);

-- При запросе всегда используем FINAL или дедупликацию
SELECT * FROM users_state FINAL WHERE user_id = 42;
-- или
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) rn
    FROM users_state
) WHERE rn = 1;

2. JOIN-ы работают иначе

В ClickHouse правая таблица JOIN полностью загружается в память. Порядок таблиц в JOIN важен - большая таблица должна быть слева:

-- Правильно: большая таблица слева
SELECT e.user_id, u.email, COUNT(*)
FROM events e  -- 100M строк
JOIN users u ON e.user_id = u.user_id  -- 1M строк
GROUP BY e.user_id, u.email;

-- Неправильно: OOM или очень медленно
SELECT e.user_id, u.email, COUNT(*)
FROM users u  -- 1M строк
JOIN events e ON u.user_id = e.user_id  -- 100M строк загрузится в память
GROUP BY e.user_id, u.email;

3. DELETE и UPDATE - дорогая операция

В ClickHouse мутации (ALTER TABLE ... DELETE/UPDATE) - тяжёлые фоновые операции, переписывающие куски данных. Не используйте их в горячем пути:

-- Это НЕ мгновенная операция, а фоновая задача:
ALTER TABLE events DELETE WHERE user_id = 42;

-- Проверить статус мутации:
SELECT * FROM system.mutations WHERE is_done = 0;

4. Не используйте nullable без необходимости

Nullable(String) хранит дополнительный битмап для null-флагов и работает медленнее. Если 99% значений не null - используйте пустую строку или 0 как sentinel-значение.

Вместо заключения

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

Мы в итоге пришли к связке: PostgreSQL для транзакционных данных + ClickHouse для аналитики + Kafka для потоковой загрузки событий. Каждый инструмент делает своё.

---

Если вам нужна помощь с проектированием аналитической архитектуры или миграцией с PostgreSQL - Aunimeda занимается именно такими задачами. Напишите нам.