ClickHouse: índice vs projection vs vista materializada
En ClickHouse®, elige según lo que te cuesta cada mecanismo. Un índice de salto (data-skipping index) cuesta metadatos por gránulo. Una projection ligera cuesta la sort key y un puntero _part_offset. Una projection completa cuesta más o menos el doble de almacenamiento. Una vista materializada cuesta tiempo de escritura y es la única que puede cambiar la forma de los datos. Antes de recurrir a ninguno, revisa la sort key.
Desde la 25.5, una projection ligera guarda la sort 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 tiene más que ver con un índice de salto que con la projection completa con la que comparte sintaxis.
Si eliges mal entre esas dos, acabas con una factura de almacenamiento doble que no necesitabas o con una query que sigue lenta toques lo que toques.
Revisa la sort key antes que nada
La mayoría de las tablas a las que se les añade una projection no la necesitaban.
La sort key va antes de la escalera. Cambiarla obliga a reconstruir la tabla, y eso la deja en otra liga respecto a cualquier cosa que puedas añadir con ALTER TABLE. Entre un índice de salto y una projection eliges; el orden de la tabla lo decidiste al crearla.
Las mejoras baratas están en el orden de las columnas dentro de esa clave. Hemos visto casos en los que ese único cambio supone leer 400 veces menos datos para la misma query, sin objetos nuevos que mantener ni nada más que sincronizar. Con PREWHERE pasa lo mismo: es gratis, viene activado por defecto en la mayoría de los casos y conviene comprobarlo antes de meter más piezas.
Cómo funcionan los dos lo tienes en optimización de queries: qué hace rápido a ClickHouse. Si tu ORDER BY ya encaja con el patrón de acceso y la query sigue lenta, sigue leyendo.
Cómo elegir entre índice, projection y vista materializada
Un índice de salto le dice a ClickHouse qué gránulos puede ignorar. Una projection le da otro conjunto de gránulos que leer. Una vista materializada le da directamente otra tabla, que se escribe en el insert.
| Índice de salto | Projection ligera | Projection completa | Vista materializada | |
|---|---|---|---|---|
| Coste de almacenamiento | Metadatos por gránulo | Sort key + _part_offset | ~2x la tabla | Lo que ocupe la tabla destino |
| Coste de escritura | Pequeño, por índice | Pequeño | Los datos se escriben dos veces | Trigger en el insert |
| Cambia la forma de los datos | No | No | No | Sí: JOINs, filtros, enrutado |
| Hay que tocar la query | No | No | No | Consultar la tabla destino |
| Consistencia | Atómica con la parte | Atómica con la parte | Atómica con la parte | Se desincroniza si falla un insert |
| Encadenable | No aplica | No | No | Sí |
| TTL propio | No | No | No | Sí |
| Deja de servir cuando | Los valores se dispersan por gránulos | Todos los gránulos coinciden | Se acaba el presupuesto de almacenamiento | Fan-out en el insert |
Casi toda la decisión está en dos filas. Cambiar la forma de los datos es la línea roja: la definición de una projection no admite JOINs ni cláusulas WHERE, así que todo lo que necesite un lookup o un filtro al escribir le toca a una vista materializada.
Cada uno tiene su artículo a fondo: las variantes de índice de salto y cómo dimensionarlas en optimización de queries, los dos tipos de projection en la guía completa de projections y el comportamiento en el insert en vistas materializadas: patrones y errores típicos.
285 milisegundos y 180 segundos en la misma tabla
La misma projection ligera resolvió una query en 285 milisegundos y dio timeout a los 180 segundos en la siguiente, con la misma tabla y la misma forma de query. Lo único que cambiaba era el valor del WHERE.
La tabla tiene 194.000 millones de filas y 13,3 TiB sobre SharedReplacingMergeTree, con ClickHouse 26.3 sobre object storage. Los usuarios buscan transferencias de tokens por receiver, que no está en la sort key, así que le añadimos una projection ligera: PROJECTION by_receiver_id INDEX (receiver, id) TYPE basic.
Fue recomendación nuestra. El almacenamiento era la restricción que mandaba; por eso fuimos al escalón que no cuesta casi nada, con la idea de subir solo si los números nos obligaban. Nos obligaron.
Con un receiver normal funciona. La projection poda hasta quedarse en 26.000 filas y 369 KB, y la query responde en 285 milisegundos con la caché del sistema de ficheros ya caliente.
Hasta que alguien consultó la dirección de un exchange. Ese receiver tiene él solo 1.570 millones de filas, más o menos el 0,8 % de la tabla. Con el orden físico de la tabla, eso son unas 65 filas suyas en cada gránulo de 8.192. Todos los gránulos tienen al menos una fila que coincide, así que no se descarta ninguno, y la query lee 828 GB antes de dar timeout a los 180 segundos.
Eso no se arregla con ningún setting.
Un mecanismo que poda gránulos solo ayuda si las filas que buscas están concentradas en una fracción pequeña de ellos, y esa concentración depende de cómo se reparten tus datos respecto al orden de la tabla. Ni el tipo de índice ni el tuning cambian nada ahí. Los índices de salto y las projections ligeras podan gránulos, de ahí que fallen 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 cada receiver queden físicamente juntas. Eso son unos 13,3 TiB más. Llegados ahí, la fila de almacenamiento de la tabla comparativa es la decisión entera.
La prueba lleva diez minutos. Coge tu valor de filtro más habitual y el peor: la cuenta más pesada o el tenant más ruidoso. Calcula a ojo cuántas filas coincidentes caen en cada gránulo para cada uno. En una tabla grande, si el peor valor aparece en la mayoría de los gránulos, el índice de salto y la projection ligera quedan descartados antes de crear ninguno, y puedes ir directo a presupuestar el almacenamiento de una projection completa o de una vista materializada.
Dónde deja de servir cada uno
Un índice de salto sobre valores repartidos por todos los gránulos no se salta nada. Pagas la sobrecarga de escritura y te quedas con el mismo escaneo que ya tenías.
Una projection puede ir más lenta que no tener ninguna. A partir de unos cuantos terabytes de tabla, el planner abre los metadatos de la projection en cada parte antes de decidir qué leer, y cada una de esas lecturas lleva su tiempo. Nos pasó en una tabla de más de 20 TB y solo bajamos a un p50 de 213 ms tras dejar todos los ficheros de marks e índices residentes en RAM, que da para un artículo aparte.
Las vistas materializadas fallan de otra manera, y peor. Si falla un insert en la tabla destino, esta se desincroniza del origen; un fallo de projection, en cambio, se resuelve en segundo plano sin partirte los datos en dos. El fan-out es la otra factura: un insert que alimenta seis vistas son seis escrituras.
La última opción sale en casi todas estas conversaciones: en vez de una projection, guardar las filas dos veces en la misma tabla con dos sort keys distintas. Ocupa el mismo disco que una projection completa, pero no escala cuando aparecen más patrones de acceso.
La regla que conviene guardarse
Primero arregla la sort key; después, valora el resto por lo que te cuesta.
Coge tu peor valor de filtro, no el medio, y calcula cuántas filas coincidentes hay por gránulo. Si están concentradas en unos pocos gránulos, te vale un índice de salto o una projection ligera, y los dos son tan baratos que puedes probarlos en una tarde. Si están repartidas por casi todos, olvídate de ambos, ve a una projection completa y presupuesta el almacenamiento antes de prometerle a nadie una cifra de latencia. Si necesitas un JOIN, un filtro o una retención propia, eso es una vista materializada, y consulta directamente la tabla destino.
El caso intermedio es el que te pilla. Una projection ligera aguanta en staging con datos de prueba bien repartidos y luego se cae en producción por esa única cuenta que tiene el 1 % de las filas.
Si tienes projections en una tabla de más de 10 TB, la revisamos contigo encantados.
Preguntas frecuentes
No. Un mecanismo que poda gránulos necesita que las filas que buscas estén concentradas en una fracción pequeña de ellos, y ningún setting cambia cómo se distribuyen tus datos. Te quedan dos opciones: una projection completa ordenada por esa columna o una vista materializada con su propio orden. Las dos cuestan almacenamiento.
Ocupa lo mismo que una projection completa. Pierdes el enrutado automático de queries, así que tu aplicación tiene que saber qué copia leer, y pierdes la consistencia atómica entre las dos. Una projection completa te da ambas cosas por el mismo disco.
Sí. Una projection completa escribe los datos dos veces, así que cuenta con un coste de escritura de más o menos el doble y pruébalo con tu ritmo de ingesta antes de dar el paso. Una projection ligera solo escribe la sort key y un puntero a la fila, con lo que la sobrecarga es mucho menor.
Compruébalo con EXPLAIN indexes=1, projections=1. Además, hay dos settings que filtran a nivel de parte y que, con sus valores por defecto, pueden desactivar la poda por el índice de la projection en tablas con muchas partes pequeñas.
No. La definición de una projection no admite JOINs ni cláusulas WHERE, aunque las queries sobre una tabla con projections sí pueden usar ambos. Todo lo que necesite un lookup o un filtro al escribir es una vista materializada.
Seguir Leyendo
Publicado originalmente en obsessionDB. Lee el artículo original aquí.
ClickHouse is a registered trademark of ClickHouse, Inc. https://clickhouse.com