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 занимается именно такими задачами. Напишите нам.