Почему мы выбрали ClickHouse для аналитики кликов

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

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

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

• События кликов предназначены только для добавления и имеют форму аналитических запросов. Колоночное хранение выигрывает по обоим пунктам.
• Накладные расходы Postgres на хранение каждой строки события, раздувание индексов и конкуренция за ресурсы при выполнении vacuum накапливаются после достижения ~30 млн событий в общей таблице.
• ClickHouse с ORDER BY (workspace_id, link_id, timestamp) возвращает 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 секунды. На тот момент этого было достаточно; но очевидно, что это не тот путь, который масштабируется, когда объём кликов растёт на порядки.

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

ClickHouse - это колоночный движок базы данных, созданный специально для аналитических нагрузок - доступ 2026-05-12. Колоночный означает, что данные каждой колонки хранятся на диске непрерывно, а не вперемешку строка за строкой. Для запроса вроде «сумма кликов по странам для рабочего пространства X за последние 30 дней» движок читает только колонки country_code, workspace_id и timestamp. Он никогда не трогает user_agent или referer. Сокращение ввода-вывода на широких таблицах со многими колонками существенно.

Степень сжатия - второе свойство. Колоночные данные сжимаются значительно лучше строковых, потому что повторяющиеся значения в одной колонке - тот же workspace_id, встречающийся тысячи раз, тот же набор из примерно 60 кодов стран, горсть значений device - сжимаются почти до нуля кодеками LZ4 и ZSTD в ClickHouse. Колонки с низкой кардинальностью, как эти, - то место, где колоночные кодеки выигрывают больше всего по сравнению со строковым хранилищем Postgres. Это не только выигрыш в хранилище: меньшие данные на диске означают, что больше их помещается в кэш страниц ОС, а значит, меньше чтений диска на запрос.

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

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

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

CREATE TABLE IF NOT EXISTS click_events
(
    timestamp       DateTime64(3, 'UTC'),
    link_id         UInt64,
    workspace_id    UInt64,
    domain_id       UInt64,
    slug            LowCardinality(String),
    host            LowCardinality(String),
    user_agent      String,
    referer         String,
    ip              String,
    country_code    LowCardinality(FixedString(2)) DEFAULT '',
    device          LowCardinality(String) DEFAULT '',
    browser         LowCardinality(String) DEFAULT '',
    destination     String DEFAULT '',
    retention_days  UInt16 DEFAULT 730,
    INDEX idx_workspace workspace_id TYPE bloom_filter GRANULARITY 4,
    INDEX idx_link      link_id      TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (workspace_id, link_id, timestamp)
TTL toDateTime(timestamp) + toIntervalDay(retention_days);

Это базовая таблица. Более поздние миграции добавляют регион, город и приблизительные координаты, суточный хеш посетителя, а также флаги ботов, сканеров и подозрительности через ALTER TABLE ... ADD COLUMN.

В схеме несколько решений заслуживают пояснения.

LowCardinality(String) для slug, host, country_code, device, browser - эти колонки имеют ограниченные или очень повторяющиеся значения (несколько сотен собственных имён хостов, около 250 кодов стран, несколько типов устройств). Идентификаторы - обычные UInt64, которые и сами хорошо сжимаются. LowCardinality активирует словарное кодирование, которое хранит значения как целочисленные ссылки на словарь уникальных строк. Скорость запросов к этим колонкам существенно растёт; сжатие улучшается ещё больше. Мы узнали об этом из документации ClickHouse о типе данных low-cardinality - эмпирическое правило гласит, что любая строковая колонка с менее чем 10 000 уникальных значений является кандидатом на это.

PARTITION BY toYYYYMM(timestamp) создаёт отдельную секцию данных для каждого календарного месяца. Целые старые секции можно удалить (или переместить на более медленное хранилище) одним оператором ALTER TABLE click_events DROP PARTITION '202501', который выполняется мгновенно - без полного сканирования таблицы. Однако секция содержит клики всех рабочих пространств за тот месяц, поэтому хранение для каждого тарифа не может быть удалением секции. Вместо этого каждая строка при загрузке помечается окном её рабочего пространства в retention_days (90 дней на Free, 365 на Pro, 730 на Business), а клауза TTL удаляет просроченные строки при слиянии частей в ClickHouse.

ip хранит усечённый адрес: edge обнуляет последний октет IPv4-адреса (203.0.113.0, а не 203.0.113.42) и оставляет первые 48 бит IPv6-адреса до того, как событие клика покинет процесс редиректа. Геопоиск выполняется на edge до усечения, поэтому страна, регион, город и приблизительные координаты хранятся вместе с кликом, как и полный user agent. Усечённый IP вместе с городом и user agent всё равно следует считать персональными данными. См. solutions/analytics для ознакомления с полной архитектурой конфиденциальности.

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

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

Группы потребителей Redpanda - доступ 2026-05-12 - обеспечивают гарантию доставки между edge и инжектором. Сервис click-ingester работает как группа потребителей против топика clicks. Продакшн сегодня запускает один инжектор; группа потребителей - то, что позволит второму экземпляру перебрать партиции и перейти при сбое в пределах тайм-аута heartbeat группы, когда мы его добавим.

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

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

Архитектура более подробно описана на странице /docs/guides/clickhouse-export.

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

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

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

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

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

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

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

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

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

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

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

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

SELECT
    link_id,
    count() AS clicks_24h
FROM click_events
WHERE
    workspace_id = 123
    AND timestamp >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

Запрос читает только партиции в диапазоне дат и только колонки, которые он называет, поэтому его стоимость растёт вместе со срезом, который вы просите, а не со всей таблицей. Эквивалентный запрос в Postgres должен проходить строки, откуда и взялись 1,4 секунды выше.

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

Сбои, к которым мы готовимся

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

Перезапуски ClickHouse. На одном сервере перезапуск или обновление ClickHouse приостанавливает вставки. Инжектор держит неудачный пакет в памяти, повторяет его и фиксирует offset Redpanda только после успешной вставки, поэтому клики ждут, а не исчезают. Видимый эффект - пробел в живых данных дашборда, пока очередь не разгрузится. Один пробел мы знаем: буфер в памяти не имеет верхней границы, пока ClickHouse недоступен, и это есть в нашем бэклоге. Репликация с ReplicatedMergeTree и ClickHouse Keeper - запланированное средство против этой паузы, и оно приносит свою: выборы лидера Keeper также ненадолго останавливают вставки в реплицируемых таблицах, поэтому узлы Keeper должны стоять на отдельных хостах.

Баланс партиций инжектора. Сегодня один инжектор владеет каждой партицией топика clicks. Когда мы добавим второй экземпляр, стратегия назначения группы потребителей будет решать, насколько равномерно партиции распределятся, а неравномерный раздел проявляется как один экземпляр, медленно пакующий, пока другой простаивает. Следить нужно за задержкой потребителя для каждого экземпляра, а не только за общей для группы.

Асинхронный ALTER TABLE. Мы несколько раз добавляли колонки в click_events (region, city, lat и lng, visitor_hash, флаги сканера). ALTER TABLE ... ADD COLUMN в ClickHouse подтверждается сразу и не переписывает существующие части; строки в старых частях возвращают значение колонки по умолчанию (пустую строку или ноль), пока слияния этого не материализуют. Ничего не ломается, но запрос, считающий пустое city «нет города», неправильно посчитает старые строки. Урок: относитесь к ALTER TABLE на больших таблицах ClickHouse как к в конечном счёте согласованному и выбирайте значения по умолчанию, которые можно отличить от настоящих.

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

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

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

Обновления отдельных строк. ClickHouse поддерживает ALTER TABLE UPDATE для мутаций на уровне строк, но это асинхронная, ресурсоёмкая операция, которая противоречит предположениям движка. Если вам приходится обновлять событие клика постфактум - скажем, чтобы ретроспективно пометить его как клик бота, - вы построили архитектуру неправильно. Мы решаем эти флаги до вставки: edge оценивает подозрительные клики и классифицирует сканеры ссылок и анонимизирующие сети, прежде чем выдать событие, поэтому is_suspicious, is_scanner и anonymizer_type записываются вместе со строкой. Если бы оценку когда-то пришлось вычислять позже, шаблоном была бы отдельная таблица, соединяемая во время запроса. Чтения дёшевы; ретроспективные обновления дороги и должны быть редкими.

---

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

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

---

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