ClickHouse Distributed Tables: Tips & Tricks

Tip: Distributed Table Abstraction

CREATE TABLE page_views_distributed ON CLUSTER my_cluster
AS page_views
ENGINE = Distributed(my_cluster, default, page_views, rand());

Queries this table automatically run across all shards.

Gotcha: Shard Key Matters

rand() distributes randomly. Use a meaningful key for co-located data:

ENGINE = Distributed(my_cluster, default, page_views, user_id);

Same user_id always goes to the same shard.

Tip: Replicated Tables

CREATE TABLE page_views_replicated ON CLUSTER my_cluster
(
    id UInt64,
    url String,
    created_at DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/page_views', '{replica}')
ORDER BY (created_at, url);

Data is replicated across nodes for fault tolerance.

Gotcha: Distributed Queries Are Slower

A query on a distributed table needs to contact all shards and merge results. Local tables are faster.

Tip: Cluster Configuration

<clickhouse_remote_servers>
    <my_cluster>
        <shard>
            <replica><host>node1</host><port>9000</port></replica>
        </shard>
        <shard>
            <replica><host>node2</host><port>9000</port></replica>
        </shard>
    </my_cluster>
</clickhouse_remote_servers>

Defines the cluster topology.

Gotcha: ON CLUSTER for DDL

ALTER TABLE page_views ON CLUSTER my_cluster ADD COLUMN browser String;

Applies the change to all nodes. Without ON CLUSTER, only the current node is affected.

Tip: Global Queries

SELECT url, count() FROM page_views_distributed GROUP BY url;

Runs on all shards, merges results automatically.

Gotcha: Distributed Tables Don't Enforce Uniqueness

Uniqueness is per shard, not global. Two shards can have the same row.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

One architectural pattern I use with ClickHouse is the 'raw + aggregated' table design. Raw data goes into a MergeTree with short TTL. Simultaneously, a materialized view aggregates the data into a SummingMergeTree or AggregatingMergeTree with long retention. This gives me both the ability to debug individual events (from raw data) and fast dashboard queries (from aggregated data). The two tables serve different purposes and neither compromises for the other.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)