Materialized Views en ClickHouse®: Patrones y Trampas

Este post es la parte 3 de la serie ClickHouse Deep Dive. En el anterior, sobre modelado de datos, ya aparecieron las materialized views junto con otros temas clave de ClickHouse: almacenamiento columnar, engines y ejecución vectorizada. Lo que estás leyendo ahora es el deep dive completo sobre materialized views, uno de los mayores diferenciadores de ClickHouse.

Si has usado materialized views en PostgreSQL, hay algo que tienes que desaprender antes de seguir. Una MV en Postgres es una consulta cacheada que se refresca periódicamente. Las MVs de ClickHouse no tienen nada que ver: son triggers. Cada INSERT las dispara, transforman las filas que llegan con la query definida y escriben el resultado en una tabla destino aparte. No hay caché ni refresh. La diferencia de fondo es que la MV actúa en escritura, no en lectura.

Vuelvo a esa distinción una y otra vez porque explica prácticamente todos los errores de los que empiezan. En ObsessionDB hemos montado y visto MVs de INSERT-time sobre tablas que ingestan decenas de millones de filas por segundo. Algunas llevan más de un año sin que nadie las toque. Otras provocaron incidentes que costaron días deshacer. La diferencia se redujo a si entendíamos que estábamos construyendo pipelines o atajos de consulta.

Este post va solo de MVs de INSERT-time. ClickHouse también tiene MVs refrescables, más cercanas al modelo Postgres. Otro bicho, otro post.

Qué hacen las materialized views en cada INSERT

Llega un bloque de filas a la tabla origen, da igual si viene de la API HTTP, de otra MV o de un conector externo. El SELECT de la MV se ejecuta contra ese bloque y solo ese bloque. Los resultados van a la tabla destino. El INSERT se completa.

Ese "solo ese bloque" es lo que genera toda la confusión. Un SELECT count(*) FROM source_table dentro de una MV no devuelve el total de la tabla, sino el número de filas de ese batch INSERT concreto. GROUP BY agrega dentro del bloque. Los self-JOINs solo ven el bloque actual por ambos lados. Las window functions se cortan en el límite del bloque. Y ORDER BY en el SELECT de la MV no sirve de nada, porque la tabla destino tiene su propio orden.

O sea que tus agregaciones son parciales. Cada bloque INSERT produce un resultado parcial. Lo que pase con esos resultados parciales depende del engine de la tabla destino, y ahí es donde empiezan las decisiones de diseño.

SummingMergeTree es la opción más directa. Las filas con la misma clave ORDER BY van sumando sus columnas numéricas durante los merges de fondo. Dos bloques producen filas para (2026-02-20, /api/users) y los merges las suman. Sin sintaxis especial. La pega: solo puede sumar. Nada más, pero es simple y eficiente.

ReplacingMergeTree es un engine de deduplicación. Se queda solo con la fila más reciente por clave ORDER BY, opcionalmente según una columna de versión. Una MV alimentando un ReplacingMergeTree es, básicamente, un pipeline de dedup. Lo usamos constantemente para CDC.

AggregatingMergeTree es el patrón más conocido y el más relevante. Pre-agrega con un mecanismo de estados intermedios, y vale la pena dedicarle un momento porque el patrón -State/-Merge es clave para entender ClickHouse a fondo.

En vez de escribir números finales, escribe estados de agregación serializados en la tabla destino. sumState(), uniqState(), quantileState(). Devuelven blobs binarios, no números, y no sirven para nada si los consultas con un SELECT * FROM source_table normal. La tabla destino los guarda en columnas AggregateFunction, y durante los merges de fondo, ClickHouse combina estados parciales de distintos bloques en estados más completos. Al consultar, los combinadores -Merge finalizan el resultado: sumMerge(), uniqMerge(), quantileMerge(), y te devuelven el valor final.

Cuatro patrones que usamos en producción

Todos corren en ObsessionDB, ingiriendo decenas de millones de filas por segundo. Los listo en el orden en que los usamos. Hay muchos más patrones posibles, pero con estos ejemplos quiero mostrar la flexibilidad de la herramienta.

Pre-agregación

En ObsessionDB monitorizamos el rendimiento de APIs. Miles de millones de filas de peticiones raw, y necesitamos estadísticas por hora y endpoint que respondan en milisegundos. La tabla origen, la destino y la MV:

CREATE TABLE api_requests (
    timestamp DateTime,
    endpoint String,
    user_id UInt64,
    response_time_ms UInt32,
    status_code UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (endpoint, timestamp);

CREATE TABLE hourly_endpoint_stats (
    hour DateTime,
    endpoint String,
    request_count AggregateFunction(sum, UInt64),
    unique_users AggregateFunction(uniq, UInt64),
    p99_response AggregateFunction(quantile(0.99), UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (endpoint, hour);

CREATE MATERIALIZED VIEW hourly_endpoint_stats_mv TO hourly_endpoint_stats AS
SELECT
    toStartOfHour(timestamp) AS hour,
    endpoint,
    sumState(toUInt64(1)) AS request_count,
    uniqState(user_id) AS unique_users,
    quantileState(0.99)(response_time_ms) AS p99_response
FROM api_requests
GROUP BY endpoint, hour;

Para consultar se usa -Merge para obtener el estado final:

SELECT
    hour,
    endpoint,
    sumMerge(request_count) AS request_count,
    uniqMerge(unique_users) AS unique_users,
    quantileMerge(0.99)(p99_response) AS p99_response
FROM hourly_endpoint_stats
WHERE endpoint = '/api/v1/users'
GROUP BY endpoint, hour
ORDER BY hour DESC;

La tabla destino es unas 30.000 veces más pequeña que la origen. Altinity hizo benchmark de este patrón y consiguió un speedup de 900x en queries. Nuestras cifras van por ahí.

Reestructuración en la ingesta

A veces la tabla raw tiene los datos que necesitas, pero no en la forma que quieres para consultarlos. Agrupar timestamps en buckets, clasificar valores, extraer campos. Si pagas ese coste en query time, cada analista lo paga en cada consulta. Si lo pagas en la ingesta, lo pagas una vez. Abusamos de este patrón, y combinado con la pre-agregación funciona de maravilla.

CREATE TABLE raw_requests (
    timestamp DateTime,
    request_id String,
    user_id UInt64,
    response_time_ms UInt32,
    status_code UInt16,
    path String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, timestamp);

CREATE TABLE requests_enriched (
    timestamp DateTime,
    hour_bucket DateTime,
    request_id String,
    user_id UInt64,
    response_time_ms UInt32,
    speed_category LowCardinality(String),
    status_category LowCardinality(String),
    api_version LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, hour_bucket);

CREATE MATERIALIZED VIEW enriched_requests_mv TO requests_enriched AS
SELECT
    timestamp,
    toStartOfHour(timestamp) AS hour_bucket,
    request_id,
    user_id,
    response_time_ms,
    multiIf(response_time_ms > 1000, 'slow',
            response_time_ms > 200, 'normal', 'fast') AS speed_category,
    multiIf(status_code >= 500, 'server_error',
            status_code >= 400, 'client_error', 'success') AS status_category,
    extract(path, '^/api/(v[0-9]+)/') AS api_version
FROM raw_requests;

Los analistas consultan la tabla enriquecida y no tocan la raw:

SELECT
    hour_bucket,
    speed_category,
    count() AS requests,
    avg(response_time_ms) AS avg_response
FROM requests_enriched
WHERE api_version = 'v2' AND speed_category = 'slow'
GROUP BY hour_bucket, speed_category
ORDER BY hour_bucket DESC;

Si necesitas enriquecimiento geográfico o lookups de metadatos de usuario, dictGet con diccionarios en memoria funciona bien aquí. No JOINs. La sección "Qué salió mal" más abajo explica por qué.

Deduplicación

CDC desde Postgres, reintentos de webhooks, replays de API. Los duplicados son inevitable en pipelines de eventos, y nos hartamos de gestionarlos en query time. A veces es más práctico tratarlos en la pipeline que en la ingesta, o si son críticos, forzar la deduplicación en ambos lados.

CREATE TABLE raw_webhooks (
    received_at DateTime,
    event_id String,
    entity_id UInt64,
    payload String,
    updated_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(received_at)
ORDER BY (entity_id, received_at);

CREATE TABLE entity_latest (
    entity_id UInt64,
    payload String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY entity_id;

CREATE MATERIALIZED VIEW entity_latest_mv TO entity_latest AS
SELECT entity_id, payload, updated_at
FROM raw_webhooks;

Los merges deduplican las filas con el mismo entity_id, quedándose con el updated_at más reciente. Entre merges, las dos filas coexisten. Usa FINAL cuando necesites dedup garantizada en lectura. En tablas grandes, FINAL tiene un coste. El patrón argMax lo esquiva resolviendo en query time. Resulta especialmente útil combinado con TTL.

-- Cláusula FINAL
SELECT entity_id, payload, updated_at
FROM entity_latest FINAL
WHERE entity_id = 123;

-- argMax con GROUP BY
SELECT
    entity_id,
    argMax(payload, updated_at) AS payload,
    max(updated_at) AS updated_at
FROM entity_latest
WHERE entity_id = 123
GROUP BY entity_id;

Enrutamiento

Este patrón es casi demasiado simple para escribir sobre él, pero en la práctica ha sido uno de los más útiles. Una tabla origen, varias MVs, cada una con su WHERE, mandando datos a un destino distinto con su propia retención y orden.

CREATE TABLE all_events (
    timestamp DateTime,
    event_type String,
    level String,
    is_audit UInt8,
    payload String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (event_type, timestamp);

CREATE TABLE error_events (timestamp DateTime, level String, payload String)
ENGINE = MergeTree() ORDER BY timestamp TTL timestamp + INTERVAL 90 DAY;

CREATE TABLE metric_events (timestamp DateTime, payload String)
ENGINE = MergeTree() ORDER BY timestamp TTL timestamp + INTERVAL 365 DAY;

CREATE TABLE audit_log (timestamp DateTime, payload String)
ENGINE = MergeTree() ORDER BY timestamp; -- sin TTL, se guarda siempre

CREATE MATERIALIZED VIEW errors_mv TO error_events AS
SELECT timestamp, level, payload FROM all_events WHERE level = 'error';

CREATE MATERIALIZED VIEW metrics_mv TO metric_events AS
SELECT timestamp, payload FROM all_events WHERE event_type = 'metric';

CREATE MATERIALIZED VIEW audit_mv TO audit_log AS
SELECT timestamp, payload FROM all_events WHERE is_audit = 1;

Nada de agregación, nada de transformación. Solo filtrado. Se amplifica la escritura y se duplican datos, pero cada destino tiene su propio orden y política de retención. Luego, cada tabla se consulta directamente:

-- Últimas 24h de errores
SELECT timestamp, level, payload
FROM error_events
WHERE timestamp > now() - INTERVAL 1 DAY
ORDER BY timestamp DESC;

-- Registro de auditoría completo para una ventana específica
SELECT timestamp, payload
FROM audit_log
WHERE timestamp BETWEEN '2026-01-01' AND '2026-02-01'
ORDER BY timestamp;

Qué salió mal

Quiero ser concreto con estos casos porque la documentación de ClickHouse explica qué hacen las MVs, pero no qué pasa cuando se van al carajo. Todo lo que viene a continuación lo vivimos en producción en proyectos anteriores.

Los JOINs en MVs son caros y asimétricos. Teníamos una MV haciendo JOIN contra una tabla de dimensiones de 500 millones de filas. Funcionalmente correcto. Pero se disparaba en cada INSERT, miles de veces por minuto, y la latencia del INSERT pasó de 50ms a 2 segundos; el coste de lectura se disparó. Lo sustituimos por dictGet.

La raíz del problema: la query de la MV solo ve el bloque ingresado de la tabla origen, pero sí ve todos los datos de cualquier otra tabla en el JOIN. Hacer JOIN contra una tabla de dimensiones funciona. Hacer self-JOIN sobre la tabla origen produce resultados vacíos para cualquier fila cuyo match estaba en un batch anterior, porque ambos lados del JOIN solo ven el bloque actual. Esto nadie te lo dice de antemano.

La write amplification se acumula. 5 MVs sobre una tabla origen son 6 escrituras por INSERT. Cada una crea parts, y los parts necesitan merges. El INSERT no termina hasta que todas las MVs acaban. Teníamos 7 MVs en nuestra tabla principal de eventos, y en picos de ingesta el backlog de merges crecía sin freno. Consolidamos a 3, combinando lógica de agregación en tablas destino más anchas. El backlog se estabilizó en una hora. El setting parallel_view_processing ejecuta las MVs en paralelo en vez de secuencialmente, pero no reduce el trabajo total: solo redistribuye los tiempos.

Las MVs en cascada esconden bugs. La tabla origen alimenta la MV A, su tabla destino la lee la MV B, y la cadena llega hasta la MV C. Lo teníamos. La MV A tenía un WHERE mal puesto que descartaba en silencio el 15% de las filas durante tres días. La MV C agregaba datos corruptos, y nadie recibió una alerta. Un cliente se dio cuenta de que los números de su dashboard no cuadraban. Depurar significó rastrear datos paso a paso. Las cadenas profundas sí funcionan: hemos tenido algunas a profundidad-8 en producción. Pero cada salto multiplica la superficie de debugging, y vuelvo a esto en las reglas.

Los cambios de esquema dejan un hueco. La cláusula TO te permite hacer ALTER TABLE mv_name MODIFY QUERY para cambiar el SELECT. Pero si necesitas cambiar el esquema de la tabla destino, el engine, ORDER BY o los tipos de columna, toca drop-recreate-backfill. Entre el drop y el recreate, los datos no se procesan. Lo hemos resuelto de tres maneras:

1. Backfill después del swap
2. Canary upgrade (nueva MV + nueva tabla destino junto a la antigua, verificar, luego intercambiar)
3. Pausar la ingesta y asumir el downtime

Da igual cuál elijas: el backfill tiene que usar las mismas funciones -State que la MV. Olvidarte de esto e insertar valores raw en una tabla destino AggregatingMergeTree produce corrupción de datos. Además, lo más probable es que la reingesta necesite correr en batches, algo como:

INSERT INTO target SELECT ... FROM source WHERE timestamp BETWEEN [gap_start] AND [gap_end]

El manejo de errores es elige tu veneno. Un error en runtime en el SELECT de la MV (cast de tipos, división por cero) falla el INSERT por defecto, pero el comportamiento no es determinista: los bloques ya escritos en el destino se conservan, solo los siguientes se pierden. Si activas materialized_views_ignore_errors = 1, la escritura en la tabla origen funciona, pero las filas que fallan en la MV desaparecen sin dejar rastro. Tampoco hay dead-letter queue en ninguno de los dos casos. Elegimos una u otra según el caso.

La dedup del origen no se propaga a las MVs. Imagina que tu tabla origen es ReplacingMergeTree y tu MV hace sumState(). La MV se dispara en cada INSERT, incluidos los duplicados. El origen deduplica durante los merges, pero para entonces la MV ya escribió el duplicado. Se cuentan dos veces. La tabla origen queda bien después de los merges. La tabla destino de la MV queda permanentemente mal. Puedes poner ReplacingMergeTree también en el destino, usar argMaxState para quedarte solo con la última versión, o diseñar la MV para que sea idempotente. Ninguna solución es limpia. Es un tradeoff que hay que pensar antes de escribir el CREATE.

Cuándo usar projections en su lugar

Las projections guardan un orden físico alternativo (y opcionalmente una agregación) de los mismos datos, dentro de la misma tabla. El optimizador las elige automáticamente.

	Materialized Views	Projections
Almacenamiento	Tabla destino separada	Inline con la tabla padre
Agregación	Completa (AggregatingMergeTree)	Limitada
Transformaciones	Cualquier SELECT (JOINs, ARRAY JOIN, dictGet)	Solo GROUP BY simple
Cambios de esquema	Drop + recreate + backfill	ALTER TABLE ADD/DROP PROJECTION
Enrutamiento de consultas	Explícito (consultar la tabla destino)	Automático (el optimizador elige)
Write amplification	Parts y merges separados	Merge con la tabla padre
Backfill	INSERT INTO ... SELECT manual	ALTER TABLE MATERIALIZE PROJECTION
Monitorización	Fácil (tabla separada, consultas directas)	Difícil (oculta dentro de la tabla, necesitas EXPLAIN)

Projections para agregaciones simples. MVs para todo lo demás. Usamos projections en tablas raw y MVs para toda la pre-agregación, y esa división nos ha ido bien. Las projections tocan techo antes de lo que esperas, sobre todo con patrones de uniqState/quantileState y enrutamiento a distintos engines.

Qué monitorizamos

Conteo de filas en la tabla origen versus las tablas destino de MVs, en la misma ventana temporal. Si divergen, se están perdiendo datos. Un SELECT count() comparativo cada cinco minutos pilla los incidentes antes de que escalen.

system.parts en cada tabla destino de MV. El número de parts activos debería estabilizarse, no subir. Si sube, los merges no dan abasto.

Latencia del INSERT, medida de punta a punta, incluyendo todas las MVs.

system.query_log filtrado por query IDs relacionados con MVs. Ahí es donde se esconden los errores cuando materialized_views_ignore_errors está activado.

Las reglas

Nacieron de incidentes, no de la documentación.

1. Las MVs en cascada funcionan, pero tienen un coste. Tenemos cadenas a profundidad-8 en producción. Ahora bien, un bug en el salto 2 corrompe en silencio todo lo que viene después, y no te enteras hasta que alguien nota que los números no cuadran. Si vas profundo, invierte en monitorización salto a salto. Siempre que puedas, dos MVs independientes sobre el origen antes que una cadena.
2. Presupuesta la write amplification. Tres MVs sobre una tabla de alto throughput ya es mucho. Siete fueron demasiadas para nosotros.
3. dictGet para enriquecimiento, no JOINs.
4. Monitoriza como si fuera un pipeline. Conteos de filas, latencia, tasa de errores. Todo.
5. Clava el esquema desde el principio. Cambiarlo implica drop-recreate-backfill, y no quieres pasar por eso.
6. -State/-Merge con AggregatingMergeTree por encima de SummingMergeTree. Summing es más simple, pero en cuanto necesites uniq o quantile, te toca reescribirlo todo.
7. Escribe y testea la query de backfill el mismo día que creas la MV. La vas a necesitar, y no quieres andar buscando las funciones -State correctas a las 2 de la mañana en medio de un incidente.

Las materialized views son la funcionalidad de ClickHouse en la que más confío. Y también la que ha causado los peores incidentes. La diferencia entre un resultado y otro es si las tratas como pipelines o como atajos de consulta.