Understanding ClickHouse Distributed JOIN Implementation and Best Practices

JOIN operations are essential in OLAP scenarios, and ClickHouse's implementation warrants a deep dive.

1. ClickHouse Single‑Node JOIN

ClickHouse uses HASH JOIN by default, with an optional MERGE JOIN that spills to disk and performs slower. The HASH JOIN algorithm builds a hash map from the right table in memory and probes it with batches from the left table.

Example syntax:

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

If the right table exceeds available memory, the join cannot complete, so the smaller table is typically chosen as the right side.

2. ClickHouse Distributed JOIN

In a cluster, distributed JOINs involve left and right tables that are themselves distributed. Common mechanisms are Broadcast JOIN, Shuffle JOIN, and Colocate JOIN. ClickHouse implements a Broadcast‑like approach with pre‑distribution to achieve Colocate JOIN.

Two execution modes exist: with and without the GLOBAL keyword.

2.1 Ordinary Distributed JOIN

The process replaces the left distributed table with its local counterpart, distributes the modified query to all nodes, each node executes it, and results are aggregated back to the initiator. This leads to a read amplification problem because each node reads the entire right table, resulting in N×N reads for N nodes.

SELECT a_.i, a_.s, b_.t FROM a_all as a_ JOIN b_all AS b_ ON a_.i = b_.i

Here a_all and b_all are distributed tables with local equivalents a_local and b_local.

2.2 GLOBAL JOIN

GLOBAL JOIN first computes the right‑hand side (or sub‑query) on the initiator, then broadcasts the result to all nodes, where each node joins it with its local left table. This avoids the N×N read amplification but can consume significant network bandwidth if the right side is large.

SELECT a_.i, a_.s, b_.t FROM a_all as a_ GLOBAL JOIN b_all AS b_ ON a_.i = b_.i

3. Best Practices for Distributed JOIN

Prefer a small right‑hand table to keep the hash map within memory.

Use GLOBAL JOIN when the right side size is controllable to prevent query amplification.

Apply data pre‑distribution (sharding by join key) to enable Colocate JOIN, turning a distributed join into a local join and eliminating unnecessary data movement.

4. Summary

The article covered ClickHouse's JOIN implementation, highlighted the performance impact of ordinary distributed joins, demonstrated how GLOBAL JOIN mitigates read amplification, and recommended practical strategies—reducing right‑table size, leveraging GLOBAL JOIN, and pre‑sharding data—to achieve efficient large‑scale joins.