ClickHouse Usage Guide: Table Engines, Best Practices, and Cluster Architecture

ClickHouse is an open‑source columnar DBMS widely used for OLAP workloads; it offers high availability and vectorized execution, making it suitable for large‑scale data analysis such as user behavior tracking, real‑time log processing, and AB testing.

Application scenarios include user behavior analysis, real‑time log monitoring, data warehousing, and game anti‑cheat statistics, with deployments handling billions of rows per day.

Table engine selection covers four engine families—Log, MergeTree, Integration, and Special. The MergeTree family is the most commonly used, supporting partitioning, primary keys, sampling, and TTL.

MergeTree engine stores data in immutable parts that are merged in the background. Key features: primary‑key sorting, partition support, data replication, and sampling.

CREATE TABLE [IF NOT EXISTS] db.table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
)
ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

ReplicatedMergeTree adds ZooKeeper‑based replication for high availability, but large data volumes can stress ZooKeeper.

CREATE TABLE [IF NOT EXISTS] db.table_name [ON CLUSTER cluster]
(
    `id` Int64,
    `ymd` Int64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/replicated/{shard}/test', '{replica}')
PARTITION BY ymd
ORDER BY id

ReplacingMergeTree removes duplicate rows with the same primary key during merges; an optional version column can control which row is kept.

SummingMergeTree aggregates numeric columns of rows sharing the same sorting 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 shards; it requires a local engine (e.g., MergeTree) for actual storage.

Distributed(cluster_name, database_name, table_name[, sharding_key])

Development standards include SQL writing guidelines (prefer IN over JOIN for small tables, avoid SELECT *, use LIMIT, leverage partition keys, avoid heavy string columns), data‑write recommendations (batch inserts of 50k‑100k rows, specify partition keys, limit partition count), and naming conventions for local and distributed tables, materialized views, and TTL settings.

Cluster architecture typically uses a 2‑shard‑2‑replica setup (expandable to more shards), with ZooKeeper and chproxy handling coordination and load balancing. High‑availability is achieved through replicated tables; sharding without replication reduces resilience.

ZooKeeper role is to coordinate distributed DDL and replicate state; large clusters may need to minimize ZooKeeper metadata using use_minimalistic_part_header_in_zookeeper=1 .

chproxy is an HTTP proxy/load balancer for ClickHouse, providing routing, caching, and SSL management.

Client tools recommended are DBeaver, Superset, and Tabix for query and visualization.

Availability considerations depend on replication and sharding choices; replication ensures failover, while sharding alone leaves single‑point failures.

Configuration parameters to tune include max_concurrent_queries , max_bytes_before_external_sort , background_pool_size , max_memory_usage , max_memory_usage_for_all_queries , and max_bytes_before_external_group_by , all of which improve stability and performance.

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