ClickHouse Overview: Architecture, Features, Performance, and Practical Use Cases at TAL Education

In the early stage of the log center, log retrieval and analysis relied on Elasticsearch, but growing data volume and complex analytical requirements led to the selection of ClickHouse as a more suitable database.

Key requirements: massive distributed data, real‑time writes, fast calculations, strong SQL capabilities, simple operations, and high compression efficiency.

ClickHouse meets these needs with a complete DBMS feature set, including DDL/DML support, permission control, backup/recovery, and distributed management.

Core features:

Column‑oriented storage and effective compression, enabling faster queries by reducing scanned data.

Vectorized execution engine that processes columns in batches, improving CPU utilization and cache performance.

Rich relational SQL support (GROUP BY, ORDER BY, etc.) covering about 90% of typical queries.

Multiple table engines (MergeTree, ReplicatedMergeTree, Distributed, etc.) allowing flexible storage strategies.

Multi‑threaded and multi‑master architecture for high concurrency and fault tolerance.

Common application scenarios include telecom data storage, user behavior analysis for social platforms, ad‑network analytics, log analysis, remote sensing, BI, and IoT data processing.

Architecture: ClickHouse’s cluster operates at the table level. A single‑node design is illustrated, followed by a clustered design where shards and replicas are defined in the <yandex> <clickhouse_remote_servers> <cluster1> <shard> <internal_replication>true</internal_replication> <replica> <host>clickhouse-node1</host> <port>9000</port> </replica> <replica> <host>clickhouse-node2</host> <port>9001</port> </replica> </shard> <shard> <internal_replication>true</internal_replication> <replica> <host>clickhouse-node3</host> <port>9000</port> </replica> <replica> <host>clickhouse-node4</host> <port>9001</port> </replica> </shard> ... </cluster1> </clickhouse_remote_servers> ... </yandex> configuration, enabling replicated MergeTree tables and Distributed tables for sharding and replication.

The Replicated*MergeTree engine provides data replication via ZooKeeper, while the Distributed engine offers query routing across shards without storing data locally.

Performance: Single‑node insertion reaches 100‑150 M rows/s; a simple GROUP BY on 100 million rows completes in ~2.3 s without indexes and ~0.1 s with indexes. Concurrency defaults to 100 queries, though heavy OLAP workloads may saturate CPU and memory.

Practice at TAL Education: Multiple business units (log center, Grafana dashboards, real‑time streaming) ingest data via Flink, Spark, or custom tools into ClickHouse, achieving faster analytics. The platform supports both departmental and cross‑departmental use, with data pipelines from Hive to ClickHouse and visualization via Grafana.

Monitoring relies on system tables and Grafana dashboards; scripts handle node health checks and automated restarts. Issues encountered include high server load from large queries, DDL statement blocking, and occasional ZooKeeper disconnections, mitigated by memory limits, query throttling, caching, and ZooKeeper scaling.

Future plans: deepen business‑driven schema design, continuously optimize SQL for ClickHouse’s strengths, introduce caching or approximate computing for heavy queries, isolate clusters for critical workloads, enhance monitoring and alerting, and explore additional ClickHouse features.