Think in Columns

Things to get right early.

AI tools such as Claude Code, Codex or the assistant built into ClickHouse Cloud can handle most of the work. Still, understand these before going to production: they are easy to decide on day one and costly to change once the data is large.

01 · Deployment

Where should ClickHouse run?#

ClickHouse Cloud

Run by ClickHouse, Inc. · clickhouse.com/cloud

Data

Object storage (S3, GCS or Azure Blob) with a local cache. Compute scales apart from storage.

Scaling

Built in: 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.

Open-source ClickHouse

Self-hosted, e.g. on Kubernetes with the open-source Altinity operator , or managed for you, e.g. by Altinity.Cloud

Data

Disks on each server (NVMe or SSD): usually faster I/O than object storage, most of all when data is not cached. Every replica holds a full copy; older data can move to HDD or S3.

Scaling

Replicas with ReplicatedMergeTree and ClickHouse Keeper (3 nodes). Shards with Distributed tables, which spread data and disk I/O across servers.

You run

Self-hosted: everything, from upgrades and backups to monitoring. Managed: the vendor does, in its cloud or yours.

Pricing

Follows the servers and disks underneath, yours or the vendor's. Storage usually costs more: it is SSD, and every replica holds a full copy.

Fits

I/O-heavy queries, steady heavy load, data residency, control over versions and hardware.

02 · Inserts

How do I keep inserts fast?#

Few, large inserts, each within one partition. With PARTITION BY toDate(timestamp), each day's rows share one partition, and background merges keep combining the parts inside it. The official guide, Selecting an insert strategy , explains how to insert; the animation shows where the rows land and how merges keep up.

Where each batch lands
Batches land in today's partitionPARTITION BY toDate(timestamp) · one partition per date; background merges combine the parts inside itpartition 2025-06-14 · yesterdayuntouched1 part · ≈ 150 M rows · ≈ 23 GiBInsertB1B1B2B2B3B3B4B4partition 2025-06-15 · todaybackground merges00:00–10:00 · merged earlier8 k8 k8 k8 k16 k16 k32 kmerge 1merge 2merge 32×rows rewritten per row inserted · merges only touch today's new parts
Batches land in today's partitionPARTITION BY toDate(timestamp) · one per datepartition 2025-06-14 · yesterdayuntouched1 part · ≈ 150 M rowsInsertB1B1B2B2B3B3B4B4partition 2025-06-15 · todaybackground merges00:00–10:008 k8 k8 k8 k16 k16 k32 kmerge 1merge 2merge 32×rewritten per row inserted · today only
Illustrative example

03 · Storage layout

What are partitions, parts and granules?#

Shard optional · open-source only
A slice of a table's rows kept on its own servers. Sharding spreads disk I/O across servers and can speed up reads a lot, but a single server works fine without it. ClickHouse Cloud has no shards.
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.
Partition → part → granule
1Partitions hold partsPARTITION BY toDate(timestamp) · each insert writes a new part into the partition of its date1partition 2025-06-132 days agowide · ≈ 120 M rowswide · ≈ 80 M rowspartition 2025-06-14yesterdaywide · ≈ 150 M rowspartition 2025-06-15 · todayevery 5 s: 8 k rows → 1 compact partskipping ahead: B13 … B17,280B1B2B3B4B5B6B7B8B9B10B11B12…B1–B4 · 32 k rows · wideB1–B100 · ≈ 0.8 M rowsB1–B1,000 · ≈ 8 M rowsB1–B10,000 · ≈ 82 M rowsB1–B17,280 · ≈ 142 M rows · wideevent_type.bin, timestamp.bin, … one file per columncompact: all columns in one file, for parts under 10 MiB (min_bytes_for_wide_part)wide: one file per column2Indexes are checked in RAMper part: primary.idx holds the first key of every granule; the skip index holds min/max revenue per granule2WHERE event_type = 'purchase' AND revenue > 500In RAMprimary: always · skip: read on use, then cachedprimary.idxevent_typetimestampskip · revenuemark 0click2025-06-15 00:00:00mark 1click2025-06-15 00:00:10mark 2view2025-06-15 00:00:00mark 3view2025-06-15 00:00:100 – 00 – 2,3100 – 00 – 0On diskmerged part B1–B4, sorted by event_type, then timestampeach bar is one granule: 8,192 rows, read or skipped as a wholeclick …granule 0skippedclickpurchasegranule 1readview …granule 2skippedview …granule 3skipped1 / 4granules read · 'purchase' sorts between click (mark 1) and view (mark 2), so only granule 1 can hold it; the skip index agrees
1Partitions hold partsPARTITION BY toDate(timestamp) · one per date1partition 2025-06-13 · 2 days agowide · ≈ 120 M rowswide · ≈ 80 M rowspartition 2025-06-14 · yesterdaywide · ≈ 150 M rowspartition 2025-06-15 · todayevery 5 s: 8 k rowsskipping B13 … B17,280B1B2B3B4B5B6B7B8B9B10B11B12…B1–B4 · 32 k rows · wideB1–B100 · ≈ 0.8 M rowsB1–B1,000 · ≈ 8 M rowsB1–B10,000 · ≈ 82 M rowsB1–B17,280 · ≈ 142 M rows · wide1 file per columncompact: all columns in one file (parts < 10 MiB)wide: one file per column2Indexes in RAMper part: primary.idx and the skip index2WHERE event_type = 'purchase'AND revenue > 500In RAMprimary always · skip cached on usem0click2025-06-15 00:00:00m1click2025-06-15 00:00:10m2view2025-06-15 00:00:00m3view2025-06-15 00:00:100 – 00 – 2,3100 – 00 – 0On diskpart B1–B4, sorted by event_type, then timestampeach bar is one granule: 8,192 rows, read or skipped wholeclick …granule 0skippedclickpurchasegranule 1readview …granule 2skippedview …granule 3skipped1 / 4granules read · only granule 1 can hold 'purchase'
Illustrative example

04 · 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.

Hash join
ClickHouse's default join. It loads the right-hand table into memory as a hash table and streams the left one through it.
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.
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 once.
One big join vs. one tenant at a time
1One join, all tenants at once✓ doneINSERT INTO dest SELECT … FROM a JOIN b … JOIN c …1a, b, c · ORDER BY (tenant_id, …)t1t2t3t4t5abcjoin, then appenddest · one table for every tenant2One tenant at a time✓ doneWHERE tenant_id = t on a, b and c, for t = 1 … 52a, b, c · ORDER BY (tenant_id, …): each tenant is one ranget1t2t3t4t5abcjoin, then appenddest · one table for every tenantRAM · hash tables of b and c, held while a streams throughtime · both clocks start together1one joint1t2t3t4t5100%2per tenantt1t2t3t4t533% peak1one join60 s2per tenant30 s3× less memory2× fastermemory peaks at the biggest tenant, t1, a third of the rows · each table is still read once in total
1One join, all tenants✓ doneINSERT … FROM a JOIN b … JOIN c …1t1t2t3t4t5abcdest2One tenant at a time✓ doneWHERE tenant_id = t for t = 1 … 52t1t2t3t4t5abcdestRAM · hash tables of b and ctime · both clocks start together1one joint1t2t3t4t5100%2per tenantt1t2t3t4t533% peak1one join60 s2per tenant30 s3× less memory · 2× fasterthe peak is the biggest tenant, t1: a third
Illustrative example