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

*Cuarta entrega de la serie **[ClickHouse desde cero a pro](/search?tag=clickhouse-desde-cero)**. Tiempo de lectura estimado: 12 minutos.*

En la [entrega III](/post/clickhouse-desde-cero-iii-consultas-analiticas-en-profundidad) 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](/post/clickhouse-desde-cero-i-instalacion-y-primeros-pasos)):

```sql
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:

```sql
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:

```sql
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

```sql
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:

```sql
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`:

```sql
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:

```sql
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

```sql
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:

```sql
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:

```sql
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:

```sql
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.

```sql
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:

```sql
-- 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](/post/clickhouse-desde-cero-v-produccion-replicacion-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](/post/clickhouse-desde-cero-i-instalacion-y-primeros-pasos)
- [II: tipos de datos y MergeTree](/post/clickhouse-desde-cero-ii-tipos-de-datos-y-mergetree)
- [III: consultas analíticas en profundidad](/post/clickhouse-desde-cero-iii-consultas-analiticas-en-profundidad)