PostgreSQL vs ClickHouse: patrones ETL que no crean caos de datos

Hechos interesantes y contexto

• PostgreSQL nació como POSTGRES en la UC Berkeley en los años 80, explorando explícitamente la extensibilidad (tipos, índices, reglas) mucho antes de que “pluggable” fuera un argumento de venta.
• MVCC en PostgreSQL (control de concurrencia multiversión) es la razón por la que las lecturas no bloquean escrituras, pero también por la que vacuum no es opcional si actualizas mucho.
• ClickHouse surgió de Yandex en los 2010 para analítica web a gran escala; sus sesgos son sinceros: “escanea rápido, agrega más rápido”.
• ClickHouse es columnar: almacena cada columna por separado, por eso puede leer solo las columnas necesarias para una consulta, pero también significa que muchas columnas pequeñas pueden aumentar la sobrecarga.
• Los motores MergeTree no son “solo anexar” en el sentido ingenuo; son “anexar y luego fusionar en segundo plano”, lo que condiciona cómo manejar actualizaciones y deduplicación.
• La replicación de PostgreSQL evolucionó por etapas: replicación física por streaming, luego replicación lógica para flujos de cambios a nivel de fila, habilitando patrones ETL que no requieren extracciones completas constantes.
• Las vistas materializadas de ClickHouse se activan en inserción: transforman datos en el momento de la inserción, ideal para pipelines de ingestión, pero no recomputan retroactivamente a menos que reinserte/resplique datos.
• Ambos sistemas tienen historias de compresión “depende”: Postgres comprime valores TOASTed; ClickHouse comprime datos columnarios extremadamente bien, especialmente columnas de baja cardinalidad.
• JSON llegó a ser ciudadano de primera clase de forma distinta: Postgres construyó indexación y operadores robustos para JSONB; ClickHouse soporta JSON pero suele esperar que normalices las rutas de consulta calientes en columnas tipadas.

Dos modelos mentales: verdad OLTP vs verdad analítica

PostgreSQL es donde guardas la verdad. No “verdad aproximada”. No “suficientemente cercana”. Verdad, con restricciones, transacciones y la incómoda tarea de decir “no” a datos erróneos. ClickHouse es donde guardas la verdad analítica: altamente consultable, normalmente desnormalizada, optimizada para group-bys sobre miles de millones de filas.

Si tratas PostgreSQL como un warehouse, acabarás peleando contra el bloat, la contención de locks y un esquema que intenta ser normalizado y desnormalizado a la vez (y termina siendo ninguno). Si tratas ClickHouse como una base transaccional, eventualmente descubrirás que “update” es una cuestión filosófica, y la respuesta es “no así”.

La implicación operacional

Los patrones ETL que no crean caos son los que respetan las limitaciones de cada motor:

• En PostgreSQL: aplica las restricciones temprano, mantiene las escrituras eficientes, usa extracción incremental y evita “cargas analíticas disfrazadas de reportes” en primarios de producción.
• En ClickHouse: abraza la semántica de anexar-y-fusionar, diseña particiones para pruning, y modela explícitamente la deduplicación y los datos que llegan tarde.

Parafraseando una idea de Werner Vogels (CTO de Amazon): deberías planear el fallo como estado normal, no como excepcional. Un ETL que solo funciona cuando nada sale mal es una demo, no un pipeline.

Qué significa realmente “sin caos de datos”

“Sin caos de datos” no es una sensación; son invariantes que puedes probar. Aquí están las que importan en producción:

• Idempotencia: volver a ejecutar un trabajo no crea duplicados ni deriva. Puedes reiniciar sin rezos.
• Determinismo: la misma ventana de entrada produce las mismas filas de salida (o el mismo estado final), incluso con reintentos.
• Frescura observable: puedes responder “¿qué tan tarde están los datos?” con una consulta, no una conjetura.
• Reconciliación: puedes comparar conteos/checksums entre origen y sink en límites significativos y detectar pérdidas silenciosas.
• Backfills controlados: reprocesar ventanas históricas no debe colapsar el clúster ni cambiar métricas sin dejar rastro.
• Contrato de esquema: cuando upstream añade una columna o cambia semántica, el pipeline falla en alto o se adapta con seguridad—nada de disparates silenciosos.

Broma #1: ETL significa “Eventualmente las cosas se atrasan.” Si el tuyo no lo hace, probablemente está saltándose trabajo.

El villano escondido: el tiempo

La mayor parte del caos tiene forma de tiempo: eventos tardíos, skew de reloj, reordenamientos, retrasos de replicación multi-región y backfills que reinyectan datos antiguos en particiones “actuales”. Un pipeline que trata event time, ingestion time y update time como lo mismo creará el tipo de discusiones en dashboards que terminan amistades.

Patrones ETL en PostgreSQL que mantienen la cordura

Patrón 1: Trata Postgres como el límite del contrato (restricciones, no esperanza)

Si puedes validar algo una vez, valídalo donde entra al sistema. Usa NOT NULL, foreign keys donde sean operativamente razonables, check constraints para reglas de dominio e índices únicos para claves de idempotencia. Este es el lugar más barato para atrapar basura.

Para la extracción ETL, quieres identificadores estables y un cursor monótono. Eso puede ser:

• una clave primaria basada en secuencia (buena para inserts, no tanto para updates),
• un timestamp last_updated (bueno si se mantiene correctamente),
• o un slot de replicación lógica / flujo CDC (mejor cuando necesitas todos los cambios).

Patrón 2: Cargas incrementales con watermarks (y una ventana de retroceso)

“Cargar todo cada vez” es un plan válido cuando todo es pequeño. Deja de serlo cuando alguien dice “deberíamos guardar más historial”. Las cargas incrementales son la opción por defecto. Pero las cargas incrementales basadas en timestamps necesitan una ventana de lookback para manejar commits tardíos y deriva de reloj.

Qué hacer:

• Mantén un watermark por tabla/pipeline (p. ej., el último updated_at exitoso visto).
• En cada ejecución, extrae updated_at > watermark - lookback.
• Haz upsert en staging/warehouse con una clave determinista.

Qué evitar: usar now() como límite sin registrarlo, o asumir que los timestamps son estrictamente crecientes. No lo son, especialmente con reintentos y sistemas con múltiples escritores.

Patrón 3: Usa CDC cuando “las actualizaciones importan”

Si las filas fuente cambian y necesitas reflejar esos cambios downstream, CDC es el enfoque limpio. La replicación lógica (o un plugin de decodificación) te da eventos de cambio ordenados con posiciones LSN. La ganancia operacional es que dejas de adivinar qué cambió.

El coste operacional: ahora ejecutas consumidores con estado y debes monitorizar el lag del slot de replicación. Pero ya ejecutas sistemas con estado; solo les llamas “pipelines” y finges que son sin estado.

Patrón 4: Separa OLTP de la carga de extracción

Ejecuta extracciones pesadas contra una réplica, no contra el primario. Si debes extraer del primario, usa predicados con índices cuidados, lotes pequeños y timeouts explícitos de sentencia. Consultas analíticas en primarios es cómo “data engineering” se convierte en categoría de incidente.

Patrón 5: Usa staging en Postgres solo si puedes mantenerlo ordenado

Las tablas de staging en Postgres están bien cuando son de corta duración y se ruedan con vacuum, y cuando no conviertes la base de datos en un depósito para eventos sin límite. Usa tablas UNLOGGED para staging transitorio si toleras pérdida de datos en crash y quieres velocidad. Para ingesta cruda y duradera a escala, Postgres suele no ser la mejor plataforma de aterrizaje.

Patrón 6: Upsert con intención (no con pensamiento deseoso)

INSERT ... ON CONFLICT es un regalo. También es un arma cargada. Si haces upsert de grandes lotes en tablas calientes sin pensar en índices y bloat, descubrirás qué significa “autovacuum scale factor” a las 2 a.m.

Para staging ETL, usa claves deterministas e incluye una columna de versión de la fuente (como LSN de origen, o updated_at, o un hash) para detectar actualizaciones no operativas y evitar reescribir filas innecesariamente.

Patrones ETL en ClickHouse que mantienen la cordura

Patrón 1: Inserta en bloques, no fila a fila

ClickHouse adora inserciones grandes. Tu objetivo son bloques menos numerosos y más grandes que compriman bien y produzcan menos partes. Muchas inserciones pequeñas crean demasiadas partes y las fusiones en segundo plano pasarán su vida limpiando por ti.

Patrón 2: Diseña tablas MergeTree alrededor de consultas, no del “esquema fuente”

El diseño de tablas en ClickHouse empieza por:

• clave de partición (lo que se poda),
• order by (cómo se agrupa para escaneos por rango y dedup),
• clave primaria (índice disperso sobre los datos ordenados).

Si eliges una clave de partición que genera particiones diminutas (como por user_id) creas un zoológico de particiones y merges. Si particionas demasiado grueso, pierdes pruning y los backfills se vuelven caros. Las particiones por tiempo (día/semana/mes) son comunes porque mapean el manejo de datos tardíos y TTL.

Patrón 3: Maneja duplicados explícitamente con ReplacingMergeTree (y entiende el trade-off)

ReplacingMergeTree es el motor “tengo duplicados y quiero una vista last-write-wins”. Funciona colapsando duplicados durante merges según la clave de ordenamiento (y columna de versión opcional). Lo no obvio: la deduplicación es eventual. Las consultas pueden ver duplicados hasta que se ejecuten los merges, a menos que fuerces una consulta FINAL (cara) o diseñes alrededor de ello.

Úsalo cuando:

• puedas tolerar duplicados temporales,
• tengas una clave de dedup estable,
• y puedas proporcionar una columna de versión (p. ej., source updated_at o secuencia de ingestión).

Evítalo cuando necesites unicidad transaccional estricta al leer. Eso no es trabajo de ClickHouse.

Patrón 4: Para hechos “append-only”, mantenlo sencillo: MergeTree + filas inmutables

Si tus datos son naturalmente inmutables (eventos, logs, page views), no inventes actualizaciones. Usa plain MergeTree o ReplicatedMergeTree, particiona por tiempo, ordena por las claves de consulta y déjalo correr.

Patrón 5: Usa vistas materializadas para transformaciones en ingestión, no como varita mágica

Las vistas materializadas son excelentes para:

• preagregaciones (p. ej., rollups horarios),
• desnormalizar atributos de referencia en tiempo de ingestión,
• dividir un stream crudo en múltiples tablas con forma.

Pero solo procesan lo que se inserta después de que existen. Si creas una vista y esperas que los datos históricos aparezcan, aprenderás una lección valiosa sobre causalidad. Los triggers de inserción no viajan en el tiempo.

Patrón 6: Datos tardíos: estrategia de partición + merges con “ventana de gracia”

Los datos tardíos son normales. Tu trabajo es hacerlos baratos. Usa particiones de tiempo que se alineen con la tardanza máxima que esperas y mantén las particiones recientes “amigables para merges”. Un enfoque común:

• Mantén particiones diarias para los últimos N días.
• Fusiona agresivamente esas particiones.
• Tras N días, considera compactar o mover particiones antiguas a almacenamiento más barato mediante TTL.

Patrón 7: Agregaciones: AggregatingMergeTree es potente, pero no es gratis

Si usas AggregatingMergeTree, almacenas estados agregados y los merges los combinan. Esto puede ser extremadamente eficiente para rollups, pero complica consultas ad-hoc y depuración porque los datos almacenados no son hechos brutos. Úsalo para métricas estables y bien definidas. Mantén una tabla de hechos crudos para reconciliación y reprocesos.

Broma #2: los merges de ClickHouse son como la colada—si los ignoras lo suficiente, todo se acumula, huele mal y te cuesta el fin de semana.

La entrega: staging, contratos y reconciliación

Deja de pensar “ETL”, empieza a pensar “contratos”

El mayor salto en fiabilidad de pipelines es definir el contrato en el límite: qué constituye una fila válida, qué claves la hacen única, qué significa “update” y cuál timestamp es autoritativo.

Un contrato útil para datos tipo evento típicamente incluye:

• event_id o clave de idempotencia determinista,
• event_time (cuando ocurrió),
• ingest_time (cuando lo vimos),
• source_version (LSN, updated_at o similar),
• schema_version (para evolucionar de forma segura).

Estrategia de staging: aterriza crudo, luego dale forma

Hay dos enfoques de staging sensatos en términos generales:

1. Crudo en ClickHouse, moldeado en ClickHouse: aterriza una tabla raw (columnas tipadas para campos calientes, más un blob/JSON para el resto), luego vistas materializadas construyen tablas con forma. Esto es común cuando ClickHouse es la tienda analítica principal.
2. Crudo fuera de ambos, carga ambos: aterriza en almacenamiento de objetos o una cola, luego carga Postgres (para usos operativos) y ClickHouse (para analítica). Esto reduce acoplamiento y hace los replays más seguros.

Cuando Postgres es la fuente de la verdad, un patrón común es CDC desde Postgres hacia ClickHouse. Funciona bien si tratas el stream CDC como tu contrato crudo, y capturas deletes/updates explícitamente.

Reconciliación: los conteos no son suficientes, pero son un inicio

Los conteos de filas son la alarma de humo. No te dirán qué habitación está en llamas, pero te dirán que el edificio no está bien. Mejor: reconcilia por particiones/ventanas usando:

• conteos por día/hora,
• conteos distintos de claves,
• checksums/hashes sobre proyecciones estables,
• tasa de datos tardíos (eventos que llegan > X horas tarde).

Si no puedes reconciliar, no estás ejecutando un pipeline; estás ejecutando un molino de rumores.

12+ tareas prácticas con comandos, salidas y decisiones

Estos son los tipos de comandos que ejecutas cuando estás on-call por datos. Cada uno incluye qué significa la salida y qué decisión tomas a continuación.

Task 1: Check Postgres table bloat symptoms via dead tuples

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 10;"
      relname       | n_live_tup | n_dead_tup | dead_pct
------------------+------------+------------+----------
events            |   92034112 |   18010233 |    16.37
orders            |    1203321 |     220331 |    15.48
...

Significado: Un n_dead_tup alto sugiere actualizaciones/eliminaciones frecuentes sin suficiente progreso de vacuum.

Decisión: Si dead_pct es alto en tablas con upserts ETL, reduce la rotación de actualizaciones (evita actualizaciones no operativas), ajusta autovacuum o cambia a append + reconstrucción periódica.

Task 2: See if autovacuum is keeping up (per table)

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT relname, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY last_autovacuum NULLS FIRST LIMIT 10;"
   relname   |     last_autovacuum     |    last_autoanalyze     | autovacuum_count | autoanalyze_count
------------+--------------------------+--------------------------+------------------+-------------------
events      | 2025-12-30 02:11:41+00   | 2025-12-30 02:15:03+00   |             182  |               210
...

Significado: Un last_autovacuum obsoleto en tablas calientes implica que vacuum no se está ejecutando lo suficiente o está bloqueado.

Decisión: Si las tablas ETL están dejando sin autovacuum, baja los scale factors para esas tablas o cambia el ETL a insertar-nuevo + swap.

Task 3: Validate index usage for incremental extract predicate

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE updated_at > now() - interval '2 hours';"
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using orders_updated_at_idx on orders  (cost=0.43..12842.11 rows=24000 width=312)
  Index Cond: (updated_at > (now() - '02:00:00'::interval))
  Buffers: shared hit=802 read=19
  Execution Time: 48.221 ms

Significado: Estás obteniendo un index scan con pocos buffers leídos. Bien.

Decisión: Si es un sequential scan, añade/ajusta el índice o cambia el predicado (p. ej., usa un cursor numérico, evita funciones sobre la columna).

Task 4: Check replication slot lag (CDC health)

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS restart_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS flush_lag FROM pg_replication_slots;"
   slot_name    | active | restart_lag | flush_lag
----------------+--------+-------------+-----------
ch_cdc_slot     | t      | 12 GB       |  420 MB

Significado: WAL se retiene porque el consumidor no ha avanzado. Restart lag afecta uso de disco; flush lag es “qué tan retrasado está el consumidor”.

Decisión: Si el lag crece, limita productores, arregla el consumidor o amplía el disco de WAL. No elimines el slot a la ligera; así se pierde historial de cambios.

Task 5: Inspect Postgres locks that may block ETL

cr0x@server:~$ psql -h pg-prod-primary -U app -d appdb -c "SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle' AND wait_event IS NOT NULL ORDER BY pid LIMIT 5;"
 pid  | wait_event_type |  wait_event   |                       query
------+-----------------+---------------+--------------------------------------------------
48211 | Lock            | relation      | ALTER TABLE orders ADD COLUMN promo_code text;
...

Significado: Tu ETL puede estar en cola detrás de un lock DDL o al revés.

Decisión: Mueve DDL a ventanas de mantenimiento, usa builds de índices concurrentes y establece timeouts de sentencia para sesiones ETL.

Task 6: Check ClickHouse parts explosion (too many small inserts)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT table, sum(parts) AS parts, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active AND database='analytics' GROUP BY table ORDER BY parts DESC LIMIT 5;"
events_raw	18234	1.21 TiB
sessions	 3921	211.33 GiB

Significado: Un alto conteo de partes activas indica muchas partes pequeñas; los merges tendrán problemas y las consultas se ralentizan.

Decisión: Agrupa inserciones, ajusta async inserts o rediseña la ingestión para reducir creación de partes. Considera ajustes de particionado si las particiones son demasiado finas.

Task 7: See if ClickHouse merges are falling behind

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT database, table, sum(rows) AS rows_merging, count() AS merge_jobs FROM system.merges GROUP BY database, table ORDER BY rows_merging DESC LIMIT 5;"
analytics	events_raw	1289031123	7
analytics	sessions	 210331119	3

Significado: Muchas filas en merging significa trabajo en segundo plano pesado; ocurre amplificación de escritura.

Decisión: Reduce el conteo de partes insertadas, evita backfills en pico, aumenta recursos de merge con cuidado o para temporalmente trabajos pesados de optimización.

Task 8: Check ClickHouse query hotspots and read volume

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS read, query FROM system.query_log WHERE type='QueryFinish' AND event_time > now() - INTERVAL 10 MINUTE ORDER BY read_bytes DESC LIMIT 3;"
8421	1290341122	78.12 GiB	SELECT * FROM events_raw WHERE ...
2103	230113112	12.03 GiB	SELECT user_id, count() FROM events_raw ...

Significado: Alguien está haciendo un SELECT * amplio o falta pruning por partición.

Decisión: Arregla las consultas, añade proyecciones/rollups materializados o aplica límites de consulta. Además: deja de seleccionar * de tablas de hechos crudos a menos que disfrutes del paginado.

Task 9: Verify partition pruning is working in ClickHouse

cr0x@server:~$ clickhouse-client -h ch01 -q "EXPLAIN indexes=1 SELECT count() FROM events_raw WHERE event_date = toDate('2025-12-29') AND customer_id = 42;"
Expression (Projection)
  Aggregating
    ReadFromMergeTree (analytics.events_raw)
    Indexes:
      MinMax
        Keys: event_date
        Condition: (event_date in [2025-12-29, 2025-12-29])
        Parts: 3/120
        Granules: 512/20480

Significado: Solo 3 de 120 partes se leen. Eso es pruning. Tu clave de partición está haciendo trabajo real.

Decisión: Si lee la mayoría de partes, revisa la clave de partición, los predicados de consulta o considera añadir un índice de salto de datos secundario para filtros comunes.

Task 10: Detect duplicate keys in ClickHouse raw ingestion (before merges hide it)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT event_id, count() AS c FROM events_raw WHERE event_date >= today()-1 GROUP BY event_id HAVING c > 1 ORDER BY c DESC LIMIT 5;"
e_9f3b2	4
e_1a77c	3

Significado: Llegan duplicados. Puede ser esperado (reintentos) o un bug (productor no idempotente).

Decisión: Si los duplicados son esperados, asegura que la estrategia de dedup (ReplacingMergeTree/version) coincide con la semántica. Si no, arregla la idempotencia del productor y añade dedup en la capa de ingestión.

Task 11: Check ClickHouse replication queue (if using ReplicatedMergeTree)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT database, table, count() AS queue, sum(num_tries) AS tries FROM system.replication_queue GROUP BY database, table ORDER BY queue DESC LIMIT 5;"
analytics	events_raw	23	41

Significado: Tareas de replicación están pendientes; los reintentos sugieren problemas de red/almacenamiento o sobrecarga.

Decisión: Si la cola crece, revisa la salud de ZooKeeper/ClickHouse Keeper, la red, la latencia de disco y si los merges están saturando I/O.

Task 12: Validate “freshness” in Postgres and ClickHouse with explicit watermarks

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT max(updated_at) AS pg_max_updated_at FROM orders;"
     pg_max_updated_at
-----------------------------
2025-12-30 08:41:12.912+00

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT max(source_updated_at) AS ch_max_updated_at FROM orders_dim;"
2025-12-30 08:39:58

Significado: ClickHouse está ~74 segundos detrás de la fuente para ese dataset.

Decisión: Si el lag excede el SLA, inspecciona lag de CDC, colas de ingestión, merges y conteo de partes. Si está dentro del SLA, deja de tocarlo.

Task 13: Track ClickHouse disk pressure and bytes moved by merges

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT name, value FROM system.asynchronous_metrics WHERE name IN ('DiskUsed_default','DiskAvailable_default','MergesBytesWritten') ORDER BY name;"
DiskAvailable_default	2.31e+12
DiskUsed_default	5.84e+12
MergesBytesWritten	1.19e+12

Significado: Uso de disco y volumen de escrituras por merges son altos; puede haber amplificación de escritura.

Decisión: Pausa backfills grandes, reduce partes y asegúrate de que TTLs de movimiento/eliminación no estén causando churn constante.

Task 14: Sanity-check a CDC consumer position against Postgres LSN

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT pg_current_wal_lsn() AS current_lsn;"
 current_lsn
-------------
 5A3/1F2B9C0

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name='ch_cdc_slot';"
 confirmed_flush_lsn
---------------------
 5A3/1A90D10

Significado: El consumidor está retrasado por una distancia WAL medible.

Decisión: Si crece, escala el consumidor, arregla backpressure downstream (a menudo merges/disco en ClickHouse) o reduce temporalmente el volumen de cambios upstream para tablas no críticas.

Guía de diagnóstico rápido

Cuando el ETL se ralentiza o las métricas derivan, no tienes tiempo para convertirte en filósofo. Necesitas una ruta de triaje rápida que reduzca el dominio de fallo en minutos.

Primero: ¿es frescura, corrección o coste?

• Problema de frescura: los datos llegan tarde; los timestamps máximos están retrasados.
• Problema de corrección: duplicados, filas faltantes, joins erróneos o “los números cambiaron después del backfill”.
• Problema de coste: picos de CPU/I/O, merges fuera de control, consultas que caducan.

Segundo: localiza el límite del cuello de botella

1. Fuente (Postgres) extracción: consultas lentas, contención de locks, lag del slot de replicación.
2. Transporte: backlog en la cola, errores del consumidor, problemas de tamaño de lote.
3. Sink (ClickHouse) ingestión: explosión de partes, merges rezagados, cola de replicación.
4. Consultas de servicio: patrones de consulta malos, falta de pruning, mal diseño de tablas.

Tercero: las comprobaciones mínimas que suelen encontrarlo

1. Lag de watermark: compara tiempos máximos updated/event fuente vs sink (Task 12).
2. Lag de slot CDC: si usas CDC, revisa lag del slot de replicación (Task 4/14).
3. Partes y merges en ClickHouse: conteo de partes y backlog de merges (Task 6/7).
4. Consultas top por bytes leídos: encuentra la consulta que está comiéndose el clúster (Task 8).
5. Chequeo de pruning de particiones: confirma que las consultas calientes podan partes (Task 9).

Si haces esas cinco cosas, normalmente sabrás si tratas con extracción lenta, carga lenta o lecturas caras. Entonces puedes arreglar lo correcto en lugar de “afinar todo”, que es solo pánico con una hoja de cálculo.

Errores comunes: síntomas → causa raíz → solución

1) Las métricas suben y bajan tras reruns

Síntomas: volver a ejecutar un trabajo cambia totales diarios; sesiones duplicadas; “misma ventana, diferente resultado”.

Causa raíz: cargas no idempotentes (append sin dedup), o claves de upsert que no representan unicidad real.

Solución: define una clave de idempotencia determinista, carga en una tabla de staging y luego combina en tablas finales con dedup explícito (Postgres: ON CONFLICT; ClickHouse: ReplacingMergeTree con versión, o dedup explícito en la capa de consulta para tablas crudas).

2) La ingestión en ClickHouse empieza rápida y luego se hunde

Síntomas: las inserciones caducan; CPU y disco ocupados; partes activas se disparan.

Causa raíz: demasiadas inserciones pequeñas causando explosión de partes; los merges no pueden seguir el ritmo.

Solución: agrupa inserciones en bloques mayores, ajusta buffering de insert, y evita inserciones por fila. Considera reducir granularidad de particiones y revisar ORDER BY para mejorar localidad de merges.

3) El backfill “funcionó” pero las consultas se volvieron más lentas

Síntomas: después de un backfill, las consultas de scan leen mucho más; caches fallan; tiempos de respuesta empeoran.

Causa raíz: el backfill insertó datos fuera de orden respecto a ORDER BY, produciendo muchas partes y mala localidad; o afectó particiones antiguas con TTL/merges y causó churn.

Solución: backfill en orden de partición, usa tamaños de lote controlados y evita entremezclar particiones antiguas y nuevas. Valida pruning con EXPLAIN antes y después.

4) El primario de Postgres se pone lento durante extracciones

Síntomas: latencia de aplicación aumenta; pool de conexiones se satura; consultas lentas muestran sentencias de extracción.

Causa raíz: ETL reads compiten con escrituras OLTP; sequential scans por índices faltantes; transacciones largas bloqueando vacuum.

Solución: ejecuta extracciones en réplicas, añade índices apropiados para predicados incrementales, limita tamaño de lote y aplica timeouts de sentencia. Mantén transacciones ETL cortas.

5) “Usamos updated_at” pero filas aún faltan

Síntomas: la fuente tiene filas no presentes en el sink; usualmente pequeños porcentajes; difícil de reproducir.

Causa raíz: updated_at no se actualiza confiablemente en todos los cambios; confusión de zonas horarias; ventana de extracción demasiado ajustada; skew de reloj en servidores de app.

Solución: añade un updated_at disparado por la base de datos si es necesario, incluye una ventana de lookback, guarda explícitamente el límite de extracción o pasa a CDC.

6) ClickHouse muestra duplicados “a veces”

Síntomas: duplicados aparecen en raw o incluso en tablas dedup; luego “desaparecen”.

Causa raíz: confiar en merges de ReplacingMergeTree para dedup; la dedup es eventual.

Solución: acepta dedup eventual y consulta con cuidado (evita FINAL salvo necesario), o dedup en la ingestión. Para necesidades estrictas, mantén una tabla de “estado actual” construida por jobs periódicos de compactación.

7) La corrección del pipeline falla tras un cambio de esquema

Síntomas: columnas desplazadas, nulos donde no deberían, dashboards incorrectos silenciosamente.

Causa raíz: evolución de esquema sin contratos; usar SELECT *; depender de importaciones CSV posicionales; tipado débil en ingestión.

Solución: versiona esquemas, fija listas de columnas, valida con checks (tasa de nulos, cardinalidad) y falla rápido ante cambios incompatibles.

Tres mini-historias corporativas (dolorosamente familiares)

Mini-historia 1: El incidente causado por una suposición equivocada

La Compañía A gestionaba un producto de suscripción. Postgres era el sistema de facturación; ClickHouse la analítica. El equipo de datos construyó un extractor incremental basado en updated_at de la tabla subscriptions. Parecía perfecto en dev, bien en staging y “bastante bien” en producción.

Entonces finanzas notó que los números de churn estaban consistentemente desviados por una pequeña cantidad. No mucho. Suficiente para provocar reuniones semanales donde todos traían su propia hoja de cálculo como manta de consuelo.

La suposición equivocada fue simple: “updated_at cambia siempre que cambia el significado del negocio de la fila.” En realidad, un job en background alternaba un booleano vía un procedimiento almacenado que no tocaba updated_at. Esas filas nunca se extrajeron. No eran tardías. Eran invisibles.

Intentaron ampliar la ventana de extracción. Ayudó un poco, luego se estancó. Intentaron una recarga completa semanal. “Arregló” los números y convirtió los sábados en un ritual desagradable.

La solución fue aburrida y correcta: añadieron un trigger en la base de datos para forzar actualizaciones de updated_at en cualquier cambio relevante, e introdujeron una comprobación de reconciliación secundaria que comparaba conteos diarios y un checksum de campos clave entre Postgres y ClickHouse. Una vez hecho eso, las actualizaciones faltantes se convirtieron en alertas en lugar de misterios.

Mini-historia 2: La optimización que salió mal

La Compañía B tenía un pipeline de eventos de alto volumen hacia ClickHouse. Las inserciones llegaban desde múltiples microservicios, cada uno enviando pequeños lotes cada pocos segundos. Alguien notó latencia de ingestión y “optimizó” aumentando concurrencia: más hilos, más inserts paralelos, intervalos de retry más cortos. Las gráficas parecían emocionantes. Como un lanzamiento de cohete.

Dos días después, la latencia de consultas se disparó. La CPU no era el problema. Era el disco. Las partes activas por tabla pasaron de “saludable” a “¿es esto un bug?”. Los merges en segundo plano empezaron a ocupar la vida del clúster, y las colas de replicación crecieron porque las réplicas no podían seguir el fetch y los merges.

Habían optimizado lo incorrecto. Redujeron la latencia por petición para los productores a costa de la estabilidad del clúster. ClickHouse puede ingerir muy rápido, pero quiere bloques grandes. Muchos bloques diminutos son como alimentar una astilladora con palillos: técnicamente posible, emocionalmente agotador.

El rollback no fue glamuroso: forzaron batching aguas arriba (bloques más grandes, menos inserciones), introdujeron un buffer/cola para suavizar picos y limitaron backfills a ventanas de baja carga. La latencia de ingestión mejoró de todos modos porque los merges se volvieron manejables. La “optimización” había sido un impuesto, no una característica.

Mini-historia 3: La práctica aburrida pero correcta que salvó el día

La Compañía C gestionaba un marketplace. Su analítica alimentaba decisiones de precios, así que la corrección importaba. Tenían una regla: cada pipeline debía publicar un informe de reconciliación diario en una pequeña tabla Postgres—conteos, claves distintas y un checksum por partición. No era sofisticado. No era machine-learning. Era aritmética.

Un martes, un deploy cambió cómo un servicio generaba event IDs. No fue malicioso. Ni siquiera obviamente erróneo. Pero cambió la semántica de idempotencia: los reintentos ahora generaban nuevos IDs en lugar de reutilizar los mismos.

En ClickHouse, los duplicados empezaron a acumularse. Los dashboards derivaron al alza. La mayoría de equipos no lo hubiese notado por una semana, porque la deriva era gradual y todos suponen “el tráfico subió”.

El job de reconciliación lo notó en horas: event_id distintos por día bajó respecto al total de filas y el checksum divergió. La alerta llegó al canal adecuado, y el on-call tuvo suficiente evidencia para señalar directamente “cambió la generación de IDs” en lugar de perseguir problemas fantasma de merge o culpar a ClickHouse.

Arreglaron el productor, reinyectaron una pequeña ventana y siguieron. No ocurrió nada heroico. Ese es el punto: las comprobaciones aburridas previenen incidentes emocionantes.

Listas de verificación / plan paso a paso

Paso a paso: elegir el patrón correcto para cada dataset

1. Clasifica el dataset: eventos inmutables, dimensiones de cambio lento, entidades mutables o métricas agregadas.
2. Define la clave única: event_id, clave natural o clave sintética + versión.
3. Elige el tiempo autoritativo: event_time vs updated_at vs CDC LSN; documéntalo.
4. Decide cómo manejar actualizaciones/eliminaciones: ignorar, anexar nueva versión o aplicar cambios de estado vía dedup/merge.
5. Elige el mecanismo de extracción: incremental por timestamp con lookback, cursor numérico o CDC.
6. Elige el motor de tabla en ClickHouse: MergeTree para inmutables, ReplacingMergeTree para “última versión”, AggregatingMergeTree para rollups.
7. Escoge particionado: normalmente por tiempo; confirma pruning con EXPLAIN.
8. Elige ORDER BY: coincide con filtros y claves de agrupación más comunes; evita caos de alta cardinalidad si perjudica la localidad de merges.
9. Construye reconciliación: por partición/ventana; conteos + claves distintas + checksum.
10. Define procedimiento de backfill: ordenado por partición, con rate-limit y observable.

Checklist operacional: antes de poner en producción un nuevo pipeline ETL

• ¿Puedo volver a ejecutar el job sin duplicados o deriva?
• ¿Tengo watermarks explícitos almacenados en algo duradero?
• ¿Conozco la tardanza máxima tolerada y el diseño la maneja?
• ¿Las extracciones pesadas están fuera del primario de Postgres?
• ¿La ingestión en ClickHouse agrupa en bloques grandes?
• ¿Puedo demostrar que el pruning de particiones funciona para las 3 consultas principales?
• ¿Tengo alerta en lag de slot CDC / lag de ingestión / explosión de partes?
• ¿Puedo backfill un día sin afectar la frescura del día actual?

Plan de backfill que no quemará producción

1. Congela la definición: versiona el código de transformación usado para el backfill.
2. Elige ventanas: no backfillees “todo el historial” en un solo job. Usa particiones día/semana.
3. Raciona: limita la tasa de inserts en ClickHouse y concurrencia de merges; evita horas pico de consulta.
4. Escribe en una tabla shadow: valida conteos/checksums antes de intercambiar vistas o enrutar consultas.
5. Reconcilia: compara origen/sink por partición, no globalmente.
6. Cambia gradualmente: empieza con dashboards internos antes de los ejecutivos. Tu yo futuro agradecerá menos reuniones sorpresa.

Preguntas frecuentes

1) ¿Debería usar PostgreSQL o ClickHouse como mi área de staging?

Si el staging es transitorio y pequeño, Postgres puede funcionar. Si el staging es grande, crudo y append-heavy, ClickHouse o una zona de aterrizaje externa suelen ser mejores. El staging no debe convertirse en un cajón de basura sin fin.

2) ¿Puede ClickHouse reemplazar mis consultas analíticas en Postgres directamente?

Para analítica de solo lectura con alto volumen, a menudo sí. Pero ClickHouse no reemplazará las semánticas transaccionales, restricciones estrictas y patrones de actualización a nivel de fila sin replantear tu modelo de datos.

3) ¿Cuál es la carga incremental segura más simple desde Postgres?

Una extracción incremental usando un updated_at fiable más una ventana de lookback, cargando en una tabla de staging y luego haciendo upsert en el modelo final con claves deterministas.

4) ¿Cuándo debo cambiar de incremental por timestamp a CDC?

Cuando las actualizaciones/eliminaciones importan, cuando no puedes confiar en updated_at o cuando necesitas captura exacta de cambios sin escanear ventanas grandes. CDC añade complejidad operacional pero elimina ambigüedad.

5) ¿ReplacingMergeTree es suficiente para “hacer upserts” en ClickHouse?

Es suficiente para “eventual last-write-wins” si proporcionas una clave de dedup estable y (idealmente) una columna de versión. Si necesitas unicidad estricta al leer, necesitarás lógica adicional o otro enfoque.

6) ¿Por qué se desaconsejan las consultas FINAL?

FINAL fuerza merges/dedup en tiempo de consulta y puede convertir un escaneo rápido en una operación costosa. Es una herramienta de depuración o último recurso, no por defecto.

7) ¿Cómo evito romper dashboards durante la evolución de esquema?

Versiona esquemas, evita SELECT *, valida tasas de nulos y cardinalidades en columnas nuevas y usa listas de columnas explícitas en ingestión. Fallar rápido es mejor que estar incorrecto en silencio.

8) ¿Cuál es la mejor forma de manejar deletes de Postgres en ClickHouse?

O modelas “tombstones” (un evento de delete) y filtras en tiempo de consulta, o mantienes una tabla de estado actual donde se aplican deletes vía dedup/versioning. ClickHouse puede hacer deletes, pero eliminaciones frecuentes a nivel fila son caras.

9) ¿Cómo sé si ClickHouse está lento por ingestión o por consultas?

Revisa partes activas y backlog de merges primero (presión por ingestión), luego el log de consultas por bytes leídos y duraciones (presión por consultas). Si ambos están altos, tus escrituras crearon el problema de lectura—normalmente demasiadas partes o mal particionado.

10) ¿Cuál es una línea base razonable para reconciliación?

Por partición/ventana: conteo de filas, conteo distinto de claves y un checksum/hash sobre una proyección estable de campos clave. Añade tasa de llegada tardía si el tiempo importa (y importa).

Conclusión: próximos pasos para reducir riesgo esta semana

Si quieres ETL que no cree caos de datos, no empieces afinando perillas. Empieza por decidir qué significa “correcto” y luego hazlo observable.

1. Define claves de idempotencia para tus 3 datasets principales y documenta donde los ingenieros los encuentren.
2. Añade watermarks explícitos (tiempo/versión max fuente y tiempo/versión max sink) y alerta por lag.
3. Ejecuta un job de reconciliación diario para un pipeline crítico: conteos, claves distintas, checksum por día. Ponlo en producción. Itera.
4. En ClickHouse, mide partes y merges y arregla patrones de inserciones pequeñas antes de que se vuelvan un estilo de vida.
5. Mueve extracciones pesadas fuera del primario de Postgres o cámbialas a CDC si las actualizaciones importan y la tabla es grande.

Los sistemas en producción no premian el optimismo. Premian contratos explícitos, modos de fallo controlados y pipelines que puedes reiniciar un martes sin negociar con el universo.