TTL dans ClickHouse : gestion du cycle de vie des données

TTL dans ClickHouse : Gestion Automatique du Cycle de Vie des Données

1. Pourquoi le TTL est nécessaire – Le problème « J'ai oublié de supprimer »

Revenons à notre casino en ligne. Vous stockez tous les paris des joueurs. Après un mois, la table pèse 500 Go. Après un an, 5 To. Les disques se remplissent, les requêtes ralentissent, et les anciennes données sont de moins en moins nécessaires. Le propriétaire de l'entreprise dit : « Les joueurs ne consultent que les 30 derniers jours de paris, et pour les rapports, nous n'avons besoin que de totaux agrégés pour les périodes plus anciennes. »

Vous pourriez écrire un script qui supprime les anciennes partitions une fois par jour (comme nous l'avons appris dans l'article sur le partitionnement). Mais cela nécessite un planificateur externe (cron), un script séparé, une surveillance de son exécution et une gestion des erreurs.

TTL (Time-To-Live) résout ce problème au niveau de la base de données. C'est un mécanisme intégré à ClickHouse qui automatiquement :

• supprime les anciennes lignes,
• les déplace vers des disques moins chers (HDD, S3),
• agrège les anciennes données (réduit les détails en résumés),
• anonymise les données personnelles (RGPD).

Tout cela se produit en arrière-plan, sans votre intervention, selon un planning que vous définissez en SQL.

Analogie réelle : Le TTL, c'est comme louer un entrepôt. Vous convenez avec le propriétaire : « Les marchandises qui sont là depuis plus de 30 jours, déplacez-les dans la section éloignée et bon marché. Si elles sont là depuis plus d'un an, jetez-les. » Le propriétaire suit les délais et fait le travail ; vous n'avez pas besoin de lui rappeler à chaque fois.

2. TTL au niveau des lignes – Suppression des enregistrements anciens

L'option la plus simple : les lignes vivent un certain temps, puis sont supprimées.

CREATE TABLE avec TTL

-- Crée une table de paris où les lignes vivent 90 jours
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 jours après created_at, la ligne est supprimée

Ce qui se passe ici :

• TTL created_at + INTERVAL 90 DAY – pour chaque ligne, la date d'expiration est calculée : created_at + 90 jours. Dès que la date courante (today()) dépasse cette date, la ligne est marquée pour suppression.
• Un processus en arrière-plan (généralement une fois par jour) parcourt les granules et supprime les lignes dont le TTL a expiré.
• La suppression se fait au niveau des parties – ClickHouse réécrit la partie sans les lignes supprimées.

ALTER TABLE – Ajouter ou modifier le TTL

La fonctionnalité clé : le TTL peut être ajouté à une table existante sans la recréer.

-- Ajouter un TTL à une table existante
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 90 DAY;

-- Modifier la durée de vie de 90 à 180 jours
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 180 DAY;

-- Supprimer le TTL (les données seront stockées indéfiniment)
ALTER TABLE bets REMOVE TTL;

Pourquoi est-ce important ? Parce que dans la vraie vie, les besoins de stockage changent. Au début, vous pensiez devoir tout stocker pour toujours. Puis vous avez découvert que les sauvegardes prennent de la place et que les analystes n'ont pas besoin des anciennes données. Avec MODIFY TTL, vous changez la règle en une seule ligne.

Que se passe-t-il si vous ajoutez un TTL à une table de 10 milliards de lignes ? Rien de grave. ClickHouse ne réécrit pas les données instantanément. Il commence simplement à appliquer le TTL en arrière-plan, progressivement. Lors de la prochaine fusion de parties, les anciennes lignes seront exclues.

3. TTL avec déplacement de disque (stockage hiérarchisé)

Parfois, c'est dommage de supprimer des données, mais les stocker sur des SSD rapides et coûteux est onéreux. Solution : déplacer les anciennes données vers des HDD lents et bon marché (ou le stockage cloud S3).

D'abord, configurez les disques dans la configuration 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>

Créez maintenant une table avec déplacement TTL :

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';   -- Après 30 jours, déplacement vers HDD

Ce qui se passe : Les lignes vivent sur le SSD rapide pendant les 30 premiers jours (quand elles sont le plus souvent nécessaires dans les rapports). Après 30 jours, ClickHouse déplace les parties de données vers le HDD en arrière-plan. Lors de l'interrogation des données pour des périodes plus anciennes, elles seront disponibles mais légèrement plus lentes.

Vous pouvez combiner – d'abord déplacer, puis supprimer :

-- 30 jours sur SSD, puis sur HDD jusqu'à 90 jours, puis suppression
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 : Un hôtel : les 30 premiers jours, vous vivez dans une suite (rapide, chère). Ensuite, vous êtes déplacé dans une chambre standard (plus lente, moins chère). Et après 90 jours, vous êtes expulsé. Tout automatique.

4. TTL avec déplacement vers S3

ClickHouse peut fonctionner avec le stockage cloud (Amazon S3, MinIO, Google Cloud Storage). Vous pouvez configurer une storage_policy avec S3 et déplacer les anciennes données vers le cloud, où le stockage coûte quelques centimes.

Configuration (simplifiée) :

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

Table avec TTL vers 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';   -- après 90 jours dans S3

Pourquoi c'est cool : Vous payez quelques centimes par gigaoctet par mois pour S3. Les données restent disponibles pour l'analyse (bien que plus lentes qu'à partir d'un disque local). Et vous n'avez pas à vous soucier de manquer d'espace serveur – S3 est infini.

5. TTL pour l'agrégation (la fonctionnalité la plus puissante)

C'est mon modèle préféré. Au lieu de supprimer les anciennes données détaillées, vous les réduisez en agrégats. Par exemple, les paris de plus de 7 jours ne sont pas nécessaires au niveau de la seconde, mais les totaux quotidiens et par utilisateur sont nécessaires.

-- Table avec paris détaillés
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),           -- somme de tous les paris du jour
        bet_count = count()                 -- nombre de paris du jour
    DELETE WHERE day < now() - INTERVAL 90 DAY;   -- après 90 jours, supprime aussi les agrégats

Détail :

• TTL created_at + INTERVAL 7 DAY – 7 jours après la création de la ligne (pari détaillé), elle cesse d'être détaillée.
• GROUP BY toDate(created_at) AS day, user_id – les lignes sont groupées par jour et utilisateur. Au lieu de milliers de paris détaillés par jour pour un utilisateur, il y aura une ligne agrégée.
• SET total_bets = sum(amount), bet_count = count() – dans la nouvelle ligne agrégée, les champs sont remplis avec des fonctions d'agrégation.
• DELETE WHERE day < now() - INTERVAL 90 DAY – les agrégats de plus de 90 jours sont finalement supprimés.

Ce qui se passe en pratique :

1. Jour 0–7 : Les données sont stockées sous forme détaillée. Vous pouvez analyser chaque pari.
2. Jour 7–90 : Les paris détaillés sont réduits en une ligne par (user_id, day) avec les champs total_bets et bet_count. L'utilisation de l'espace diminue de 10 à 100 fois.
3. Jour 90+ : Même les agrégats sont supprimés, seules les sauvegardes restent.

Analogie : Vous tenez un journal avec des entrées horaires. Après une semaine, vous réécrivez les entrées horaires en résumés quotidiens (totaux). Et après trois mois, vous jetez même les résumés quotidiens, ne gardant que les rapports mensuels.

6. TTL pour des colonnes spécifiques (Anonymisation RGPD)

Par la loi (RGPD en Europe, données personnelles), vous êtes tenu de conserver les informations personnelles (email, adresse IP) pendant une durée limitée. Mais les analyses anonymes (montant des paris, nombre de jeux) peuvent être stockées indéfiniment.

Le TTL peut être appliqué non pas à toute la ligne, mais à des colonnes individuelles.

-- Table avec données personnelles
CREATE TABLE user_events
(
    user_id     UInt64,
    email       String,           -- colonne personnelle
    ip_address  String,           -- colonne personnelle
    event_type  String,
    event_value UInt64,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    email + INTERVAL 1 YEAR,      -- après un an, email est nullifié
    ip_address + INTERVAL 1 YEAR, -- après un an, IP est nullifiée
    created_at + INTERVAL 10 YEAR; -- après 10 ans, toute la ligne est supprimée

Ce qui se passe : Un an après la création de la ligne, les colonnes email et ip_address sont remplacées par des valeurs par défaut (chaîne vide pour String, 0 pour les nombres). Les données restent utiles pour l'analyse (vous savez qu'il y avait un utilisateur, mais pas qui exactement). Après 10 ans, toute la ligne est supprimée.

Pourquoi c'est important pour la RGPD : Vous vous conformez automatiquement à la loi sans scripts manuels. Un auditeur peut venir, regarder vos règles TTL et vérifier que les données personnelles ne sont pas stockées plus longtemps que permis.

7. Configuration du processus TTL en arrière-plan

Le TTL ne se déclenche pas instantanément après l'expiration. ClickHouse exécute un processus en arrière-plan qui :

• vérifie les granules,
• applique les règles TTL,
• réécrit les parties sans les données obsolètes ou avec des colonnes modifiées.

Paramètres de configuration (dans config.xml ou via SET) :

-- Intervalle entre les exécutions du processus TTL (par défaut 1 jour)
ALTER SYSTEM MODIFY SETTING merge_with_ttl_timeout = 86400;  -- en secondes

-- Pour les tests, vous pouvez le rendre plus fréquent
SET merge_with_ttl_timeout = 3600;   -- toutes les heures

Pourquoi le TTL ne doit-il pas être trop fréquent ? Parce qu'appliquer le TTL est une opération de réécriture de partie de données qui sollicite le CPU et les disques. Une fois par jour est bien. Toutes les heures pourrait interférer si vous avez des téraoctets de données.

Comment vérifier que le TTL fonctionne :

-- Voir quelles parties ont un TTL actif
SELECT 
    partition,
    name,
    rows,
    modification_time,
    has_ttl_info   -- 1 = TTL a été appliqué à cette partie
FROM system.parts
WHERE table = 'bets' AND active = 1;

8. MATERIALIZE TTL – Application forcée

Parfois, vous avez besoin que le TTL s'applique immédiatement, sans attendre le processus en arrière-plan. Par exemple :

• Vous venez d'ajouter un TTL à une énorme table et voulez nettoyer immédiatement les anciennes données.
• Vous testez des règles TTL et ne voulez pas attendre un jour.

-- Forcer l'application du TTL pour toute la table
ALTER TABLE bets MATERIALIZE TTL;

-- Uniquement pour une partition spécifique (plus rapide)
ALTER TABLE bets MATERIALIZE TTL IN PARTITION '202501';

Ce qui se passe : ClickHouse analyse toutes les parties de la table (ou de la partition) et applique immédiatement toutes les règles TTL. Cela peut prendre des minutes ou des heures sur les grandes tables. Ne faites pas cela pendant les heures de pointe.

Quand l'utiliser : La nuit, pendant la maintenance, ou avant de créer une sauvegarde pour éviter de sauvegarder des données déjà mortes.

9. Cas d'utilisation réel : Casino avec RGPD et agrégats

Maintenant, rassemblons tout cela. Imaginez un schéma complet pour stocker les événements dans un casino en ligne.

-- Événements bruts (chaque pari, chaque jeu)
CREATE TABLE raw_events
(
    user_id         UInt64,
    session_id      String,
    ip_address      String,           -- sensible RGPD
    event_type      String,           -- 'bet', 'win', 'login'
    event_value     Int64,
    created_at      DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    -- Événements détaillés stockés pendant 30 jours
    created_at + INTERVAL 30 DAY DELETE,
    -- Mais l'adresse IP est supprimée après 14 jours (RGPD)
    ip_address + INTERVAL 14 DAY,
    -- Agrégation pour les anciennes données : après 30 jours, réduction en résumés quotidiens
    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;   -- agrégats stockés pendant 2 ans

Ce que nous avons accompli :

• 0–14 jours : Informations complètes, y compris les adresses IP. Possibilité d'enquêter sur les incidents, de détecter les multi-comptes.
• 15–30 jours : Les adresses IP sont déjà nullifiées (anonymisées), mais les événements détaillés existent toujours. Possibilité d'analyser le comportement des utilisateurs sans géolocalisation.
• 31 jours – 2 ans : Les événements détaillés sont supprimés. À la place, des lignes agrégées par (day, user). L'utilisation de l'espace est 100 fois moindre. Les tableaux de bord fonctionnent rapidement.
• Plus de 2 ans : Tout est supprimé. Seules les sauvegardes sur S3 (si vous les faites).

Comment lire les données agrégées après TTL :

-- Maintenant la table est mixte : lignes détaillées (30 premiers jours) et agrégats (jusqu'à 2 ans)
-- Vous écrivez toujours une requête d'agrégation normale ; elle fonctionne sur les deux types de lignes
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 détermine lui-même que pour les données fraîches, il additionne les lignes détaillées, et pour les anciennes données, il utilise les agrégats précalculés. Magique.

10. Quand ne PAS utiliser le TTL et alternatives

Quand le TTL n'est pas adapté :

• Les données sont mises à jour fréquemment. Le TTL se déclenche sur l'insertion, pas sur la dernière mise à jour. Si vous modifiez une ligne via INSERT avec annulation (CollapsingMergeTree), le TTL comptera à partir du created_at d'origine. Solution : utilisez une colonne updated_at dans l'expression TTL.

• Besoin d'un contrôle précis du temps. Le processus TTL est en arrière-plan et imprécis. Si vous devez garantir la suppression exactement à minuit – cela ne fonctionnera pas. Le délai peut aller jusqu'à plusieurs heures.

• Très grandes tables avec des fusions rares. Le TTL est appliqué lors des fusions de parties. Si la table fusionne rarement (par exemple, à cause des paramètres merge_with_ttl_timeout), les anciennes données peuvent persister plus longtemps.

Alternatives au TTL :

Approche	Quand l'utiliser	Avantages	Inconvénients
Partitionnement + DROP PARTITION	Flux de données continu, suppression par calendrier	Suppression instantanée, pas de surcharge	Nécessite un planificateur externe, pas de flexibilité (uniquement par partitions)
TTL DELETE	Les données peuvent arriver en retard, suppression par âge de ligne	Intégré, flexible, pas de scripts externes nécessaires	Temps de suppression imprécis, charge de fusion
TTL TO DISK	Besoin de conserver les données mais sur un stockage bon marché	Économies de coûts de stockage, transparent pour les requêtes	Nécessite une configuration storage_policy
TTL GROUP BY	Les données détaillées ne sont pas nécessaires, seulement les agrégats	Réduction drastique de l'espace (100x+)	Perte de détails, complexité de débogage

Combiner avec le partitionnement pour de meilleurs résultats

Meilleure pratique : utilisez partitionnement + TTL ensemble.

CREATE TABLE bets_optimized
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)           -- partitions par mois
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY DELETE;    -- TTL par 30 jours

Pourquoi c'est bien :

• Les partitions aident à supprimer rapidement des mois entiers (si le TTL est en retard).
• Le TTL nettoie à l'intérieur des partitions de manière plus flexible (par âge de ligne, pas par calendrier).

Prochaines étapes

Vous avez maintenant tous les outils pour la gestion automatique des données dans ClickHouse. Prochains sujets :

• Politiques de stockage avancées – comment configurer le déplacement automatique de SSD → HDD → S3 avec différents TTL à chaque étape.
• Surveillance du TTL – comment utiliser system.query_log pour suivre la quantité de données supprimées et la vitesse.
• TTL + Vues matérialisées – comment agréger automatiquement les anciennes données dans une table séparée sans les mélanger avec les données détaillées.

Résumé : Le TTL dans ClickHouse est un couteau suisse pour la gestion du cycle de vie des données. Il peut supprimer, déplacer, agréger et anonymiser. Utilisez TTL ... DELETE pour le nettoyage, TTL ... TO DISK pour les économies, TTL ... GROUP BY pour la magie d'agrégation, et TTL sur les colonnes pour la RGPD. Et n'oubliez pas MATERIALIZE TTL lorsque vous devez appliquer les règles immédiatement.

---

← Précédente: ORDER BY et PRIMARY KEY dans ClickHouse : comment bien choisir son index
→ Suivante: Dictionnaires dans ClickHouse : Recherche rapide sans JOIN

— Editorial Team