Dlaczego 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, timestamp) 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, która skaluje się wraz ze wzrostem liczby kliknięć o rzędy wielkości.

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_code, workspace_id i timestamp. Nigdy nie dotyka user_agent ani referer. Redukcja I/O na szerokich tabelach z wieloma kolumnami jest znaczna.

Druga 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 device - kompresują się prawie do zera pod kodekami LZ4 i ZSTD ClickHouse. Kolumny o niskiej kardynalności, jak te, to miejsce, w którym kodeki kolumnowe zyskują najwięcej względem wierszowego przechowywania w Postgres. 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, timestamp). Zapytanie o kliknięcia workspace'u 123 w ciągu ostatnich 7 dni czyta tylko te części danych, gdzie pojawia się workspace_id = 123, 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 lub więcej węzłów ClickHouse z równoważną koordynacją ZooKeeper (ClickHouse Keeper), replikującą części w miarę ich zapisywania. Jeszcze tego nie uruchamiamy: produkcja to jeden host z jednym serwerem ClickHouse na zwykłym MergeTree. Replikacja jest planowanym kolejnym krokiem, gdy jeden serwer przestanie wystarczać.

Schemat, który dostarczamy

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);

To jest tabela bazowa. Późniejsze migracje dodają region, miasto i przybliżone współrzędne, dzienny hash odwiedzającego oraz flagi bota, skanera i podejrzanego ruchu przez ALTER TABLE ... ADD COLUMN.

Kilka decyzji schematycznych wartych omówienia.

LowCardinality(String) dla slug, host, country_code, device, browser - te kolumny mają ograniczone lub bardzo powtarzalne wartości (kilkaset własnych nazw hostów, ~250 kodów krajów, kilka typów urządzeń). Identyfikatory to zwykłe UInt64, które dobrze się kompresują same z siebie. 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(timestamp) tworzy oddzielną partycję danych na każdy miesiąc kalendarzowy. Całe stare partycje można usunąć (lub przenieść na wolniejsze przechowywanie) jednym poleceniem ALTER TABLE click_events DROP PARTITION '202501', które jest natychmiastowe - bez pełnego skanowania tabeli. Partycja zawiera jednak kliknięcia wszystkich workspace'ów z danego miesiąca, więc retencja zależna od planu nie może być usunięciem partycji. Zamiast tego każdy wiersz jest przy ingestii oznaczany oknem swojego workspace'u w retention_days (90 dni w Free, 365 w Pro, 730 w Business), a klauzula TTL usuwa wygasłe wiersze podczas scalania części przez ClickHouse.

ip przechowuje skrócony adres: edge zeruje ostatni oktet adresu IPv4 (203.0.113.0 zamiast 203.0.113.42) i zachowuje pierwsze 48 bitów adresu IPv6, zanim zdarzenie kliknięcia opuści proces przekierowania. Geolookup odbywa się na edge przed skróceniem, więc kraj, region, miasto i przybliżone współrzędne są zapisywane razem z kliknięciem, wraz z pełnym user agentem. Skrócony adres IP wraz z miastem i user agentem nadal należy traktować jako dane osobowe. Pełna architektura prywatności jest opisana na solutions/analytics.

Ścieżka ingestion

Handler przekierowania w serwisie edge działa na zasadzie fire-and-forget. Dołącza zdarzenie kliknięcia do tematu Redpanda (clicks na produkcji) 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. Produkcja uruchamia dziś jeden ingester; grupa konsumentów pozwoli drugiej instancji przejąć partycje i przejąć pracę w ramach timeoutu heartbeat grupy, gdy ją dodamy.

Ingester gromadzi zdarzenia w pamięci i opróżnia je w partiach: 1000 wierszy lub 5 sekund, 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 / 5 sekundach i nie napotkaliśmy problemów z liczbą części w normalnej pracy.

Backpressure propaguje się przez Redpanda. Ingester zatwierdza offset rekordu dopiero po zapisaniu jego partii w ClickHouse, więc jeśli ClickHouse jest wolny lub restartuje się, nieudane partie są ponawiane, a lag konsumenta rośnie, co jest wiodącym wskaźnikiem problemów ze świeżością danych. Jeśli sam ingester się restartuje, niezatwierdzone rekordy są czytane ponownie z Redpanda, w ramach okna retencji tematu.

Architektura jest opisana bardziej szczegółowo pod adresem /docs/guides/clickhouse-export.

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ę click_events. 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 liczbowymi. 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(timestamp) dla ostatnich 24 godzin dla workspace'u.

Podział według kraju dla kampanii. Zespół marketingowy sprawdzający wyniki kampanii chce GROUP BY country_code, COUNT(*) filtrowanego do linków kampanii 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, browser, 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, timestamp). Filtr workspace'u natychmiast ogranicza skan do właściwego wycinka danych; kolumny link_id i timestamp 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 click_events
WHERE
    workspace_id = 123
    AND timestamp >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

Zapytanie czyta tylko partycje z zakresu dat i tylko kolumny, które wymienia, więc jego koszt rośnie wraz z wycinkiem, o który prosisz, a nie z całą tabelą. Równoważne zapytanie w Postgres musi przejść po wierszach, i stąd wzięło się 1,4 sekundy z wcześniejszego przykładu.

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, na które się przygotowujemy

Produkcja uruchamia jeden serwer ClickHouse, jeden broker Redpanda i jeden ingester na jednym hoście, więc tryby awarii warte zapisania to te, które ma ten układ dziś, i te, które pojawią się wraz z planowanym skalowaniem.

Restarty ClickHouse. Na jednym serwerze restart lub aktualizacja ClickHouse wstrzymuje wstawki. Ingester trzyma nieudaną partię w pamięci, ponawia ją i zatwierdza offsety Redpanda dopiero po udanej wstawce, więc kliknięcia czekają, zamiast znikać. Widocznym skutkiem jest przerwa w danych dashboardu na żywo, aż zaległość się opróżni. Jedna znana nam luka: bufor w pamięci nie ma górnego limitu, gdy ClickHouse jest niedostępny, co jest w naszym backlogu. Replikacja z ReplicatedMergeTree i ClickHouse Keeper to planowana naprawa tej przerwy i wnosi własny problem: wybór lidera Keepera też na krótko wstrzymuje wstawki do tabel replikowanych, więc węzły Keeper muszą stać na oddzielnych hostach.

Równoważenie partycji w ingesterze. Dziś jeden ingester posiada wszystkie partycje tematu clicks. Gdy dodamy drugą instancję, strategia przydziału grupy konsumentów decyduje, jak równo partycje zostaną podzielone, a nierówny podział objawia się tym, że jedna instancja batchuje wolno, podczas gdy druga się nudzi. Warto obserwować lag konsumenta per instancja, a nie tylko sumę dla grupy.

ALTER TABLE jest asynchroniczny. Kilka razy dodawaliśmy kolumny do click_events (region, city, lat i lng, visitor_hash, flagi skanera). ALTER TABLE ... ADD COLUMN ClickHouse potwierdza natychmiast i nie przepisuje istniejących części; wiersze w starszych częściach zwracają wartość domyślną kolumny (pusty ciąg lub zero), dopóki scalanie jej nie zmaterializuje. Nic się nie psuje, ale zapytanie, które traktuje pusty city jako "brak miasta", źle policzy stare wiersze. Lekcja: traktuj ALTER TABLE na dużych tabelach ClickHouse jako ostatecznie spójne i wybieraj wartości domyślne, które da się odróżnić od prawdziwych.

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 click_events 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 destination, adres URL, na który odwiedzający został faktycznie wysłany), 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. Te flagi ustalamy przed wstawką: edge ocenia podejrzane kliknięcia i klasyfikuje skanery linków oraz sieci anonimizujące, zanim wyemituje zdarzenie, więc is_suspicious, is_scanner i anonymizer_type są zapisywane razem z wierszem. Gdyby wynik trzeba było kiedyś obliczyć później, wzorcem byłaby oddzielna tabela złączana 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/guides/clickhouse-export. 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ł, czytając logi scalania ClickHouse.