Problem
X5 Tech's logistics platform feeds a supply-chain "control tower" for robotized warehouses, where an idle warehouse costs money, so alerts must fire within 60 seconds. Reports must also cover the full history, and heavy queries have to leave the source systems. The earlier stack (Debezium, Kafka, Flink, S3, Trino) had end-to-end latency of 58–571 seconds, and the first ClickHouse version gave 93–379. Every source update arrives as a new insert, so each query pays for deduplication and repeated joins.
Example from the article: a load of about 20 thousand updates per second (40 thousand at worst) from Oracle, PostgreSQL and MS SQL, with reports required under 60 seconds.
Solution in brief
Split the work into landing and querying. For landing, read all Debezium topics through one Kafka Engine table as raw JSON, parse them in a materialized view, and collapse partitioning so merges and replication keep up. For queries, avoid FINAL on the full table, materialize repeated joins, and compute repeated CTE logic once. The latency range fell to 26–56 seconds.
Questions this case answers
- How to keep end-to-end latency under one minute when streaming change-data updates into an analytical store that must deduplicate on every query
- How to avoid repeated deduplication and repeated joins in heavy queries over an append-only store of row versions
- How to keep end-to-end lag under 60 seconds when streaming Debezium/Kafka change data into ClickHouse
- How to speed up heavy reporting queries over the full depth of data in a streaming ClickHouse table
Tools and methods
- Kafka Engine plus Materialized View — one table reads many topics as
JSONAsString - Debezium
__opfield — separates insert, update, delete and archiving OPTIMIZE TABLEand deltaUNION ALL— deduplicated layer plus a fresh delta- Pre-materialized joins, temporary tables — compute heavy joins and CTEs once
system.parts,system.merges— diagnose merge pressure
Google or a one-time email code. No password.
