TTL in ClickHouse: Datenlebenszyklus-Management

TTL in ClickHouse: Automatisches Datenlebenszyklus-Management

1. Warum TTL benötigt wird – Das „Vergessen zu löschen“-Problem

Gehen wir zurück zu unserem Online-Casino. Sie speichern alle Spielerwetten. Nach einem Monat wiegt die Tabelle 500 GB. Nach einem Jahr 5 TB. Festplatten füllen sich, Abfragen werden langsamer, und alte Daten werden immer seltener benötigt. Der Geschäftsinhaber sagt: „Spieler schauen sich nur die letzten 30 Tage Wetten an, und für Berichte brauchen wir nur aggregierte Summen für ältere Zeiträume.“

Sie könnten ein Skript schreiben, das einmal täglich alte Partitionen löscht (wie wir im Partitionierungsartikel gelernt haben). Das erfordert jedoch einen externen Scheduler (cron), ein separates Skript, Überwachung seiner Ausführung und Fehlerbehandlung.

TTL (Time-To-Live) löst dieses Problem auf Datenbankebene. Es ist ein eingebauter ClickHouse-Mechanismus, der automatisch:

• alte Zeilen löscht,
• sie auf günstigere Datenträger (HDD, S3) verschiebt,
• alte Daten aggregiert (Details zu Zusammenfassungen verdichtet),
• personenbezogene Daten anonymisiert (DSGVO).

All dies geschieht im Hintergrund, ohne Ihr Zutun, nach einem Zeitplan, den Sie in SQL definieren.

Analogie aus dem echten Leben: TTL ist wie das Mieten eines Lagers. Sie vereinbaren mit dem Eigentümer: „Waren, die länger als 30 Tage liegen, bringen Sie in den günstigen hinteren Bereich. Wenn sie länger als ein Jahr liegen, werfen Sie sie weg.“ Der Eigentümer behält die Fristen im Auge und erledigt die Arbeit; Sie müssen ihn nicht jedes Mal daran erinnern.

2. Zeilenebene-TTL – Löschen alter Datensätze

Die einfachste Option: Zeilen leben eine bestimmte Zeit und werden dann gelöscht.

CREATE TABLE mit TTL

-- Erstellen Sie eine Wetten-Tabelle, in der Zeilen 90 Tage leben
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;   -- 90 Tage nach created_at wird die Zeile gelöscht

Was hier passiert:

• TTL created_at + INTERVAL 90 DAY – für jede Zeile wird das Ablaufdatum berechnet: created_at + 90 Tage. Sobald das aktuelle Datum (today()) dieses Datum überschreitet, wird die Zeile zum Löschen markiert.
• Ein Hintergrundprozess (normalerweise einmal täglich) durchläuft Granulen und löscht Zeilen, deren TTL abgelaufen ist.
• Das Löschen erfolgt auf Part-Ebene – ClickHouse schreibt das Part ohne die gelöschten Zeilen neu.

ALTER TABLE – TTL hinzufügen oder ändern

Die Hauptfunktion: TTL kann zu einer vorhandenen Tabelle hinzugefügt werden, ohne sie neu zu erstellen.

-- TTL zu einer vorhandenen Tabelle hinzufügen
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 90 DAY;

-- Lebensdauer von 90 auf 180 Tage ändern
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 180 DAY;

-- TTL entfernen (Daten werden für immer gespeichert)
ALTER TABLE bets REMOVE TTL;

Warum ist das wichtig? Weil sich im echten Leben die Speicheranforderungen ändern. Zuerst dachten Sie, Sie müssten alles für immer speichern. Dann stellten Sie fest, dass Backups Platz fressen und Analysten keine alten Daten brauchen. Mit MODIFY TTL ändern Sie die Regel mit einer einzigen Zeile.

Was passiert, wenn Sie TTL zu einer Tabelle mit 10 Milliarden Zeilen hinzufügen? Nichts Schlimmes. ClickHouse wird die Daten nicht sofort umschreiben. Es beginnt einfach, TTL im Hintergrund anzuwenden, nach und nach. Beim nächsten Merge von Parts werden alte Zeilen ausgeschlossen.

3. TTL mit Datenträgerverlagerung (Tiered Storage)

Manchmal ist es schade, Daten zu löschen, aber die Speicherung auf schnellen teuren SSDs ist kostspielig. Lösung: Verschieben Sie alte Daten auf langsame günstige HDDs (oder Cloud-Speicher S3).

Konfigurieren Sie zunächst Datenträger in der ClickHouse-Konfiguration (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>

Erstellen Sie nun eine Tabelle mit TTL-Verschiebung:

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';   -- Nach 30 Tagen Verschiebung auf HDD

Was passiert: Zeilen leben die ersten 30 Tage auf der schnellen SSD (wenn sie am häufigsten in Berichten benötigt werden). Nach 30 Tagen verschiebt ClickHouse die Daten-Parts im Hintergrund auf die HDD. Beim Abfragen von Daten für ältere Zeiträume sind diese verfügbar, aber etwas langsamer.

Sie können kombinieren – zuerst verschieben, dann löschen:

-- 30 Tage auf SSD, dann auf HDD bis 90 Tage, dann löschen
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;

Analogie: Ein Hotel: Die ersten 30 Tage wohnen Sie in einer Suite (schnell, teuer). Dann werden Sie in ein Standardzimmer verlegt (langsamer, günstiger). Und nach 90 Tagen werden Sie ausgewiesen. Alles automatisch.

4. TTL mit Verschiebung nach S3

ClickHouse kann mit Cloud-Speicher arbeiten (Amazon S3, MinIO, Google Cloud Storage). Sie können eine storage_policy mit S3 konfigurieren und alte Daten in die Cloud verschieben, wo die Speicherkosten nur Cent betragen.

Konfiguration (vereinfacht):

<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>   <!-- lokale SSD -->
                </hot>
                <cold_s3>
                    <disk>s3</disk>        <!-- Cloud -->
                </cold_s3>
            </volumes>
        </s3_policy>
    </policies>
</storage_configuration>

Tabelle mit TTL nach 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';   -- nach 90 Tagen in S3

Warum das cool ist: Sie zahlen nur Cent pro Gigabyte pro Monat für S3. Daten bleiben für Analysen verfügbar (wenn auch langsamer als von der lokalen Festplatte). Und Sie müssen sich keine Sorgen machen, dass der Serverplatz ausgeht – S3 ist unendlich.

5. TTL für Aggregation (Die leistungsstärkste Funktion)

Dies ist mein Lieblingsmuster. Anstatt alte detaillierte Daten zu löschen, verdichten Sie sie zu Aggregaten. Zum Beispiel werden Wetten, die älter als 7 Tage sind, nicht mehr auf Sekundenebene benötigt, aber tägliche und benutzerbezogene Summen sind erforderlich.

-- Tabelle mit detaillierten Wetten
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),           -- Summe aller Wetten des Tages
        bet_count = count()                 -- Anzahl der Wetten des Tages
    DELETE WHERE day < now() - INTERVAL 90 DAY;   -- nach 90 Tagen auch Aggregate löschen

Aufschlüsselung:

• TTL created_at + INTERVAL 7 DAY – 7 Tage nach Erstellung der Zeile (detaillierte Wette) ist sie nicht mehr detailliert.
• GROUP BY toDate(created_at) AS day, user_id – Zeilen werden nach Tag und Benutzer gruppiert. Statt Tausender detaillierter Wetten pro Tag für einen Benutzer gibt es eine aggregierte Zeile.
• SET total_bets = sum(amount), bet_count = count() – in der neuen aggregierten Zeile werden Felder mit Aggregatfunktionen gefüllt.
• DELETE WHERE day < now() - INTERVAL 90 DAY – Aggregate, die älter als 90 Tage sind, werden endgültig gelöscht.

Was in der Praxis passiert:

1. Tag 0–7: Daten werden detailliert gespeichert. Sie können jede Wette analysieren.
2. Tag 7–90: Detaillierte Wetten werden zu einer Zeile pro (user_id, day) mit den Feldern total_bets und bet_count verdichtet. Der Speicherplatzverbrauch sinkt um das 10- bis 100-fache.
3. Tag 90+: Selbst Aggregate werden gelöscht, nur Backups bleiben.

Analogie: Sie führen ein Tagebuch mit stündlichen Einträgen. Nach einer Woche schreiben Sie die stündlichen Einträge in tägliche Zusammenfassungen (Summen) um. Und nach drei Monaten werfen Sie sogar die täglichen Zusammenfassungen weg und behalten nur monatliche Berichte.

6. TTL für bestimmte Spalten (DSGVO-Anonymisierung)

Laut Gesetz (DSGVO in Europa, personenbezogene Daten) sind Sie verpflichtet, personenbezogene Informationen (E-Mail, IP-Adresse) nicht länger als einen bestimmten Zeitraum zu speichern. Aber anonyme Analysen (Wettbetrag, Anzahl der Spiele) können für immer gespeichert werden.

TTL kann nicht auf die gesamte Zeile, sondern auf einzelne Spalten angewendet werden.

-- Tabelle mit personenbezogenen Daten
CREATE TABLE user_events
(
    user_id     UInt64,
    email       String,           -- personenbezogene Spalte
    ip_address  String,           -- personenbezogene Spalte
    event_type  String,
    event_value UInt64,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    email + INTERVAL 1 YEAR,      -- nach einem Jahr wird die E-Mail auf null gesetzt
    ip_address + INTERVAL 1 YEAR, -- nach einem Jahr wird die IP auf null gesetzt
    created_at + INTERVAL 10 YEAR; -- nach 10 Jahren wird die gesamte Zeile gelöscht

Was passiert: Ein Jahr nach Erstellung der Zeile werden die Spalten email und ip_address durch Standardwerte ersetzt (leerer String für String, 0 für Zahlen). Die Daten bleiben für Analysen nützlich (Sie wissen, dass es einen Benutzer gab, aber nicht genau, wer). Nach 10 Jahren wird die gesamte Zeile gelöscht.

Warum dies für die DSGVO wichtig ist: Sie erfüllen automatisch das Gesetz, ohne manuelle Skripte. Ein Prüfer kann kommen, Ihre TTL-Regeln ansehen und überprüfen, dass personenbezogene Daten nicht länger als erlaubt gespeichert werden.

7. Konfiguration des Hintergrund-TTL-Prozesses

TTL wird nicht sofort nach Ablauf ausgelöst. ClickHouse führt einen Hintergrundprozess aus, der:

• Granulen überprüft,
• TTL-Regeln anwendet,
• Teile ohne veraltete Daten oder mit geänderten Spalten neu schreibt.

Konfigurationsparameter (in config.xml oder über SET):

-- Intervall zwischen TTL-Prozessläufen (Standard 1 Tag)
ALTER SYSTEM MODIFY SETTING merge_with_ttl_timeout = 86400;  -- in Sekunden

-- Zum Testen können Sie es häufiger machen
SET merge_with_ttl_timeout = 3600;   -- jede Stunde

Warum sollte TTL nicht zu häufig sein? Weil das Anwenden von TTL eine Operation zum Neuschreiben von Datenteilen ist, die CPU und Festplatten belastet. Einmal täglich ist in Ordnung. Jede Stunde könnte stören, wenn Sie Terabytes an Daten haben.

So überprüfen Sie, ob TTL funktioniert:

-- Sehen Sie, welche Teile aktives TTL haben
SELECT 
    partition,
    name,
    rows,
    modification_time,
    has_ttl_info   -- 1 = TTL wurde auf dieses Teil angewendet
FROM system.parts
WHERE table = 'bets' AND active = 1;

8. MATERIALIZE TTL – Erzwungene Anwendung

Manchmal müssen Sie TTL sofort anwenden, nicht auf den Hintergrundprozess warten. Zum Beispiel:

• Sie haben gerade TTL zu einer riesigen Tabelle hinzugefügt und möchten alte Daten sofort bereinigen.
• Sie testen TTL-Regeln und möchten nicht einen Tag warten.

-- TTL für die gesamte Tabelle erzwingen
ALTER TABLE bets MATERIALIZE TTL;

-- Nur für eine bestimmte Partition (schneller)
ALTER TABLE bets MATERIALIZE TTL IN PARTITION '202501';

Was passiert: ClickHouse durchsucht alle Teile der Tabelle (oder Partition) und wendet sofort alle TTL-Regeln an. Dies kann bei großen Tabellen Minuten oder Stunden dauern. Tun Sie dies nicht während der Hauptgeschäftszeiten.

Wann verwenden: Nachts, während Wartungsarbeiten oder vor der Erstellung eines Backups, um zu vermeiden, dass bereits tote Daten gesichert werden.

9. Reales Anwendungsbeispiel: Casino mit DSGVO und Aggregaten

Lassen Sie uns nun alles zusammenführen. Stellen Sie sich ein vollständiges Schema zum Speichern von Ereignissen in einem Online-Casino vor.

-- Rohereignisse (jede Wette, jedes Spiel)
CREATE TABLE raw_events
(
    user_id         UInt64,
    session_id      String,
    ip_address      String,           -- DSGVO-sensitiv
    event_type      String,           -- 'bet', 'win', 'login'
    event_value     Int64,
    created_at      DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    -- Detaillierte Ereignisse werden 30 Tage gespeichert
    created_at + INTERVAL 30 DAY DELETE,
    -- Aber die IP-Adresse wird nach 14 Tagen entfernt (DSGVO)
    ip_address + INTERVAL 14 DAY,
    -- Aggregation für alte Daten: nach 30 Tagen Verdichtung zu täglichen Zusammenfassungen
    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;   -- Aggregate werden 2 Jahre gespeichert

Was wir erreicht haben:

• 0–14 Tage: Vollständige Informationen, einschließlich IP-Adressen. Kann Vorfälle untersuchen, Mehrfachkonten erkennen.
• 15–30 Tage: IP-Adressen sind bereits auf null gesetzt (anonymisiert), aber detaillierte Ereignisse existieren noch. Kann Benutzerverhalten ohne Geolokalisierung analysieren.
• 31 Tage – 2 Jahre: Detaillierte Ereignisse werden gelöscht. Stattdessen aggregierte Zeilen pro (day, user). Speicherplatzverbrauch ist 100-mal geringer. Dashboards arbeiten schnell.
• Älter als 2 Jahre: Alles wird gelöscht. Nur Backups auf S3 (falls Sie welche erstellen).

So lesen Sie aggregierte Daten nach TTL:

-- Jetzt ist die Tabelle gemischt: detaillierte Zeilen (erste 30 Tage) und Aggregate (bis zu 2 Jahre)
-- Sie schreiben weiterhin eine normale Aggregationsabfrage; sie funktioniert auf beiden Zeilentypen
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 erkennt selbst, dass es für frische Daten detaillierte Zeilen summiert und für alte Daten vorberechnete Aggregate verwendet. Magie.

10. Wann TTL NICHT verwendet werden sollte und Alternativen

Wann TTL nicht geeignet ist:

• Daten werden häufig aktualisiert. TTL wird beim Einfügen ausgelöst, nicht bei der letzten Aktualisierung. Wenn Sie eine Zeile über INSERT mit Stornierung (CollapsingMergeTree) ändern, zählt TTL ab dem ursprünglichen created_at. Lösung: Verwenden Sie eine updated_at-Spalte im TTL-Ausdruck.

• Präzise Zeitsteuerung erforderlich. Der TTL-Prozess ist Hintergrund und ungenau. Wenn Sie garantieren müssen, dass die Löschung genau um Mitternacht erfolgt – das wird nicht funktionieren. Die Verzögerung kann bis zu mehreren Stunden betragen.

• Sehr große Tabellen mit seltenen Merges. TTL wird während Part-Merges angewendet. Wenn die Tabelle selten merged (z. B. aufgrund von merge_with_ttl_timeout-Einstellungen), können alte Daten länger verbleiben.

Alternativen zu TTL:

Ansatz	Wann verwenden	Vorteile	Nachteile
Partitionierung + DROP PARTITION	Daten fließen kontinuierlich, Löschung nach Kalender	Sofortige Löschung, kein Overhead	Erfordert externen Scheduler, keine Flexibilität (nur nach Partitionen)
TTL DELETE	Daten können mit Verzögerung ankommen, Löschung nach Zeilenalter	Eingebaut, flexibel, keine externen Skripte nötig	Ungenaue Löschzeit, Merge-Last
TTL TO DISK	Daten müssen behalten werden, aber auf günstigem Speicher	Speicherkostenersparnis, transparent für Abfragen	Erfordert storage_policy-Konfiguration
TTL GROUP BY	Detaillierte Daten nicht benötigt, nur Aggregate	Drastische Platzersparnis (100x+)	Verlust von Details, Debugging-Komplexität

Kombinieren Sie mit Partitionierung für beste Ergebnisse

Beste Praxis: Verwenden Sie Partitionierung + TTL zusammen.

CREATE TABLE bets_optimized
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)           -- Partitionen nach Monat
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY DELETE;    -- TTL nach 30 Tagen

Warum das gut ist:

• Partitionen helfen, ganze Monate schnell zu löschen (falls TTL hinterherhinkt).
• TTL bereinigt innerhalb von Partitionen flexibler (nach Zeilenalter, nicht nach Kalender).

Wie es weitergeht

Jetzt haben Sie alle Werkzeuge für die automatische Datenverwaltung in ClickHouse. Nächste Themen:

• Erweiterte Storage Policies – wie man die automatische Verschiebung von SSD → HDD → S3 mit unterschiedlichem TTL in jeder Stufe einrichtet.
• TTL überwachen – wie man system.query_log verwendet, um zu verfolgen, wie viele Daten gelöscht werden und wie schnell.
• TTL + Materialized Views – wie man alte Daten automatisch in eine separate Tabelle aggregiert, ohne sie mit detaillierten Daten zu vermischen.

Zusammenfassung: TTL in ClickHouse ist ein Schweizer Taschenmesser für das Datenlebenszyklus-Management. Es kann löschen, verschieben, aggregieren und anonymisieren. Verwenden Sie TTL ... DELETE für die Bereinigung, TTL ... TO DISK für Kosteneinsparungen, TTL ... GROUP BY für Aggregationsmagie und TTL on columns für die DSGVO. Und vergessen Sie nicht MATERIALIZE TTL, wenn Sie Regeln sofort anwenden müssen.

---

← Vorherige: ORDER BY und PRIMARY KEY in ClickHouse: So setzen Sie den Index richtig
→ Nächste: Wörterbücher in ClickHouse: Schnelle Lookups ohne JOIN

— Editorial Team