CollapsingMergeTree w ClickHouse: aktualizacja bez UPDATE

CollapsingMergeTree: Jak aktualizować agregaty bez UPDATE w ClickHouse

1. Po co CollapsingMergeTree — problem aktualizacji agregatów

Wróćmy do naszego kasyna online. Każdy gracz ma saldo. Gdy gracz stawia zakład — saldo maleje. Gdy wygrywa — rośnie. Gdy administrator anuluje podejrzaną transakcję — saldo zmienia się ponownie.

W zwykłej bazie danych (PostgreSQL) po prostu zrobiłbyś UPDATE players SET balance = balance - 100 WHERE user_id = 123. Proste i zrozumiałe.

Ale ClickHouse nie umie aktualizować danych. W ogóle. Dlaczego? Ponieważ ClickHouse został stworzony do analityki, gdzie dane są tylko dodawane (append-only). Aktualizacje są bolesne dla magazynów kolumnowych, gdzie dane leżą w skompresowanych blokach. Aby zmienić jedną komórkę, trzeba by przepisywać całe bloki.

Jak więc zmieniać saldo? Nie zmieniasz starego rekordu. Dodajesz NOWY rekord, który mówi: „anuluj poprzednią zmianę” i „dodaj nową”. Nazywa się to materializacją zmian przez anulowanie.

Analogia z życia: Wyobraź sobie księgę rachunkową, w której wszystkie zapisy są już wykonane atramentem. Nie można wymazać i poprawić. Zamiast tego dopisujesz na dole: „Wiersz nr 45 — błędny, anulowany. Nowy wiersz nr 46 — prawidłowa kwota”. Potem przy obliczaniu sumy czytasz wszystkie wiersze, uwzględniając anulowania.

CollapsingMergeTree to silnik ClickHouse, który automatycznie zwija pary „+1” i „-1” podczas tła (merge). Robi to jak ten księgowy: widzi parę anulującą i wyrzuca oba wiersze.

2. Zasada kolumny sign — matematyka anulowania

Główna idea CollapsingMergeTree — specjalna kolumna sign (znak) z dwiema możliwymi wartościami:

• +1 — to „dodaj” (aktualna wersja)
• -1 — to „anuluj” (nieaktualna wersja)

Podczas scalania (merge) fragmentów danych ClickHouse szuka par wierszy z tym samym kluczem sortowania (ORDER BY), które mają sign = +1 i sign = -1. Gdy taka para zostanie znaleziona — oba wiersze są usuwane. Pozostają tylko wiersze bez pary — czyli te, które nie mają anulowania.

Dlaczego to działa: Każda zmiana danych jest reprezentowana jako anulowanie starej wersji i dodanie nowej. Para (+1, -1) w sumie daje zero. Gdy robisz SUM(amount * sign), stare wersje są zerowane, nowe pozostają.

Analogia z podwójną księgowością: W księgowości każda operacja jest zapisywana dwukrotnie: debet i kredyt. CollapsingMergeTree robi to samo — każda zmiana ma swoje przeciwieństwo. Przy podsumowywaniu wzajemnie się znoszą.

3. CREATE TABLE — rozkładamy na czynniki pierwsze

-- Tworzymy tabelę dla salda gracza z historią zmian
CREATE TABLE player_balance
(
    user_id      UInt64,              -- ID gracza
    date         Date,                -- Data zmiany salda
    amount       Int64,               -- Zmiana salda (+100, -50 itp.)
    balance_after Int64,              -- Saldo po operacji (opcjonalnie)
    sign         Int8,                -- +1 — dodaj, -1 — anuluj
    updated_at   DateTime DEFAULT now()  -- Czas operacji
)
ENGINE = CollapsingMergeTree(sign)    -- Silnik zwijania, wskazujemy kolumnę sign
ORDER BY (user_id, date)              -- Klucz do grupowania i zwijania

Co jest tutaj ważne:

• ENGINE = CollapsingMergeTree(sign) — jedyny obowiązkowy parametr to nazwa kolumny ze znakiem (zwykle sign lub is_active). Ta kolumna musi być typu Int8 (liczba całkowita od -128 do 127), ale w praktyce używa się tylko +1 i -1.

• ORDER BY (user_id, date) — kolumny w tym kluczu określają, które wiersze są uważane za „parę”. Dwa wiersze z tymi samymi wartościami wszystkich kolumn z ORDER BY i przeciwnymi sign (+1 i -1) zostaną zwinięte.

Co się stanie, jeśli ORDER BY nie obejmuje wszystkich potrzebnych pól? Na przykład, jeśli nie uwzględnisz user_id, mogą zostać zwinięte wiersze różnych użytkowników — katastrofa. Wszystkie pola, według których chcesz rozróżniać operacje, muszą być w ORDER BY.

Dlaczego nie PRIMARY KEY? Te same powody, co w przypadku innych silników MergeTree — ORDER BY zarządza fizycznym porządkiem i scalaniem, a PRIMARY KEY (jeśli podany) tylko indeksem.

4. Wstawianie — jak poprawnie zaktualizować dane

Zakładając, że gracz ma saldo 1000 rubli. Stawia zakład 100 rubli. Zamiast zmieniać saldo w jednym wierszu, wykonujesz dwa wstawienia:

-- Krok 1: Anulujemy starą wersję salda (było 1000, teraz potrzebujemy 900)
-- Stara wersja: user_id=123, date='2025-06-01', amount = 1000 (to saldo PRZED operacją)
-- Aby anulować, wstawiamy wiersz z sign = -1
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', 1000, 1000, -1, now());   -- Anulowanie starego salda

-- Krok 2: Dodajemy nową wersję salda (teraz 900 po zakładzie)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());   -- Nowa wersja: zmiana -100, wynik 900

Ale to niewygodne. W praktyce łatwiej myśleć w kategoriach „zmian”, a nie „pełnego salda”. Oto bardziej typowy wzorzec:

-- Gracz stawia zakład 100 rubli (saldo maleje)
-- Wstawiamy tylko jeden wiersz z sign = +1, gdzie amount to zmiana salda (-100)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());

-- Jeśli trzeba anulować ten zakład (np. z powodu błędu technicznego)
-- Wstawiamy parę anulującą
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, -1, now()),   -- Anulowanie zakładu
    (123, '2025-06-01', +100, 1000, +1, now());   -- Przywrócenie salda

Dlaczego to działa: Gdy nadejdzie czas scalania, ClickHouse znajdzie parę wierszy z tymi samymi user_id, date i amount (jeśli amount jest w ORDER BY) i różnymi sign — i usunie je. Pozostanie tylko aktualne saldo.

Ważne: Musisz sam zadbać o poprawność par. ClickHouse nie sprawdza, czy suma zmian się zgadza. Po prostu zwija wiersze z przeciwnymi sign i tym samym kluczem ORDER BY.

5. SELECT z sumą sign — jak poprawnie czytać

Gdy czytasz dane, musisz agregować je z uwzględnieniem znaku. Podstawowy wzorzec:

-- Pobieramy aktualne saldo każdego gracza
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM player_balance
WHERE sign != 0   -- Odfiltrowujemy przypadkowe zera (nie powinno ich być)
GROUP BY user_id;

Analiza logiki:

• amount * sign — jeśli sign = +1, składnik = amount; jeśli sign = -1, składnik = -amount (anuluje poprzednie)
• SUM(...) — wszystkie pary anulujące wzajemnie się znoszą w sumie
• GROUP BY user_id — agregujemy po użytkowniku

Dlaczego nie trzeba FINAL? W przeciwieństwie do ReplacingMergeTree, dla CollapsingMergeTree zawsze używasz agregacji z SUM(amount * sign). Działa to poprawnie zarówno przed, jak i po scaleniach, ponieważ matematyka znaków nie zależy od tego, czy wiersze są fizycznie zwinięte, czy nie.

Przykład z życia:

-- Dane źródłowe (przed merge):
-- (123, -100, +1)   — zakład 100 rub
-- (123, -100, -1)   — anulowanie zakładu
-- (123, +100, +1)   — przywrócenie salda

-- Zapytanie: SUM(amount * sign) = (-100*1) + (-100*-1) + (100*1) = -100 + 100 + 100 = 100
-- Poprawnie: saldo wzrosło o 100 (anulowanie zakładu zwróciło pieniądze)

Jeśli chcesz zobaczyć historię bez agregacji (np. wszystkie operacje w porządku chronologicznym), po prostu SELECT * pokaże wszystkie wiersze, włącznie z anulowanymi. To normalne — tak zostało zaprojektowane.

6. Pułapki — problem kolejności wierszy

Największa pułapka: CollapsingMergeTree wymaga, aby wiersze z tym samym kluczem przychodziły w odpowiedniej kolejności — najpierw +1, potem -1 (lub odwrotnie? Zaraz się dowiemy).

ClickHouse nie sprawdza znaczników czasu. Patrzy na kolejność wierszy wewnątrz każdego fragmentu (part) podczas scalania. Jeśli w jednym fragmencie jest para (+1, -1), zostanie zwinięta. Ale jeśli +1 jest w jednym fragmencie, a -1 w innym — nie zostaną zwinięte, dopóki te fragmenty nie zostaną scalone w jeden (co może nastąpić nieprędko).

Dlaczego to problem w systemie rozproszonym?

Wyobraź sobie, że twoje dane płyną przez Kafka (kolejka komunikatów) z trzech różnych serwerów. Serwer nr 1 wysłał „zakład 100 rub” (+1). Serwer nr 2 wysłał „anulowanie zakładu” (-1). Serwer nr 3 wysłał „przywrócenie salda” (+1). Mogą trafić do różnych fragmentów ClickHouse w różnej kolejności.

Jeśli do jednego fragmentu trafią tylko +1, a -1 do innego — tymczasowo saldo będzie nieprawidłowe (suma pokaże dodatkowe pieniądze). Gdy fragmenty się scalą — dane się poprawią, ale do tego momentu może minąć godzina.

Analogia: To tak, jakbyś wkładał listy do dwóch różnych teczek. W jednej teczce — „dług 100 rubli” (+1), w drugiej — „dług umorzony” (-1). Dopóki nie scalisz teczek w jedną, twój system księgowy będzie myślał, że jesteś winien 100 rubli.

7. VersionedCollapsingMergeTree — rozwiązanie problemu kolejności

Aby pokonać problem kolejności, programiści ClickHouse dodali VersionedCollapsingMergeTree. Dodaje on trzecią kolumnę — wersję (zwykle version lub timestamp).

CREATE TABLE player_balance_versioned
(
    user_id  UInt64,
    date     Date,
    amount   Int64,
    version  UInt64,          -- Monotonicznie rosnący numer wersji
    sign     Int8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)   -- Dwa parametry!
ORDER BY (user_id, date);

Jak to działa:

• Podczas scalania ClickHouse szuka par (+1, -1) z tym samym kluczem ORDER BY i tą samą wersją.
• Jeśli wiersze mają ten sam klucz, ale różne wersje — NIE są zwijane. Zamiast tego pozostaje wiersz z najwyższą wersją (ostatni stan).
• Wersja pozwala poprawnie przetwarzać nawet jeśli wiersze przyszły w nieprawidłowej kolejności — wystarczy, że wersja anulowania jest taka sama jak oryginalnej operacji.

Dlaczego to rozwiązuje problem kolejności: Nawet jeśli +1 przyszedł później niż -1, ClickHouse zobaczy, że mają różne wersje (lub takie same — wtedy zwija). Jeśli wersje są takie same, pary zostaną zwinięte niezależnie od fizycznej kolejności we fragmentach. Jeśli wersje są różne — pozostanie nowsza.

Przykład użycia wersji:

-- Operacja 1: zakład 100 rub (wersja 1001)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, +1);

-- Anulowanie tego samego zakładu (ta sama wersja 1001, sign = -1)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, -1);

-- Nowy poprawny zakład 50 rub (wersja 1002)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -50, 1002, +1);

Teraz nawet jeśli wszystkie trzy wiersze przyjdą w różnej kolejności i do różnych fragmentów, podczas scalania pary z tymi samymi wersjami zostaną zwinięte. Pozostanie tylko zakład 50 rub z wersją 1002.

8. Rzeczywisty przypadek użycia: saldo gracza w czasie rzeczywistym w kasynie

Wyobraź sobie, że masz mikrousługę „Saldo”, która musi pokazywać aktualne saldo gracza z dokładnością do grosza i opóźnieniem nie większym niż 5 sekund.

Schemat działania:

-- Tabela dla wszystkich operacji na saldzie
CREATE TABLE balance_operations
(
    user_id      UInt64,
    operation_id String,          -- Unikalny ID operacji (zakład, wypłata, anulowanie)
    amount       Int64,           -- Zmiana (+1000 wygrana, -500 zakład)
    version      UInt64,          -- Monotoniczny numer wersji
    sign         Int8,            -- +1 = nowa operacja, -1 = anulowanie
    created_at   DateTime DEFAULT now()
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, operation_id, version);

Scenariusz 1: Gracz stawia zakład 100 rub

-- Wstawiamy jeden wiersz (sign = +1)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, +1, now());

Scenariusz 2: Gracz wygrywa 500 rub (wypłata)

INSERT INTO balance_operations VALUES (123, 'win_001', +500, 1002, +1, now());

Scenariusz 3: Administrator anuluje zakład bet_001 (gracz oszukiwał?)

-- Anulujemy stary zakład (ta sama operation_id, sign = -1, ta sama wersja 1001)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, -1, now());
-- Dodajemy operację korygującą (zwracamy 100 rub)
INSERT INTO balance_operations VALUES (123, 'admin_correction_bet_001', +100, 1003, +1, now());

Jak czytać saldo w czasie rzeczywistym:

-- Zapytanie dla dashboardu (wykonywane co 3 sekundy)
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM balance_operations
WHERE user_id = 123 AND created_at > now() - interval 1 day  -- ograniczamy czasowo
GROUP BY user_id;

Przy prawidłowym użyciu wersji to zapytanie będzie zwracać poprawne saldo nawet przy chaotycznej kolejności wstawień.

9. Porównanie podejść: CollapsingMergeTree vs ReplacingMergeTree dla salda

Wielu początkujących zadaje pytanie: „A dlaczego po prostu nie użyć ReplacingMergeTree i nie aktualizować salda jako wersji wiersza?”

Porównajmy.

Cecha	CollapsingMergeTree	ReplacingMergeTree
Jak przedstawić zmianę	Dwa wiersze: anulowanie (-1) i nowy (+1)	Jeden nowy wiersz z wyższą wersją
Czy trzeba przechowywać całą historię	Tak, dopóki nie zostanie zwinięta	Tak, dopóki nie zostanie scalona
Podejście do odczytu	SUM(amount * sign)	argMax(amount, version) lub FINAL
Złożoność wstawiania	Wyższa (trzeba myśleć o parze)	Niższa (po prostu nowa wersja)
Złożoność odczytu	Niższa (agregacja prosta)	Wyższa (FINAL wolny lub argMax)
Ryzyko błędu	Pary się nie zgadzają (błąd w logice)	Wersja nie jest monotoniczna (błąd klienta)

Kiedy wybrać CollapsingMergeTree:

• Potrzebujesz często zmieniać te same klucze (np. saldo gracza zmienia się 100 razy na godzinę).
• Chcesz używać prostej agregacji SUM(amount * sign) i nie chcesz polegać na FINAL.
• Masz kontrolowaną kolejność wstawień lub używasz VersionedCollapsingMergeTree.
• Potrzebujesz wycofywać operacje (anulowanie zakładu) — w ReplacingMergeTree wymagałoby to wstawienia nowego wiersza z wyższą wersją, co nie odzwierciedla jawnie „anulowania”.

Kiedy lepszy jest ReplacingMergeTree:

• Masz rzadkie aktualizacje (np. status zamówienia: utworzone → opłacone → dostarczone).
• Przechowujesz niezmienne agregaty liczbowe, ale zmienne atrybuty.
• Potrzebujesz widzieć historię wersji każdego rekordu.

Przykład dla salda — który lepszy? Dla obciążonego konta (tysiące zakładów na sekundę) lepszy jest VersionedCollapsingMergeTree. Zapewnia przewidywalną wydajność i poprawne działanie przy chaotycznej kolejności.

10. Wydajność i kiedy jest to lepsze niż UPDATE w PostgreSQL

Wydajność CollapsingMergeTree

• Wstawianie: Bardzo szybkie (zwykły INSERT, brak blokad). Płacisz za przechowywanie dwóch wierszy zamiast aktualizacji jednego — ale w bazie kolumnowej nie jest to tak straszne.
• Odczyt z agregacją: ClickHouse czyta tylko kolumny amount i sign (przechowywanie kolumnowe!), wykonuje szybkie obliczenia wektorowe. Dla miliarda wierszy — ułamki sekund.
• Scalanie (merge): Praca w tle. Nie wpływa na wstawianie.

Porównanie z PostgreSQL

W PostgreSQL aktualizacja salda:

-- Atomowa aktualizacja z blokadą wiersza
UPDATE players SET balance = balance - 100 WHERE user_id = 123;

Zalety: prostota, gwarancje ACID (atomowość, spójność, izolacja, trwałość), natychmiastowa spójność.

Wady: Przy 10 000 aktualizacji na sekundę — blokady (locki na wierszach), WAL (dziennik przedzapisu), próżnia. Uderzasz w IO.

W ClickHouse z CollapsingMergeTree:

Zalety: 100 000+ wstawień na sekundę na jednym serwerze, kompresja danych (10:1), brak blokad, liniowe skalowanie.

Wady: Brak natychmiastowej spójności (do scalania trzeba używać agregacji), bardziej skomplikowana logika (sign, version), ostateczna spójność (eventual consistency) — system dojdzie do poprawnego stanu, ale nie natychmiast.

Kiedy CollapsingMergeTree jest lepszy od PostgreSQL:

• Potrzebujesz bardzo dużo aktualizacji (tysiące-dziesiątki tysięcy na sekundę).
• Opóźnienie kilku sekund (na zwinięcie) jest dopuszczalne.
• I tak używasz ClickHouse do analityki.

Kiedy PostgreSQL wciąż jest lepszy:

• Potrzebujesz ścisłej natychmiastowej spójności (przelew bankowy między kontami).
• Aktualizacji jest niewiele (<1000 na sekundę).
• Nie chcesz komplikować architektury.

Co dalej

Teraz znasz CollapsingMergeTree i jego starszego brata VersionedCollapsingMergeTree. Kolejne tematy do nauki:

• Jak wybierać między CollapsingMergeTree a ReplacingMergeTree — lista kontrolna dla każdego zadania.
• Optymalizacja scalania — ustawienia merge_with_ttl_timeout, aby pary zwijały się szybciej.
• Wzorzec „widok zmaterializowany + CollapsingMergeTree” — dla wielopoziomowych agregatów.

Podsumowanie: CollapsingMergeTree to potężne, ale wymagające dyscypliny narzędzie. Nie wybacza błędów w kolejności wstawień i poprawności par. Ale jeśli skonfigurujesz go poprawnie (zwłaszcza z wersjami), zapewni wydajność niedostępną dla tradycyjnych baz danych. Pamiętaj o głównej zasadzie: zawsze sprawdzaj swoje zapytania przez SUM(amount * sign) i używaj VersionedCollapsingMergeTree w systemach rozproszonych.

---

← Poprzedni: SummingMergeTree i AggregatingMergeTree: inkrementalna agregacja bez bólu
→ Następny: Partycyjonowanie w ClickHouse: Jak zarządzać danymi na poziomie folderów

— Editorial Team