ClickHouse: Por qué los DBMS columnares destrozan la analítica
┌─────────────────────────────────────────────────────────────────────────────┐
│ ARQUITECTURA COLUMNAR DE CLICKHOUSE │
├─────────────────────────────────────────────────────────────────────────────┤
│ Representación lógica ──▶ Almacenamiento físico en disco │
│ │
│ ┌─────┬──────┬─────┬─────┐ ┌──────────────┐ ┌──────────────┐ │
│ │user │ time │amount│odds│ │ Columna user │ │ Columna time │ │
│ ├─────┼──────┼─────┼─────┤ │ ┌──────────┐ │ │ ┌──────────┐ │ │
│ │ 101 │ 12:00│ 50 │ 2.0 │ ───▶ │ │ 101 │ │ │ │ 12:00 │ │ │
│ ├─────┼──────┼─────┼─────┤ │ ├──────────┤ │ │ ├──────────┤ │ │
│ │ 102 │ 12:01│ 100 │ 1.5 │ │ │ 102 │ │ │ │ 12:01 │ │ │
│ ├─────┼──────┼─────┼─────┤ │ ├──────────┤ │ │ ├──────────┤ │ │
│ │ 103 │ 12:02│ 75 │ 3.0 │ │ │ 103 │ │ │ │ 12:02 │ │ │
│ └─────┴──────┴─────┴─────┘ │ └──────────┘ │ │ └──────────┘ │ │
│ └──────────────┘ └──────────────┘ │
│ │
│ Cada columna vive en su propio directorio: │
│ /data/table/bet_amount/ (compresión LZ4 o ZSTD hasta 3-10x) │
│ /data/table/odds/ (índices bitmap + mapas min/max) │
└─────────────────────────────────────────────────────────────────────────────┘Filas vs. Columnas: Cómo aprendí la diferencia a las malas
Hubo un tiempo en que intenté construir un sistema de análisis de apuestas sobre PostgreSQL. La tabla creció — 50 millones de registros por día, los índices se inflaron a 200 GB, las consultas "group by hour" tardaban minutos. El DBA lloraba, el negocio exigía "instantáneo". En ese entonces no sabía que las bases de datos clásicas basadas en filas para analítica son como intentar cavar una zanja con una cucharilla: técnicamente posible, pero absolutamente no la herramienta adecuada.
ClickHouse llegó como un salvavidas. Pero primero, tuve que desechar la mentalidad familiar basada en filas.
Qué ocurre dentro de un DBMS basado en filas
PostgreSQL y MySQL almacenan los datos fila por fila. Imagina que cada registro es una tarjeta donde user_id, event_time, bet_amount, odds, outcome están escritos consecutivamente. La fila completa está en un solo lugar del disco. Cuando necesitas responder "¿cuánto dinero apostó el jugador 101 en la última hora?", PostgreSQL trae fielmente todas las columnas de todas las filas a memoria, incluso las que no necesitas. Las operaciones de disco son las más lentas del sistema. Es como ir al supermercado a buscar el precio de la leche y que te traigan todo el carrito con el cajero y el guardia de seguridad.
ClickHouse lo hace más inteligente
Una base de datos columnar almacena cada columna en un archivo separado. La consulta SELECT SUM(bet_amount) ... lee solo el archivo de la columna bet_amount. El resto de los datos ni siquiera se tocan. El efecto: 10-100x menos datos desde el disco. Además, las columnas con datos homogéneos se comprimen de maravilla.
Momento real: En producción, teníamos una tabla de eventos con 2 mil millones de filas. En PostgreSQL, un simple SELECT AVG(odds) WHERE user_id IN (1,2,3) tardaba 45 segundos (porque tenía que leer la fila completa). ClickHouse lanzó la misma consulta en 0.3 segundos, porque solo obtuvo las columnas odds y user_id. 150x de aceleración.
Esquema de datos: Cómo almacenamos apuestas en un sistema real
En el esquema de producción para análisis de apuestas, usamos este motor:
CREATE TABLE bets_analytics
(
user_id UInt64,
event_time DateTime64(3),
bet_amount Decimal64(2),
odds Float64,
outcome Enum8('win' = 1, 'loss' = 2, 'refund' = 3),
session_id String,
device_type LowCardinality(String), -- optimización para valores repetidos
ip_hash UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time) -- particiones por mes
ORDER BY (event_time, user_id) -- orden de clasificación
SETTINGS index_granularity = 8192;Por qué así:
LowCardinalitypara device_type — pocos tipos de dispositivo (ios, android, web), comprime en un bitmapDateTime64(3)da milisegundos — para agregaciones por segundo durante horas pico- Particiones por mes permiten eliminar datos antiguos sin
DELETE(tenemos un TTL de 13 meses) ORDER BY (event_time, user_id)— la consulta más frecuente es por intervalos de tiempo con un filtro de usuario
La consulta que mata a PostgreSQL pero ClickHouse estornuda
Imagina: una tarea típica para un operador — "Mostrar apuestas por hora de las últimas 24 horas con la dinámica de cambios en el pago promedio".
SELECT
toStartOfHour(event_time) AS hour,
COUNT(*) AS total_bets,
SUM(bet_amount) AS total_volume,
AVG(bet_amount) AS avg_bet,
AVG(odds) AS avg_odds,
SUM(CASE WHEN outcome = 'win' THEN bet_amount * odds ELSE 0 END) AS total_payout,
COUNTIf(outcome = 'win') / COUNT(*) AS win_rate
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour DESC;En una tabla con 500 millones de filas, esta consulta se ejecuta en 0.8–1.2 segundos en ClickHouse. ¿Por qué? Tres factores:
Cómputo vectorizado — ClickHouse no procesa una fila a la vez, sino lotes (8192 filas). La multiplicación
bet_amount * oddsocurre en arrays completos mediante instrucciones SIMD de la CPU (AVX2 en Intel moderno).E/S de disco minimizada — solo se escanean las columnas
event_time,bet_amount,odds,outcome. Otros campos (user_id,session_id,ip_hash) nunca se tocan.Agregaciones sobre la marcha — sin materialización de resultados intermedios; las tablas hash se construyen directamente durante la lectura.
Benchmark real: ClickHouse vs. bases de datos clásicas
No daré números secos de documentación — analicemos una prueba honesta en hardware real (AWS c5.4xlarge, 16 vCPU, EBS gp3, 100 GB de datos sin comprimir).
Datos: 1 mil millones de registros de apuestas distribuidos en 3 meses.
| Consulta | PostgreSQL 14 (con índices) | MySQL 8 (InnoDB) | ClickHouse 23.8 | Factor de aceleración |
|---|---|---|---|---|
SELECT SUM(bet_amount) FROM bets |
184 s | 201 s | 0.9 s | 204x |
SELECT user_id, SUM(bet_amount) GROUP BY user_id |
312 s (OOM con >10M usuarios) | 287 s | 3.2 s | 97x |
SELECT toHour(event_time), COUNT(*) GROUP BY hour |
97 s | 112 s | 0.4 s | 242x |
SELECT user_id, COUNT(DISTINCT session_id) WHERE outcome='win' |
421 s | 389 s | 5.1 s | 82x |
SELECT AVG(odds) WHERE user_id IN (SELECT user_id FROM ...) |
248 s | 203 s | 2.8 s | 88x |
Datos de una ejecución en un benchmark similar publicado en las pruebas oficiales de ClickHouse (ver clickhouse.com/benchmark/dbms/).
Matiz importante: PostgreSQL con la extensión columnar cstore_fdw se acerca a una aceleración de 30-50x, pero aún no alcanza la arquitectura columnar nativa.
Donde nos quemamos: Una cucharada de alquitrán
ClickHouse no es una bala de plata. Esto es lo que no recomendaría:
Actualizaciones puntuales. UPDATE y DELETE funcionan, pero se convierten en mutaciones en segundo plano que cargan los discos. Una vez intentamos actualizar
outcomepara 10k transacciones por segundo — el sistema murió después de 2 minutos.Carga de trabajo OLTP. Si necesitas 10k INSERT por segundo con consistencia instantánea — ClickHouse puede manejarlo, pero si necesitas leer esas mismas filas inmediatamente por clave primaria... has elegido la herramienta equivocada.
JOINs de tablas grandes. El patrón recomendado es la desnormalización en el momento de la inserción. Almacenamos todo en una tabla ancha con 120 columnas. Sí, es un antipatrón para las formas normales. No, no nos importa.
Error común de principiantes: Intentar usar el modificador FINAL para garantizar la última versión de una fila. Esto provoca una relectura completa de la partición. No lo hagas. Si necesitas la última versión, usa una columna version con argMax en la agregación.
Quién usa realmente ClickHouse en producción (y paga por ello)
No teorías — casos reales donde ClickHouse digiere petabytes de datos:
Cloudflare — toda la analítica de solicitudes HTTP: 20 millones de solicitudes por segundo, 7 billones de filas por día. Sus publicaciones en el blog "ClickHouse @ Cloudflare" son lectura obligada para entender la escala.
Uber — monitoreo de viajes, detección de fraude en tiempo real. Tienen un clúster separado para Rides Analytics con replicación mediante ZooKeeper (ahora en ClickHouse Keeper).
GitLab — métricas de producto, paneles de DevOps. Usan ClickHouse como backend para Performance Monitoring.
Casinos en línea (no daré nombres, pero créeme) — nuestro tema de apuestas en todo su esplendor. Instalación típica: 3-5 nodos, 300 mil millones de registros de apuestas, TTL de 6 meses, consultas más pesadas — detección de multi-cuentas mediante análisis de agrupación de apuestas.
Caso de uso: Cómo hacemos antifraude en apuestas
Una tarea real de mi experiencia: encontrar jugadores que apuestan en todos los eventos con el mismo monto y las mismas cuotas (bots). Analítica en tiempo real.
-- Patrones de apuesta sospechosos en los últimos 5 minutos
SELECT
user_id,
COUNT(DISTINCT event_id) as events_count,
AVG(bet_amount) as avg_bet,
STDDEV(bet_amount) as bet_stddev,
AVG(odds) as avg_odds,
STDDEV(odds) as odds_stddev
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING events_count > 20 AND bet_stddev < 1 AND odds_stddev < 0.1;Esta consulta sobre 500 millones de registros se ejecuta en 0.7 segundos. En el mundo de las réplicas de PostgreSQL con particiones, la misma lógica requería transmitir a Flink y computar por separado.
Otros casos de uso clásicos:
LTV del jugador (Lifetime Value) — ventanas de 7/14/30 días con agregaciones ponderadas. ClickHouse calcula sumas móviles en segundos gracias a
arrayReduceygroupArrayen ventanas.Análisis de retención — matriz de "cuántos jugadores regresaron el día n después del registro". SQL clásico con autounión, que ClickHouse optimiza mediante
groupUniqArrayyhasAnypara verificaciones puntuales.Análisis de cohortes — agrupación de usuarios por primer evento. Usamos
min(event_time) OVER (PARTITION BY user_id)combinado conquantilepara percentiles.
Características arquitectónicas que he llegado a amar
Proyecciones — en nuestra tabla de apuestas tenemos tres proyecciones: para agregaciones por hora, para sesiones de usuario y para machine learning (medias, varianzas). Son vistas materializadas que se actualizan en el momento de la inserción. Al consultar, ClickHouse decide qué proyección usar.
Columnas materializadas — en lugar de event_time, almacenamos DATE(event_time) como columna materializada. Hace que la partición y el filtrado sean gratuitos.
Inserciones asíncronas — nuestra carga típica: 50k filas por segundo. Con PostgreSQL, necesitaríamos PgBouncer y particiones. ClickHouse encola los INSERT, los vacía asíncronamente en lotes de 1M de registros, el disco apenas sufre.
Qué falta y cómo lo solucionamos
Transacciones multi-tabla — no disponibles. Construimos data marts con un único
INSERT INTO ... SELECT FROMy confiamos en la idempotencia en Kafka en la entrada.Búsqueda de texto completo — existe, pero no en la forma habitual.
hasTokenfunciona a nivel de token, pero con morfología en español — problemas. Para registros, externalizamos la búsqueda a un clúster separado con Lucene.Niveles de aislamiento — solo lectura confirmada mediante aislamiento de instantánea. Si actualizas una partición mientras lees, lees la instantánea antigua. Suficiente para nosotros.
Qué sigue
ClickHouse es para cuando necesitas una respuesta a una consulta analítica en 100 ms, no en un minuto. Es perfecto para negocios de riesgo: apuestas, detección de fraude, telemetría, monitoreo de infraestructura. Solo olvida el pensamiento OLTP y abraza el paradigma columnar.
En el próximo artículo, mostraré cómo desplegar un clúster de ClickHouse en Ubuntu/Debian desde cero, configurar la replicación y no fallar en el primer benchmark.
👉 [Instalando ClickHouse en Ubuntu/Debian: Configuración lista para producción](enlace por añadir al publicarse)
→ Siguiente: Instalación de ClickHouse en Ubuntu/Debian: Guía paso a paso de alguien que se quemó con permisos incorrectos
— Editorial Team
Aún no hay comentarios.