TTL en ClickHouse: gestión del ciclo de vida de los datos

TTL en ClickHouse: Gestión Automática del Ciclo de Vida de los Datos

1. Por qué se necesita TTL – El problema de «olvidar eliminar»

Volvamos a nuestro casino online. Almacenas todas las apuestas de los jugadores. Después de un mes, la tabla pesa 500 GB. Después de un año, 5 TB. Los discos se llenan, las consultas se ralentizan y los datos antiguos se necesitan cada vez menos. El dueño del negocio dice: «Los jugadores solo miran las últimas 30 días de apuestas, y para los informes solo necesitamos totales agregados para períodos anteriores».

Podrías escribir un script que elimine particiones antiguas una vez al día (como aprendimos en el artículo de particionamiento). Pero eso requiere un programador externo (cron), un script separado, monitoreo de su ejecución y manejo de errores.

TTL (Time-To-Live) resuelve este problema a nivel de base de datos. Es un mecanismo integrado de ClickHouse que automáticamente:

• elimina filas antiguas,
• las mueve a discos más baratos (HDD, S3),
• agrega datos antiguos (colapsa detalle en resúmenes),
• anonimiza datos personales (GDPR).

Todo esto ocurre en segundo plano, sin tu intervención, según un cronograma que defines en SQL.

Analogía de la vida real: TTL es como alquilar un almacén. Acuerdas con el dueño: «Las mercancías que llevan más de 30 días, muévelas a la sección barata del fondo. Si llevan más de un año, tíralas». El dueño lleva el control de los plazos y hace el trabajo; no necesitas recordárselo cada vez.

2. TTL a nivel de fila – Eliminación de registros antiguos

La opción más simple: las filas viven un tiempo determinado y luego se eliminan.

CREATE TABLE con TTL

-- Crear una tabla de apuestas donde las filas viven 90 días
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 días después de created_at, la fila se elimina

¿Qué está pasando aquí?

• TTL created_at + INTERVAL 90 DAY – para cada fila, se calcula la fecha de expiración: created_at + 90 días. Tan pronto como la fecha actual (today()) supera esta fecha, la fila se marca para eliminación.
• Un proceso en segundo plano (generalmente una vez al día) recorre los gránulos y elimina las filas cuyo TTL ha expirado.
• La eliminación ocurre a nivel de parte – ClickHouse reescribe la parte sin las filas eliminadas.

ALTER TABLE – Agregar o cambiar TTL

La característica clave: TTL se puede agregar a una tabla existente sin necesidad de recrearla.

-- Agregar TTL a una tabla existente
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 90 DAY;

-- Cambiar la vida útil de 90 a 180 días
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 180 DAY;

-- Eliminar TTL (los datos se almacenarán para siempre)
ALTER TABLE bets REMOVE TTL;

¿Por qué es importante? Porque en la vida real, los requisitos de almacenamiento cambian. Al principio, pensaste que necesitabas almacenar todo para siempre. Luego descubriste que las copias de seguridad ocupan espacio y los analistas no necesitan datos antiguos. Con MODIFY TTL, cambias la regla con una sola línea.

¿Qué pasa si agregas TTL a una tabla con 10 mil millones de filas? Nada terrible. ClickHouse no reescribirá los datos instantáneamente. Simplemente comenzará a aplicar TTL en segundo plano, gradualmente. Durante la próxima fusión de partes, las filas antiguas serán excluidas.

3. TTL con reubicación de disco (almacenamiento por niveles)

A veces es una pena eliminar datos, pero almacenarlos en SSD rápidos y costosos es caro. Solución: mover datos antiguos a HDD lentos y baratos (o almacenamiento en la nube S3).

Primero, configura los discos en la configuración de 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>

Ahora crea una tabla con reubicación 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';   -- Después de 30 días, se mueve a HDD

¿Qué sucede? Las filas viven en el SSD rápido durante los primeros 30 días (cuando se necesitan con más frecuencia en los informes). Después de 30 días, ClickHouse mueve las partes de datos a HDD en segundo plano. Al consultar datos de períodos anteriores, estarán disponibles pero un poco más lentos.

Puedes combinar – primero mover, luego eliminar:

-- 30 días en SSD, luego en HDD hasta 90 días, luego eliminar
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;

Analogía: Un hotel: los primeros 30 días vives en una suite (rápida, cara). Luego te trasladan a una habitación estándar (más lenta, más barata). Y después de 90 días te desalojan. Todo automático.

4. TTL con reubicación a S3

ClickHouse puede trabajar con almacenamiento en la nube (Amazon S3, MinIO, Google Cloud Storage). Puedes configurar una storage_policy con S3 y mover datos antiguos a la nube, donde el almacenamiento cuesta centavos.

Configuración (simplificada):

<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>        <!-- nube -->
                </cold_s3>
            </volumes>
        </s3_policy>
    </policies>
</storage_configuration>

Tabla con TTL a 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';   -- después de 90 días en S3

Por qué es genial: Pagas centavos por gigabyte al mes por S3. Los datos permanecen disponibles para análisis (aunque más lentos que desde el disco local). Y no tienes que preocuparte por quedarte sin espacio en el servidor – S3 es infinito.

5. TTL para agregación (la característica más potente)

Este es mi patrón favorito. En lugar de eliminar datos detallados antiguos, los colapsas en agregados. Por ejemplo, las apuestas de más de 7 días no se necesitan a nivel de segundos, pero sí los totales diarios por usuario.

-- Tabla con apuestas detalladas
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 de todas las apuestas del día
        bet_count = count()                 -- número de apuestas del día
    DELETE WHERE day < now() - INTERVAL 90 DAY;   -- después de 90 días, elimina también los agregados

Desglose:

• TTL created_at + INTERVAL 7 DAY – 7 días después de que se crea la fila (apuesta detallada), deja de ser detallada.
• GROUP BY toDate(created_at) AS day, user_id – las filas se agrupan por día y usuario. En lugar de miles de apuestas detalladas por día para un usuario, habrá una fila agregada.
• SET total_bets = sum(amount), bet_count = count() – en la nueva fila agregada, los campos se llenan con funciones de agregación.
• DELETE WHERE day < now() - INTERVAL 90 DAY – los agregados de más de 90 días finalmente se eliminan.

¿Qué sucede en la práctica?

1. Día 0–7: Los datos se almacenan en forma detallada. Puedes analizar cada apuesta.
2. Día 7–90: Las apuestas detalladas se colapsan en una fila por (user_id, day) con los campos total_bets y bet_count. El uso de espacio disminuye entre 10 y 100 veces.
3. Día 90+: Incluso los agregados se eliminan, solo quedan las copias de seguridad.

Analogía: Llevas un diario con entradas por hora. Después de una semana, reescribes las entradas por hora en resúmenes diarios (totales). Y después de tres meses, tiras incluso los resúmenes diarios, conservando solo los informes mensuales.

6. TTL para columnas específicas (Anonimización GDPR)

Por ley (GDPR en Europa, datos personales), estás obligado a almacenar información personal (correo electrónico, dirección IP) no más de un período determinado. Pero los análisis anónimos (monto de apuesta, número de juegos) se pueden almacenar para siempre.

TTL se puede aplicar no a toda la fila, sino a columnas individuales.

-- Tabla con datos personales
CREATE TABLE user_events
(
    user_id     UInt64,
    email       String,           -- columna personal
    ip_address  String,           -- columna personal
    event_type  String,
    event_value UInt64,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    email + INTERVAL 1 YEAR,      -- después de un año, el correo se anula
    ip_address + INTERVAL 1 YEAR, -- después de un año, la IP se anula
    created_at + INTERVAL 10 YEAR; -- después de 10 años, se elimina toda la fila

¿Qué sucede? Un año después de que se crea la fila, las columnas email e ip_address se reemplazan con valores predeterminados (cadena vacía para String, 0 para números). Los datos siguen siendo útiles para análisis (sabes que hubo algún usuario, pero no quién exactamente). Después de 10 años, se elimina toda la fila.

Por qué es importante para GDPR: Cumples automáticamente con la ley sin scripts manuales. Un auditor puede venir, mirar tus reglas TTL y verificar que los datos personales no se almacenan más tiempo del permitido.

7. Configuración del proceso TTL en segundo plano

TTL no se activa inmediatamente después de la expiración. ClickHouse ejecuta un proceso en segundo plano que:

• verifica gránulos,
• aplica reglas TTL,
• reescribe partes sin datos obsoletos o con columnas modificadas.

Parámetros de configuración (en config.xml o mediante SET):

-- Intervalo entre ejecuciones del proceso TTL (por defecto 1 día)
ALTER SYSTEM MODIFY SETTING merge_with_ttl_timeout = 86400;  -- en segundos

-- Para pruebas, puedes hacerlo más frecuente
SET merge_with_ttl_timeout = 3600;   -- cada hora

¿Por qué no debería ser TTL demasiado frecuente? Porque aplicar TTL es una operación de reescritura de partes que carga la CPU y los discos. Una vez al día está bien. Cada hora podría interferir si tienes terabytes de datos.

Cómo verificar que TTL está funcionando:

-- Ver qué partes tienen TTL activo
SELECT 
    partition,
    name,
    rows,
    modification_time,
    has_ttl_info   -- 1 = TTL se ha aplicado a esta parte
FROM system.parts
WHERE table = 'bets' AND active = 1;

8. MATERIALIZE TTL – Aplicación forzada

A veces necesitas que TTL se aplique de inmediato, sin esperar al proceso en segundo plano. Por ejemplo:

• Acabas de agregar TTL a una tabla enorme y quieres limpiar datos antiguos de inmediato.
• Estás probando reglas TTL y no quieres esperar un día.

-- Forzar la aplicación de TTL para toda la tabla
ALTER TABLE bets MATERIALIZE TTL;

-- Solo para una partición específica (más rápido)
ALTER TABLE bets MATERIALIZE TTL IN PARTITION '202501';

¿Qué sucede? ClickHouse escanea todas las partes de la tabla (o partición) y aplica inmediatamente todas las reglas TTL. Esto puede tomar minutos u horas en tablas grandes. No lo hagas durante las horas pico.

Cuándo usarlo: Por la noche, durante el mantenimiento, o antes de crear una copia de seguridad para evitar respaldar datos que ya están muertos.

9. Caso de uso real: Casino con GDPR y agregados

Ahora juntemos todo. Imagina un esquema completo para almacenar eventos en un casino online.

-- Eventos sin procesar (cada apuesta, cada juego)
CREATE TABLE raw_events
(
    user_id         UInt64,
    session_id      String,
    ip_address      String,           -- sensible a GDPR
    event_type      String,           -- 'bet', 'win', 'login'
    event_value     Int64,
    created_at      DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    -- Eventos detallados almacenados durante 30 días
    created_at + INTERVAL 30 DAY DELETE,
    -- Pero la dirección IP se elimina después de 14 días (GDPR)
    ip_address + INTERVAL 14 DAY,
    -- Agregación para datos antiguos: después de 30 días, colapsar en resúmenes diarios
    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;   -- agregados almacenados durante 2 años

¿Qué logramos?

• 0–14 días: Información completa, incluidas las direcciones IP. Se pueden investigar incidentes, detectar multi-cuentas.
• 15–30 días: Las direcciones IP ya están anuladas (anonimizadas), pero los eventos detallados aún existen. Se puede analizar el comportamiento del usuario sin geolocalización.
• 31 días – 2 años: Los eventos detallados se eliminan. En su lugar, filas agregadas por (day, user). El uso de espacio es 100 veces menor. Los paneles funcionan rápido.
• Más de 2 años: Todo se elimina. Solo quedan copias de seguridad en S3 (si las haces).

Cómo leer datos agregados después de TTL:

-- Ahora la tabla es mixta: filas detalladas (primeros 30 días) y agregados (hasta 2 años)
-- Aún escribes una consulta de agregación normal; funciona en ambos tipos de filas
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 mismo descubre que para datos frescos suma filas detalladas, para datos antiguos usa agregados precalculados. Magia.

10. Cuándo NO usar TTL y alternativas

Cuándo TTL no es adecuado:

• Los datos se actualizan con frecuencia. TTL se activa en la inserción, no en la última actualización. Si modificas una fila mediante INSERT con cancelación (CollapsingMergeTree), TTL contará desde el created_at original. Solución: usa una columna updated_at en la expresión TTL.

• Necesitas control de tiempo preciso. El proceso TTL es en segundo plano e impreciso. Si necesitas garantizar la eliminación exactamente a medianoche – no funcionará. El retraso puede ser de hasta varias horas.

• Tablas muy grandes con fusiones raras. TTL se aplica durante las fusiones de partes. Si la tabla se fusiona raramente (por ejemplo, debido a la configuración de merge_with_ttl_timeout), los datos antiguos pueden permanecer más tiempo.

Alternativas a TTL:

Enfoque	Cuándo usarlo	Pros	Contras
Particionamiento + DROP PARTITION	Los datos fluyen continuamente, eliminación por calendario	Eliminación instantánea, sin sobrecarga	Requiere programador externo, sin flexibilidad (solo por particiones)
TTL DELETE	Los datos pueden llegar con retraso, eliminación por antigüedad de fila	Integrado, flexible, sin scripts externos	Tiempo de eliminación impreciso, carga de fusión
TTL TO DISK	Necesitas conservar datos pero en almacenamiento barato	Ahorro en costos de almacenamiento, transparente para consultas	Requiere configuración de storage_policy
TTL GROUP BY	No se necesitan datos detallados, solo agregados	Reducción drástica de espacio (100x+)	Pérdida de detalle, complejidad de depuración

Combínalo con particionamiento para mejores resultados

Mejor práctica: usar particionamiento + TTL juntos.

CREATE TABLE bets_optimized
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)           -- particiones por mes
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY DELETE;    -- TTL por 30 días

Por qué es bueno:

• Las particiones ayudan a eliminar rápidamente meses completos (si TTL se retrasa).
• TTL limpia dentro de las particiones de manera más flexible (por antigüedad de fila, no por calendario).

Qué sigue

Ahora tienes todas las herramientas para la gestión automática de datos en ClickHouse. Próximos temas:

• Políticas de almacenamiento avanzadas – cómo configurar el movimiento automático de SSD → HDD → S3 con diferentes TTL en cada etapa.
• Monitoreo de TTL – cómo usar system.query_log para rastrear cuántos datos se están eliminando y a qué velocidad.
• TTL + Vistas materializadas – cómo agregar automáticamente datos antiguos en una tabla separada sin mezclarlos con datos detallados.

Resumen: TTL en ClickHouse es una navaja suiza para la gestión del ciclo de vida de los datos. Puede eliminar, mover, agregar y anonimizar. Usa TTL ... DELETE para limpieza, TTL ... TO DISK para ahorro, TTL ... GROUP BY para magia de agregación, y TTL en columnas para GDPR. Y no olvides MATERIALIZE TTL cuando necesites aplicar reglas de inmediato.

---

← Anterior: ORDER BY y PRIMARY KEY en ClickHouse: Cómo Configurar el Índice Correctamente
→ Siguiente: Diccionarios en ClickHouse: Búsqueda Rápida Sin JOIN

— Editorial Team