Dlaczego używamy ClickHouse do analityki kliknięć (a nie Postgres)

Postgres jest doskonały. Używamy go intensywnie i mu ufamy. Przechowuje każdy link, każdy workspace, każdy rekord rozliczeniowy, każdego użytkownika. Ale mniej więcej po 90 dniach na rosnącym skracaczu URL trafiasz na ścianę, jeśli przechowujesz tam też surowe zdarzenia kliknięć. Ściana nie jest dramatyczna - Postgres nie pada, po prostu staje się wolniejszy w tym specyficznym, frustrującym sposób, w jaki zapytania agregujące na dużych tabelach tylko do dołączania stają się wolniejsze. Skany indeksów przestają pomagać. Autovacuum nie nadąża. Panel ładujący się w 800ms ładuje się teraz przez 4 sekundy. Dodajesz więcej indeksów. Robi się gorzej.

Ten wpis wyjaśnia, dlaczego przenieśliśmy zdarzenia kliknięć do ClickHouse, jak wygląda schemat, jak działa ścieżka ingestion i gdzie są ostre krawędzie. Jest uzupełnieniem wpisu o opóźnieniu przekierowań i omawia warstwę danych znajdującą się poniżej przekierowania - część, która zamienia surowe zdarzenia kliknięć w dashboardy na stronie rozwiązań analitycznych.

TL;DR#

• Zdarzenia kliknięć są tylko do dołączania i mają kształt zapytań agregujących. Kolumnowe przechowywanie wygrywa na obu frontach.
• Narzut wiersz-na-zdarzenie Postgres, rozrost indeksów i rywalizacja o vacuum kumulują się powyżej ~30M zdarzeń na współdzielonej tabeli.
• ClickHouse z ORDER BY (workspace_id, link_id, created_at) zwraca 90-dniowe zestawienia per-workspace w czasie poniżej 100ms bez dodatkowych indeksów.
• Metadane linków pozostają w Postgres (modyfikowalne, transakcyjne); zdarzenia kliknięć pozostają w ClickHouse (tylko do dołączania, analityczne). Podział jest czysty, bo oba systemy nie muszą się pokrywać w czasie zapisu.

Dlaczego Postgres ma trudności z tym obciążeniem#

Problem tkwi w kształcie obciążenia. Zdarzenie kliknięcia:

• Jest zapisywane raz i nigdy nie aktualizowane.
• Jest zapisywane przy wysokich, nagłych stawkach - stosunek zapisu do odczytu wynosi około 50:1, a przy starcie kampanii wzrasta jeszcze bardziej.
• Jest odpytywane w sposób agregujący: łączna liczba kliknięć per link, podział geograficzny dla workspace'u, podział urządzeń w 100 najlepszych linkach kampanii. Prawie nikt nie odpytuje bezpośrednio pojedynczego wiersza kliknięcia.

Postgres jest zbudowany na innym zestawie założeń. Każdy wiersz niesie nagłówek krotki, pola identyfikatora transakcji (xmin, xmax) i metadane widoczności. Dla obciążeń transakcyjnych, gdzie aktualizujesz wiersze w miejscu i potrzebujesz MVCC do obsługi współbieżnych odczytów i zapisów, ten narzut jest ceną za poprawność. Dla danych analitycznych tylko do dołączania, których nigdy nie dotykasz ponownie, to czyste marnotrawstwo.

Problem z indeksami jest gorszy. Indeksy B-tree Postgres dobrze działają dla selektywnych wyszukiwań punktowych - znajdowanie wiersza z link_id = 'xyz'. Mniej pomagają, gdy uruchamiasz GROUP BY link_id, country na 10 milionach wierszy dla workspace'u. Indeks zawęża skan, ale zapytanie i tak musi pobierać zdekompresowane dane wierszy ze stron heap, deserializować je i agregować. Przy 30M wierszach zapytanie jest wolne. Przy 100M wierszach jest bolesne.

Autovacuum pogarsza to w praktyce. Wysokoprzepustowe wstawki generują martwe krotki (wersje wierszy MVCC, które nie są już widoczne dla aktywnych transakcji, ale nie zostały jeszcze oczyszczone). Autovacuum je odzyskuje, ale konkuruje z żywymi zapytaniami o I/O. Na dashboardach Postgres zobaczysz okresowe skoki opóźnień, które odpowiadają uruchamianiu autovacuum na tabeli zdarzeń. Dokumentacja kompresji i architektury chunków TimescaleDB warto przeczytać dla szczegółów dotyczących tego, jak rozszerzenie Postgres próbuje to obejść - problem jest na tyle realny, że istnieje dedykowana warstwa szeregów czasowych na szczycie Postgres, aby go rozwiązać.

Uruchamialiśmy kliknięcia w Postgres przez pierwsze 90 dni projektu. Workspace z ~4M zdarzeniami kliknięć miał dashboard, który potrzebował 3,2 sekundy na wyrenderowanie 7-dniowego zestawienia według krajów. Dodanie częściowego indeksu na (workspace_id, created_at) obniżyło to do 1,4 sekundy. Wystarczające w tamtym czasie; wyraźnie nie ścieżka skalująca się do 100M+ zdarzeń miesięcznie.

Dlaczego ClickHouse pasuje#

ClickHouse to kolumnowy silnik bazy danych zbudowany wprost dla obciążeń analitycznych - dostęp z 2026-05-12. Kolumnowy oznacza, że dane każdej kolumny są przechowywane ciągiem na dysku, a nie przeplatane wiersz po wierszu. Dla zapytania „zsumuj liczbę kliknięć według kraju dla workspace'u X w ciągu ostatnich 30 dni" silnik czyta tylko kolumny country, workspace_id i created_at. Nigdy nie dotyka user_agent_hash ani referrer_host. Redukcja I/O na szerokich tabelach z wieloma kolumnami jest znaczna.

Drugi właściwość to współczynnik kompresji. Dane kolumnowe kompresują się znacznie lepiej niż dane wierszowe, ponieważ powtarzające się wartości w tej samej kolumnie - ten sam workspace_id pojawiający się tysiące razy, ten sam zestaw około 60 kodów krajów, kilka wartości os - kompresują się prawie do zera pod kodekami LZ4 i ZSTD ClickHouse. Nasze zdarzenia kliknięć kompresują się do około 12–15% swojego surowego rozmiaru na dysku. Te same dane w Postgres zajmują 35–45% surowego rozmiaru przy domyślnej kompresji toast. To nie tylko korzyść w przechowywaniu - mniejsze dane na dysku oznaczają, że więcej z nich mieści się w pamięci podręcznej strony OS, co oznacza mniej odczytów dysku per zapytanie.

Trzecią właściwością jest rodzina silników MergeTree - dostęp z 2026-05-12. Tabele MergeTree sortują i klastrują dane według klucza ORDER BY tabeli. Zapytania filtrujące lub grupujące według wiodących kolumn tego klucza czytają tylko te części danych, które nakładają się na zakres filtra. To ekwiwalent klastrowanego indeksu w ClickHouse, z tą różnicą, że jest obowiązkowy (definiujesz go przy tworzeniu tabeli) i dotyczy każdego zapytania, nie tylko zaindeksowanych kolumn.

Używamy ORDER BY (workspace_id, link_id, created_at). Zapytanie o kliknięcia workspace'u abc123 w ciągu ostatnich 7 dni czyta tylko te części danych, gdzie pojawia się workspace_id = 'abc123', a następnie w ich obrębie czyta tylko wycinek zakresu czasowego. Na 90 dniach danych kliknięć dotyczy to zazwyczaj 3–8% wszystkich części danych. Rzadki indeks podstawowy, który ClickHouse utrzymuje nad kolumnami ORDER BY, sprawia, że to targetowanie jest efektywne bez konieczności gęstego B-tree dla każdego wzorca zapytań. ReplicatedMergeTree dodaje HA - dwa węzły ClickHouse z równoważną koordynacją ZooKeeper (ClickHouse Keeper), replikującą części w miarę ich zapisywania.

Schemat, który dostarczamy#

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;

Kilka decyzji schematycznych wartych omówienia.

LowCardinality(String) dla workspace_id, country, device, os, browser, utm_source, utm_medium - te kolumny mają ograniczoną kardynalność (setki odrębnych identyfikatorów workspace, ~250 kodów krajów, kilka typów urządzeń). LowCardinality aktywuje kodowanie słownikowe, które przechowuje wartości jako odwołania całkowitoliczbowe do słownika unikalnych ciągów. Szybkość zapytań na tych kolumnach znacznie się poprawia; kompresja poprawia się dalej. Nauczyliśmy się tego z dokumentacji ClickHouse o typie danych o niskiej kardynalności - zasada kciuka mówi, że każda kolumna tekstowa z mniej niż 10 000 odrębnych wartości jest kandydatem.

PARTITION BY toYYYYMM(created_at) tworzy oddzielną partycję danych na każdy miesiąc kalendarzowy. Stare partycje można usunąć (lub przenieść na wolniejsze przechowywanie) jednym poleceniem ALTER TABLE clicks DROP PARTITION '202501', które jest natychmiastowe - bez pełnego skanowania tabeli. Dla egzekwowania retencji danych RODO ma to praktyczne znaczenie: usunięcie historycznych kliknięć workspace'u to jedna operacja na miesięczną partycję, a nie rozproszony DELETE WHERE workspace_id = ?.

ip_truncated przechowuje adres IP z wyzerowanym ostatnim oktetem (203.0.113.0 zamiast 203.0.113.42). Pełny adres IP nigdy nie jest przechowywany. Skrócona forma wystarczy do geolookup - który wykonujemy w czasie ingestii, przed przechowywaniem - i nie stanowi danych osobowych w rozumieniu definicji z Artykułu 4 RODO w połączeniu z naszymi praktykami minimalizacji danych. Pełna architektura prywatności jest opisana na solutions/analytics.

Ścieżka ingestion#

Handler przekierowania w każdym edge POP działa na zasadzie fire-and-forget. Dołącza zdarzenie kliknięcia do tematu Redpanda (clicks.v1) i wydaje 302. Nie czeka na potwierdzenie od ClickHouse. Jeśli Redpanda jest w danym momencie niedostępna, kliknięcie jest gubione - przekierowanie nie jest. Hierarchia jest celowa: pominięte kliknięcie jest odwracalne (możemy zrekonstruować przybliżone liczby z logów dostępu); nieudane przekierowanie jest widoczne dla użytkownika.

Grupy konsumentów Redpanda - dostęp z 2026-05-12 - zapewniają gwarancję dostarczenia między edge a ingesterem. Serwis click-ingester działa jako grupa konsumentów na temat clicks.v1. Partycje są przydzielane automatycznie; jeśli instancja ingestera zawiedzie, jej partycje są równoważone do przeżywających instancji w ramach timeoutu heartbeat grupy.

Ingester gromadzi zdarzenia w pamięci i opróżnia je w partiach: 1000 wierszy lub 250ms, w zależności od tego, co nastąpi pierwsze. To ważne. Silnik MergeTree ClickHouse jest zoptymalizowany pod kątem zbiorczych wstawek - każdą wstawkę zapisuje jako nową część danych, a wiele małych wstawek generuje wiele małych części, co wyzwala agresywne scalanie w tle i spowalnia zapytania podczas obciążenia scalaniem. Dokumentacja ClickHouse zaleca wstawki co najmniej 1000 wierszy, aby utrzymać liczbę części pod kontrolą. Opróżniamy przy 1000 wierszach / 250ms i nie napotkaliśmy problemów z liczbą części w normalnej pracy.

Backpressure propaguje się przez Redpanda. Jeśli ClickHouse jest wolny lub restartuje się, lag konsumenta ingestera wzrasta, co monitorujemy jako wiodący wskaźnik problemów ze świeżością danych. Konfiguracja retencji Redpanda (14 dni) daje nam znaczące okno odtwarzania, jeśli ClickHouse musi być przebudowany od zera.

Architektura jest opisana bardziej szczegółowo pod adresem /docs/architecture/click-ingester.

Postgres kontra ClickHouse: podział#

Dwie bazy danych dla jednego serwisu to koszt wymagający uzasadnienia. Uzasadnieniem tutaj jest to, że dwa obciążenia są rzeczywiście niekompatybilne przy skali, a dane nie nakładają się w sposób wymagający synchronicznej koordynacji.

Postgres posiada metadane linków: tabele links, workspaces, users, billing, rules. Są one modyfikowalne (linki są archiwizowane, docelowe URL się zmieniają, rekordy rozliczeniowe są aktualizowane), transakcyjne (tworzenie linku i bicie jego sluga musi być atomowe) i o wysokim stosunku odczytów do zapisów. Indeksy B-tree i MVCC to dokładnie to, czego chcesz.

ClickHouse posiada zdarzenia kliknięć: tabelę clicks. Są one tylko do dołączania (kliknięcie, raz zapisane, nigdy nie jest aktualizowane), intensywne w zapisie i odpytywane we wzorcach agregujących, które kolumnowe przechowywanie obsługuje lepiej. W czasie zapisu nie ma złączeń między bazami danych - ingester wstawia zdarzenia kliknięć z workspace_id i link_id jako zdenormalizowanymi polami tekstowymi. W czasie zapytania API analityczne może oddzielnie pobierać metadane linków z Postgres i liczby kliknięć z ClickHouse, a następnie łączyć je w kodzie aplikacji. To jedna dodatkowa pętla sieciowa; to znacznie lepszy kompromis niż obciążanie Postgres analitycznymi zapytaniami.

Dokumentacja rozproszonego Postgres Citus opisuje alternatywę: rozproszone rozszerzenia Postgres zaprojektowane do obsługi analitycznych obciążeń szeregów czasowych. Oceniliśmy tę ścieżkę i doszliśmy do wniosku, że wydajność zapytań i współczynnik kompresji ClickHouse są na tyle wyższe dla czystych obciążeń analitycznych, że operacyjny narzut dwóch baz danych jest tego wart. Jeśli Twoje obciążenie jest mieszane - nieco analityczne, dużo mutacji na poziomie wierszy - podejście Citus ma więcej sensu.

Wzorce zapytań, które uderzamy co godzinę#

Odświeżanie dashboardu dla każdego workspace'u odbywa się w cyklu około 60 sekund. Są trzy ciężkie zapytania:

Zestawienie kliknięć per link, ostatnie 24 godziny. Górna część każdej listy linków pokazuje sparkline. Jest to GROUP BY link_id, toHour(created_at) dla ostatnich 24 godzin dla workspace'u.

Podział według kraju dla kampanii. Zespół marketingowy sprawdzający wyniki kampanii chce GROUP BY country, COUNT(*) filtrowanego przez utm_campaign dla zakresu dat.

Podział urządzeń w 100 najlepszych linkach workspace'u. Zakładka analityki pokazuje podział urządzeń/OS. Jest to GROUP BY device, os, COUNT(*) dla najwyżej ruchowych linków workspace'u w ciągu ostatnich 30 dni.

Wszystkie trzy wzorce są dobrze obsługiwane przez klasterowanie ORDER BY (workspace_id, link_id, created_at). Filtr workspace'u natychmiast ogranicza skan do właściwego wycinka danych; kolumny link_id i created_at dalej go zawężają dla pierwszych dwóch wzorców. Zapytanie o podział urządzeń skanuje więcej kolumn, ale nadal korzysta z klasterowania na poziomie workspace'u.

Reprezentatywne zapytanie dla 100 najlepszych linków według liczby kliknięć w ciągu ostatnich 24 godzin:

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

Na workspace'ie z 12 milionami zdarzeń kliknięć obejmujących 90 dni to zapytanie zwraca wyniki w przybliżeniu w 45–80ms, w zależności od liczby części danych w odpowiedniej partycji. To samo zapytanie na tabeli Postgres o równoważnym rozmiarze działa 2–4 sekundy przy najlepszej konfiguracji indeksów, jaką próbowaliśmy.

Nie używamy zmaterializowanych widoków ClickHouse dla tych wzorców. Klasterowanie ORDER BY i przycinanie partycji są wystarczające, a zmaterializowane widoki dodają złożoność migracji schematu. Jeśli opóźnienie zapytań kiedykolwiek stanie się problemem przy wyższych wolumenach zdarzeń, zmaterializowane widoki są kolejną dźwignią - nie dodatkowe indeksowanie.

Tryby awarii, które napotkaliśmy#

Trzy incydenty produkcyjne warte udokumentowania.

Zaburzenie ClickHouse Keeper. ClickHouse Keeper to warstwa koordynacji równoważna ZooKeeper dla ReplicatedMergeTree. Pewnego razu restart węzła Keeper podczas scalania w tle spowodował, że replikowana tabela przestała przyjmować wstawki na około 90 sekund podczas wyboru nowego lidera. Ingester ponawiał próby z wykładniczym backoffem (2s, 4s, 8s, cap 60s) - jest to standardowa logika ponawiania Go z obsługą context.DeadlineExceeded. Podczas okna awarii około 1800 zdarzeń kliknięć zgromadziło się w buforze pamięci ingestera i zostało pomyślnie opróżnionych po odzyskaniu Keepera. Jedynym widocznym dla użytkownika efektem była ~2-minutowa przerwa w danych dashboardu na żywo. Naprawa polegała na zapewnieniu, że węzły Keeper są rozłożone na oddzielnych fizycznych hostach z oddzielnymi obwodami zasilania w sieci Hetzner.

Nierównoważność partycji w Redpanda. Temat clicks.v1 używa 12 partycji. Po dodaniu drugiej instancji ingestera, domyślny balancer Redpanda przypisał 9 partycji jednej instancji, a 3 drugiej - używał strategii łącznej liczby partycji na konsumenta, a liczby instancji dały nierównoważność 3:1. Nie stanowiło to ryzyka utraty danych, ale nadmiernie obciążona instancja batchwała wolno pod tym obciążeniem, zwiększając opóźnienie przetwarzania kliknięć do około 800ms. Naprawa polegała na ręcznym przełączeniu równoważenia partycji za pomocą API administratora Redpanda i przejściu na balancer rack-aware z przypisaniem round-robin.

ALTER TABLE na dużej tabeli jest asynchroniczny. W styczniu dodaliśmy kolumnę browser_version. ALTER TABLE ... ADD COLUMN ClickHouse potwierdza natychmiast i stosuje zmianę schematu asynchronicznie podczas scalania części w tle. Przez około 20 minut po alterze, zapytania odwołujące się do nowej kolumny zwracały NULL dla wierszy w niezmutowanych częściach. Nic się nie popsuło - dodawaliśmy kolumnę, a nie zmieniali istniejącą - ale zapytanie zakładające browser_version IS NOT NULL zwróciłoby nieprawidłowe wyniki. Lekcja: traktuj ALTER TABLE na dużych tabelach ClickHouse jako ostatecznie spójne, a nie synchroniczne.

Czego nie robimy w ClickHouse#

Dwie rzeczy, które pojawiają się w rozmowach i których celowo unikaliśmy.

Złączenia per zdarzenie z tabelami Postgres. Niektóre zapytania analityczne naturalnie chcą łączyć zdarzenia kliknięć z metadanymi linków - „pokaż mi wszystkie kliknięcia dla linków oznaczonych 'kampania-q2' wraz z ich docelowymi URL". Robienie tego jako sfederowanego zapytania (złączenie clicks ClickHouse z links Postgres przez funkcję tabeli JDBC ClickHouse) jest możliwe, ale wolne. Zamiast tego albo denormalizujemy odpowiednie pola do zdarzenia kliknięcia w czasie ingestii (jak utm_campaign, który jest pochodną szablonu UTM linku), albo rozwiązujemy złączenie w kodzie aplikacji: dwa oddzielne zapytania, scalane w warstwie API analitycznego. To utrzymuje obie bazy danych działające w ich zakresach wydajności.

Aktualizacje per wiersz. ClickHouse obsługuje ALTER TABLE UPDATE dla mutacji na poziomie wierszy, ale jest to operacja asynchroniczna i zasobochłonna, działająca wbrew założeniom silnika. Jeśli okaże się, że musisz zaktualizować zdarzenie kliknięcia po fakcie - powiedzmy, aby retroaktywnie oznaczyć je jako kliknięcie bota - zbudowałeś architekturę źle. Właściwym wzorcem jest przechowywanie oddzielnej tabeli click_quality w ClickHouse, która mapuje click_id na wynik jakości, obliczany przez serwis url-scanner asynchronicznie, i złączanie ich w czasie zapytania. Odczyty są tanie; retroaktywne aktualizacje są kosztowne i powinny być rzadkie.

Dla szerszego obrazu infrastruktury - jak przekierowanie krawędziowe współdziała z Redpanda, jak działa deduplikacja kliknięć i jak serwis click-ingester wpisuje się w topologię serwisów - patrz /docs/architecture/click-ingester. Historia po stronie edge (publikacja Redpanda fire-and-forget, budżet opóźnienia) jest opisana we wpisie o p95 przekierowania. Dla tego, co dane umożliwiają poniżej - podziały geograficzne, podziały urządzeń, dashboardy atrybucji UTM - strona solutions/analytics obejmuje powierzchnię produktu. Jeśli budujesz kampanie linków przez API i interesuje Cię, jakie wzorce zapytań eksponuje SDK, solutions/developers jest odpowiednim punktem wejścia.

Dla zespołów oceniających Elido jako infrastrukturę linków, wybór architektoniczny tutaj - ClickHouse dla kliknięć, Postgres dla wszystkiego innego - jest też tym, co pozwala nam oferować analitykę bez próbkowania. Wpis o inteligentnych linkach omawia, jak działają decyzje routingu generujące te zdarzenia kliknięć na edge.

Marius Voß jest DevRel i inżynierem edge infra w Elido. Pomagał zaprojektować potok click-ingestera i spędził więcej czasu, niż by chciał, debugując czas elekcji ClickHouse Keeper.