ClickHouse Cloud: Fast, Updatable Lookups with the Join Table Engine

Summary

ClickHouse Cloud provides an in-memory Join table engine for precomputed hash-map lookups and joins, aimed at enriching append-only fact data with dimensions that change occasionally. With Join(ANY, LEFT, key), inserting another row for a key acts as an upsert, while joinGet and matching JOIN clauses use the in-memory structure. Open-source Join tables are not distributed and persist each INSERT batch as separate .bin files without automatic compaction, but ClickHouse Cloud transparently uses SharedJoin with MergeTree for ALL or ReplacingMergeTree for ANY. In the demonstrated enrichment query, duplicate inserts for id 1 leave value 42 visible, while the underlying table retains duplicates until merging; the in-memory load applies FINAL and always selects the latest entry, making ANY LEFT suitable for frequently updated dimensions.

Takeaways

1. 1

   Join(ANY, LEFT, id) treats a later insert with the same key as an upsert and exposes the latest value in enrichment queries.

2. 2

   Open-source Join tables keep one compressed Native-format .bin file per INSERT batch and have no automatic background compaction, so repeated updates can degrade performance.

3. 3

   ClickHouse Cloud's SharedJoin backing automatically clusters Join tables and uses ReplacingMergeTree for ANY joins, retaining duplicates in storage until merges while presenting deduplicated in-memory results.