ClickHouse desde cero (IV): materialized views, projections y TTL

Cuarta entrega de la serie ClickHouse desde cero a pro. Tiempo de lectura estimado: 12 minutos.

En la entrega III vimos cómo escribir consultas analíticas que en PostgreSQL serían ciencia ficción. En este post damos el siguiente salto: cómo evitar recalcular lo mismo una y otra vez.

Cuando un dashboard consulta los mismos datos cada minuto, con las mismas agregaciones, escanear mil millones de filas en cada petición es tirar recursos a la basura. ClickHouse ofrece tres herramientas complementarias: materialized views, projections y TTL.

Materialized views: la joya de la corona

Un materialized view en ClickHouse no es lo que llamas así en PostgreSQL. No es una vista que refrescas manualmente. Es un trigger de inserción: se dispara cuando insertas datos en la tabla origen y escribe el resultado de una consulta en una tabla destino.

Conceptualmente: cada INSERT en la tabla base ejecuta una consulta sobre ese batch y escribe el resultado en otra tabla. Los datos quedan pre-agregados automáticamente, sin refresh, sin cron jobs.

Ejemplo: estadísticas diarias de pageviews

Tabla base (recordando la entrega I):

CREATE TABLE blog.pageviews (
  ts           DateTime,
  user_id      UInt64,
  path         String,
  country      LowCardinality(String),
  duration_ms  UInt32
)
ENGINE = MergeTree()
ORDER BY (ts, user_id);

Tabla destino que guardará las pre-agregaciones:

CREATE TABLE blog.pageviews_daily (
  day         Date,
  country     LowCardinality(String),
  views       UInt64,
  visitors    AggregateFunction(uniq, UInt64),
  duration    AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, country);

Presta atención a los tipos: AggregateFunction(uniq, ...) no guarda el resultado final de uniq, sino un estado intermedio que puede fusionarse con otros estados. Es lo que permite que la agregación siga siendo correcta cuando luego se insertan más datos el mismo día.

Y ahora, el materialized view que conecta ambas:

CREATE MATERIALIZED VIEW blog.pageviews_daily_mv
TO blog.pageviews_daily
AS
SELECT
  toDate(ts)          AS day,
  country,
  count()             AS views,
  uniqState(user_id)  AS visitors,
  avgState(duration_ms) AS duration
FROM blog.pageviews
GROUP BY day, country;

A partir de ese momento, cada INSERT en pageviews dispara el MV, que inserta (o actualiza) las filas correspondientes en pageviews_daily. No hay que hacer nada más.

Consultando los estados

SELECT
  day,
  country,
  sum(views)             AS views,
  uniqMerge(visitors)    AS visitors,
  avgMerge(duration)     AS avg_ms
FROM blog.pageviews_daily
WHERE day >= today() - 30
GROUP BY day, country
ORDER BY day DESC;

Fíjate en los sufijos *State y *Merge. El MV escribe uniqState. La consulta lee con uniqMerge, que consolida los estados de cada partición de pageviews_daily. ClickHouse se encarga del resto.

Consultar 30 días desde pageviews_daily lee unas pocas miles de filas. Hacer la misma consulta contra pageviews podría leer cientos de millones. La diferencia es de dos o tres órdenes de magnitud.

Backfill de datos históricos

Si creas el MV cuando la tabla base ya tiene datos, el MV no se aplica retroactivamente. Tienes que poblar la tabla destino manualmente:

INSERT INTO blog.pageviews_daily
SELECT
  toDate(ts)          AS day,
  country,
  count()             AS views,
  uniqState(user_id)  AS visitors,
  avgState(duration_ms) AS duration
FROM blog.pageviews
WHERE ts < today()  -- histórico; el MV se encarga del resto
GROUP BY day, country;

Cuidado con solapar ventanas: suele ser más seguro parar los inserts en la base, crear el MV, hacer el backfill con WHERE ts < X, y reanudar.

Patrones útiles

• MV por granularidad: un MV por hora, otro por día, otro por mes. Las consultas eligen el más fino que cumpla su rango.
• Cadenas de MVs: el destino de un MV puede ser origen de otro MV. Útil para segundo nivel de agregación.
• MVs con joins: posible, pero con asterisco. Solo se ejecutan sobre la tabla "izquierda" cuando hay inserts ahí. Cambios en la tabla joinada no los ven.

Projections

Las projections son como materialized views encapsulados dentro de la propia tabla. ClickHouse las gestiona de forma transparente: cuando haces una consulta, el planner mira si alguna projection de la tabla responde mejor que la tabla principal.

Se declaran en el CREATE TABLE:

CREATE TABLE blog.pageviews (
  ts           DateTime,
  user_id      UInt64,
  path         String,
  country      LowCardinality(String),
  duration_ms  UInt32,

  PROJECTION by_country (
    SELECT
      country,
      toStartOfDay(ts) AS day,
      count(),
      uniq(user_id)
    GROUP BY country, day
  ),

  PROJECTION by_path (
    SELECT path, count() GROUP BY path
  )
)
ENGINE = MergeTree()
ORDER BY (ts, user_id);

O añadirlas a una tabla existente:

ALTER TABLE blog.pageviews
ADD PROJECTION by_country (
  SELECT country, toStartOfDay(ts) AS day, count(), uniq(user_id)
  GROUP BY country, day
);

ALTER TABLE blog.pageviews MATERIALIZE PROJECTION by_country;

Ventajas sobre los MVs:

• Transparentes: no cambias las consultas, el planner decide.
• Siempre consistentes: viven dentro de la misma tabla y son atómicas.
• Rebuild automático: MATERIALIZE PROJECTION reprocesa los parts existentes.

Desventajas:

• Requieren más disco (cada projection es una copia de los datos con otra ORDER BY).
• Las proyecciones normales (sin GROUP BY) son menos flexibles que un MV.
• No sirven para joins ni para tablas destino distintas.

Regla práctica: empieza con projections para casos simples y pasa a MVs cuando la lógica sea más compleja o cuando quieras controlar explícitamente dónde se almacenan los datos.

TTL: ciclo de vida automático

En casi cualquier analytics acabas teniendo una política: "guarda 12 meses de detalle y 36 de agregado". El TTL de ClickHouse automatiza eso.

TTL de filas: borrar automáticamente

CREATE TABLE blog.pageviews (
  ts           DateTime,
  ...
)
ENGINE = MergeTree()
ORDER BY (ts, user_id)
TTL ts + INTERVAL 12 MONTH;

Durante los merges, ClickHouse descarta las filas cuya ts + 12 MONTH ya ha pasado. No es instantáneo, pero tampoco corre prisa: lo hace en background.

Puedes forzarlo:

ALTER TABLE blog.pageviews MATERIALIZE TTL;

TTL a otro disco (movimiento por edad)

Si tienes discos SSD rápidos y HDDs grandes, puedes mover los datos viejos a los lentos:

TTL ts + INTERVAL 3 MONTH TO DISK 'cold',
    ts + INTERVAL 24 MONTH DELETE;

Necesitas configurar un storage policy con varios discos en el servidor. Es la receta para dejar en SSD los últimos 3 meses (los que reciben tráfico de dashboards) y en HDD el histórico.

TTL de columna

Puedes vaciar columnas específicas después de cierto tiempo:

CREATE TABLE blog.pageviews (
  ts          DateTime,
  user_id     UInt64,
  path        String,
  ip          String TTL ts + INTERVAL 30 DAY,
  user_agent  String TTL ts + INTERVAL 90 DAY,
  ...
)
ENGINE = MergeTree()
ORDER BY (ts, user_id);

Útil para políticas de privacidad: la IP se vacía a los 30 días, el user agent a los 90, y las métricas agregadas se mantienen indefinidamente.

TTL con GROUP BY (expiración agregando)

Una variante potente: en lugar de borrar, agrega y sigue guardando.

ENGINE = MergeTree()
ORDER BY (ts, country)
TTL ts + INTERVAL 6 MONTH
    GROUP BY toStartOfDay(ts), country
    SET views = sum(views);

A los 6 meses, en lugar de borrar las filas, colapsa cada día-país en una sola fila con las sumas. Ahorras espacio manteniendo agregado.

Un diseño completo

Ejemplo para un producto con ~100M pageviews mensuales:

-- Tabla base con detalle, 12 meses
CREATE TABLE blog.pageviews (
  ts           DateTime,
  user_id      UInt64,
  path         String,
  country      LowCardinality(String),
  device       LowCardinality(String),
  duration_ms  UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, user_id)
TTL ts + INTERVAL 12 MONTH;

-- Agregado diario, sin expirar
CREATE TABLE blog.pageviews_daily (
  day       Date,
  country   LowCardinality(String),
  device    LowCardinality(String),
  views     UInt64,
  visitors  AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, country, device);

CREATE MATERIALIZED VIEW blog.pageviews_daily_mv
TO blog.pageviews_daily
AS
SELECT
  toDate(ts)          AS day,
  country,
  device,
  count()             AS views,
  uniqState(user_id)  AS visitors
FROM blog.pageviews
GROUP BY day, country, device;

Los dashboards consultan pageviews_daily y son instantáneos. Las consultas ad hoc que necesitan detalle van a pageviews. El TTL limpia los datos crudos al año. El agregado se guarda para siempre: ocupa pocos KB por día.

Este patrón (tabla cruda + MV de agregado + TTL) es la receta estándar en ClickHouse. Si dominas los tres, ya estás resolviendo el 90% de los casos.

Cosas a evitar

• No hacer el backfill al crear un MV sobre una tabla con datos históricos. El MV solo se aplica a inserts nuevos.
• Hacer MVs sobre MVs sin cuidado. Un error en el primero se propaga al segundo.
• Olvidar que los MVs se ejecutan en el contexto del INSERT. Si el MV falla, el insert falla (salvo que uses materialized_views_ignore_errors).
• Abusar de projections en tablas con alta tasa de escritura. Cada projection multiplica el trabajo de los merges.
• TTL demasiado agresivo sin storage policies en producción, sin tener un snapshot por si acaso. Borrar es fácil, reconstruir no.

Por dónde seguir

• V: producción, replicación y clusters — lo que viene después: replicación, sharding, backups y operar el servicio bajo presión.

Si estás aterrizando en la serie:

• I: instalación y primeros pasos
• II: tipos de datos y MergeTree
• III: consultas analíticas en profundidad