MergeTree en ClickHouse: gránulos, partes e índice disperso

MergeTree en ClickHouse: Cómo el motor divide la analítica en gránulos y fusiona partes

Siete años de dolor, tres producciones perdidas y una placa arquitectónica

Cuando escuché por primera vez sobre MergeTree, pensé: "Otro motor con un nombre de moda". Luego, en producción, una tabla con 500 millones de apuestas empezó a ralentizar consultas que antes volaban. Miramos EXPLAIN y vimos Read 250000 granules. En ese entonces, no sabía qué era un gránulo.

Resulta que creé una tabla con el ORDER BY incorrecto. Cada consulta escaneaba el 80% de todos los datos, aunque solo filtrara por una columna.

MergeTree no es solo un motor. Es una arquitectura que determina cómo tus datos se almacenan en disco, cómo se comprimen y, lo más importante, cómo ClickHouse decide qué fragmentos leer y cuáles saltar. Entender los internos me salvó tres proyectos. A continuación, un mapa que he seguido durante cinco años.

1. Parte, Gránulo, Marca: Una Matrioska en Disco

ClickHouse no almacena una tabla como un único archivo. Divide los datos en partes, dentro de cada parte en gránulos, y navega usando marcas.

Estructura en disco de la tabla bets:
/var/lib/clickhouse/data/betting/bets/
├── 202401_1_1_0/          # parte #1 (enero 2024)
│   ├── user_id.bin        # columna user_id (datos binarios)
│   ├── user_id.mrk        # marcas para user_id
│   ├── created_at.bin
│   ├── created_at.mrk
│   ├── amount.bin
│   ├── amount.mrk
│   └── ...
├── 202401_2_2_0/          # parte #2
└── 202402_3_3_0/          # parte #3 (febrero)

Parte — la unidad más pequeña gestionada por MergeTree. Cada parte se crea al insertar, luego se fusiona en segundo plano con partes vecinas.

Gránulo — un bloque de datos de index_granularity filas (por defecto 8192). ClickHouse lee datos en gránulos completos. No se puede leer una sola fila, solo un gránulo entero.

Marca — un puntero a la posición de un gránulo en el archivo .bin. El archivo .mrk contiene el desplazamiento: dónde comienza el gránulo en disco y su desplazamiento.

Por qué importa: cuando ejecutas SELECT amount FROM bets WHERE user_id = 123, ClickHouse usa el índice disperso para determinar qué gránulos podrían contener este user_id y solo lee esos. Ni siquiera abre los otros gránulos.

2. Fusión de Partes: Por Qué ClickHouse No se Rompe con un Millón de Inserts Pequeños

Cada INSERT crea una nueva parte en disco. Si insertas 100 registros 10,000 veces, tendrás 10,000 partes. Eso es un desastre: una consulta tendría que abrir 10,000 archivos.

Cómo ClickHouse salva el día:

El proceso de fusión en segundo plano une partes pequeñas en otras más grandes. Por ejemplo:

• 10 partes de 1 GB cada una → 1 parte de 10 GB

Parámetros que ajusto en producción:

<merge_tree>
    <min_rows_for_wide_part>100000</min_rows_for_wide_part>
    <max_bytes_for_merge>100000000000</max_bytes_for_merge>  <!-- 100 GB -->
    <merge_with_ttl_timeout>3600</merge_with_ttl_timeout>
</merge_tree>

Consejo profesional: si haces un insert grande (1M+ filas), la parte no se fusionará con otras hasta que aparezca una parte vecina. ClickHouse almacena las partes en orden ascendente de clave, así que INSERT ... ORDER BY ayuda.

Dónde me quemé: transmitíamos apuestas vía Kafka a 10-50 registros por segundo. Después de una semana, teníamos 300,000 partes. Las consultas se ralentizaron porque cada consulta abría todos los archivos. Lo solucionamos elevando min_rows_for_wide_part a 500k y aumentando max_insert_block_size a 1M. El flujo de datos tuvo que ser almacenado en búfer en Kafka, pero las partes se redujeron a 500.

3. PRIMARY KEY vs ORDER BY: El Error Más Común de Principiantes

En MySQL, PRIMARY KEY es un identificador único. En ClickHouse, no exactamente.

-- Veo esto todo el tiempo
CREATE TABLE bets (
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
) ENGINE = MergeTree()
PRIMARY KEY (user_id)      -- ← error
ORDER BY (user_id);        -- ← también error

La verdad:

• ORDER BY determina el orden físico de las filas en disco. Obligatorio.
• PRIMARY KEY es igual a ORDER BY si no se especifica. Pero puede ser un PREFIJO de ORDER BY.

Correcto:

ORDER BY (created_at, user_id)   -- tiempo primero, luego usuario
PRIMARY KEY (created_at)         -- índice solo en tiempo

Qué sucede: ClickHouse construye un índice disperso basado en ORDER BY. PRIMARY KEY solo indica qué parte de ORDER BY usar para filtrar.

Ejemplo real de nuestra producción:

-- Incorrecto (lento)
ORDER BY (user_id, created_at)
-- Consulta: encontrar apuestas de la última hora. El índice no ayuda, escaneamos todo.

-- Correcto (rápido)
ORDER BY (created_at, user_id)
-- Consulta: salta a la fecha requerida mediante el índice, luego filtra por user_id dentro

4. Índice Disperso: Cómo 8192 Filas se Convierten en una Entrada de Índice

ClickHouse NO construye un índice para cada fila. Toma un gránulo (8192 filas) y escribe en el índice:

• valor mínimo de ORDER BY en ese gránulo
• valor máximo de ORDER BY

Eso es todo. No es un B-tree, ni una tabla hash, solo un simple array de pares min-max.

Cómo acelera una consulta:

-- Encontrar apuestas de 5 minutos
SELECT * FROM bets WHERE created_at BETWEEN '2024-03-15 14:00:00' AND '2024-03-15 14:05:00';

-- El índice (disperso) verifica cada gránulo:
-- Gránulo 1: min='2024-03-15 13:00:00' max='2024-03-15 14:00:00' → NO coincide (max < 14:05?)
-- Gránulo 2: min='2024-03-15 14:00:00' max='2024-03-15 15:00:00' → COINCIDE (min <= 14:05)
-- Gránulo 3: min='2024-03-15 15:00:00' max='2024-03-15 16:00:00' → NO COINCIDE (min > 14:05)

Por qué es rápido: el índice ocupa (número de gránulos) * 16 bytes. Para 1 mil millones de filas, eso es ~1.9 millones de gránulos → 30 MB de índice. El índice completo cabe en memoria.

5. Particionamiento: Salta al Mes Correcto

PARTITION BY es la regla por la cual ClickHouse coloca partes en diferentes directorios en disco.

PARTITION BY toYYYYMM(created_at)  -- particiones mensuales

En disco:

/var/lib/clickhouse/data/betting/bets/
├── 202401/   # enero 2024
├── 202402/   # febrero 2024
└── 202403/   # marzo 2024

Cómo acelera las consultas:

SELECT * FROM bets WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- ClickHouse va directamente a la carpeta 202402/, ni siquiera abre otras particiones

Cuándo el particionamiento no ayuda:

• Particiones pequeñas (por día con 100 millones de filas por día → 365 particiones, cada una 300 MB → muchos archivos)
• Filtro no está en la clave de partición

Mi elección: toYYYYMM() para 10–100 millones de filas por mes, toYYYYMMDD() si 1+ mil millones por día (pero entonces necesitas un clúster).

6. Formato .bin y .mrk: Cómo los Datos Yacen en Disco

Una vez husmeé en un directorio de tabla y vi:

$ ls -la /var/lib/clickhouse/data/betting/bets/202401_1_1_0/
-rw-r----- 1 clickhouse clickhouse 1.2G  user_id.bin
-rw-r----- 1 clickhouse clickhouse  12M  user_id.mrk
-rw-r----- 1 clickhouse clickhouse 900M  created_at.bin
-rw-r----- 1 clickhouse clickhouse  12M  created_at.mrk
-rw-r----- 1 clickhouse clickhouse 2.1G  amount.bin
-rw-r----- 1 clickhouse clickhouse  12M  amount.mrk

• .bin — datos reales de la columna, comprimidos con LZ4 (o ZSTD si está configurado)
• .mrk — marcas: posición de cada gránulo en .bin

Cómo se lee:

1. La consulta quiere la columna amount para user_id=123
2. El índice disperso dice: este user_id podría estar en los gránulos #45, #46, #47
3. ClickHouse abre user_id.mrk, toma el desplazamiento para el gránulo #45
4. Va a user_id.bin en ese desplazamiento, lee 8192 valores
5. Encuentra filas con el user_id deseado, recuerda los números de fila
6. Usando los números de fila, calcula posiciones en amount.mrk y lee solo los bytes necesarios de amount.bin

Conclusión: físicamente, los datos se leen solo para las columnas requeridas y solo para los gránulos requeridos. Todo lo demás es metadatos.

7. Ejemplo de ORDER BY Correcto en una Tabla de Apuestas

ORDER BY incorrecto (lo hice yo):

CREATE TABLE betting.bets_wrong
(
    user_id UInt64,
    created_at DateTime64(3),
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- índice por usuario primero

Problema: el 90% de las consultas en nuestro proyecto son "mostrar apuestas de la última hora" (filtrar por tiempo). El índice no ayuda porque user_id cambia más rápido que el tiempo. ClickHouse escanea todas las particiones.

ORDER BY correcto:

CREATE TABLE betting.bets_correct
(
    user_id UInt64,
    created_at DateTime64(3),
    amount Decimal(18,2),
    sport LowCardinality(String),
    outcome Enum8('win'=1,'loss'=2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);   -- índice por tiempo primero

Ahora:

• Consulta por rango de fechas: salta instantáneamente a los gránulos correctos
• Dentro de una fecha, puedes filtrar por user_id
• Opcionalmente, puedes agregar un SECONDARY INDEX (pero esa es otra historia)

8. EXPLAIN indexes = 1: Ve Cuántos Gránulos se Leen Realmente

La herramienta de depuración más útil:

EXPLAIN indexes = 1
SELECT user_id, sum(amount)
FROM betting.bets
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'
  AND user_id = 100500
GROUP BY user_id;

Salida:

Expression (Projection)
  Aggregating
    Expression
      ReadFromMergeTree (betting.bets)
        Indexes:
          Partition key:   partition_idx   (1/3 particiones, 1 leída)
          Primary key:     created_at      (42/500 gránulos, 42 leídos)
          MinMax:          created_at      (0 saltados, 1 leído)

Lo que vemos: de 500 gránulos en la partición, solo se leyeron 42. Grado de filtrado: 8%. Sin el ORDER BY correcto, serían 500 de 500.

9. Comandos para Gestionar Particiones

Ver todas las particiones:

SELECT 
    partition,
    name,
    rows,
    bytes_on_disk,
    modification_time
FROM system.parts
WHERE table = 'bets' AND active = 1;

Eliminar una partición antigua (más rápido que DELETE):

ALTER TABLE betting.bets DROP PARTITION '202401';

Limpia el disco al instante. DELETE FROM elimina fila por fila, luego fusiona — diferencia de horas.

Desmontar una partición (sin eliminar datos):

ALTER TABLE betting.bets DETACH PARTITION '202402';
-- datos movidos a detached/

Volver a montarla:

ALTER TABLE betting.bets ATTACH PARTITION '202402';

Copiar una partición a otra tabla (caso real):

ALTER TABLE betting.bets_archive REPLACE PARTITION '202401' FROM betting.bets;

10. OPTIMIZE TABLE — Cuándo No es Necesario (y Cuándo Sí lo es)

OPTIMIZE TABLE fuerza una fusión manual de partes.

Malas noticias: la mayoría de los artículos recomiendan ejecutarlo periódicamente. Buenas noticias: en el 99% de los casos, no es necesario. ClickHouse fusiona en segundo plano automáticamente.

Cuándo realmente usé OPTIMIZE:

• Después de cargar un gran bloque de datos históricos (100 millones de filas en un solo insert) — para que otras particiones no esperen una fusión programada
• Antes de hacer una copia de seguridad, para reducir el número de archivos en la tabla
• Pruebas — para ver el tamaño real después de la compresión

Cómo hacerlo de forma segura:

OPTIMIZE TABLE betting.bets PARTITION '202403' FINAL;

FINAL fusiona todas las partes en una para esa partición. Sin FINAL — solo las partes que ya están listas.

Mi consejo: no toques OPTIMIZE en scripts automatizados. Las fusiones en segundo plano están bien ajustadas. Si las partes no se fusionan, revisa max_bytes_to_merge y el espacio libre en disco.

Qué Sigue

MergeTree es el corazón de ClickHouse. Ahora sabes cómo late. Próximo artículo — sobre indexación avanzada: skip indexes, columnas materializadas y proyecciones.

---

← Anterior: ClickHouse: Referencia completa de tipos de datos para análisis de apuestas (lo que me costó caro)
→ Siguiente: Carga de datos en ClickHouse: Cómo dejé de insertar una fila a la vez y aceleré la ingesta 500 veces

— Editorial Team