Почему мы используем ClickHouse для аналитики кликов (а не Postgres)

Postgres великолепен. Мы активно его используем и доверяем ему. Он хранит каждую ссылку, каждое рабочее пространство, каждую запись о выставлении счетов, каждого пользователя. Но примерно на 90-й день развития сервиса сокращения ссылок вы упираетесь в стену, если также храните там необработанные события кликов. Эта стена не возникает внезапно - Postgres не выходит из строя, он просто замедляется тем специфическим, раздражающим образом, которым замедляются агрегирующие запросы к большим таблицам, предназначенным только для добавления. Сканирование индексов перестает помогать. Autovacuum не справляется. Дашборд, который загружался за 800 мс, теперь загружается за 4 секунды. Вы добавляете новые индексы. Становится только хуже.

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

Краткие итоги#

• События кликов предназначены только для добавления и имеют форму аналитических запросов. Колоночное хранение выигрывает по обоим пунктам.
• Накладные расходы Postgres на хранение каждой строки события, раздувание индексов и конкуренция за ресурсы при выполнении vacuum накапливаются после достижения ~30 млн событий в общей таблице.
• ClickHouse с ORDER BY (workspace_id, link_id, created_at) возвращает 90-дневные сводки по рабочим пространствам менее чем за 100 мс без дополнительных индексов.
• Метаданные ссылок остаются в Postgres (изменяемые, транзакционные); события кликов остаются в ClickHouse (только для добавления, аналитические). Разделение четкое, так как этим двум типам данных не нужно пересекаться во время записи.

Почему Postgres плохо справляется с такой нагрузкой#

Проблема в характере нагрузки. Событие клика это:

• Запись один раз, без последующих обновлений.
• Запись с высокой, импульсной скоростью - примерное соотношение записи к чтению составляет 50:1, а в день запуска кампании это соотношение вырастает еще больше.
• Агрегированные запросы: общее количество кликов на ссылку, распределение по географии для рабочего пространства, разделение по устройствам среди 100 лучших ссылок кампании. Почти никто не запрашивает отдельную строку клика напрямую.

Postgres построен на других предположениях. Каждая строка содержит заголовок кортежа, поля ID транзакции (xmin, xmax) и метаданные видимости. Для транзакционных нагрузок, где вы обновляете строки на месте и вам нужен MVCC для обработки одновременного чтения и записи, эти накладные расходы - цена за корректность. Для аналитических данных, предназначенных только для добавления, которые вы больше никогда не трогаете, это чистая трата ресурсов.

Проблема с индексами еще серьезнее. Индексы B-tree в Postgres хорошо работают для выборочного поиска точек - поиска строки с link_id = 'xyz'. Они меньше помогают, когда вы выполняете GROUP BY link_id, country по 10 миллионам строк для рабочего пространства. Индекс сужает область сканирования, но запросу все равно нужно извлекать распакованные данные строк из страниц кучи (heap pages), десериализовать их и агрегировать. При 30 млн строк запрос выполняется медленно. При 100 млн строк это становится болезненным.

Autovacuum усугубляет ситуацию на практике. Высокопроизводительные вставки генерируют «мертвые» кортежи (версии строк MVCC, которые больше не видны активным транзакциям, но еще не очищены). Autovacuum восстанавливает их, но он конкурирует с активными запросами за ввод-вывод (I/O). Вы будете видеть периодические всплески задержки на дашбордах Postgres, которые соответствуют выполнению autovacuum в таблице событий. Документацию TimescaleDB по сжатию и архитектуре чанков стоит прочитать, чтобы понять специфику того, как расширение Postgres пытается обойти это - проблема достаточно реальна, чтобы для ее решения существовал выделенный уровень временных рядов поверх Postgres.

Мы хранили клики в Postgres в течение первых 90 дней проекта. Рабочее пространство с ~4 млн событий кликов имело дашборд, рендеринг которого для 7-дневной сводки по странам занимал 3,2 секунды. Добавление частичного индекса на (workspace_id, created_at) позволило сократить время до 1,4 секунды. На тот момент этого было достаточно; но очевидно, что это не тот путь, который масштабируется до 100 млн+ событий в месяц.

Почему подходит ClickHouse#

ClickHouse - это колоночный движок базы данных, созданный специально для аналитических нагрузок - доступно на 12.05.2026. «Колоночный» означает, что данные для каждого столбца хранятся на диске непрерывно, а не перемежаются строка за строкой. Для запроса типа «просуммировать количество кликов по странам для рабочего пространства X за последние 30 дней» движок считывает только столбцы country, workspace_id и created_at. Он никогда не касается user_agent_hash или referrer_host. Сокращение ввода-вывода (I/O) на широких таблицах со множеством столбцов весьма существенно.

Степень сжатия - второе важное свойство. Колоночные данные сжимаются значительно лучше, чем строковые, потому что повторяющиеся значения в одном и том же столбце - один и тот же workspace_id, появляющийся тысячи раз, тот же набор из 60 с лишним кодов стран, горстка значений os - сжимаются почти до нуля с использованием кодеков ClickHouse LZ4 и ZSTD. Наши события кликов сжимаются примерно до 12-15% от их исходного размера на диске. Те же данные в Postgres занимают 35-45% от исходного размера с использованием сжатия toast по умолчанию. Это не просто экономия места - меньший объем данных на диске означает, что больше данных помещается в страничный кэш ОС, что приводит к меньшему количеству чтений с диска на запрос.

Третье свойство - это семейство движков MergeTree - доступно на 12.05.2026. Таблицы MergeTree сортируют и кластеризуют данные по ключу ORDER BY таблицы. Запросы, которые фильтруют или группируют по ведущим столбцам этого ключа, считывают только те части данных, которые перекрываются с диапазоном фильтра. Это эквивалент кластеризованного индекса в ClickHouse, за исключением того, что он является обязательным (вы определяете его при создании таблицы) и применяется к каждому запросу, а не только к индексированным столбцам.

Мы используем ORDER BY (workspace_id, link_id, created_at). Запрос кликов рабочего пространства abc123 за последние 7 дней считывает только те части данных, где появляется workspace_id = 'abc123', а затем внутри этих частей считывает только срез по временному диапазону. В данных кликов за 90 дней это обычно затрагивает 3-8% от общего количества частей данных. Разреженный первичный индекс, который ClickHouse поддерживает для столбцов ORDER BY, делает это таргетирование эффективным без необходимости использования плотного B-tree для каждого шаблона запроса. ReplicatedMergeTree добавляет высокую доступность (HA) - два узла ClickHouse с координацией, эквивалентной ZooKeeper (ClickHouse Keeper), реплицируют части по мере их записи.

Схема, которую мы используем#

CREATE TABLE clicks
(
    workspace_id    LowCardinality(String),
    link_id         String,
    click_id        UUID,
    created_at      DateTime64(3, 'UTC'),
    country         LowCardinality(FixedString(2)),
    device          LowCardinality(String),
    os              LowCardinality(String),
    browser         LowCardinality(String),
    referrer_host   String,
    utm_source      LowCardinality(String),
    utm_medium      LowCardinality(String),
    utm_campaign    String,
    utm_term        String,
    utm_content     String,
    ip_truncated    String,
    user_agent_hash FixedString(64)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/clicks', '{replica}')
PARTITION BY toYYYYMM(created_at)
ORDER BY (workspace_id, link_id, created_at)
SETTINGS index_granularity = 8192;

Стоит отметить несколько решений в схеме.

LowCardinality(String) для workspace_id, country, device, os, browser, utm_source, utm_medium - эти столбцы имеют ограниченную кардинальность (сотни уникальных ID рабочих пространств, около 250 кодов стран, несколько типов устройств). LowCardinality активирует словарное кодирование, которое хранит значения как целочисленные ссылки на словарь уникальных строк. Скорость выполнения запросов по этим столбцам существенно возрастает; сжатие улучшается еще больше. Мы узнали об этом из документации ClickHouse по типу данных LowCardinality - эмпирическое правило гласит, что любой строковый столбец с менее чем 10 000 уникальных значений является кандидатом.

PARTITION BY toYYYYMM(created_at) создает отдельный раздел данных для каждого календарного месяца. Старые разделы можно удалить (или переместить в более медленное хранилище) с помощью одной команды ALTER TABLE clicks DROP PARTITION '202501', которая выполняется мгновенно и без полного сканирования таблицы. Для соблюдения требований GDPR по хранению данных это практически важно: удаление исторических кликов рабочего пространства - это одна операция на ежемесячный раздел, а не распределенная операция DELETE WHERE workspace_id = ?.

ip_truncated хранит IP-адрес с обнуленным последним октетом (203.0.113.0, а не 203.0.113.42). Полный IP-адрес никогда не сохраняется. Усеченной формы достаточно для определения местоположения (что мы делаем на этапе сбора данных, перед сохранением), и она не является персональными данными согласно определению статьи 4 GDPR в сочетании с нашими практиками минимизации данных. См. solutions/analytics для ознакомления с полной архитектурой конфиденциальности.

Путь сбора данных#

Обработчик редиректов в каждой точке присутствия (edge POP) работает по принципу «выстрелил и забыл». Он добавляет событие клика в топик Redpanda (clicks.v1) и выдает 302. Он не ждет подтверждения от ClickHouse. Если Redpanda недоступна в этот момент, клик отбрасывается - редирект нет. Иерархия продумана: пропущенный клик восстановим (мы можем восстановить приблизительные значения из логов доступа); сбой редиректа виден пользователю.

Группы потребителей Redpanda - доступно на 12.05.2026 - обеспечивают гарантию доставки между границей (edge) и инжектором. Сервис click-ingester работает как группа потребителей для топика clicks.v1. Разделы назначаются автоматически; если экземпляр инжектора выходит из строя, его разделы перераспределяются между оставшимися экземплярами в группе в течение таймаута сердцебиения (heartbeat timeout).

Инжектор накапливает события в памяти и сбрасывает их пакетами: 1000 строк или 250 мс, в зависимости от того, что наступит раньше. Это важно. Движок MergeTree в ClickHouse оптимизирован для массовых вставок - он записывает каждую вставку как новую часть данных, и множество крошечных вставок генерирует множество крошечных частей, что вызывает агрессивное фоновое слияние и замедляет запросы во время нагрузки при слиянии. Документация ClickHouse рекомендует вставки не менее 1000 строк, чтобы количество частей оставалось управляемым. Мы сбрасываем данные по правилу 1000 строк / 250 мс и не видели проблем с количеством частей при нормальной работе.

Обратное давление (backpressure) распространяется через Redpanda. Если ClickHouse работает медленно или перезагружается, задержка потребителя (consumer lag) инжектора увеличивается, что мы отслеживаем как опережающий индикатор проблем со свежестью данных. Конфигурация хранения Redpanda (14 дней) дает нам значительное окно для повторного воспроизведения данных, если ClickHouse потребуется перестроить с нуля.

Архитектура более подробно описана на странице /docs/architecture/click-ingester.

Postgres против ClickHouse: разделение#

Две базы данных для одного сервиса - это расходы, требующие обоснования. Обоснование здесь заключается в том, что эти две нагрузки действительно несовместимы в масштабе, и данные не пересекаются так, чтобы требовалась синхронная координация.

Postgres владеет метаданными ссылок: таблицы links, workspaces, users, billing, rules. Они изменяемы (ссылки архивируются, целевые URL меняются, записи о счетах обновляются), транзакционны (создание ссылки и генерация её слага должны быть атомарными) и ориентированы на чтение по сравнению с записью. Индексы B-tree и MVCC - это именно то, что нужно.

ClickHouse владеет событиями кликов: таблица clicks. Они предназначены только для добавления (клик после записи никогда не обновляется), ориентированы на интенсивную запись и запрашиваются по агрегированным шаблонам, с которыми колоночное хранилище справляется лучше. При записи нет объединений между базами данных (cross-database joins) - инжектор вставляет события кликов с денормализованными строковыми полями workspace_id и link_id. Во время запроса API аналитики может запрашивать метаданные ссылок из Postgres и количество кликов из ClickHouse отдельно, а затем объединять их в коде приложения. Это один дополнительный сетевой переход; это гораздо более выгодная сделка, чем перекладывание нагрузки аналитических запросов на Postgres.

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

Шаблоны запросов, которые выполняются ежечасно#

Обновление дашборда для каждого рабочего пространства происходит примерно с 60-секундным циклом. Есть три тяжелых запроса:

Сводка кликов по каждой ссылке за последние 24 часа. В верхней части каждого списка ссылок отображается спарклайн. Это GROUP BY link_id, toHour(created_at) за последние 24 часа для рабочего пространства.

Распределение по странам для кампании. Маркетинговая команда, проверяющая эффективность кампании, хочет получить GROUP BY country, COUNT(*) с фильтрацией по utm_campaign за определенный диапазон дат.

Разделение по устройствам среди 100 лучших ссылок рабочего пространства. Вкладка аналитики показывает разбивку по устройствам/ОС. Это GROUP BY device, os, COUNT(*) для ссылок с самым высоким трафиком в рабочем пространстве за последние 30 дней.

Все три шаблона хорошо обслуживаются кластеризацией ORDER BY (workspace_id, link_id, created_at). Фильтр рабочего пространства немедленно сокращает сканирование до нужного среза данных; столбцы link_id и created_at еще больше сужают его для первых двух шаблонов. Запрос разделения по устройствам сканирует больше столбцов, но все равно выигрывает от кластеризации на уровне рабочего пространства.

Типичный запрос для 100 лучших ссылок по количеству кликов за последние 24 часа:

SELECT
    link_id,
    count() AS clicks_24h
FROM clicks
WHERE
    workspace_id = 'ws_abc123'
    AND created_at >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

В рабочем пространстве с 12 миллионами событий кликов за 90 дней этот запрос возвращается примерно через 45-80 мс, в зависимости от количества частей данных в соответствующем разделе. Тот же запрос к таблице Postgres эквивалентного размера выполняется 2-4 секунды при наилучшей конфигурации индексов, которую мы пробовали.

Мы не используем материализованные представления ClickHouse для этих шаблонов. Кластеризации ORDER BY и отсечения разделов (partition pruning) достаточно, а материализованные представления усложняют миграцию схемы. Если задержка запросов когда-либо станет проблемой при более высоких объемах событий, следующим рычагом станут материализованные представления, а не дополнительное индексирование.

Сценарии сбоев, с которыми мы столкнулись#

Стоит задокументировать три инцидента в продакшене.

Сбой ClickHouse Keeper. ClickHouse Keeper - это уровень координации, эквивалентный ZooKeeper, для ReplicatedMergeTree. Однажды перезагрузка узла Keeper во время фонового слияния привела к тому, что реплицированная таблица перестала принимать вставки примерно на 90 секунд, пока выбирался новый лидер. Инжектор повторял попытки с экспоненциальной задержкой (2с, 4с, 8с, предел 60с) - это стандартная логика повторов в Go с обработкой context.DeadlineExceeded. Во время окна сбоя около 1800 событий кликов накопились в буфере памяти инжектора и были успешно сброшены после восстановления Keeper. Единственным видимым эффектом для пользователей был ~2-минутный разрыв в данных живого дашборда. Решением стало обеспечение размещения узлов Keeper на разных физических хостах с отдельными цепями питания в сети Hetzner.

Дисбаланс разделов в Redpanda. Топик clicks.v1 использует 12 разделов. После добавления второго экземпляра инжектора стандартный балансировщик Redpanda назначил 9 разделов одному экземпляру и 3 другому - он использовал стратегию общего количества разделов на потребителя, и количество экземпляров привело к дисбалансу 3:1. Это не было риском потери данных, но перегруженный экземпляр медленно обрабатывал пакеты под такой нагрузкой, увеличивая задержку обработки кликов примерно до 800 мс. Решением стало ручное перераспределение разделов с помощью Redpanda admin API и переключение на балансировщик с учетом стоек (rack-aware) для циклического назначения.

ALTER TABLE на большой таблице выполняется асинхронно. В январе мы добавили столбец browser_version. Команда ALTER TABLE ... ADD COLUMN в ClickHouse подтверждается немедленно и применяет изменения схемы асинхронно в ходе фоновых слияний частей. В течение примерно 20 минут после изменения запросы, ссылающиеся на новый столбец, возвращали NULL для строк в неизмененных частях. Ничего не сломалось - мы добавляли столбец, а не меняли существующий - но запрос, предполагающий browser_version IS NOT NULL, вернул бы неверные результаты. Урок: относитесь к ALTER TABLE на больших таблицах ClickHouse как к конечно согласованным (eventually consistent), а не синхронным.

Чего мы не делаем в ClickHouse#

Две вещи, которые всплывают в разговорах и которых мы сознательно избегаем.

Объединения на каждое событие с таблицами Postgres. Некоторые аналитические запросы естественным образом требуют объединения событий кликов с метаданными ссылок - «показать мне все клики для ссылок с тегом 'campaign-q2' вместе с их целевыми URL». Выполнение этого в виде федеративного запроса (ClickHouse clicks, объединенная с Postgres links через табличную функцию ClickHouse JDBC) возможно, но медленно. Вместо этого мы либо денормализуем соответствующие поля в событие клика на этапе сбора данных (например, utm_campaign, который извлекается из шаблона UTM ссылки), либо выполняем объединение в коде приложения: два отдельных запроса, объединяемых на уровне API аналитики. Это позволяет обеим базам данных работать в пределах их производительности.

Обновления по строкам. ClickHouse поддерживает ALTER TABLE UPDATE для изменений на уровне строк, но это асинхронная, ресурсозатратная операция, которая идет вразрез с принципами работы движка. Если вам нужно обновить событие клика после факта - скажем, задним числом пометить его как клик бота - значит, вы неправильно построили архитектуру. Правильный шаблон - хранить отдельную таблицу click_quality в ClickHouse, которая сопоставляет click_id с оценкой качества, вычисляемой сервисом url-scanner асинхронно, и объединять их (JOIN) во время запроса. Чтение обходится дешево; ретроактивные обновления дороги и должны быть редкими.

Более подробную информацию об инфраструктуре - о том, как редирект на границе взаимодействует с Redpanda, как работает дедупликация кликов и как сервис click-ingester вписывается в топологию сервисов - см. в /docs/architecture/click-ingester. Описание пограничной части (публикация в Redpanda по принципу «выстрелил и забыл», бюджет задержки) приведено в посте о p95 редиректа. О том, какие возможности данные открывают в дальнейшем - распределение по географии, разбивку по устройствам, дашборды атрибуции UTM - рассказывается на странице аналитических решений. Если вы создаете кампании ссылок через API и вас интересует, какие шаблоны запросов предоставляет SDK, соответствующей точкой входа является solutions/developers.

Для команд, рассматривающих Elido в качестве инфраструктуры ссылок, выбор архитектуры - ClickHouse для кликов, Postgres для всего остального - это также то, что позволяет нам предлагать аналитику без сэмплирования. В посте с объяснением смарт-ссылок рассказывается, как на границе принимаются решения о маршрутизации, которые генерируют эти события кликов.

Мариус Фосс - DevRel и edge infra в Elido. Он помогал проектировать конвейер click-ingester и провел больше времени, чем ему хотелось бы, отлаживая тайминги выборов ClickHouse Keeper.