These decisions shape how ClickHouse stores, reads and transforms data. Understand them before production: they are easy to set early and costly to change once the data grows.
Object storage (S3, GCS or Azure Blob) with a local cache. Compute scales apart from storage.
Scaling
Built in, vertical and horizontal: bigger or more replicas. MergeTree runs as SharedMergeTree, and replicas share one copy of the data. No Keeper, shards or ON CLUSTER to manage.
You run
Tables and queries. Backups, upgrades and scaling are handled.
Pricing
Pay for what you use. Compute is billed while it runs and is the expensive part; storage in object storage is cheap.
Fits
Spiky or growing load, large and rarely read data, a fast start, a small team.
Usually local NVMe or SSD on each server, with a full copy on every replica. Object storage and tiered setups are also possible.
Scaling
Replicas with ReplicatedMergeTree, coordinated by ClickHouse Keeper (typically 3 nodes). Optional shards use Distributed tables to spread data and I/O across servers.
You run
Self-hosted: everything, from upgrades and backups to monitoring. Managed: the vendor operates it in its cloud or yours.
Pricing
Follows the servers and storage underneath, yours or the vendor's. Local SSD plus full replica copies can cost more per stored byte than object storage.
Fits
Steady heavy load, low-latency local I/O, private infrastructure, data residency, control over versions and hardware.
Batch rows, and avoid spreading one insert across many partitions. Here, PARTITION BY toDate(timestamp) gives each day one partition, where background merges keep combining the parts. Partitioning is mainly for data lifecycle; a real table may need coarser partitions, or none. See the official guide, Selecting an insert strategy ↗.
Where each batch lands
Illustrative exampleMerge 4 is possible, not certain: it rewrites the whole 00:00–10:00 part to absorb 32 k rows, and ClickHouse prefers to merge parts of similar size.
TOO_MANY_PARTS is a common production error: parts pile up faster than background merges can combine them. Sensible partition and sorting keys let merges run faster and use fewer resources, since merges never combine parts across partitions and well-sorted data compresses into fewer bytes to rewrite.
Shard optional · open-source ClickHouse
A slice of a table's rows kept on its own servers. Sharding spreads disk I/O across servers and can speed up large reads, but a single server works fine without it. ClickHouse Cloud uses shared storage, so there are no shards for users to manage.
Partition
A logical group of parts that share the same partition-key value. Parts from different partitions are never merged.
Part
A self-contained, immutable unit of sorted columnar data. Inserts create new parts; background merges replace smaller parts with larger ones within the same partition. Small parts can use Compact format, while larger parts typically use Wide format.
Granule
A consecutive range of rows within a part, ordered by the sorting key. It is the smallest unit ClickHouse reads—usually 8,192 rows by default, though the size can vary.
Indexes
The sparse primary index records sorting-key values at granule boundaries. Data-skipping indexes store metadata for one or more granules. Together they rule out granules that cannot match a query before reading column data. Index data is analyzed in RAM; depending on settings, it may stay resident or be loaded and cached on demand.
Partition → part → granule
Illustrative exampleB1 here is the day's first batch. The 10 MiB compact-part threshold is configurable.
FINAL gives exact results before merges catch up. Duplicates stay until their parts merge; FINAL drops them while reading: cheap on one merged part, costly on overlapping ones.
Force the merge
Background merges may leave a closed day in several parts. Force one part with min_age_to_force_merge_seconds plus min_age_to_force_merge_on_partition_only, or a nightly OPTIMIZE TABLE … PARTITION … FINAL per closed day. ClickHouse advises against OPTIMIZE FINAL, but exact results on frequent queries need it.
The same FINAL query on 6 parts vs 1 merged part
Illustrative exampleLate and re-delivered events make a day's parts overlap.
The example table
CREATE TABLE events (event_id UUID, …)ENGINE = ReplacingMergeTreePARTITION BY toDate(timestamp)ORDER BY (event_type, timestamp, event_id)PRIMARY KEY (event_type, timestamp)
Merge in RAM
Overlapping parts are merge-sorted by the whole sorting key while reading.
More data read
The rest of the sorting key, here event_type and the UUID event_id, plus every duplicate row.
Sorting key vs primary key
ORDER BY sets the row order and which rows count as duplicates, so it ends with event_id.
PRIMARY KEY sets what the sparse index stores, by default the whole sorting key.
PRIMARY KEY (event_type, timestamp) leaves the 16-byte UUID out of every index entry and does not change the row order.
Each partition on its own
Two copies of an event share its timestamp, so they land in the same day's partition.
FINAL then merges within one partition only, and a day that is one merged part needs no merge.
This is do_not_merge_across_partitions_select_final, automatic since 23.12 when the partition key uses only primary-key columns, as here.
05 · Transformations
How do I join big tables without running out of memory? #
ClickHouse has a rich set of functions for all kinds of transformations. However, performance problems, above all MEMORY_LIMIT_EXCEEDED errors, happen often when pipelines are not optimized.
Parallel hash join
The default in current releases. It builds in-memory hash tables from the right-hand tables and streams the left one through them; if configured, oversized hash data can spill to disk.
One tenant per run
Every table is filtered with WHERE tenant_id = t before the join, so only that tenant reaches memory. Each run appends to the same destination table. This lowers peak memory but adds repeated query overhead, so benchmark the real pipeline.
tenant_id first
With ORDER BY (tenant_id, …), a tenant is one range in each table, so a run reads only its own granules. Together, the runs read each table about once, plus a few boundary granules.
One big join vs. one tenant at a time
Illustrative example
Other join algorithms
grace_hash and full_sorting_merge need less memory, but they are usually a compromise: they pay for it with disk I/O or a sort.