CollapsingMergeTree v ClickHouse: aktualizace bez UPDATE

CollapsingMergeTree: Jak aktualizovat agregáty bez UPDATE v ClickHouse

1. Proč je CollapsingMergeTree potřeba – problém aktualizace agregátů

Vraťme se k našemu online casinu. Každý hráč má zůstatek. Když hráč vsadí – zůstatek se sníží. Když vyhraje – zvýší se. Když administrátor zruší podezřelou transakci – zůstatek se změní znovu.

V běžné databázi (PostgreSQL) bys jednoduše provedl UPDATE players SET balance = balance - 100 WHERE user_id = 123. Jednoduché a přehledné.

Ale ClickHouse neumí aktualizovat data. Vůbec. Proč? Protože ClickHouse je vytvořen pro analytiku, kde se data pouze přidávají (append-only). Aktualizace jsou bolestivé pro sloupcová úložiště, kde data leží v komprimovaných blocích. Ke změně jedné buňky by se musely přepsat celé bloky.

Jak tedy měnit zůstatek? Neměníš starý záznam. Přidáš NOVÝ záznam, který říká: „zruš předchozí změnu“ a „přidej novou“. Tomu se říká materializace změn pomocí zrušení.

Analogii ze života: Představ si účetní knihu, kde jsou všechny zápisy již napsány inkoustem. Nelze je vymazat a opravit. Místo toho připíšeš dole: „Řádek č. 45 – chybný, ruší se. Nový řádek č. 46 – správná částka“. Potom při výpočtu součtu přečteš všechny řádky s ohledem na zrušení.

CollapsingMergeTree je engine ClickHouse, který automaticky sráží páry „+1“ a „-1“ při slučování na pozadí (merge). Dělá to jako ten účetní: vidí rušící pár a vyhodí oba řádky.

2. Princip sloupce sign – matematika zrušení

Hlavní myšlenka CollapsingMergeTree – speciální sloupec sign (znaménko) se dvěma možnými hodnotami:

• +1 – „přidat“ (aktuální verze)
• -1 – „zrušit“ (zastaralá verze)

Při slučování (merge) kusů dat ClickHouse hledá páry řádků se stejným klíčem řazení (ORDER BY), které mají sign = +1 a sign = -1. Když takový pár najde – oba řádky se odstraní. Zůstanou pouze řádky bez páru – tedy ty, které nemají zrušení.

Proč to funguje: Jakákoli změna dat je reprezentována jako zrušení staré verze a přidání nové. Pár (+1, -1) v součtu dává nulu. Když uděláš SUM(amount * sign), staré verze se vynulují, nové zůstanou.

Analogii s podvojným účetnictvím: V účetnictví se každá operace zapisuje dvakrát: na vrub a ve prospěch. CollapsingMergeTree dělá totéž – každá změna má svůj protějšek. Při sestavování souhrnů se vzájemně zruší.

3. CREATE TABLE – rozebíráme na atomy

-- Vytvoříme tabulku pro zůstatek hráče s historií změn
CREATE TABLE player_balance
(
    user_id      UInt64,              -- ID hráče
    date         Date,                -- Datum změny zůstatku
    amount       Int64,               -- Změna zůstatku (+100, -50 atd.)
    balance_after Int64,              -- Zůstatek po operaci (volitelné)
    sign         Int8,                -- +1 – přidat, -1 – zrušit
    updated_at   DateTime DEFAULT now()  -- Čas operace
)
ENGINE = CollapsingMergeTree(sign)    -- Engine srážení, uvádíme sloupec sign
ORDER BY (user_id, date)              -- Klíč pro seskupení a srážení

Co je zde důležité:

• ENGINE = CollapsingMergeTree(sign) – jediný povinný parametr je název sloupce se znaménkem (obvykle sign nebo is_active). Tento sloupec musí být typu Int8 (celé číslo od -128 do 127), ale reálně se používají pouze +1 a -1.

• ORDER BY (user_id, date) – sloupce v tomto klíči určují, které řádky jsou považovány za „pár“. Dva řádky se stejnými hodnotami všech sloupců z ORDER BY a opačnými sign (+1 a -1) budou sraženy.

Co se stane, pokud ORDER BY nezahrnuje všechna potřebná pole? Například pokud nezahrneš user_id, mohou se srazit řádky různých uživatelů – katastrofa. Všechna pole, podle kterých chceš operace rozlišovat, musí být v ORDER BY.

Proč ne PRIMARY KEY? Stejné důvody jako u jiných engine MergeTree – ORDER BY řídí fyzické pořadí a slučování, zatímco PRIMARY KEY (je-li uveden) pouze index.

4. Vkládání – jak správně aktualizovat data

Dejme tomu, že hráč má zůstatek 1000 Kč. Vsadí 100 Kč. Místo změny zůstatku v jednom řádku provedeš dva vklady:

-- Krok 1: Zrušíme starou verzi zůstatku (bylo 1000, nyní má být 900)
-- Stará verze: user_id=123, date='2025-06-01', amount = 1000 (to je zůstatek PŘED operací)
-- Pro zrušení vložíme řádek s sign = -1
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', 1000, 1000, -1, now());   -- Zrušení starého zůstatku

-- Krok 2: Přidáme novou verzi zůstatku (po sázce je 900)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());   -- Nová verze: změna -100, výsledek 900

Ale to je nepohodlné. V praxi je jednodušší uvažovat v pojmech „změna“, nikoli „celkový zůstatek“. Zde je typičtější vzor:

-- Hráč vsadí 100 Kč (zůstatek se snižuje)
-- Vložíme pouze jeden řádek s sign = +1, kde amount je změna zůstatku (-100)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());

-- Pokud je třeba tuto sázku zrušit (např. kvůli technické chybě)
-- Vložíme rušící pár
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, -1, now()),   -- Zrušení sázky
    (123, '2025-06-01', +100, 1000, +1, now());   -- Obnovení zůstatku

Proč to funguje: Až přijde čas sloučení, ClickHouse najde pár řádků se stejnými user_id, date a amount (pokud je amount v ORDER BY) a různými sign – a odstraní je. Zůstane pouze aktuální zůstatek.

Důležité: O správnost párů se musíš postarat sám. ClickHouse nekontroluje, zda součet změn sedí. Pouze sráží řádky s opačnými sign a stejným klíčem ORDER BY.

5. SELECT se součtem sign – jak správně číst

Když čteš data, musíš je agregovat s ohledem na znaménko. Základní vzor:

-- Získáme aktuální zůstatek každého hráče
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM player_balance
WHERE sign != 0   -- Odstraníme náhodné nuly (neměly by být)
GROUP BY user_id;

Rozebíráme logiku:

• amount * sign – pokud sign = +1, sčítanec = amount; pokud sign = -1, sčítanec = -amount (ruší předchozí)
• SUM(...) – všechny rušící páry se v součtu vzájemně zruší
• GROUP BY user_id – agregujeme podle uživatele

Proč není potřeba FINAL? Na rozdíl od ReplacingMergeTree pro CollapsingMergeTree** vždy** používáš agregaci s SUM(amount * sign)`. To funguje správně jak před sloučením, tak po něm, protože matematika znamének nezávisí na tom, zda jsou řádky fyzicky sraženy.

Příklad ze života:

-- Výchozí data (před merge):
-- (123, -100, +1)   – sázka 100 Kč
-- (123, -100, -1)   – zrušení sázky
-- (123, +100, +1)   – obnovení zůstatku

-- Dotaz: SUM(amount * sign) = (-100*1) + (-100*-1) + (100*1) = -100 + 100 + 100 = 100
-- Správně: zůstatek se zvýšil o 100 (zrušení sázky vrátilo peníze)

Pokud chceš vidět historii bez agregace (např. všechny operace v chronologickém pořadí), prostý SELECT * zobrazí všechny řádky včetně zrušených. To je normální – tak je to zamýšleno.

6. Úskalí – problém pořadí řádků

Největší past: CollapsingMergeTree vyžaduje, aby řádky se stejným klíčem přicházely ve správném pořadí – nejprve +1, poté -1 (nebo naopak? Teď si to ujasníme).

ClickHouse nekontroluje časová razítka. Dívá se na pořadí řádků v rámci každého kusu (part) při slučování. Pokud je v jednom kusu pár (+1, -1), srazí ho. Ale pokud je +1 v jednom kusu a -1 v jiném – nesrazí se, dokud se tyto kusy nesloučí do jednoho (což může trvat dlouho).

Proč je to problém v distribuovaném systému?

Představ si, že tvá data jdou přes Kafka (fronta zpráv) ze tří různých serverů. Server č. 1 odeslal „sázka 100 Kč“ (+1). Server č. 2 odeslal „zrušení sázky“ (-1). Server č. 3 odeslal „obnovení zůstatku“ (+1). Mohou se dostat do různých kusů ClickHouse v různém pořadí.

Pokud se do jednoho kusu dostanou pouze +1 a -1 do jiného – dočasně bude zůstatek nesprávný (součet ukáže peníze navíc). Až se kusy sloučí – data se opraví, ale do té doby může uplynout hodina.

Analogii: Je to, jako bys ukládal dopisy do dvou různých složek. V jedné složce – „dluh 100 Kč“ (+1), v druhé – „dluh odpuštěn“ (-1). Dokud složky nesloučíš do jedné, tvůj účetní systém si bude myslet, že ti dluží 100 Kč.

7. VersionedCollapsingMergeTree – řešení problému pořadí

Aby překonali problém pořadí, přidali vývojáři ClickHouse VersionedCollapsingMergeTree. Ten přidává třetí sloupec – verzi (obvykle version nebo timestamp).

CREATE TABLE player_balance_versioned
(
    user_id  UInt64,
    date     Date,
    amount   Int64,
    version  UInt64,          -- Monotónně rostoucí číslo verze
    sign     Int8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)   -- Dva parametry!
ORDER BY (user_id, date);

Jak to funguje:

• Při slučování ClickHouse hledá páry (+1, -1) se stejným klíčem ORDER BY a stejnou verzí.
• Pokud mají řádky stejný klíč, ale různé verze – NESRAZÍ se. Místo toho zůstane řádek s nejvyšší verzí (poslední stav).
• Verze umožňuje správně zpracovat i případy, kdy řádky přišly v nesprávném pořadí – stačí, aby verze u zrušení byla stejná jako u původní operace.

Proč to řeší problém pořadí: I když +1 přijde později než -1, ClickHouse uvidí, že mají různé verze (nebo stejné – pak je srazí). Pokud jsou verze stejné, páry se srazí bez ohledu na fyzické pořadí v kusech. Pokud jsou verze různé – zůstane novější.

Příklad použití verzí:

-- Operace 1: sázka 100 Kč (verze 1001)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, +1);

-- Zrušení téže sázky (stejná verze 1001, sign = -1)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, -1);

-- Nová správná sázka 50 Kč (verze 1002)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -50, 1002, +1);

Nyní, i když všechny tři řádky přijdou v různém pořadí a do různých kusů, při sloučení se páry se stejnými verzemi srazí. Zůstane pouze sázka 50 Kč s verzí 1002.

8. Reálný use case: real-time zůstatek hráče v casinu

Představ si, že máš mikroslužbu „Zůstatek“, která musí zobrazovat aktuální zůstatek hráče s přesností na haléř a zpožděním maximálně 5 sekund.

Schéma práce:

-- Tabulka pro všechny operace se zůstatkem
CREATE TABLE balance_operations
(
    user_id      UInt64,
    operation_id String,          -- Unikátní ID operace (sázka, výplata, zrušení)
    amount       Int64,           -- Změna (+1000 výhra, -500 sázka)
    version      UInt64,          -- Monotónní číslo verze
    sign         Int8,            -- +1 = nová operace, -1 = zrušení
    created_at   DateTime DEFAULT now()
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, operation_id, version);

Scénář 1: Hráč vsadí 100 Kč

-- Vložíme jeden řádek (sign = +1)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, +1, now());

Scénář 2: Hráč vyhraje 500 Kč (výplata)

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

Scénář 3: Administrátor zruší sázku bet_001 (hráč podváděl?)

-- Zrušíme starou sázku (stejné operation_id, sign = -1, stejná verze 1001)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, -1, now());
-- Přidáme opravnou operaci (vracíme 100 Kč)
INSERT INTO balance_operations VALUES (123, 'admin_correction_bet_001', +100, 1003, +1, now());

Jak číst zůstatek v reálném čase:

-- Dotaz pro dashboard (provádí se každé 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  -- omezíme časově
GROUP BY user_id;

Při správném použití verzí bude tento dotaz vracet správný zůstatek i při chaotickém pořadí vkládání.

9. Srovnání přístupů: CollapsingMergeTree vs ReplacingMergeTree pro zůstatek

Mnoho začátečníků si klade otázku: „Proč prostě nepoužít ReplacingMergeTree a neaktualizovat zůstatek jako verzi řádku?“

Pojďme to porovnat.

Vlastnost	CollapsingMergeTree	ReplacingMergeTree
Jak reprezentovat změnu	Dva řádky: zrušení (-1) a nový (+1)	Jeden nový řádek s vyšší verzí
Je třeba uchovávat celou historii	Ano, dokud se nesrazí	Ano, dokud se nesloučí
Přístup ke čtení	SUM(amount * sign)	argMax(amount, version) nebo FINAL
Složitost vkládání	Vyšší (je třeba myslet na pár)	Nižší (prostě nová verze)
Složitost čtení	Nižší (agregace je jednoduchá)	Vyšší (FINAL pomalý nebo argMax)
Riziko chyby	Páry nesedí (chyba v logice)	Verze není monotónní (chyba klienta)

Kdy zvolit CollapsingMergeTree:

• Potřebuješ často měnit stejné klíče (např. zůstatek hráče se mění 100krát za hodinu).
• Chceš používat jednoduchou agregaci SUM(amount * sign) a nechceš být závislý na FINAL.
• Máš kontrolované pořadí vkládání nebo používáš VersionedCollapsingMergeTree.
• Potřebuješ vracet operace (zrušení sázky) – v ReplacingMergeTree by to vyžadovalo vložení nového řádku se zvýšenou verzí, což explicitně neodráží „zrušení“.

Kdy je lepší ReplacingMergeTree:

• Máš vzácné aktualizace (např. stav objednávky: vytvořeno → zaplaceno → doručeno).
• Ukládáš neměnné číselné agregáty, ale proměnlivé (mutable) atributy.
• Potřebuješ vidět historii verzí každého záznamu.

Příklad pro zůstatek – který je lepší? Pro vysoce zatížený účet (tisíce sázek za sekundu) je lepší VersionedCollapsingMergeTree. Poskytuje předvídatelný výkon a správnou funkci při chaotickém pořadí.

10. Výkon a kdy je to lepší než UPDATE v PostgreSQL

Výkon CollapsingMergeTree

• Vkládání: Velmi rychlé (běžný INSERT, žádné zámky). Platíš za uložení dvou řádků místo aktualizace jednoho – ale ve sloupcové databázi to není tak hrozné.
• Čtení s agregací: ClickHouse čte pouze sloupce amount a sign (sloupcové uložení!), provádí rychlý vektorový výpočet. Pro miliardu řádků – zlomky sekundy.
• Slučování (merge): Práce na pozadí. Neovlivňuje vkládání.

Srovnání s PostgreSQL

V PostgreSQL aktualizace zůstatku:

-- Atomická aktualizace se zámkem řádku
UPDATE players SET balance = balance - 100 WHERE user_id = 123;

Výhody: jednoduché, záruky ACID (atomicita, konzistence, izolace, trvanlivost), okamžitá konzistence.

Nevýhody: Při 10 000 aktualizacích za sekundu – zámky (řádkové zámky), WAL (write-ahead log), vakuum. Narazíš na IO.

V ClickHouse s CollapsingMergeTree:

Výhody: 100 000+ vkládání za sekundu na jednom serveru, komprese dat (10:1), žádné zámky, lineární škálování.

Nevýhody: Žádná okamžitá konzistence (do sloučení je třeba použít agregaci), složitější logika (sign, version), eventuální konzistence (systém dospěje ke správnému stavu, ale ne okamžitě).

Kdy je CollapsingMergeTree lepší než PostgreSQL:

• Potřebuješ velmi mnoho aktualizací (tisíce až desetitisíce za sekundu).
• Zpoždění několika sekund (na sražení) je přijatelné.
• Stejně používáš ClickHouse pro analytiku.

Kdy je PostgreSQL stále lepší:

• Potřebuješ přísnou okamžitou konzistenci (bankovní převod mezi účty).
• Aktualizací je málo (<1000 za sekundu).
• Nechceš komplikovat architekturu.

Co dál

Nyní znáš CollapsingMergeTree a jeho staršího bratra VersionedCollapsingMergeTree. Další témata ke studiu:

• Jak vybírat mezi CollapsingMergeTree a ReplacingMergeTree – checklist pro každý úkol.
• Optimalizace slučování – nastavení merge_with_ttl_timeout, aby se páry srážely rychleji.
• Vzor „materializovaný pohled + CollapsingMergeTree“ – pro víceúrovňové agregáty.

Shrnutí: CollapsingMergeTree je mocný, ale vyžaduje disciplínu. Neodpouští chyby v pořadí vkládání a správnosti párů. Pokud ho ale nastavíš správně (zejména s verzemi), poskytne výkon, který je tradičním databázím nedostupný. Pamatuj na hlavní pravidlo: vždy kontroluj své dotazy pomocí SUM(amount * sign) a používej VersionedCollapsingMergeTree v distribuovaných systémech.

---

← Předchozí: SummingMergeTree a AggregatingMergeTree: inkrementální agregace bez bolesti
→ Další: Partitionování v ClickHouse: Jak spravovat data na úrovni složek

— Editorial Team