CollapsingMergeTree en ClickHouse: actualizar sin UPDATE

CollapsingMergeTree: Cómo Actualizar Agregados Sin UPDATE en ClickHouse

1. Por Qué se Necesita CollapsingMergeTree — El Problema de Actualizar Agregados

Volvamos a nuestro casino online. Cada jugador tiene un saldo. Cuando un jugador hace una apuesta, el saldo disminuye. Cuando gana, aumenta. Cuando un administrador cancela una transacción sospechosa, el saldo cambia de nuevo.

En una base de datos normal (PostgreSQL), simplemente harías UPDATE players SET balance = balance - 100 WHERE user_id = 123. Simple y claro.

Pero ClickHouse no puede actualizar datos. En absoluto. Punto. ¿Por qué? Porque ClickHouse está diseñado para analítica, donde los datos son de solo añadidura. Las actualizaciones son problemáticas para el almacenamiento columnar, donde los datos residen en bloques comprimidos. Para cambiar una sola celda, habría que reescribir bloques enteros.

Entonces, ¿cómo cambias el saldo? No modificas el registro antiguo. Añades un NUEVO registro que dice: "cancelar el cambio anterior" y "añadir el nuevo". Esto se llama materializar cambios mediante cancelación.

Analogía de la vida real: Imagina un libro de contabilidad donde todas las entradas están escritas con tinta. No puedes borrar y corregir. En su lugar, escribes al final: "Fila n.º 45 — errónea, cancelada. Nueva fila n.º 46 — cantidad correcta." Luego, al calcular el total, lees todas las filas teniendo en cuenta las cancelaciones.

CollapsingMergeTree es un motor de ClickHouse que colapsa automáticamente pares de "+1" y "-1" durante las fusiones en segundo plano. Actúa como ese contable: ve un par cancelador y descarta ambas filas.

2. El Principio de la Columna sign — La Matemática de la Cancelación

La idea principal de CollapsingMergeTree es una columna especial sign con dos valores posibles:

• +1 — "añadir" (versión actual)
• -1 — "cancelar" (versión obsoleta)

Durante una fusión de partes de datos, ClickHouse busca pares de filas con la misma clave de ordenamiento (ORDER BY) donde una tiene sign = +1 y la otra sign = -1. Cuando se encuentra dicho par, ambas filas se eliminan. Solo quedan las filas sin par, es decir, aquellas sin cancelación.

Por qué funciona: Cualquier cambio de datos se representa como cancelar la versión antigua y añadir una nueva. El par (+1, -1) suma cero. Cuando haces SUM(amount * sign), las versiones antiguas se cancelan y las nuevas permanecen.

Analogía con la contabilidad por partida doble: En contabilidad, cada transacción se registra dos veces: debe y haber. CollapsingMergeTree hace lo mismo: cada cambio tiene su opuesto. Al sumar, se cancelan mutuamente.

3. CREATE TABLE — Desglosándolo

-- Crear una tabla para el saldo del jugador con historial de cambios
CREATE TABLE player_balance
(
    user_id      UInt64,              -- ID del jugador
    date         Date,                -- Fecha del cambio de saldo
    amount       Int64,               -- Cambio de saldo (+100, -50, etc.)
    balance_after Int64,              -- Saldo después de la operación (opcional)
    sign         Int8,                -- +1 — añadir, -1 — cancelar
    updated_at   DateTime DEFAULT now()  -- Marca de tiempo de la operación
)
ENGINE = CollapsingMergeTree(sign)    -- Motor de colapso, especificar columna sign
ORDER BY (user_id, date)              -- Clave para agrupar y colapsar

Lo importante aquí:

• ENGINE = CollapsingMergeTree(sign) — el único parámetro obligatorio es el nombre de la columna sign (normalmente sign o is_active). Esta columna debe ser de tipo Int8 (entero de -128 a 127), pero solo se usan +1 y -1.

• ORDER BY (user_id, date) — las columnas en esta clave determinan qué filas se consideran un "par". Dos filas con valores idénticos en todas las columnas de ORDER BY y sign opuesto (+1 y -1) se colapsarán.

¿Qué pasa si ORDER BY no incluye todos los campos necesarios? Por ejemplo, si no incluyes user_id, se podrían colapsar filas de diferentes usuarios — un desastre. Todos los campos por los que quieras distinguir operaciones deben estar en ORDER BY.

¿Por qué no PRIMARY KEY? Mismas razones que con otros motores MergeTree — ORDER BY controla el orden físico y la fusión, mientras que PRIMARY KEY (si se especifica) solo controla el índice.

4. Inserción — Cómo Actualizar Datos Correctamente

Supongamos que un jugador tiene un saldo de 1000 rublos. Hace una apuesta de 100 rublos. En lugar de cambiar el saldo en una sola fila, haces dos inserciones:

-- Paso 1: Cancelar la versión antigua del saldo (era 1000, ahora debería ser 900)
-- Versión antigua: user_id=123, date='2025-06-01', amount = 1000 (saldo ANTES de la operación)
-- Para cancelar, inserta una fila con sign = -1
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', 1000, 1000, -1, now());   -- Cancelar saldo antiguo

-- Paso 2: Añadir la nueva versión del saldo (ahora 900 después de la apuesta)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());   -- Nueva versión: cambio -100, resultado 900

Pero esto es incómodo. En la práctica, es más fácil pensar en términos de "cambios" en lugar de "saldo completo". Aquí hay un patrón más típico:

-- El jugador hace una apuesta de 100 rublos (el saldo disminuye)
-- Inserta solo una fila con sign = +1, donde amount es el cambio de saldo (-100)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());

-- Si necesitas cancelar esta apuesta (p. ej., por un error técnico)
-- Inserta un par cancelador
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, -1, now()),   -- Cancelar apuesta
    (123, '2025-06-01', +100, 1000, +1, now());   -- Restaurar saldo

Por qué funciona: Cuando llegue el momento de la fusión, ClickHouse encontrará un par de filas con el mismo user_id, date y amount (si amount está en ORDER BY) y diferente sign — y las eliminará. Solo quedará el saldo actual.

Importante: Debes asegurar la corrección de los pares tú mismo. ClickHouse no verifica que la suma de los cambios cuadre. Simplemente colapsa filas con signo opuesto y la misma clave ORDER BY.

5. SELECT con SUM de sign — Cómo Leer Correctamente

Al leer datos, debes agregar teniendo en cuenta el signo. El patrón principal:

-- Obtener el saldo actual de cada jugador
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM player_balance
WHERE sign != 0   -- Filtrar ceros accidentales (no deberían existir)
GROUP BY user_id;

Desglosando la lógica:

• amount * sign — si sign = +1, el término es amount; si sign = -1, el término es -amount (cancela el anterior)
• SUM(...) — todos los pares canceladores se anulan entre sí en la suma
• GROUP BY user_id — agregar por usuario

¿Por qué no FINAL? A diferencia de ReplacingMergeTree, para CollapsingMergeTree siempre usas agregación con SUM(amount * sign). Esto funciona correctamente tanto antes como después de las fusiones porque la matemática del signo no depende de si las filas están físicamente colapsadas o no.

Ejemplo real:

-- Datos iniciales (antes de la fusión):
-- (123, -100, +1)   — apuesta 100 rublos
-- (123, -100, -1)   — cancelar apuesta
-- (123, +100, +1)   — restaurar saldo

-- Consulta: SUM(amount * sign) = (-100*1) + (-100*-1) + (100*1) = -100 + 100 + 100 = 100
-- Correcto: el saldo aumentó en 100 (la cancelación de la apuesta devolvió el dinero)

Si quieres ver el historial sin agregación (p. ej., todas las operaciones en orden cronológico), simplemente SELECT * mostrará todas las filas, incluidas las canceladas. Es normal — está diseñado así.

6. Dificultades — El Problema del Orden de las Filas

La trampa más grande: CollapsingMergeTree requiere que las filas con la misma clave lleguen en el orden correcto — primero +1, luego -1 (¿o viceversa? Averigüémoslo).

ClickHouse no verifica marcas de tiempo. Observa el orden de las filas dentro de cada parte durante la fusión. Si una parte contiene un par (+1, -1), lo colapsará. Pero si +1 está en una parte y -1 en otra, no se colapsarán hasta que esas partes se fusionen en una (lo que puede llevar un tiempo).

¿Por qué es esto un problema en un sistema distribuido?

Imagina que tus datos llegan a través de Kafka (una cola de mensajes) desde tres servidores diferentes. El servidor n.º 1 envió "apuesta 100 rublos" (+1). El servidor n.º 2 envió "cancelar apuesta" (-1). El servidor n.º 3 envió "restaurar saldo" (+1). Pueden terminar en diferentes partes de ClickHouse en diferentes órdenes.

Si una parte contiene solo +1 y otra contiene -1, el saldo será temporalmente incorrecto (la suma mostrará dinero extra). Cuando las partes se fusionen, los datos se corregirán, pero eso podría llevar una hora.

Analogía: Es como poner cartas en dos carpetas diferentes. Una carpeta tiene "deuda 100 rublos" (+1), la otra tiene "deuda perdonada" (-1). Hasta que fusiones las carpetas en una, tu sistema contable pensará que te deben 100 rublos.

7. VersionedCollapsingMergeTree — Resolviendo el Problema del Orden

Para superar el problema del orden, los desarrolladores de ClickHouse añadieron VersionedCollapsingMergeTree. Añade una tercera columna — versión (normalmente version o timestamp).

CREATE TABLE player_balance_versioned
(
    user_id  UInt64,
    date     Date,
    amount   Int64,
    version  UInt64,          -- Número de versión monótonamente creciente
    sign     Int8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)   -- ¡Dos parámetros!
ORDER BY (user_id, date);

Cómo funciona:

• Durante una fusión, ClickHouse busca pares (+1, -1) con la misma clave ORDER BY y la misma versión.
• Si las filas tienen la misma clave pero diferentes versiones, NO se colapsan. En su lugar, permanece la fila con la versión más alta (el estado más reciente).
• La versión permite un manejo correcto incluso si las filas llegan desordenadas — siempre que la cancelación tenga la misma versión que la operación original.

Por qué resuelve el problema del orden: Incluso si +1 llega después de -1, ClickHouse verá que tienen versiones diferentes (o la misma — entonces colapsa). Si las versiones son iguales, los pares se colapsan independientemente del orden físico en las partes. Si las versiones difieren, permanece la más nueva.

Ejemplo de uso de versiones:

-- Operación 1: apuesta 100 rublos (versión 1001)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, +1);

-- Cancelar la misma apuesta (misma versión 1001, sign = -1)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, -1);

-- Nueva apuesta correcta de 50 rublos (versión 1002)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -50, 1002, +1);

Ahora, incluso si las tres filas llegan en diferentes órdenes y a diferentes partes, durante la fusión con las mismas versiones, los pares se colapsarán. Solo queda la apuesta de 50 rublos con versión 1002.

8. Caso de Uso Real: Saldo de Jugador en Tiempo Real en un Casino

Imagina que tienes un microservicio "Saldo" que debe mostrar el saldo actual del jugador al céntimo con un retraso de no más de 5 segundos.

Flujo de trabajo:

-- Tabla para todas las operaciones de saldo
CREATE TABLE balance_operations
(
    user_id      UInt64,
    operation_id String,          -- ID único de operación (apuesta, pago, cancelación)
    amount       Int64,           -- Cambio (+1000 ganancia, -500 apuesta)
    version      UInt64,          -- Número de versión monótono
    sign         Int8,            -- +1 = nueva operación, -1 = cancelación
    created_at   DateTime DEFAULT now()
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, operation_id, version);

Escenario 1: El jugador hace una apuesta de 100 rublos

-- Insertar una fila (sign = +1)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, +1, now());

Escenario 2: El jugador gana 500 rublos (pago)

INSERT INTO balance_operations VALUES (123, 'win_001', +500, 1002, +1, now());

Escenario 3: El administrador cancela la apuesta bet_001 (¿el jugador hizo trampa?)

-- Cancelar la apuesta antigua (mismo operation_id, sign = -1, misma versión 1001)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, -1, now());
-- Añadir una operación correctiva (devolver 100 rublos)
INSERT INTO balance_operations VALUES (123, 'admin_correction_bet_001', +100, 1003, +1, now());

Cómo leer el saldo en tiempo real:

-- Consulta para el panel de control (se ejecuta cada 3 segundos)
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM balance_operations
WHERE user_id = 123 AND created_at > now() - interval 1 day  -- limitar por tiempo
GROUP BY user_id;

Con el uso adecuado de versiones, esta consulta devolverá el saldo correcto incluso con un orden de inserción caótico.

9. Comparación de Enfoques: CollapsingMergeTree vs ReplacingMergeTree para Saldo

Muchos principiantes preguntan: "¿Por qué no usar simplemente ReplacingMergeTree y actualizar el saldo como una versión de la fila?"

Comparemos.

Característica	CollapsingMergeTree	ReplacingMergeTree
Cómo representar un cambio	Dos filas: cancelar (-1) y nueva (+1)	Una nueva fila con una versión superior
Necesidad de almacenar historial completo	Sí, hasta que se colapse	Sí, hasta que se fusione
Enfoque de lectura	SUM(amount * sign)	argMax(amount, version) o FINAL
Complejidad de inserción	Mayor (hay que pensar en pares)	Menor (solo una nueva versión)
Complejidad de lectura	Menor (agregación simple)	Mayor (FINAL es lento o argMax)
Riesgo de error	Los pares no coinciden (error lógico)	La versión no es monótona (error del cliente)

Cuándo elegir CollapsingMergeTree:

• Necesitas cambiar las mismas claves con frecuencia (p. ej., el saldo del jugador cambia 100 veces por hora).
• Quieres usar agregación simple SUM(amount * sign) y no depender de FINAL.
• Tienes un orden de inserción controlado o usas VersionedCollapsingMergeTree.
• Necesitas revertir operaciones (cancelar una apuesta) — en ReplacingMergeTree, esto requeriría insertar una nueva fila con una versión aumentada, lo que no refleja explícitamente "cancelación".

Cuándo es mejor ReplacingMergeTree:

• Tienes actualizaciones poco frecuentes (p. ej., estado del pedido: creado → pagado → entregado).
• Almacenas atributos mutables no numéricos en lugar de agregados numéricos.
• Necesitas ver el historial de versiones de cada registro.

Ejemplo para saldo — ¿cuál es mejor? Para una cuenta de alto rendimiento (miles de apuestas por segundo), VersionedCollapsingMergeTree es mejor. Proporciona un rendimiento predecible y un comportamiento correcto bajo orden caótico.

10. Rendimiento y Cuándo es Mejor que UPDATE en PostgreSQL

Rendimiento de CollapsingMergeTree

• Inserción: Muy rápida (INSERT normal, sin bloqueos). Pagas por almacenar dos filas en lugar de actualizar una — pero en una base de datos columnar, eso no es tan malo.
• Lectura con agregación: ClickHouse lee solo las columnas amount y sign (¡almacenamiento columnar!), realiza cálculos vectorizados rápidos. Para mil millones de filas — fracciones de segundo.
• Fusión: Trabajo en segundo plano. No afecta las inserciones.

Comparación con PostgreSQL

En PostgreSQL, actualizar un saldo:

-- Actualización atómica con bloqueo de fila
UPDATE players SET balance = balance - 100 WHERE user_id = 123;

Ventajas: simple, garantías ACID (atomicidad, consistencia, aislamiento, durabilidad), consistencia instantánea.

Desventajas: A 10 000 actualizaciones por segundo — bloqueos (bloqueos de fila), WAL (registro de escritura anticipada), vacío. Alcanzarás los límites de E/S.

En ClickHouse con CollapsingMergeTree:

Ventajas: Más de 100 000 inserciones por segundo en un solo servidor, compresión de datos (10:1), sin bloqueos, escalado lineal.

Desventajas: Sin consistencia instantánea (se necesita agregación hasta la fusión), lógica más compleja (sign, versión), consistencia eventual — el sistema alcanzará el estado correcto, pero no instantáneamente.

Cuándo CollapsingMergeTree es mejor que PostgreSQL:

• Necesitas muchísimas actualizaciones (miles a decenas de miles por segundo).
• Un retraso de unos segundos (para el colapso) es aceptable.
• Ya estás usando ClickHouse para analítica.

Cuándo PostgreSQL sigue siendo mejor:

• Necesitas consistencia instantánea estricta (transferencia bancaria entre cuentas).
• Las actualizaciones son pocas (<1000 por segundo).
• No quieres complicar la arquitectura.

Qué Sigue

Ahora conoces CollapsingMergeTree y su hermano mayor VersionedCollapsingMergeTree. Próximos temas para explorar:

• Cómo elegir entre CollapsingMergeTree y ReplacingMergeTree — una lista de verificación para cada tarea.
• Optimización de fusiones — ajustes como merge_with_ttl_timeout para que los pares se colapsen más rápido.
• Patrón: vista materializada + CollapsingMergeTree — para agregados multinivel.

Resumen: CollapsingMergeTree es una herramienta potente pero que requiere disciplina. No perdona errores en el orden de inserción o en la corrección de los pares. Pero si lo configuras correctamente (especialmente con versiones), ofrece un rendimiento inalcanzable para las bases de datos tradicionales. Recuerda la regla de oro: verifica siempre tus consultas con SUM(amount * sign) y usa VersionedCollapsingMergeTree en sistemas distribuidos.

---

← Anterior: SummingMergeTree y AggregatingMergeTree: Agregación Incremental sin Dolor
→ Siguiente: Particionamiento en ClickHouse: Cómo gestionar datos a nivel de carpeta

— Editorial Team