Modelado de Datos en ClickHouse® DB: Engines, Tablas y MVs

En el último post sobre ClickHouse me metí en los internals: almacenamiento columnar, cómo MergeTree gestiona parts y granules, y el papel del sparse index. Si no conoces los fundamentos de ClickHouse, te recomiendo arrancar por ahí.

Si sigues aquí es que ya manejas lo básico de los engines, así que vamos a hablar de modelado de datos en ClickHouse.

ClickHouse tiene varias familias de engines, cada una con su propio conjunto. Este artículo se centra en la familia MergeTree, que cubre prácticamente cualquier caso de uso. Las demás quedan para un futuro post.

La documentación explica cada engine en su propia página. Lo que nadie te dice es cómo elegir entre ellos, ni cómo interactúan la elección de engine, ORDER BY y PARTITION BY. Tuve que aprenderlo a base de golpes, operando ClickHouse a escala de petabytes para analítica en Numia y ObsessionDB. Este post es la guía que me habría encantado tener hace tres años.

El modelo mental que lo aclara todo

Todos los engines de la familia MergeTree comparten la misma capa de almacenamiento. Mismos parts en disco, mismos granules, mismos sparse indexes, misma compresión. Idéntico.

Lo único que cambia es qué pasa durante los merges en background. Y ahí está la clave, aunque no ocurre de forma síncrona. Los merges corren en segundo plano cuando el servidor tiene capacidad libre, es decir, cuando no está ocupado con ingestión ni resolviendo queries externas.

MergeTree simplemente consolida parts. ReplacingMergeTree deduplica filas con la misma clave ORDER BY durante los merges, quedándose con la versión más reciente. SummingMergeTree suma columnas numéricas. AggregatingMergeTree combina estados parciales de agregación. VersionedCollapsingMergeTree cancela estados viejos con pares de signo +1/-1.

Eso es todo. Mismo almacenamiento, distinto comportamiento de merge. Elegir engine es básicamente responder una pregunta: ¿cómo evolucionan tus datos con el tiempo?

Tu patrón de datos	Engine	Comportamiento del merge
Solo inserción (logs, eventos, clicks)	MergeTree	Consolida parts. Nada especial.
Las filas se actualizan lógicamente (CDC, perfiles)	ReplacingMergeTree	Deduplica por clave ORDER BY, se queda con la última versión
Necesitas sumas/conteos pre-agregados	SummingMergeTree	Suma columnas numéricas con claves coincidentes
Necesitas pre-agregar cualquier cosa (avg, uniq)	AggregatingMergeTree	Combina estados parciales de agregación
Rastreas cambios de estado (cualquier orden de inserción)	VersionedCollapsingMergeTree	Cancela estado antiguo vía columna de signo, emparejado por versión

No incluí CollapsingMergeTree porque exige orden estricto de inserción, y en la práctica casi siempre vas a usar VersionedCollapsing. El otro que falta es GraphiteMergeTree, que solo tiene sentido si corres Graphite. SummingMergeTree aparece en la tabla pero no tiene sección propia porque es el hermano pequeño de AggregatingMergeTree: suma columnas numéricas automáticamente en el merge, sin la sintaxis State/Merge, pero limitado a sumas. Cloudflare lo usa para analítica HTTP a 6M requests/segundo, así que funciona a escala seria. Si necesitas avg, uniq o cualquier otra cosa, ve directo a AggregatingMergeTree.

Lo que la mayoría no se espera: el 99% de las tablas en producción son MergeTree plano, ReplacingMergeTree o AggregatingMergeTree. Tres engines para cubrir casi todo.

Vistas materializadas

Las MVs de ClickHouse no son los refreshes periódicos por lotes que conoces de PostgreSQL. Son triggers AFTER INSERT. Cada vez que llegan filas a una tabla origen, el SELECT de la MV se ejecuta solo sobre esas filas nuevas (ojo, la query no tiene acceso al resto de filas) y escribe los resultados en una tabla destino. Cero lecturas de disco sobre el origen. Solo el buffer RAM del INSERT.

El patrón habitual: eventos raw llegan a una tabla origen, una MV agrega sobre la marcha y los resultados van a un AggregatingMergeTree o SummingMergeTree destino. Defines el rollup una vez y cada insert lo mantiene actualizado. Más abajo muestro un ejemplo completo en la sección de AggregatingMergeTree.

ORDER BY y PARTITION BY

Puedes elegir el engine perfecto y seguir teniendo un rendimiento pésimo si tu ORDER BY está mal. Lo he visto. Me ha pasado.

Los datos se ordenan físicamente en disco según ORDER BY. El sparse index tiene una entrada cada 8.192 filas (valor por defecto, ajustable) y depende de ese orden. Si tu primera columna de ORDER BY es un timestamp (miles de millones de valores únicos), el sparse index apenas puede saltar nada cuando filtras por customer_id. Tu "base de datos analítica rápida" termina escaneando gigas que no necesitaba tocar.

CloudQuery publicó un post-mortem genial sobre exactamente esto. Ordenaron por timestamp primero. Las queries de clientes escaneaban 10x más datos de los necesarios. Movieron customer_id antes del timestamp y el problema desapareció. Mismo cluster, mismos datos.

La regla: ordena siempre por las columnas que usas para filtrar. Y recuerda: baja cardinalidad primero, alta cardinalidad al final.

ORDER BY (tenant_id, event_type, toStartOfHour(timestamp), timestamp)

En un MergeTree plano, eso es todo lo que hace ORDER BY: optimizar rendimiento. Pero los engines especializados le dan una segunda función.

En ReplacingMergeTree, ORDER BY define qué filas se consideran duplicadas. Mismos valores de ORDER BY = misma fila, y el merge se queda con la más reciente. En AggregatingMergeTree, ORDER BY define la clave de agregación. Las filas con valores de ORDER BY coincidentes combinan sus estados de agregación en cada merge.

Aquí surge una tensión. Imagina un ReplacingMergeTree con filas identificadas por (tenant_id, entity_type, entity_id). Las queries casi siempre filtran por tenant_id y a veces por entity_type, pero nunca por entity_id. Necesitas entity_id en el ORDER BY para que la dedup funcione, pero está desperdiciando espacio en el sparse index.

Solución: haz que PRIMARY KEY sea un prefijo de ORDER BY.

CREATE TABLE entities (
    tenant_id UInt32,
    entity_type LowCardinality(String),
    entity_id String,
    data String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (tenant_id, entity_type, entity_id)
PRIMARY KEY (tenant_id, entity_type);

Los datos se ordenan por las tres columnas en disco (bien para dedup). Solo las dos primeras entran en el sparse index (bien para RAM y rendimiento de queries). La mayoría de la gente ni sabe que PRIMARY KEY y ORDER BY pueden ser distintos en ClickHouse.

ClickHouse nunca mergea parts entre particiones. Suena trivial, pero muerde fuerte.

Con ReplacingMergeTree: versión antigua de una fila en la partición de enero, versión actualizada en la de febrero. No se deduplican jamás. La fila existe dos veces para siempre. Así que particiona por algo estable, como el mes de creación, no por updated_at.

Igual con AggregatingMergeTree. Los estados parciales de agregación en particiones distintas no se combinan durante los merges. Tus queries tienen que tenerlo en cuenta (GROUP BY con funciones -Merge lo resuelve, pero tienes que saber que está ahí).

Apunta a 1-300 GB por partición para MergeTree, 400 MB-40 GB para los engines especializados donde los merges curran más. Un error clásico que veo en producción es PARTITION BY customer_id en una tabla con millones de clientes. Espiral de muerte instantánea.

ReplacingMergeTree para los que vienen de PostgreSQL

En PostgreSQL haces UPDATE users SET name = 'Alice' WHERE id = 1 y listo. Atómico. Consistente. Consultas la tabla y ves el valor nuevo.

ClickHouse no va así.

Insertas una fila nueva con los valores actualizados. En algún momento (potencialmente minutos o hasta horas después) ClickHouse ejecuta un merge en background y deduplica: para la misma clave ORDER BY, se queda con la fila que tiene el valor de versión más alto. Hasta que ese merge ocurre, la fila antigua y la nueva son visibles a la vez. No es un bug, es el diseño. ClickHouse eligió velocidad de escritura sobre consistencia de lectura, y cuando analizas miles de millones de filas, ese tradeoff tiene sentido.

Pero tus queries tienen que lidiar con ello.

-- FINAL fuerza la deduplicación en tiempo de consulta
SELECT * FROM entities FINAL WHERE tenant_id = 1;

-- O hazlo tú mismo
SELECT entity_id, argMax(data, updated_at) AS data
FROM entities WHERE tenant_id = 1
GROUP BY entity_id;

FINAL solía ser terriblemente lento (penalizaciones de 10x eran habituales), pero desde la v22.6 corre en multi-hilo. Por otro lado, configurar do_not_merge_across_partitions_select_final = 1 redujo la sobrecarga 7x en las pruebas de Altinity. Nosotros usamos FINAL en producción, pero viene bien conocer las alternativas.

Dos cosas más. La dedup solo ocurre dentro de una partición (ya lo dije arriba, pero vale la pena insistir para ReplacingMergeTree porque es donde más duele). Y las columnas de ORDER BY no se pueden cambiar mediante reemplazo. Son la identidad de la fila. ¿Quieres cambiar el ID de un usuario? Cancela la fila vieja e inserta una nueva.

Desde la v23.3 también existe la columna is_deleted: ENGINE = ReplacingMergeTree(updated_at, is_deleted). Soft deletes limpios sin ñapas.

Voy a profundizar mucho más en ReplacingMergeTree en un post dedicado en dos semanas. Pipelines CDC desde PostgreSQL, estrategias de columnas de versión, optimización de FINAL. Hay material de sobra para su propio artículo.

AggregatingMergeTree

Este prefiero explicarlo a través del problema que resuelve.

En Numia tenemos un dashboard que muestra transacciones diarias y wallets únicos por blockchain. La tabla raw blockchain_events tiene miles de millones de filas. ¿Calcular count(*) y uniq(wallet_address) agrupado por día y chain en cada carga de página? Inviable.

AggregatingMergeTree te deja almacenar estados parciales de agregación en vez de valores raw. Cuando los parts se mergean, ClickHouse combina esos estados. Pagas el coste de agregación una vez al escribir, y luego las lecturas salen baratas.

CREATE TABLE daily_chain_stats (
    day Date,
    chain_id UInt16,
    tx_count AggregateFunction(sum, UInt64),
    unique_wallets AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (chain_id, day);

El tipo de columna AggregateFunction(sum, UInt64) es raro si no lo has visto antes. No guarda un número. Guarda un estado intermedio de suma, un blob que sabe cómo mergearse con otros blobs del mismo tipo. Escribes con sumState(), lees con sumMerge(). Si haces SELECT sin la función -Merge, te sale basura binaria. No es un error, simplemente no tiene sentido.

Para cosas sencillas (sum, max, min, any) existe SimpleAggregateFunction, que guarda valores planos y se consulta normal. Ahórrate el baile de State/Merge siempre que puedas.

No vas a insertar estados de agregación a mano. Para eso están las vistas materializadas:

CREATE MATERIALIZED VIEW daily_chain_stats_mv TO daily_chain_stats AS
SELECT
    toDate(block_timestamp) AS day,
    chain_id,
    sumState(toUInt64(1)) AS tx_count,
    uniqState(wallet_address) AS unique_wallets
FROM blockchain_events
GROUP BY chain_id, day;

-- Leyendo los datos pre-agregados en la tabla resultante
SELECT day, chain_id,
    sumMerge(tx_count) AS tx_count,
    uniqMerge(unique_wallets) AS unique_wallets
FROM daily_chain_stats
GROUP BY chain_id, day;

Altinity hizo benchmarks de este patrón y midió un speedup de 900x en queries, con la tabla destino 30.000x más pequeña que la origen. Su workload concreto, pero nosotros vemos ratios parecidos.

Errores de modelado de datos en ClickHouse que he visto (y cometido)

El peor: un equipo tenía una MV con GROUP BY escribiendo en una tabla destino MergeTree plana. La MV agrupaba bien cada batch de INSERT, pero el merge de MergeTree simplemente concatena parts. Sin agregación. La tabla destino fue acumulando claves duplicadas que nunca se combinaron, y nadie se dio cuenta durante semanas porque los números salían "parecidos"... hasta que dejaron de serlo. Si tu MV agrega, la tabla destino tiene que ser SummingMergeTree o AggregatingMergeTree.

PARTITION BY toYYYYMM(updated_at) en ReplacingMergeTree. Clásico. La fila se actualiza en febrero, updated_at cruza el límite de mes, la versión antigua se queda en enero para siempre. Particiona por algo que no cambie.

ORDER BY que sirve al engine pero ignora las queries. ORDER BY (entity_id) para dedup de ReplacingMergeTree, pero todas las queries filtran por tenant_id. La dedup funciona perfecto. Las queries escanean la tabla entera. Pon los filtros de tus queries primero en ORDER BY, las columnas de identidad del engine después. También puedes meter un índice si no quieres tocar el ORDER BY, pero algo hay que hacer.

Otra cosa que se olvida: los merges en la familia MergeTree son eventuales. Si haces SELECT * FROM daily_stats esperando un resultado limpio, vas a ver múltiples filas por clave con estados parciales que todavía no se han mergeado. Consulta siempre con GROUP BY y funciones -Merge.

Cloud-native y Replicated MergeTrees

Todo lo de arriba sobre selección de engine y diseño de tablas aplica igual sin importar cómo despliegues ClickHouse. Lo que cambia es la capa operacional.

En clusters self-hosted, cada engine tiene una variante Replicated: ReplicatedMergeTree, ReplicatedReplacingMergeTree, ReplicatedAggregatingMergeTree. Usan ClickHouse Keeper (o ZooKeeper en setups más antiguos) para coordinar la replicación entre nodos. El modelado de datos es idéntico. Le pones el prefijo Replicated al nombre del engine y añades dos parámetros para la ruta de Keeper y el nombre de la réplica.

Donde se complica es en las operaciones. Un cluster de producción típico tiene N shards con X réplicas cada uno, más una tabla distribuida encima para enrutar queries. Una tabla lógica se convierte en N*X tablas físicas más la capa de enrutamiento. Salud de Keeper, lag de cola de replicación, pesos de shards, failover. Funciona a escala, y hay empresas corriéndolo en producción. Pero la superficie es enorme. Si te interesa, lo cuento en detalle en este post que escribí hace unas semanas.

La arquitectura desacoplada reemplaza todo eso. Los datos van a object storage compartido, el compute es stateless, escribes ENGINE = MergeTree() y funciona. Sin Keeper. Sin gestión de réplicas. Sin topología de shards. Las mismas decisiones de modelado de datos, mucho menos ruido operacional.

Para el setup más sencillo y rentable, prueba ObsessionDB. Es un servicio gestionado de ClickHouse que usa object storage compartido para darte una experiencia serverless.