Чому ClickHouse для аналітики кліків, а не Postgres

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

Цей допис пояснює, чому ми перенесли події кліків у ClickHouse, як виглядає схема, як працює шлях завантаження даних і де знаходяться гострі кути. Це доповнення до допису про затримку редиректу, яке охоплює рівень даних, розташований після редиректу - частину, що перетворює сирі події кліків на дашборди на сторінці рішень для аналітики.

TL;DR

• Події кліків призначені лише для додавання (append-only) і мають форму аналітичних запитів. Колоночне зберігання перемагає в обох випадках.
• Накладні витрати 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 млн подій кліків мав дашборд, завантаження якого займало 3,2 секунди для 7-денного зведення за країною. Додавання часткового індексу на (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 - доступ отримано 2026-05-12. Таблиці 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 та відсікання секцій достатньо, а матеріалізовані представлення додають складності міграції схем. Якщо затримка запитів колись стане проблемою при вищих обсягах подій, матеріалізовані представлення будуть наступним кроком - а не додаткове індексування.

Збої, до яких ми готуємося

Продакшн запускає один сервер 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 записуються разом із рядком. Якби оцінку колись довелося обчислювати пізніше, шаблоном була б окрема таблиця, з'єднана під час запиту. Читання дешеві; ретроспективні оновлення дорогі й мають бути рідкісними.

---

Для ширшого розуміння інфраструктури - як редирект на edge взаємодіє з Redpanda, як працює дедуплікація кліків та як сервіс click-ingester вписується в топологію сервісів - дивіться /docs/guides/clickhouse-export. Історія на стороні edge (відправка в Redpanda за принципом «відправив і забув», бюджет затримок) описана в дописі про p95 редиректу. Про те, що ці дані дають на виході - розподіл за географією, пристроями, дашборди UTM-атрибуції - розповідає сторінка рішень для аналітики. Якщо ви створюєте кампанії посилань через API і вас цікавить, які шаблони запитів відкриває SDK, відповідною точкою входу є solutions/developers.

Для команд, що оцінюють Elido як інфраструктуру посилань, вибір архітектури тут - ClickHouse для кліків, Postgres для всього іншого - це також те, що дозволяє нам пропонувати аналітику без семплювання. Допис з поясненням смарт-посилань розповідає про те, як рішення про маршрутизацію, що генерують ці події кліків, працюють на edge.

---

Marius Voß - DevRel та edge infra в Elido. Він допоміг спроєктувати конвеєр click-ingester і провів більше часу, ніж хотілося б, читаючи логи злиття ClickHouse.