TTL w ClickHouse: zarządzanie cyklem życia danych

TTL w ClickHouse: Automatyczne zarządzanie cyklem życia danych

1. Po co TTL — problem „zapomniałem usunąć”

Wróćmy do naszego kasyna online. Przechowujesz wszystkie zakłady graczy. Po miesiącu tabela waży 500 GB. Po roku — 5 TB. Dyski się zapychają, zapytania zwalniają, a stare dane są potrzebne coraz rzadziej. Właściciel biznesu mówi: „Gracze patrzą tylko na ostatnie 30 dni zakładów, a do raportów potrzebny jest tylko zagregowany wynik za stare okresy”.

Możesz napisać skrypt, który raz dziennie będzie usuwał stare partycje (jak uczyliśmy w artykule o partycjonowaniu). Ale to wymaga zewnętrznego harmonogramu (cron), osobnego skryptu, monitorowania jego działania, obsługi błędów.

TTL (Time-To-Live — „czas życia”) rozwiązuje ten problem na poziomie bazy danych. To wbudowany mechanizm ClickHouse, który automatycznie:

• usuwa stare wiersze,
• przenosi je na tańsze dyski (HDD, S3),
• agreguje stare dane (zwija szczegóły w podsumowania),
• anonimizuje dane osobowe (RODO).

Wszystko to dzieje się w tle, bez Twojego udziału, zgodnie z harmonogramem, który ustawiasz w SQL.

Analogia z życia: TTL to jak wynajem powierzchni magazynowej. Umawiasz się z właścicielem: „Towary, które leżą dłużej niż 30 dni, przenieś do dalekiej, taniej strefy. Jeśli leżą dłużej niż rok — wyrzuć”. Właściciel sam pilnuje terminów i wykonuje pracę, nie musisz za każdym razem przypominać.

2. TTL na poziomie wierszy — usuwanie starych rekordów

Najprostszy wariant: wiersze żyją określony czas, potem są usuwane.

CREATE TABLE z TTL

-- Tworzymy tabelę zakładów, gdzie wiersze żyją 90 dni
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 90 DAY;   -- Po 90 dniach od created_at wiersz jest usuwany

Co się tutaj dzieje:

• TTL created_at + INTERVAL 90 DAY — dla każdego wiersza obliczana jest data wygaśnięcia: created_at + 90 dni. Gdy bieżąca data (today()) staje się większa od tej daty, wiersz jest oznaczany do usunięcia.
• Proces w tle (zwykle raz dziennie) przechodzi przez granulki i usuwa wiersze, których TTL wygasł.
• Usuwanie odbywa się na poziomie części (parts) — ClickHouse przepisuje część bez usuniętych wierszy.

ALTER TABLE — dodanie lub zmiana TTL

Główna zaleta: TTL można dodać do istniejącej tabeli bez jej odtwarzania.

-- Dodajemy TTL do istniejącej tabeli
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 90 DAY;

-- Zmieniamy czas życia z 90 na 180 dni
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 180 DAY;

-- Usuwamy TTL (dane będą przechowywane wiecznie)
ALTER TABLE bets REMOVE TTL;

Dlaczego to ważne? Ponieważ w rzeczywistości wymagania dotyczące przechowywania się zmieniają. Najpierw myślałeś, że trzeba przechowywać wszystko zawsze. Potem okazało się, że kopie zapasowe zajmują miejsce, a analitycy nie potrzebują starych danych. Dzięki MODIFY TTL zmieniasz regułę jednym wierszem.

Co się stanie, jeśli dodasz TTL do tabeli z 10 miliardami wierszy? Nic strasznego. ClickHouse nie przepisze danych natychmiast. Po prostu zacznie stosować TTL w tle, stopniowo. Przy następnym scaleniu części stare wiersze zostaną pominięte.

3. TTL z przenoszeniem na inny dysk (tiered storage)

Czasami szkoda usuwać dane, ale przechowywanie ich na szybkich, drogich SSD jest kosztowne. Rozwiązanie: przenoszenie starych danych na wolne, tanie HDD (lub do chmury S3).

Najpierw konfigurujemy dyski w pliku konfiguracyjnym ClickHouse (config.xml):

<storage_configuration>
    <disks>
        <ssd>
            <path>/mnt/ssd/clickhouse/</path>
        </ssd>
        <hdd>
            <path>/mnt/hdd/clickhouse/</path>
        </hdd>
    </disks>
    <policies>
        <hot_to_cold>
            <volumes>
                <hot>
                    <disk>ssd</disk>
                </hot>
                <cold>
                    <disk>hdd</disk>
                </cold>
            </volumes>
        </hot_to_cold>
    </policies>
</storage_configuration>

Teraz tworzymy tabelę z TTL przenoszącym:

CREATE TABLE bets_tiered
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY TO DISK 'hdd';   -- Po 30 dniach przenosi się na HDD

Co się stanie: Wiersze żyją na szybkim SSD przez pierwsze 30 dni (kiedy są najczęściej potrzebne w raportach). Po 30 dniach ClickHouse w tle przenosi części danych (parts) na HDD. Podczas zapytania o dane za stare okresy będą one dostępne, ale nieco wolniej.

Można łączyć — najpierw przenieść, potem usunąć:

-- 30 dni na SSD, potem na HDD do 90 dni, potem usunąć
CREATE TABLE bets_multi_ttl
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
ORDER BY user_id
TTL
    created_at + INTERVAL 30 DAY TO DISK 'hdd',
    created_at + INTERVAL 90 DAY DELETE;

Analogia: Hotel: pierwsze 30 dni mieszkasz w apartamencie (szybko, drogo). Potem przenoszą cię do standardowego pokoju (wolniej, taniej). A po 90 dniach eksmitują. Wszystko automatycznie.

4. TTL z przenoszeniem na S3

ClickHouse potrafi współpracować z magazynami w chmurze (Amazon S3, MinIO, Google Cloud Storage). Możesz skonfigurować storage_policy z S3 i przenosić stare dane do chmury, gdzie przechowywanie kosztuje grosze.

Konfiguracja (uproszczona):

<storage_configuration>
    <disks>
        <s3>
            <type>s3</type>
            <endpoint>https://s3.amazonaws.com/mybucket/clickhouse/</endpoint>
            <access_key_id>AKIAIOSFODNN7EXAMPLE</access_key_id>
            <secret_access_key>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</secret_access_key>
        </s3>
    </disks>
    <policies>
        <s3_policy>
            <volumes>
                <hot>
                    <disk>default</disk>   <!-- lokalny SSD -->
                </hot>
                <cold_s3>
                    <disk>s3</disk>        <!-- chmura -->
                </cold_s3>
            </volumes>
        </s3_policy>
    </policies>
</storage_configuration>

Tabela z TTL do S3:

CREATE TABLE bets_s3
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
ORDER BY created_at
TTL created_at + INTERVAL 90 DAY TO VOLUME 'cold_s3';   -- po 90 dniach do S3

Dlaczego to fajne: Płacisz za S3 grosze za gigabajt miesięcznie. Dane pozostają dostępne do analizy (choć wolniej niż z lokalnego dysku). Nie musisz martwić się o brak miejsca na serwerze — S3 jest nieskończone.

5. TTL dla agregacji (najpotężniejsza funkcja)

To mój ulubiony wzorzec. Zamiast usuwać stare szczegółowe dane, zwijasz je w agregaty. Na przykład zakłady starsze niż 7 dni nie są potrzebne w rozbiciu na każdą sekundę, ale potrzebne są podsumowania dzienne i według użytkowników.

-- Tabela ze szczegółowymi zakładami
CREATE TABLE bets_detailed
(
    user_id     UInt64,
    bet_id      String,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 7 DAY
    GROUP BY toDate(created_at) AS day, user_id
    SET total_bets = sum(amount),           -- suma wszystkich zakładów za dzień
        bet_count = count()                 -- liczba zakładów za dzień
    DELETE WHERE day < now() - INTERVAL 90 DAY;   -- po 90 dniach usuwamy również agregaty

Analiza krok po kroku:

• TTL created_at + INTERVAL 7 DAY — po 7 dniach od utworzenia wiersza (szczegółowego zakładu) przestaje on być szczegółowy.
• GROUP BY toDate(created_at) AS day, user_id — wiersze są grupowane według dnia i użytkownika. Zamiast tysięcy szczegółowych zakładów dziennie dla jednego użytkownika będzie jeden wiersz-agregat.
• SET total_bets = sum(amount), bet_count = count() — w nowym zagregowanym wierszu pola są wypełniane funkcjami agregującymi.
• DELETE WHERE day < now() - INTERVAL 90 DAY — agregaty starsze niż 90 dni są ostatecznie usuwane.

Co się stanie w praktyce:

1. Dzień 0–7: Dane leżą w szczegółowej postaci. Możesz analizować każdy zakład.
2. Dzień 7–90: Szczegółowe zakłady są zwijane do wiersza na (user_id, day) z polami total_bets i bet_count. Miejsca jest 10–100 razy mniej.
3. Dzień 90+: Nawet agregaty są usuwane, pozostają tylko kopie zapasowe.

Analogia: Prowadzisz dziennik z wpisami co godzinę. Po tygodniu przepisujesz wpisy godzinowe na podsumowania dzienne (sumarycznie). A po trzech miesiącach wyrzucasz nawet dzienne podsumowania, zostawiając tylko miesięczne raporty.

6. TTL dla konkretnych kolumn (anonimizacja RODO)

Zgodnie z prawem (RODO w Europie, dane osobowe) jesteś zobowiązany przechowywać dane osobowe (e-mail, adres IP) nie dłużej niż określony czas. Natomiast anonimowe dane analityczne (suma zakładów, liczba gier) można przechowywać wiecznie.

TTL można zastosować nie do całego wiersza, ale do poszczególnych kolumn.

-- Tabela z danymi osobowymi
CREATE TABLE user_events
(
    user_id     UInt64,
    email       String,           -- kolumna osobowa
    ip_address  String,           -- kolumna osobowa
    event_type  String,
    event_value UInt64,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    email + INTERVAL 1 YEAR,      -- po roku e-mail jest zerowany
    ip_address + INTERVAL 1 YEAR, -- po roku IP jest zerowane
    created_at + INTERVAL 10 YEAR; -- po 10 latach usuwany jest cały wiersz

Co się stanie: Rok po utworzeniu wiersza kolumny email i ip_address zostaną zastąpione wartościami domyślnymi (pusty ciąg dla String, 0 dla liczb). Dane pozostaną przydatne do analizy (wiesz, że był jakiś użytkownik, ale nie wiesz kto dokładnie). Po 10 latach usunięty zostanie cały wiersz.

Dlaczego to ważne dla RODO: Spełniasz wymóg prawa automatycznie, bez ręcznych skryptów. Audytor może przyjść, spojrzeć na Twoje reguły TTL i upewnić się, że dane osobowe nie są przechowywane dłużej niż dozwolony okres.

7. Konfiguracja procesu tła TTL

TTL nie działa natychmiast po wygaśnięciu terminu. ClickHouse uruchamia proces w tle, który:

• sprawdza granulki,
• stosuje reguły TTL,
• przepisuje części bez przestarzałych danych lub ze zmienionymi kolumnami.

Parametry konfiguracji (w config.xml lub przez SET):

-- Interwał między uruchomieniami procesu TTL (domyślnie 1 dzień)
ALTER SYSTEM MODIFY SETTING merge_with_ttl_timeout = 86400;  -- w sekundach

-- Do testów można ustawić częściej
SET merge_with_ttl_timeout = 3600;   -- co godzinę

Dlaczego nie należy ustawiać TTL zbyt często? Ponieważ zastosowanie TTL to operacja przepisywania części danych, obciąża CPU i dyski. Raz dziennie — normalnie. Co godzinę — może już przeszkadzać, jeśli masz terabajty danych.

Jak sprawdzić, czy TTL działa:

-- Sprawdzamy, dla których części TTL jest aktywne
SELECT 
    partition,
    name,
    rows,
    modification_time,
    has_ttl_info   -- 1 = do tej części zastosowano TTL
FROM system.parts
WHERE table = 'bets' AND active = 1;

8. MATERIALIZE TTL — wymuszone zastosowanie

Czasami potrzebujesz, aby TTL zastosował się natychmiast, a nie czekał na proces w tle. Na przykład:

• Właśnie dodałeś TTL do ogromnej tabeli i chcesz od razu wyczyścić stare dane.
• Testujesz reguły TTL i nie chcesz czekać doby.

-- Wymuszamy zastosowanie TTL dla całej tabeli
ALTER TABLE bets MATERIALIZE TTL;

-- Tylko dla konkretnej partycji (szybciej)
ALTER TABLE bets MATERIALIZE TTL IN PARTITION '202501';

Co się stanie: ClickHouse przeskanuje wszystkie części tabeli (lub partycji) i natychmiast zastosuje wszystkie reguły TTL. Może to zająć minuty lub godziny na dużych tabelach. Nie rób tego w godzinach szczytu.

Kiedy używać: W nocy, podczas konserwacji, lub przed utworzeniem kopii zapasowej, aby nie kopiować danych, które i tak są martwe.

9. Rzeczywisty przypadek użycia: kasyno z RODO i agregatami

Teraz zbierzmy wszystko razem. Wyobraź sobie pełny schemat przechowywania zdarzeń w kasynie online.

-- Surowe zdarzenia (każdy zakład, każda gra)
CREATE TABLE raw_events
(
    user_id         UInt64,
    session_id      String,
    ip_address      String,           -- wrażliwe dane RODO
    event_type      String,           -- 'bet', 'win', 'login'
    event_value     Int64,
    created_at      DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    -- Szczegółowe zdarzenia przechowujemy 30 dni
    created_at + INTERVAL 30 DAY DELETE,
    -- Ale adres IP usuwamy po 14 dniach (RODO)
    ip_address + INTERVAL 14 DAY,
    -- Agregacja dla starych danych: po 30 dniach zwijamy w dzienne podsumowania
    created_at + INTERVAL 30 DAY
        GROUP BY toDate(created_at) AS day, user_id
        SET total_bets = sumIf(event_value, event_type = 'bet'),
            total_wins = sumIf(event_value, event_type = 'win'),
            sessions_count = countDistinct(session_id)
        DELETE WHERE day < now() - INTERVAL 2 YEAR;   -- agregaty przechowujemy 2 lata

Co otrzymaliśmy:

• 0–14 dni: Pełna informacja, włączając adresy IP. Można odtwarzać incydenty, wykrywać multi-konta.
• 15–30 dni: Adresy IP są już wyzerowane (anonimowe), ale szczegółowe zdarzenia nadal istnieją. Można analizować zachowanie użytkowników bez przypisania do lokalizacji.
• 31 dni – 2 lata: Szczegółowe zdarzenia usunięte. Zamiast nich — zagregowane wiersze na (dzień, użytkownik). Miejsca 100 razy mniej. Dashboardy działają szybko.
• Ponad 2 lata: Wszystko usunięte. Tylko kopie zapasowe na S3 (jeśli je robisz).

Jak czytać zagregowane dane po TTL:

-- Teraz w tabeli mieszanka: szczegółowe wiersze (pierwsze 30 dni) i agregaty (do 2 lat)
-- Nadal piszesz zwykłą agregację, która zadziała na obu typach wierszy
SELECT 
    toDate(created_at) AS day,
    user_id,
    sum(event_value) AS total
FROM raw_events
WHERE created_at >= today() - 45
GROUP BY day, user_id;

ClickHouse sam rozpozna, że dla świeżych danych sumuje szczegółowe wiersze, a dla starych bierze gotowe agregaty. Magia.

10. Kiedy NIE używać TTL i alternatywy

Kiedy TTL nie jest odpowiednie:

• Dane są często aktualizowane. TTL działa na podstawie wstawienia, a nie ostatniej aktualizacji. Jeśli zmieniasz wiersz przez INSERT z anulowaniem (CollapsingMergeTree), TTL będzie liczyć od oryginalnego created_at. Rozwiązanie: użyj kolumny updated_at w wyrażeniu TTL.

• Potrzebujesz dokładnego zarządzania czasem. Proces TTL jest w tle i nieprecyzyjny. Jeśli potrzebujesz zagwarantować usunięcie wiersza dokładnie o północy — nie da się. Rozrzut może wynosić do kilku godzin.

• Bardzo duże tabele z rzadkimi scaleniami. TTL jest stosowane podczas scalania części. Jeśli tabela rzadko się scala (np. z powodu ustawień merge_with_ttl_timeout), stare dane mogą wisieć dłużej.

Alternatywy dla TTL:

Podejście	Kiedy używać	Zalety	Wady
Partycjonowanie + DROP PARTITION	Dane płyną ciągłym strumieniem, usuwanie według kalendarza	Natychmiastowe usuwanie, brak narzutu	Wymaga zewnętrznego harmonogramu, brak elastyczności (tylko według partycji)
TTL DELETE	Dane mogą przychodzić z opóźnieniem, usuwanie według wieku wiersza	Wbudowane, elastyczne, nie wymaga zewnętrznych skryptów	Niedokładny czas usuwania, obciążenie merge
TTL TO DISK	Potrzebujesz zachować dane, ale na tanim nośniku	Oszczędność na przechowywaniu, transparentne dla zapytań	Wymaga konfiguracji storage_policy
TTL GROUP BY	Szczegółowe dane nie są potrzebne, tylko agregaty	Gwałtowne zmniejszenie miejsca (ponad 100 razy)	Utrata szczegółowości, złożoność debugowania

Łącz z partycjonowaniem dla lepszych rezultatów

Najlepsza praktyka: używaj partycjonowania razem z TTL.

CREATE TABLE bets_optimized
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)           -- partycje miesięczne
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY DELETE;    -- TTL po 30 dniach

Dlaczego to dobrze:

• Partycje pomagają szybko usuwać całe miesiące (jeśli TTL się opóźni).
• TTL czyści wewnątrz partycji bardziej elastycznie (według wieku wiersza, a nie kalendarza).

Co dalej

Teraz znasz wszystkie narzędzia automatycznego zarządzania danymi w ClickHouse. Kolejne tematy:

• Zaawansowane storage policies — jak skonfigurować automatyczne przenoszenie z SSD → HDD → S3 z różnymi TTL na każdym etapie.
• Monitorowanie TTL — jak przez system.query_log śledzić, ile danych jest usuwanych i jak szybko.
• TTL + Materialized Views — jak automatycznie agregować stare dane w osobnej tabeli, nie mieszając ich ze szczegółowymi.

Podsumowanie: TTL w ClickHouse to szwajcarski scyzoryk do zarządzania cyklem życia danych. Potrafi usuwać, przenosić, agregować i anonimizować. Używaj TTL ... DELETE do czyszczenia, TTL ... TO DISK do oszczędzania, TTL ... GROUP BY do magii z agregatami, a TTL na kolumny do RODO. I nie zapominaj o MATERIALIZE TTL, gdy potrzebujesz natychmiast zastosować reguły.

---

← Poprzedni: ORDER BY i PRIMARY KEY w ClickHouse: jak nie przegapić z indeksem
→ Następny: Słowniki w ClickHouse: szybki lookup bez JOIN

— Editorial Team