Optimizing ClickHouse: ByteHouse’s Real‑Time Data Warehouse Breakthrough

ByteHouse Overview

ByteHouse is a cloud‑native data warehouse built on Volcano Engine, offering ultra‑fast analysis for both real‑time and massive offline workloads, with elastic scaling, high performance, and enterprise‑grade features.

Business Scenarios and ROI‑Driven Evaluation

Within ByteDance, middle‑platform services support various products such as Douyin. Real‑time data enables rapid content iteration and precise ad targeting. The authors evaluate real‑time warehouse value by measuring output effectiveness and ROI, noting superior timeliness and accuracy compared with offline warehouses.

Timeliness means end‑to‑end data flow with minimal latency; accuracy ensures no duplication or loss even in complex pipelines. Development, operations, and resource costs are also considered, highlighting the need for fast iteration, observability, and high resource utilization.

Why ClickHouse Was Chosen

ClickHouse matches the required timeliness, accuracy, and low operational cost. Its strong single‑table query performance, ability to scale by adding machines, non‑intrusive deployment, and high hardware utilization make it a natural fit.

Observed Limitations of ClickHouse

Write capability : throughput issues at very large volumes; lack of strong guarantees for “single‑write” scenarios; inefficient data updates.

Multi‑table performance : query performance degrades in multi‑table joins, a limitation of the query engine.

Stability : large‑scale deployments encounter ZooKeeper‑related stability problems and require manual operations due to missing visual management tools.

ClickHouse Evolution at ByteDance

The team iteratively optimized ClickHouse across four stages:

2017: Initial OLAP use for user‑growth analysis on petabyte‑scale data.

Expansion to the “Fengshen” BI platform, exposing issues such as ZooKeeper stability and resource fragmentation.

Addition of a wide‑table engine, data‑update capability, and a custom optimizer to support broader analytical scenarios.

Scale to 18,000 nodes (largest cluster 2,400 nodes) and introduction of multi‑level resource isolation with compute‑storage separation.

ByteHouse Real‑Time Warehouse Architecture

Data sources feed Kafka or Flink into ByteHouse, which serves the DWD and DWS layers. Projection or materialized views provide lightweight aggregation. Built‑in tools handle anomaly monitoring, tenant and task management, and resource isolation.

Key capabilities include high‑throughput ingestion, exactly‑once semantics, a proprietary ClickHouse optimizer that boosts both single‑table and multi‑table query performance, and optional compute‑storage separation with MPP or native engines.

Financial Industry Real‑Time Warehouse Considerations

The financial sector has progressed from centralized to distributed warehouses, then to big‑data platforms, and now to real‑time data lakes. Real‑time requirements arise in risk control, fraud detection, and anomaly monitoring, demanding low latency, high consistency, and robust scalability.

Common architectural choices include:

Lambda architecture: separates real‑time and batch layers but requires data cleaning for consistency.

Kappa architecture: treats all data as streams, simplifying pipelines but demanding real‑time processing for historically batch data.

Data‑lake‑plus‑stream‑batch hybrid: unifies compute but may limit performance for high‑throughput queries.

MPP storage: a variant of Kappa that leverages ByteHouse’s ClickHouse enhancements for efficient real‑time storage.

For modest early‑stage needs, ByteHouse’s storage can be adopted quickly before scaling to more complex data‑lake solutions.

Case Study 1: Real‑Time Operations Monitoring

A public‑sector bank used ByteHouse to build a real‑time monitoring dashboard, analyzing acquisition channels, ROI per user segment, and anomaly detection. ByteHouse provided high‑throughput ingestion, sub‑second query latency, and high availability, enabling phased roll‑outs of indicator panels, detailed metric templates, and customized user‑behavior analyses.

Case Study 2: Real‑Time Risk Control

Another bank’s credit‑card center deployed ByteHouse to replace batch‑oriented risk calculations. By ingesting millions of daily transactions and feeding them to a rule engine via SQL, the solution achieved near‑real‑time risk alerts, handling tens of thousands of risk transactions per day while maintaining strong query performance.