Projections en ClickHouse®: La Guía Completa

El deep dive de materialized views terminó con una tabla comparativa y una frase: projections para agregaciones sencillas, MVs para todo lo demás. Este post es parte de la serie ClickHouse Deep Dive.

Esa frase no está mal, pero se salta el contexto completo. Qué son las projections por dentro, cómo las selecciona el query planner, los tres tipos disponibles y la evolución reciente que las convierte en verdaderos índices secundarios. Después de este post, sabrás exactamente cuándo una projection es suficiente y cuándo necesitas una materialized view.

Usaremos una tabla a lo largo de todo el post:

CREATE TABLE web_analytics (
    event_time DateTime,
    user_id UInt64,
    page String,
    duration UInt32,
    country LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY (user_id, event_time);

Esta tabla está ordenada por user_id, event_time. Las queries que filtran por usuario son rápidas. Las queries que filtran por country escanean todo.

Qué son las projections en ClickHouse®

Una projection no es una tabla separada. Es un ordenamiento físico alternativo de los mismos datos, almacenado dentro de la misma tabla. Piénsalo así: tu tabla es un libro ordenado por número de capítulo. Una projection es un índice al final, ordenado por tema. Mismo contenido, ruta de acceso diferente.

CREATE TABLE web_analytics (
    event_time DateTime,
    user_id UInt64,
    page String,
    duration UInt32,
    country LowCardinality(String),
    PROJECTION by_country (
        SELECT * ORDER BY country
    )
) ENGINE = MergeTree()
ORDER BY (user_id, event_time);

La projection by_country almacena las mismas filas, reordenadas por country. Cuando una query filtra por país, ClickHouse puede leer de la projection en vez de la tabla base. La query no cambia. Sigues escribiendo SELECT ... FROM web_analytics WHERE country = 'DE'. El optimizador gestiona el enrutamiento.

Sin una tabla separada que gestionar (lo que implicaría inserts diferentes y monitorización). Los datos nuevos alimentan la projection automáticamente en cada INSERT, como parte de la misma operación de escritura.

Esa es la propuesta. Ahora veamos qué pasa realmente por dentro.

Cómo funcionan las projections internamente

En esta sección estudiaremos las projections desde tres ángulos: dónde viven los datos en disco, cómo se mantienen sincronizados durante los merges y cómo el query planner decide usarlas.

Almacenamiento

Cada tabla MergeTree almacena datos en parts, que son directorios en disco. Cuando una tabla tiene una projection, cada directorio de part tiene un subdirectorio para los datos de la projection. Ese subdirectorio contiene lo que es, en la práctica, una tabla MergeTree anónima: sus propias columnas, su propio índice primario, sus propios gránulos.

El checksum del part padre incluye el checksum del part de la projection, con lo que la verificación de integridad cubre ambos.

Merges

Esta es la garantía de consistencia que importa. Los parts de la projection se fusionan simultáneamente con los parts de la tabla principal, en la misma operación de merge.

No hay lag, la projection siempre tiene los mismos datos que la fuente. Esto difiere de las materialized views, que se disparan en el INSERT pero pueden desviarse cuando ejecutas mutations, lightweight deletes o deduplicación.

Si añades una projection a una tabla existente, los parts nuevos la obtienen automáticamente. Los antiguos no. Para datos antiguos, el optimizador siempre recurre a leer la tabla base, como si la projection no existiese. Puedes materializar la projection para los parts existentes con:

ALTER TABLE web_analytics
    MATERIALIZE PROJECTION by_country;

Hasta que ejecutes esto, las queries sobre datos antiguos no se beneficiarán de la projection. Este es el error número uno: añaden una projection, no ven mejora y asumen que las projections están rotas. Comprueba system.projection_parts para ver qué está realmente materializado.

Enrutamiento de queries

El optimizador evalúa las projections por part en tiempo de query. Para cada part, comprueba si la projection tiene todas las columnas que la query necesita, estima cuántos gránulos leería de la tabla base vs la projection, y elige la opción que lea menos datos.

Múltiples projections pueden cooperar en una misma query para pruning a nivel de gránulo. Si estás en una versión anterior a v25.6, solo se evalúa una projection por query.

Puedes verificar qué projection elige el optimizador:

EXPLAIN indexes = 1, projections = 1
SELECT count()
FROM web_analytics
WHERE country = 'DE';

Dos settings de query permiten sobreescribir el optimizador cuando sea necesario:

• preferred_optimize_projection_name sugiere una projection específica
• force_optimize_projection_name la fuerza

Tres tipos de projections

No todas las projections almacenan datos de la misma forma. Una projection normal copia las filas completas, una projection aggregate pre-computa resultados GROUP BY, y las más recientes, las projections index-only, no almacenan casi nada.

Projections normales

El tipo más sencillo. Copia completa de las columnas seleccionadas en un orden de clasificación diferente. El ejemplo by_country anterior es una projection normal.

Caso de uso: tu tabla está ordenada por (user_id, event_time), pero algunas queries filtran por country. En vez de crear una materialized view que copie datos a una tabla separada ordenada por país, añades una projection. Mismos datos, orden diferente, enrutamiento automático.

El coste es almacenamiento. Una projection con SELECT * aproximadamente duplica el tamaño de la tabla. Puedes reducirlo seleccionando solo las columnas que necesitas:

PROJECTION country_duration (
    SELECT country, duration ORDER BY country
)

Esto almacena solo country y duration en la projection, no la fila completa. Más pequeña, pero las queries que necesiten otras columnas no podrán usarla.

Projections aggregate

Cuando añades un GROUP BY a la definición de una projection, ClickHouse crea un AggregatingMergeTree oculto por debajo. La projection almacena estados de agregación intermedios que se fusionan incrementalmente durante los merges de fondo.

PROJECTION country_stats (
    SELECT country, count(), sum(duration)
    GROUP BY country
)

Una query como SELECT country, count(), sum(duration) FROM web_analytics GROUP BY country puede servirse completamente desde esta projection, leyendo datos pre-agregados en vez de escanear todas las filas.

La mecánica interna es el mismo patrón -State/-Merge cubierto en el post de AggregatingMergeTree y el deep dive de materialized views. La diferencia es que ClickHouse lo gestiona todo: la conversión de estado, los merges incrementales y la reescritura de queries. Tú solo defines el GROUP BY.

Hay límites. La query debe coincidir con las funciones de agregación y las columnas GROUP BY de la projection. SELECT country, avg(duration) no usará una projection que solo tiene count() y sum(duration), aunque podrías calcular la media a partir de esas dos. El matching de expresiones es estricto.

Projections index-only (v25.5+)

Esta es la última incorporación al equipo. Las projections index-only no almacenan casi nada. En vez de copiar datos, una projection index-only almacena solo la clave de ordenación y un _part_offset, un puntero a la posición de la fila en el part de la tabla base. ClickHouse usa el índice primario de la projection para encontrar qué gránulos contienen filas coincidentes, y luego lee los datos reales de la tabla base.

PROJECTION by_country_idx (
    SELECT _part_offset ORDER BY country
)

La sobrecarga de almacenamiento es mínima: solo los valores de la columna country y los offsets, organizados con su propio índice primario. Compáralo con una projection normal que copia todas las columnas.

Eso es pruning a nivel de gránulo. En vez de escanear toda la tabla, ClickHouse lee el índice primario de la projection para identificar qué gránulos de 8.192 filas contienen filas coincidentes, y luego lee solo esos gránulos de la tabla base.

v26.1 introdujo una sintaxis simplificada, el mismo comportamiento con menos ruido.

PROJECTION by_country INDEX country TYPE basic

ClickHouse ya tiene skip indexes (bloom_filter, minmax, set, ngrambf_v1). ¿En qué se diferencian las projections index-only?

Los skip indexes adjuntan resúmenes estadísticos a cada gránulo. Un índice minmax almacena el valor mínimo y máximo por gránulo. Un bloom filter almacena una estructura probabilística de pertenencia a conjuntos. Durante la ejecución de la query, ClickHouse comprueba el resumen y salta los gránulos que definitivamente no coinciden.

La palabra "definitivamente" es la clave. Los skip indexes tienen falsos positivos. Un índice minmax sobre country sabe que el gránulo contiene valores entre 'AT' y 'US', pero si buscas 'DE', no puede saltar ese gránulo porque 'DE' cae en el rango. Un bloom filter podría tener una tasa de falsos positivos del 1%. Son rápidos y baratos, pero aproximados.

Las projections index-only funcionan de forma diferente. Reordenan los punteros por la clave ORDER BY de la projection y construyen un índice primario sobre ese orden. La búsqueda es precisa: ClickHouse navega el índice primario para encontrar exactamente qué gránulos contienen 'DE', con cero falsos positivos.

	Skip indexes	Projections index-only
Coste de almacenamiento	Casi gratuito (metadatos por gránulo)	Pequeño (punteros reordenados + índice primario)
Precisión de pruning	Aproximada (resúmenes estadísticos)	Precisa (búsqueda por índice primario)
Falsos positivos	Sí	No
Sobrecarga de escritura	Mínima	Algo (materialización de punteros en INSERT)
Ideal para	Filtros de rango, exclusión de alta cardinalidad	Búsquedas exactas, órdenes de clasificación alternativos

Cuando un skip index te da una reducción de 10x pero necesitas 1000x, una projection index-only es el siguiente paso. Cuando un bloom filter sobre una columna de baja cardinalidad salta el 80% de los gránulos pero necesitas saltar el 99,9%, el pruning preciso de una projection index-only cierra la brecha.

El tradeoff es el coste, ya que los skip indexes son prácticamente gratuitos. Las projections index-only requieren almacenar y mantener los punteros reordenados. Para la mayoría de tablas, empieza con skip indexes. Si el pruning no es lo bastante selectivo, añade una projection index-only.

Limitaciones y errores comunes

Las projections tienen límites estrictos.

Las restricciones funcionales son claras: no se permiten JOINs en las definiciones de projection, no se admiten cláusulas WHERE (todas las filas se materializan), no hay encadenamiento equivalente a MVs en cascada, y no hay compatibilidad con FINAL. Las queries con FINAL ignoran las projections por completo, así que si usas ReplacingMergeTree o CollapsingMergeTree con FINAL, las projections no ayudarán.

El matching de expresiones es estricto. La query debe usar las mismas expresiones que la definición de la projection. Una projection con count() y sum(duration) no será utilizada por una query que pida avg(duration), aunque la matemática sea trivial.

Las mutations son una preocupación aparte. Por defecto, los lightweight deletes en tablas con projections lanzan un error. Desde v24.7, puedes configurar esto con lightweight_mutation_projection_mode: throw (por defecto), drop (elimina los parts de la projection, se reconstruyen en el siguiente merge) o rebuild (reconstruye inmediatamente, más lento).

El error que más gente comete: la materialización no es automática para datos existentes. Añadir una projection solo afecta a los inserts nuevos. Los datos antiguos necesitan ALTER TABLE ... MATERIALIZE PROJECTION. En tablas grandes, es una operación pesada. Comprueba el progreso en system.mutations.

La sobrecarga de almacenamiento depende del tipo. Las projections normales pueden duplicar el tamaño de tu tabla. Las projections aggregate suelen ser mucho más pequeñas (una fila por combinación de clave GROUP BY). Las projections index-only son las más ligeras. Y las projections comparten el TTL de la tabla padre, con lo que no puedes expirar datos de la projection en un calendario diferente.

Projections vs materialized views: cuándo usar cada una

El post de materialized views tenía una tabla comparativa de 8 filas. Aquí está la versión ampliada con los matices que importan en la práctica.

	Projections	Materialized views
Qué hace	Reordena o pre-agrega datos existentes	Transforma, filtra, une, enruta datos
Gestión de tablas	Ninguna (parte de la tabla origen)	Tabla(s) destino separada(s) que gestionar
Cambios en queries	Ninguno (el optimizador enruta de forma transparente)	Hay que consultar la tabla destino directamente
Filtrado	Sin WHERE en la definición	WHERE soportado
JOINs	No soportados	Totalmente soportados
Pipelines multi-etapa	No es posible	Encadena MVs libremente
Consistencia	Atómica (nivel de merge, nunca se desvía)	Solo trigger en INSERT, puede desviarse con mutations
Control de almacenamiento	Compartido con la tabla padre	Engine, TTL y particionado independientes
Mutations	Problemáticas (necesita config especial)	No se ven afectadas por mutations de la fuente
Backfill	MATERIALIZE PROJECTION	INSERT INTO ... SELECT manual
Monitorización	Difícil (oculta, requiere EXPLAIN)	Fácil (tabla separada, queries directas)
Sobrecarga de escritura	Parte de la misma operación de escritura	INSERT separado en la tabla destino

La decisión se reduce a una pregunta: ¿los datos necesitan cambiar de forma?

Si no, una projection lo resuelve. Mismos datos, ruta de acceso diferente. Si sí, eso es una materialized view: JOINs, filtrado, enrutamiento a un engine diferente, TTL independiente.

Para cerrar

Las projections son la herramienta de optimización silenciosa: sin tablas nuevas que gestionar, sin cambios en las queries. Cubren los casos donde tus datos están bien pero tu patrón de acceso no coincide con la primary key. Las projections index-only en v25.5+ debilitan el argumento del almacenamiento frente a lo que era antes.

Las materialized views siguen siendo la herramienta adecuada cuando los datos necesitan cambiar de forma: JOINs, filtrado, enrutamiento, ciclos de vida independientes. El deep dive de MVs cubre esos patrones en profundidad.

Siguiente en la serie: optimización de queries, qué hace que ClickHouse sea rápido a nivel de query y qué lo mata.