Чому ми використовуємо ClickHouse для аналітики кліків (а не Postgres)

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

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

TL;DR#

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

Чому ClickHouse підходить#

ClickHouse - це колоночний рушій бази даних, побудований спеціально для аналітичних навантажень - доступ отримано 2026-05-12. Колоночний означає, що дані для кожної колонки зберігаються на диску безперервно, а не чергуються рядок за рядком. Для запиту на кшталт «сума кліків за країною для робочого простору X за останні 30 днів» рушій читає лише колонки country, workspace_id та created_at. Він ніколи не чіпає user_agent_hash або referrer_host. Скорочення I/O у широких таблицях з багатьма колонками є значним.

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

Третя властивість - сімейство рушіїв MergeTree - доступ отримано 2026-05-12. Таблиці 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 про тип даних low-cardinality - емпіричне правило каже, що будь-яка рядкова колонка з менш ніж 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 - доступ отримано 2026-05-12 - забезпечують гарантію доставки між edge та інжектором. Сервіс click-ingester працює як група споживачів для топіка clicks.v1. Партиції призначаються автоматично; якщо екземпляр інжектора виходить з ладу, його партиції перерозподіляються між іншими екземплярами в групі протягом таймауту хартбіту.

Інжектор накопичує події в пам'яті та скидає їх пачками: 1 000 рядків або кожні 250 мс, залежно від того, що настане раніше. Це важливо. Рушій MergeTree у ClickHouse оптимізований для масових вставок - він записує кожну вставку як нову частину даних, а велика кількість крихітних вставок створює багато дрібних частин, що запускає агресивне фонове злиття і сповільнює запити під час навантаження від злиття. Документація ClickHouse рекомендує вставки щонайменше по 1 000 рядків, щоб тримати кількість частин під контролем. Ми скидаємо дані по 1 000 рядків / 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. Вони призначені лише для додавання (клік після запису ніколи не оновлюється), інтенсивні на запис і запитуються за шаблонами агрегації, які краще обробляє колоночне зберігання. На етапі запису немає перехресних з’єднань між базами даних - інжектор вставляє події кліків з 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 та відсікання секцій достатньо, а матеріалізовані представлення додають складності міграції схем. Якщо затримка запитів колись стане проблемою при вищих обсягах подій, матеріалізовані представлення будуть наступним кроком - а не додаткове індексування.

Збої, з якими ми стикалися#

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

Збій ClickHouse Keeper. ClickHouse Keeper - це координаційний рівень для ReplicatedMergeTree, еквівалентний ZooKeeper. Одного разу перезавантаження вузла Keeper під час фонового злиття спричинило призупинення прийому вставок у репліковану таблицю приблизно на 90 секунд, поки тривали вибори нового лідера. Інжектор повторював спроби з експоненціальною затримкою (2с, 4с, 8с, максимум 60с) - це стандартна логіка повторів у Go з обробкою context.DeadlineExceeded. Під час цього вікна простою близько 1 800 подій кліків накопичилися в буфері пам'яті інжектора і були успішно записані після відновлення Keeper. Єдиним помітним для користувача ефектом була ~2-хвилинна прогалина в даних дашборду в реальному часі. Виправленням стало забезпечення розміщення вузлів Keeper на окремих фізичних хостах з окремими лініями живлення в мережі Hetzner.

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

ALTER TABLE на великій таблиці є асинхронним. У січні ми додали колонку browser_version. Команда ClickHouse ALTER TABLE ... ADD COLUMN миттєво підтверджує отримання і застосовує зміну схеми асинхронно під час фонового злиття частин. Протягом приблизно 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 під час запиту. Читання дешеві; ретроактивні оновлення дорогі і мають бути рідкісними.

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

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

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