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

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

Разберём, как понять, что пора, как устроить поток данных и какие ошибки встречаются при внедрении.

Почему PostgreSQL перестаёт справляться с аналитикой?

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

Признаки, что аналитике тесно в основной базе:

• отчёты и дашборды выполняются десятки секунд и дольше;
• тяжёлые аналитические запросы замедляют работу сайта для пользователей;
• таблица событий выросла до сотен миллионов строк и продолжает расти;
• ради отчётов приходится заводить всё больше предрасчитанных таблиц и ночных пересчётов.

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

Чем ClickHouse отличается от PostgreSQL?

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

Критерий	PostgreSQL	ClickHouse
Тип нагрузки	Транзакционная (OLTP)	Аналитическая (OLAP)
Хранение	Построчное	Колоночное, с сильным сжатием
Агрегации по миллиардам строк	Медленно	Секунды и быстрее
Обновление и удаление отдельных строк	Быстро и привычно	Дорого, асинхронными мутациями
Транзакции	Полноценные	Ограниченные
Вставка	Построчная — нормально	Лучше крупными пачками
Типичные данные	Заказы, пользователи, платежи	События, логи, метрики, клики

Фото: Unsplash

Как устроить хранение событий в ClickHouse?

События в ClickHouse хранят в таблице семейства MergeTree с ключом сортировки под самые частые запросы и разбиением по месяцам. Правильный ключ сортировки определяет скорость запросов сильнее, чем что-либо ещё.

CREATE TABLE events
(
    event_time  DateTime,
    event_name  LowCardinality(String),
    user_id     UInt64,
    order_id    Nullable(UInt64),
    amount      Decimal(18, 2),
    properties  String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_name, event_time, user_id);

-- Выручка по дням за квартал
SELECT toDate(event_time) AS day, sum(amount) AS revenue
FROM events
WHERE event_name = 'order_paid'
  AND event_time >= now() - INTERVAL 90 DAY
GROUP BY day
ORDER BY day;

Правила для таблиц событий

1. Ключ сортировки начинается со столбцов, по которым чаще всего фильтруют.
2. Вставляйте данные пачками — тысячи строк за раз, а не по одной.
3. Для повторяющихся строковых значений используйте LowCardinality.
4. Задайте срок хранения (TTL) для сырых событий, если они не нужны вечно.

Как доставлять данные из основной базы в ClickHouse?

Данные доставляют тремя способами: приложение пишет события сразу в ClickHouse через очередь, основная база периодически выгружает изменения пачками или изменения передаются потоком через механизм захвата изменений (CDC). Выбор зависит от того, насколько свежими должны быть отчёты.

Варианты:

• события из приложения — через очередь задач или брокер, пачками; подходит для кликов, просмотров, действий пользователей;
• пакетная выгрузка — раз в час или сутки из PostgreSQL; просто и надёжно, если отчёты не нужны в реальном времени;
• CDC — поток изменений из журнала PostgreSQL; свежие данные, но сложнее в эксплуатации.

Как устроить надёжную отправку событий через очередь, мы разбирали в статье очереди задач в веб-приложении.

Какие ошибки встречаются при внедрении ClickHouse?

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

Что ещё встречается:

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

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

Коротко

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