ClickHouse для аналитики: обработка 3 миллиардов событий в сутки

Рекламная платформа «АналитикПро» обратилась к инженерам itfresh.ru с проблемой масштабирования. Система обрабатывала показы, клики и конверсии интернет-рекламы. Ежедневный объём составлял 3 миллиарда событий — около 35 000 записей в секунду в пиковые часы.

Существующее решение на PostgreSQL 15 с таблицами, партицированными по дням, работало приемлемо до 500 миллионов записей в день. Когда объём вырос в шесть раз, проблемы стали критическими:

• Аналитические запросы по данным за неделю выполнялись 40-120 секунд — клиенты платформы не готовы ждать.
• Ежедневная очистка старых партиций создавала блокировки и замедляла вставку.
• Дисковое пространство росло на 150 ГБ в день — сжатие TOAST не справлялось.
• Elasticsearch, используемый параллельно для поисковой аналитики, потреблял 96 ГБ RAM на кластере из трёх нод и всё равно тормозил на агрегациях.

Нам требовалась СУБД, способная принимать десятки тысяч записей в секунду и отвечать на аналитические запросы за миллисекунды, а не минуты.

ClickHouse — колоночная аналитическая СУБД, изначально разработанная в Яндексе для Яндекс.Метрики (второй по величине системы веб-аналитики в мире). В отличие от строковых баз (PostgreSQL, MySQL), ClickHouse хранит данные каждого столбца отдельно. Это даёт три ключевых преимущества для аналитических нагрузок.

Первое — чтение только нужных столбцов. Когда запрос агрегирует данные по двум столбцам из таблицы с тридцатью, ClickHouse читает с диска только эти два столбца, а не все тридцать. Для таблицы с 3 миллиардами строк разница в объёме I/O может составлять 10-15x.

Второе — сжатие. Однотипные данные в столбце сжимаются значительно лучше, чем разнородные данные в строке. В нашем случае средний коэффициент сжатия составил 8:1, тогда как PostgreSQL обеспечивал 2-3:1.

Третье — SIMD-оптимизация. ClickHouse использует векторные инструкции процессора (SSE4.2, AVX2) для обработки данных целыми столбцами, а не по одной строке. Это позволяет фильтровать миллиард строк за секунды.

Лицензия Apache 2.0 означает отсутствие расходов на лицензирование — существенное преимущество перед коммерческими OLAP-решениями вроде Vertica или Exasol.

Мы развернули кластер из шести серверов: три шарда по две реплики. Каждый сервер — 32 vCPU, 128 ГБ RAM, 4 ТБ NVMe SSD. Координация реплик осуществляется через ClickHouse Keeper (встроенная альтернатива ZooKeeper).

Основная таблица событий использует движок ReplicatedMergeTree:

CREATE TABLE events_local ON CLUSTER 'analytics_cluster'
(
    event_date Date,
    event_time DateTime64(3),
    event_type Enum8('impression' = 1, 'click' = 2, 'conversion' = 3),
    campaign_id UInt32,
    advertiser_id UInt32,
    publisher_id UInt32,
    creative_id UInt32,
    user_hash UInt64,
    country LowCardinality(String),
    region LowCardinality(String),
    device_type Enum8('desktop' = 1, 'mobile' = 2, 'tablet' = 3),
    os LowCardinality(String),
    browser LowCardinality(String),
    cost Decimal64(6),
    revenue Decimal64(6),
    url String,
    referer String
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (advertiser_id, campaign_id, event_date, event_time)
TTL event_date + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

Ключевые решения в схеме:

• ORDER BY — сортировка по advertiser_id и campaign_id, поскольку 90% запросов фильтруют по этим полям. ClickHouse пропускает целые гранулы (блоки по 8192 строк), не содержащие нужный advertiser_id.
• LowCardinality — обёртка для строковых столбцов с малым количеством уникальных значений. Хранит словарь вместо повторяющихся строк, ускоряя фильтрацию в 5-10 раз.
• Enum8 — хранит строковые типы как числа (1 байт вместо строки), экономя дисковое пространство и память.
• TTL — автоматическое удаление данных старше 90 дней, без ручных операций по очистке партиций.

Поверх локальных таблиц создаётся распределённая таблица, которая маршрутизирует запросы по шардам:

CREATE TABLE events_distributed ON CLUSTER 'analytics_cluster'
AS events_local
ENGINE = Distributed(
    'analytics_cluster',
    'analytics',
    'events_local',
    sipHash64(user_hash)  -- ключ шардирования
);

Данные поступают из рекламных серверов через Apache Kafka. ClickHouse имеет встроенный движок Kafka для прямого потребления сообщений:

-- Таблица-источник, подключённая к Kafka
CREATE TABLE events_kafka
(
    event_date Date,
    event_time DateTime64(3),
    event_type String,
    campaign_id UInt32,
    advertiser_id UInt32,
    publisher_id UInt32,
    creative_id UInt32,
    user_hash UInt64,
    country String,
    region String,
    device_type String,
    os String,
    browser String,
    cost Decimal64(6),
    revenue Decimal64(6),
    url String,
    referer String
) ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka01:9092,kafka02:9092,kafka03:9092',
    kafka_topic_list = 'ad_events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4,
    kafka_max_block_size = 65536;

-- Материализованное представление для автоматической загрузки
CREATE MATERIALIZED VIEW events_kafka_mv TO events_local AS
SELECT * FROM events_kafka;

Движок Kafka в ClickHouse потребляет сообщения блоками (до 65 536 строк), вставляя их атомарно. Четыре потребителя обеспечивают пропускную способность 80 000 записей в секунду — с двукратным запасом относительно пиковой нагрузки «АналитикПро».

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

-- Почасовая агрегация по кампаниям
CREATE MATERIALIZED VIEW campaign_hourly_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (advertiser_id, campaign_id, event_date, hour, event_type)
AS
SELECT
    event_date,
    toHour(event_time) AS hour,
    advertiser_id,
    campaign_id,
    event_type,
    count() AS events_count,
    uniqState(user_hash) AS unique_users,
    sum(cost) AS total_cost,
    sum(revenue) AS total_revenue
FROM events_local
GROUP BY event_date, hour, advertiser_id, campaign_id, event_type;

-- Запрос к агрегированному представлению: CTR по кампаниям
SELECT
    campaign_id,
    sum(events_count) FILTER (WHERE event_type = 'impression') AS impressions,
    sum(events_count) FILTER (WHERE event_type = 'click') AS clicks,
    clicks / impressions AS ctr,
    sum(total_cost) AS spend,
    sum(total_revenue) AS revenue,
    revenue - spend AS profit
FROM campaign_hourly_mv
WHERE advertiser_id = 42
  AND event_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY campaign_id
ORDER BY profit DESC
LIMIT 20;

Движок SummingMergeTree автоматически складывает числовые столбцы для строк с одинаковым ключом сортировки при фоновых мержах. Функция uniqState сохраняет промежуточное состояние HyperLogLog-счётчика, позволяя точно считать уникальных пользователей при объединении агрегатов.

Результат: запрос по месяцу данных для одного рекламодателя выполняется за 15 мс вместо 8 секунд на сырых данных.

Мы провели серию бенчмарков на одинаковом датасете (1 миллиард строк, 7 дней данных):

Основные приёмы оптимизации запросов в ClickHouse:

-- 1. Используем PREWHERE вместо WHERE для тяжёлых фильтров
-- PREWHERE читает только столбцы фильтра, затем подгружает остальные
SELECT campaign_id, count()
FROM events_distributed
PREWHERE event_date = '2026-04-01'
WHERE event_type = 'click'
GROUP BY campaign_id;

-- 2. Используем примерные функции для дашбордов
-- uniqHLL12 вместо uniq — погрешность 1%, скорость 10x
SELECT
    toDate(event_time) AS day,
    uniqHLL12(user_hash) AS approx_unique_users
FROM events_distributed
WHERE event_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY day;

-- 3. SAMPLE для мгновенных оценок на больших объёмах
SELECT
    country,
    count() * 10 AS estimated_events  -- масштабируем обратно
FROM events_distributed
SAMPLE 0.1  -- 10% данных
WHERE event_date = '2026-04-01'
GROUP BY country
ORDER BY estimated_events DESC;

Для мониторинга ClickHouse-кластера мы настроили сбор метрик в Prometheus через встроенный endpoint /metrics и набор кастомных дашбордов в Grafana:

# /etc/clickhouse-server/config.d/prometheus.xml
<clickhouse>
    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
    </prometheus>
</clickhouse>

Ключевые метрики, на которые мы настроили алерты:

• ReplicasMaxQueueSize — очередь репликации. Алерт при значении > 100 означает, что реплика отстаёт.
• MaxPartCountForPartition — количество партов в партиции. Более 300 — признак того, что мержи не успевают за вставками.
• MemoryTracking — общее потребление памяти. Алерт при 80% от max_memory_usage.
• Query duration p99 — если 99-й перцентиль запросов превышает 5 секунд, нужна оптимизация.

Полезные системные запросы для диагностики:

-- Текущие запросы и их ресурсы
SELECT query_id, user, elapsed, read_rows, memory_usage,
       formatReadableSize(memory_usage) AS mem
FROM system.processes
ORDER BY elapsed DESC;

-- Размер таблиц и сжатие
SELECT table,
       formatReadableSize(sum(bytes_on_disk)) AS disk_size,
       formatReadableSize(sum(data_uncompressed_bytes)) AS raw_size,
       round(sum(data_uncompressed_bytes) / sum(bytes_on_disk), 2) AS ratio
FROM system.parts
WHERE active
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

-- Медленные запросы за последние сутки
SELECT query, query_duration_ms, read_rows,
       formatReadableSize(read_bytes) AS read
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date = today()
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 20;

После миграции «АналитикПро» с PostgreSQL + Elasticsearch на ClickHouse результаты выглядят следующим образом:

ClickHouse не является заменой PostgreSQL для OLTP-нагрузок: он не поддерживает UPDATE/DELETE отдельных строк, не имеет полноценных транзакций и не подходит для point-lookups. Однако для аналитических задач — агрегация, фильтрация и сканирование миллиардов строк — это решение, не имеющее аналогов в мире open-source.

Если ваш проект генерирует миллионы событий в день и требует интерактивной аналитики, специалисты itfresh.ru помогут спроектировать и развернуть кластер ClickHouse, настроить ингест из Kafka, оптимизировать схему данных и построить мониторинг.