ClickHouse Usage Guide: Application Scenarios, Table Engine Selection, Development Standards, Cluster Architecture, and Configuration

ClickHouse is an open‑source columnar DBMS widely used for OLAP workloads; it offers high availability and performance through a vectorized execution engine and rich features.

Application scenarios include user behavior analytics, real‑time log analysis, AB testing, user profiling, and large‑scale data warehousing, handling billions of rows with sub‑second query latency.

Table engine selection is divided into four series—Log, MergeTree, Integration, and Special—plus Replicated and Distributed variants. The MergeTree family is the most commonly used, supporting partitioning, primary keys, sampling, and TTL.

MergeTree engine stores data in immutable parts that are periodically merged; it provides sorting by primary key, partitioning, replication, and sampling. Example creation syntax:

CREATE TABLE IF NOT EXISTS db.table_name (
    name1 type1,
    name2 type2,
    ...
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[TTL expr]
[SETTINGS name=value, ...];

ReplicatedMergeTree adds replication via ZooKeeper, enabling high availability but increasing ZooKeeper load; its DDL includes the ZooKeeper path and replica identifier.

ReplacingMergeTree deduplicates rows with the same sorting key during merges, optionally using a version column to control which row is kept.

SummingMergeTree aggregates numeric columns for rows sharing the same primary key, similar to a GROUP BY operation.

AggregatingMergeTree allows custom aggregate functions for incremental statistics.

Distributed engine acts as a logical table that routes queries to underlying local tables; it requires a cluster name, database, table, and optional sharding key.

Development standards cover SQL writing guidelines (prefer IN over JOIN for small tables, avoid SELECT *, use LIMIT, include partition key in queries, avoid high‑cardinality group‑by, monitor CPU usage) and data‑write best practices (batch inserts, avoid single‑row inserts, specify partition keys, limit number of partitions, pre‑group data before insert, be aware of replication constraints).

Cluster architecture typically uses a 2‑shard, 2‑replica setup (expandable to more shards), with separate local tables for writes and distributed tables for reads, leveraging chproxy for load balancing.

ZooKeeper role is to coordinate distributed DDL and replica state; it can become a bottleneck for large tables, so minimalistic part headers and proper configuration are recommended.

chproxy is an HTTP load‑balancer for ClickHouse, providing routing, caching, rate limiting, and SSL management. Example test command:

echo 'show databases;' | curl 'http://10.200.161.49:9009/?user=writeuser&password=xxxx' --data-binary @-

Client tools include DBeaver (open‑source DB admin tool) and Superset/Tabix for BI dashboards.

Availability considerations depend on replication and sharding choices; replication provides failover, while pure sharding does not. ZooKeeper outages affect writes regardless of replication.

Key configuration parameters such as max_concurrent_queries, max_bytes_before_external_sort, background_pool_size, max_memory_usage, and max_bytes_before_external_group_by should be tuned to improve stability and performance.

For further learning, refer to the official ClickHouse documentation and Chinese community site.

Author : Liu Yanpeng, Database Engineer at NetEase Hangzhou Research Institute.