ClickHouse® ReplacingMergeTree: Deduplicación Bien Hecha

Este post es el cuarto de la serie ClickHouse Deep Dive. Si no has leído los anteriores, te recomiendo arrancar desde el principio. El post de modelado de datos presentó ReplacingMergeTree como el engine para filas que se actualizan, flujos CDC (Change Data Capture), tablas de dimensiones y cualquier caso con upserts. Un párrafo dentro de una guía más amplia. Esto es el deep dive completo.

Si vienes de Postgres o MySQL, hay algo que desaprender. INSERT ON CONFLICT en Postgres deduplica al instante. Consultas y siempre te sale una fila. ReplacingMergeTree no va así: deduplica cuando el merger de fondo decide ejecutarse según los recursos del servidor. Pueden ser segundos. Pueden ser horas. No lo controlas, y entre inserts, la fila antigua y la nueva conviven en almacenamiento, visibles para cualquier query.

Esa diferencia está detrás de casi todos los bugs de ReplacingMergeTree que he visto en producción. Este post cubre la mecánica, la configuración, los patrones de consulta y las trampas de migración. Después de leerlo, no deberías necesitar nada más sobre este engine.

Cómo funciona realmente la deduplicación

Insertas una fila. Luego insertas una versión más nueva de esa misma fila. En Postgres, la antigua desaparece. En ClickHouse, las dos se quedan ahí. Y las dos aparecen en los resultados de tus queries.

ClickHouse elimina la versión antigua en los merges de fondo. Los merges son asíncronos y heurísticos: el sistema decide cuándo ejecutarlos según el tamaño de las partes, la carga y la planificación interna. He visto tablas donde el merge ocurrió a los dos segundos de un insert, y la misma tabla en un día con mucha carga donde los duplicados siguieron ahí más de una hora. No hay SLA sobre el timing de los merges. O diseñas teniendo esto en cuenta, o te va a morder.

SELECT sin FINAL devuelve los duplicados. COUNT(*) sale inflado. Las agregaciones cuentan doble. Lo peor es que los números parecen razonables: la forma correcta, las columnas correctas, los rangos de fechas esperados. Solo están inflados. A nadie le salta una alerta por un row count 1,8x mayor. Te enteras tres semanas después cuando un cliente pregunta por qué los números de su dashboard no cuadran con el sistema de origen.

El alcance de la deduplicación lo complica más. Solo ocurre dentro de una misma partición. Si la misma fila lógica cae en dos particiones distintas, esas copias no se van a fusionar nunca. OPTIMIZE FINAL no lo arregla. Nada lo arregla. Lo mismo con sharding: la deduplicación es local a cada shard.

Ahora viene la parte que pilla desprevenido a casi todo el que viene de bases de datos relacionales.

ORDER BY define qué filas se consideran duplicadas. No PRIMARY KEY. En Postgres, PRIMARY KEY garantiza unicidad. En ClickHouse, PRIMARY KEY controla el índice disperso, un mecanismo de rendimiento de queries. ORDER BY controla la deduplicación, un mecanismo de corrección de datos. Están relacionados (PRIMARY KEY tiene que ser un prefijo de ORDER BY), pero no tienen por qué ser iguales.

-- La deduplicación usa las cuatro columnas
-- El índice disperso usa solo las dos primeras
ORDER BY (event_type, toDate(created_at), created_at, id)
PRIMARY KEY (event_type, toDate(created_at))

El orden de las columnas es intencionado. Las de baja cardinalidad van primero porque ClickHouse usa ORDER BY como orden físico en disco, y al ordenar primero por valores de baja cardinalidad el índice disperso puede saltar grandes bloques de datos durante los escaneos. El identificador único (id en este ejemplo) va al final porque solo está para garantizar la corrección de la dedup, no para mejorar el rendimiento de las queries.

Todas las columnas en ORDER BY tienen que ser inmutables para una fila lógica dada. Si event_type cambia para una fila con id=123, la versión antigua tiene la key (A, 2026-01-15, 123) y la nueva (B, 2026-01-15, 123). Son claves distintas. El engine las trata como filas distintas, y las dos copias vivirán para siempre: ningún merge las va a resolver. Esto lo desarrollo en la sección de trampas de migración.

Una cosa más que va aquí porque pilla a la gente en pleno diseño: las materialized views se disparan en INSERT, antes de que corra ningún merge. Si tienes una MV haciendo sumState() sobre un origen ReplacingMergeTree, va a contar los duplicados. La tabla origen queda bien después del merge. El target de la MV queda permanentemente mal: ya agregó ambas versiones antes de que la dedup ocurriera. Puedes esquivarlo con ReplacingMergeTree en el target, o argMaxState, o un diseño de MV idempotente. Ningún workaround es limpio. Piénsalo antes de escribir el CREATE. El post de materialized views entra más en detalle.

Configuración de la tabla: ver e is_deleted

Dos parámetros. Ambos opcionales en la sintaxis. ver es obligatorio en la práctica si vas a correr algo real. is_deleted depende de si tu caso de uso necesita gestionar borrados desde el origen.

ver es la columna de versión. Durante un merge, gana la fila con la versión más alta. Sin ella, gana la última fila insertada físicamente, que depende de qué thread o conector escribió último. En una importación single-threaded, probablemente puedes saltártela si el orden está garantizado. Pero en cuanto tienes dos conectores CDC, batch inserts en paralelo, o cualquier cosa donde el orden de inserción no sea determinista, necesitas una columna de versión explícita.

Qué sirve como valor de versión: un entero incremental del sistema de origen, un DateTime64 con precisión de microsegundos o, si haces CDC, el LSN (log sequence number) de la base de datos de origen. He usado los tres. El LSN es lo más fiable para CDC porque refleja el orden de escritura real del origen, pero necesitas que tu conector lo exponga.

is_deleted llegó en ClickHouse 23.2 y resolvió un problema que antes exigía CollapsingMergeTree. Para borrar una fila: insertas una nueva versión con la misma ORDER BY key, versión más alta, e is_deleted = 1. Durante los merges, ClickHouse lo interpreta como marcador de borrado. Las queries con FINAL filtran esas filas automáticamente.

CREATE TABLE events (
    _version UInt64,
    _is_deleted UInt8,
    id UInt64,
    event_type LowCardinality(String),
    created_at DateTime64(3),
    payload String
) ENGINE = ReplacingMergeTree(_version, _is_deleted)
PARTITION BY toYYYYMM(created_at)
ORDER BY (event_type, toDate(created_at), id)
SETTINGS min_age_to_force_merge_seconds = 86400;

Las filas borradas no desaparecen del disco solas. Los marcadores de borrado se quedan ahí, y los datos también. min_age_to_force_merge_seconds se encarga: las partes más antiguas que el umbral se fusionan por la fuerza, y de paso se limpian los marcadores de borrado. Una ventana de 24 horas funciona bien en la práctica, aunque se puede ajustar a tu pipeline. Las particiones históricas se mantienen limpias sin intervención manual, las recientes conservan sus marcadores hasta que caducan, y mientras tanto la corrección se resuelve en query time. El margen de tiempo también absorbe llegadas fuera de orden. Si una fila tardía llega antes de la ventana de merge, el marcador de borrado sigue ahí para gestionarla.

Hubo un setting clean_deleted_rows = 'Always' para limpieza más agresiva, pero se deprecó y eliminó.

Si tu tabla tiene una tasa de borrado alta (por encima del ~10% de filas), este overhead de limpieza empieza a notarse. CollapsingMergeTree gestiona borrados masivos de forma más natural, pero eso lo dejaremos para un post futuro.

Queries: FINAL y las alternativas

Cualquier query contra una tabla ReplacingMergeTree sin FINAL puede devolver duplicados. FINAL fuerza la deduplicación en tiempo de lectura.

SELECT * FROM events FINAL WHERE event_type = 'purchase';

Durante años, el consejo estándar era evitar FINAL. Y tenía razón: era terrible. Single-threaded, leía columnas del primary key además de las solicitadas, 12x más lento en benchmarks.

Eso ya no aplica. Antes de la versión 20.5, FINAL era single-threaded. El rendimiento fue mejorando entre versiones, y en la 22.6 pasó a ser completamente multi-threaded. Pero la mejora más grande de golpe fue un setting:

SET do_not_merge_across_partitions_select_final = 1;

Procesa cada partición de forma independiente durante FINAL en vez de fusionarlas todas. Altinity midió la diferencia: 7x más rápido. FINAL queda a un ~30% de las queries sin FINAL.

Usamos FINAL en todas las queries ReplacingMergeTree. Más de 100.000 millones de filas, cada query, siempre. El overhead se nota. Resultados incorrectos no son opción. Ese es el tradeoff, y me parece bien.

Un matiz: do_not_merge_across_partitions_select_final solo funciona bien si la misma ORDER BY key nunca aparece en más de una partición. Debería estar garantizado por un buen diseño, pero vale la pena mencionarlo.

Algo más: FINAL desactiva la optimización PREWHERE por defecto. PREWHERE es como ClickHouse filtra filas antes de leer todas las columnas, y normalmente se aplica solo. Con FINAL activo, filtrar por columnas que no están en ORDER BY se vuelve más lento porque ClickHouse lee más datos de lo necesario. No vale con añadir un PREWHERE explícito: PREWHERE filtra filas antes de que FINAL aplique la dedup, así que puede descartar la fila de reemplazo y devolverte una versión obsoleta (GitHub #23702). Si esto te frena, reestructura la query con una subconsulta: deduplica en la interna con FINAL, filtra en la externa.

Aun así, la gente sigue preguntando por alternativas, así que ahí van. El patrón argMax selecciona el valor de columna de la fila con la versión más alta:

SELECT
    id,
    argMax(event_type, _version) AS event_type,
    argMax(payload, _version) AS payload
FROM events
GROUP BY id;

LIMIT 1 BY ordena por versión descendente y toma la primera por clave:

SELECT * FROM events
ORDER BY id, _version DESC
LIMIT 1 BY id;

Los dos funcionan. Pero echando un ojo a los benchmarks de Altinity con 40M filas y 10M claves únicas:

Método	Memoria	Tiempo
argMax (GROUP BY)	866 MiB	9,7s
ORDER BY + LIMIT 1 BY	8,39 GiB	14,5s
Subquery (IN)	924 MiB	4,1s
FINAL	834 MiB	2,3s

FINAL es el más rápido y el que menos memoria gasta en escaneos de tabla completa. En point queries todos los métodos van parejos (~0,006s). En range queries (10K claves de 40M), FINAL sigue ganando con 0,357s.

Entonces, ¿por qué sigue apareciendo argMax? Porque FINAL y argMax resuelven formas distintas del mismo problema.

FINAL deduplica las filas y te entrega el result set. Si solo necesitas "dame la última versión de estas filas", FINAL es más rápido y más sencillo. Pero FINAL solo deduplica. No agrega. Si quieres "el último nombre de cada usuario Y el conteo de sus eventos agrupados por mes", necesitas un GROUP BY encima de FINAL, así que ClickHouse deduplica primero y luego agrega: dos pasadas sobre los datos.

argMax hace las dos cosas en una sola pasada. Deduplica y agrega en el mismo GROUP BY. Ahí está su ventaja real: cuando la query ya necesita un GROUP BY por lógica de negocio, argMax te deja meter la dedup dentro en vez de apilarla encima. A cambio, SQL más verboso: escribes argMax(col, _version) por cada columna que quieras deduplicar, y en queries de dedup pura sin agregación es más lento que FINAL.

Antes recomendaba argMax por defecto. Las optimizaciones de FINAL en versiones recientes de ClickHouse cambiaron eso. Hoy: FINAL para queries de dedup directas, argMax cuando ya estás haciendo un GROUP BY y quieres combinar dedup con agregación de un tirón.

OPTIMIZE TABLE FINAL vs SELECT FINAL

Una palabra en común. Operaciones completamente distintas. He visto a gente meter OPTIMIZE TABLE FINAL en un cron job pensando que es lo mismo que usar FINAL en queries. No lo es.

	OPTIMIZE TABLE FINAL	SELECT ... FINAL
Qué es	DDL. Fusiona físicamente todas las partes en disco.	Modificador de query. Deduplica en tiempo de lectura.
¿Escribe datos?	Sí. Descomprime, fusiona, recomprime.	No. Solo lectura.
Coste	Enorme. Bloquea otros merges.	Overhead por query.
Cuándo usarlo	Ventanas de mantenimiento. Limpieza post-carga masiva.	En cada query.

OPTIMIZE TABLE FINAL es para mantenimiento. Lánzalo después de un backfill grande o en horas de baja carga cuando quieras compactar partes. No lo metas en un cron contra tablas de producción que estén recibiendo escrituras. El merge es single-threaded, la I/O es brutal, y bloquea otros merges de fondo mientras se ejecuta.

Patrones de producción

Cuatro patrones que cubren casi todo lo que hemos necesitado. El código es copy-pasteable, y los settings son los que usamos de verdad.

CDC desde Postgres

El pan nuestro de cada día. Los cambios de la base de datos origen llegan por CDC (Debezium, PeerDB, un conector custom, lo que sea), y ClickHouse guarda el último estado.

CREATE TABLE cdc_events (
    _version UInt64,
    _is_deleted UInt8,
    id UInt64,
    event_type LowCardinality(String),
    created_at DateTime64(3),
    payload String
) ENGINE = ReplacingMergeTree(_version, _is_deleted)
PARTITION BY toYYYYMM(created_at)
ORDER BY (event_type, toDate(created_at), id)
SETTINGS min_age_to_force_merge_seconds = 86400;

id al final del ORDER BY para la dedup. Columnas de baja cardinalidad primero para el índice disperso. Partición por mes para que el mismo id caiga siempre en la misma partición, asumiendo que created_at no cambia para una fila dada. Si cambia, necesitas otra estrategia de partición. Acuérdate de activar do_not_merge_across_partitions_select_final = 1 a nivel de sesión o query para el rendimiento de FINAL.

Tablas de dimensiones

Tablas de lookup pequeñas. La versión más reciente de cada entidad. No hace falta particionar.

CREATE TABLE user_profiles (
    version UInt64,
    id UInt64,
    name String,
    email String,
    plan LowCardinality(String),
    updated_at DateTime64(3)
) ENGINE = ReplacingMergeTree(version)
ORDER BY id;

id es la clave de dedup, version elige la superviviente. Ya está.

Row policy para filtrado de borrados

Si usas is_deleted, dos líneas de SQL te ahorran meter WHERE en todas partes:

CREATE ROW POLICY hide_deleted ON cdc_events
FOR SELECT USING _is_deleted = 0 TO ALL;

Cada query filtra automáticamente las filas borradas. Se aplica al principio de la pipeline, antes de que se ejecute tu WHERE. Lo tenemos en casi todas las tablas que usan is_deleted.

Force-merge de particiones antiguas

Los datos recientes se quedan sin fusionar para que la ingesta sea rápida (FINAL se encarga de la corrección en lectura). Los datos históricos ganan al estar completamente fusionados: menos partes que FINAL tenga que procesar.

ALTER TABLE cdc_events MODIFY SETTING
    min_age_to_force_merge_seconds = 86400;

Las partes de más de 24 horas se fusionan a la fuerza. Simple y efectivo. Las queries históricas se aceleran, y los datos recientes siguen ingiriéndose rápido. Puedes ajustar el plazo según tus necesidades y tu pipeline de ingesta.

Cuándo usar otra cosa

ReplacingMergeTree es el engine por defecto para upserts y CDC. Dos engines cubren lo que le falta.

CollapsingMergeTree va por otro lado totalmente. En vez de "inserta la nueva versión y la antigua acaba limpiándose", usa contabilidad de partida doble. Insertas la fila original con sign=1. Para actualizar: insertas una fila de cancelación con sign=-1, y luego la nueva con sign=1. Las queries usan sum(amount * sign) y obtienen resultados correctos sin FINAL, porque el +1 y el -1 se cancelan matemáticamente.

	ReplacingMergeTree	CollapsingMergeTree
Patrón de insert	Inserta la nueva versión	Cancela antigua + inserta nueva
¿Necesita estado previo?	No	Sí, hay que conocer la fila antigua para cancelarla
Borrados	Flag is_deleted + limpieza	Nativo, fila de cancelación
¿Las MVs funcionan correctamente?	Solo tipo uniq()	Counts y sums funcionan
Complejidad de aplicación	Baja	Alta

El tradeoff está claro. CollapsingMergeTree te da agregaciones correctas sin FINAL y buen soporte de MVs, pero tu aplicación tiene que conocer el estado anterior de la fila para emitir cancelaciones. Si puedes con eso y tu volumen de borrados es alto, es mejor engine.

AggregatingMergeTree resuelve otro problema. Almacena estados de funciones de agregación pre-computados para un propósito fundamentalmente diferente: "dame métricas pre-agregadas" vs. "dame la última versión de esta fila". No es intercambiable con ReplacingMergeTree. Úsalo para agregaciones GROUP BY repetidas donde quieras lecturas sub-milisegundo.

Las trampas de migración desde Postgres

Esta es la tabla de referencia que me habría encantado tener la primera vez que migré un pipeline CDC de Postgres a ClickHouse:

Postgres/MySQL	ClickHouse ReplacingMergeTree
UPDATE modifica la fila in place	INSERT de nueva fila con versión más alta
DELETE elimina la fila	INSERT de fila con is_deleted=1, versión más alta
PRIMARY KEY garantiza unicidad	Sin garantía de unicidad. Dedup eventual.
INSERT ON CONFLICT = inmediato	INSERT = eventual (duplicados visibles hasta el merge)
Fuente única de verdad	Múltiples versiones coexisten hasta el merge
Cualquier columna es mutable	Las columnas de ORDER BY son inmutables

Cuatro trampas concretas.

La primera pilla a todo el mundo. Haces una query sin FINAL. COUNT() devuelve el doble de lo esperado. No te das cuenta porque los datos parecen bien: la forma correcta, las columnas correctas, los rangos de fechas esperados. Solo están inflados. He visto esto pasar inadvertido semanas, hasta que alguien cruzó con el sistema de origen y los números no cuadraban.

Columnas mutables en ORDER BY es la segunda. En Postgres, actualizas la columna que quieras. En ClickHouse, si el valor de una columna ORDER BY cambia para una fila lógica, la fila antigua y la nueva tienen claves de dedup distintas. Son filas distintas para el engine. Dos copias permanentes, para siempre; ningún merge las va a resolver. La solución: solo columnas inmutables en ORDER BY, la PK del origen al final, y si de verdad necesitas cambiar una columna clave, prepárate para sufrir.

Tercera: duplicados cross-partition. Si created_at forma parte de tu PARTITION BY y puede cambiar (quizá el sistema de origen corrige timestamps retroactivamente), la versión antigua y la nueva caen en particiones distintas. La dedup se limita a la partición. Esas copias son permanentes.

La última es sutil pero duele. Las columnas de ClickHouse no son nullable por defecto. Una columna declarada como String sin Nullable() convierte NULL a string vacío en silencio. UInt64 convierte NULL a 0. Date convierte NULL a 1970-01-01. Tu stream manda un NULL porque la columna en Postgres es null, ClickHouse guarda un cero, y te enteras cuando un cliente pregunta por qué su informe muestra transacciones con fecha de enero de 1970.

Conclusiones

ReplacingMergeTree gestiona dedup a nivel de fila con mínima complejidad en la aplicación. Insertas la nueva versión, y el engine se encarga. Pero te castiga si vienes con supuestos de Postgres. Dedup eventual, columnas ORDER BY inmutables, merges limitados a la partición. Son los costes del rendimiento columnar a escala, no bugs.

Usa FINAL en cada query. Diseña ORDER BY keys inmutables. Piensa las interacciones con tus MVs. Esa es la versión corta.