ClickHouse: index vs projection vs materialized view

En ClickHouse®, elige según lo que te cuesta cada mecanismo. Un data-skipping index cuesta metadatos por gránulo. Una projection ligera cuesta el sorting key y un puntero _part_offset. Una projection completa cuesta aproximadamente el doble de almacenamiento. Una materialized view cuesta tiempo de escritura y es la única que puede transformar los datos. Antes de tocar cualquiera de ellas, revisa el sorting key.

Desde la versión 25.5, una projection ligera guarda el sorting key más un puntero _part_offset, y usa su propio índice primario para podar gránulos mientras lee las filas de la tabla base. Una projection completa guarda una segunda copia entera de los datos en otro orden. La ligera se parece más a un data-skipping index que a la projection completa que vive bajo la misma sintaxis.

Equivocarte entre esas dos te cuesta una factura de almacenamiento duplicada que no necesitabas, o una query que sigue lenta por mucho que la ajustes.

Revisa el sorting key antes que nada

La mayoría de tablas que acaban con una projection no la necesitaban.

El sorting key va por delante de la escalera. Cambiarlo implica reconstruir la tabla, y eso lo pone en otra categoría distinta de cualquier cosa que puedas añadir con ALTER TABLE. Un skip index lo comparas con una projection. El orden de la tabla es una decisión que ya tomaste al crearla.

El orden de las columnas dentro de esa clave es donde están las victorias baratas. Hemos visto casos en los que solo ese cambio se traduce en leer 400 veces menos datos para la misma query, sin objetos nuevos que mantener ni nada extra que mantener sincronizado. PREWHERE entra en la misma categoría: gratis, activado por defecto en la mayoría de casos, y merece la pena confirmarlo antes de montar maquinaria.

La mecánica de ambos está en query optimization: qué hace rápido a ClickHouse. Si tu ORDER BY ya encaja con tu patrón de acceso y la query sigue lenta, sigue leyendo.

Cómo elegir entre index, projection y materialized view

Un skip index le dice a ClickHouse qué gránulos puede ignorar. Una projection le da un conjunto distinto de gránulos que leer. Una materialized view le da directamente otra tabla, escrita en tiempo de inserción.

	Skip index	Projection ligera	Projection completa	Materialized view
Coste de almacenaje	Metadatos por gránulo	Sorting key + _part_offset	~2x la tabla	El tamaño de la tabla destino
Coste de escritura	Pequeño, por índice	Pequeño	Los datos se escriben dos veces	Trigger en tiempo de inserción
Transforma los datos	No	No	No	Sí: JOINs, filtros, enrutado
Hay que cambiar queries	No	No	No	Consultas a la tabla destino
Consistencia	Atómica con la parte	Atómica con la parte	Atómica con la parte	Diverge si falla un insert
Encadenable	No aplica	No	No	Sí
TTL propio	No	No	No	Sí
Falla cuando	Los valores se dispersan por los gránulos	Coinciden todos los gránulos	Se acaba el presupuesto de disco	Fan-out en el insert

Dos filas concentran casi toda la decisión. Transformar los datos es la frontera dura: la definición de una projection no admite JOINs ni cláusulas WHERE, así que todo lo que necesite una búsqueda o un filtro en tiempo de escritura pertenece a una materialized view.

El detalle de cada uno está en otro sitio: las variantes de skip index y cómo dimensionarlas en query optimization, los dos tipos de projection en la guía completa de projections, y el comportamiento en tiempo de inserción en materialized views: patrones y trampas.

285 milisegundos y 180 segundos en la misma tabla

La misma projection ligera respondió una query en 285 milisegundos y se quedó colgada 180 segundos en la siguiente, sobre la misma tabla y con la misma forma de query. Lo único que cambiaba era el valor de la cláusula WHERE.

La tabla tiene 194.000 millones de filas repartidas en 13,3 TiB sobre SharedReplacingMergeTree, con ClickHouse 26.3 y almacenamiento de objetos. Los usuarios buscan transferencias de tokens por receiver, que no está en el sorting key, así que la tabla se llevó una projection ligera: PROJECTION by_receiver_id INDEX (receiver, id) TYPE basic.

Esa fue nuestra recomendación. El almacenamiento era la restricción que mandaba, así que cogimos el escalón que no cuesta casi nada y dejamos para después subir solo si los números nos obligaban. Nos obligaron.

Con un receiver normal funciona. La projection poda hasta 26.000 filas y 369 KB, y la query vuelve en 285 milisegundos con la caché de sistema de ficheros caliente.

Entonces alguien consultó una dirección de exchange. Ese único receiver tiene 1.570 millones de filas, en torno al 0,8% de la tabla. Repartidas según el orden físico de la tabla, unas 65 de sus filas caen en cada gránulo de 8.192. Todos los gránulos contienen al menos una fila coincidente, así que ninguno se poda, y la query lee 828 GB antes de agotar el tiempo a los 180 segundos.

Eso no lo arregla ningún ajuste.

Un mecanismo de poda por gránulos solo ayuda cuando las filas coincidentes están concentradas en una parte pequeña de los gránulos, y esa concentración depende de cómo se distribuyen tus datos frente al orden de la tabla. Ni el tipo de índice ni tus ajustes influyen. Los skip indexes y las projections ligeras podan por gránulos, así que fallan con los mismos valores y por el mismo motivo.

La solución fue subir a una projection completa, ordenada por (receiver, block_time, id), para que las filas de un mismo receiver queden físicamente juntas. Eso cuesta otros 13,3 TiB. A esas alturas, la fila de almacenamiento de la tabla comparativa es toda la decisión.

La prueba lleva diez minutos. Coge tu valor de filtro más habitual y el peor de todos, la cuenta más pesada o el tenant más ruidoso. Estima cuántas filas coincidentes caen en cada gránulo para cada uno. En una tabla grande, si el peor valor coincide con la mayoría de los gránulos, ya puedes descartar el skip index y la projection ligera antes de construir ninguno, e ir directo a presupuestar el almacenamiento de una projection completa o una materialized view.

Dónde deja de funcionar cada uno

Un skip index sobre valores dispersos entre gránulos no se salta nada. Pagaste la sobrecarga de escritura y te quedaste con el mismo escaneo de antes.

Una projection puede ser más lenta que no tener ninguna. Pasados unos cuantos terabytes de tabla, el planificador abre los metadatos de la projection dentro de cada parte antes de decidir qué leer, y cada una de esas lecturas cuesta tiempo. Nos pasó en una tabla de más de 20 TB y solo llegamos a un p50 de 213 ms después de mantener en RAM todos los ficheros de marcas e índice, algo que tiene su propio artículo.

Las materialized views fallan de otra forma, y peor. Un insert fallido en la tabla destino de una MV la deja inconsistente con el origen, mientras que un fallo de projection se resuelve en segundo plano sin partir tus datos en dos. El fan-out es la otra factura: un insert que alimenta seis vistas son seis escrituras.

Lo último sale en casi todas estas conversaciones. En vez de una projection, guardar las filas dos veces en la misma tabla con dos sorting keys distintos. Cuesta el mismo disco que una projection completa, pero no escala a más patrones de acceso.

La regla que merece la pena guardarse

Arregla primero el sorting key, y luego pon precio al resto según lo que te cuesta.

Coge tu peor valor de filtro en lugar del promedio y estima las filas coincidentes por gránulo. Concentradas en unos pocos gránulos: te vale un skip index o una projection ligera, y las dos salen lo bastante baratas como para probarlas en una tarde. Repartidas por casi todos: sáltate ambas, ve a una projection completa y presupuesta el almacenamiento antes de prometerle a nadie una cifra de latencia. Necesitas un JOIN, un filtro o una retención propia: eso es una materialized view, y consultas directamente la tabla destino.

El caso intermedio es el que te muerde. Una projection ligera aguanta en staging contra datos de prueba bien repartidos, y luego se cae en producción con la única cuenta que tiene el 1% de las filas.

Si tienes projections en una tabla de más de 10 TB, te echamos un ojo encantados.

Preguntas frecuentes