How ClickHouse works

Columns, not rows

A transactional database stores a record as one block, which is perfect for "fetch order 5512" and wasteful for "sum revenue by region over 90 days". ClickHouse stores each column in its own file, so an aggregate reads only the columns it names, and reads them compressed, in vectorised batches.

One INSERT, one part

ClickHouse tables are MergeTree: writes are never edited in place. Each INSERT statement writes a new immutable part, and background merges keep compacting parts into bigger ones. Queries read across whatever parts exist right now.

That design is why writes are fast and why the failure mode is so specific. If statements arrive faster than merges compact, parts accumulate, every query has more files to open, and past a threshold ClickHouse refuses the write outright with too many parts.

Why not just merge harder when parts pile up?

Merging is real work: read the parts, sort, re-compress, write a new one, delete the old ones. It competes with queries for the same CPU and disk bandwidth. Merging harder buys insert headroom by taking it out of query latency, which is why the sustainable part rate is a property of the hardware rather than a setting you can simply turn up.

Batch, or let the server batch

The classic fix is client-side batching: buffer rows in the producer and send one statement per second per table. When the producer cannot buffer (many small stateless writers, for example), async_insert moves the buffer into ClickHouse itself: the server accumulates incoming rows and flushes one part per window.

⚓ On the SysPad node, turn on Async inserts and watch the ceiling jump from the merge rate to the CPU rate, with the extra milliseconds showing up in added latency. That swap IS the decision.

Replicas read for free, write for full price

A replicated table keeps a full copy on each replica. Any replica can answer a query, so read capacity scales with the cluster. But every replica has to apply every insert, so a write costs the cluster its CPU multiplied by the replication factor, and shards, not nodes, are what the part rate scales with.

⚓ Four nodes at replication 2 is two shards, not four. Doubling nodes to buy insert headroom does nothing if you double the copies at the same time.

Cloud units or your own nodes

ClickHouse Cloud separates compute from storage: you pay per compute unit hour (a unit is 2 vCPU and 8 GiB) and about $25 per compressed TB per month for storage that all replicas share. Self-hosted, you pay for the nodes by the hour and for disk on every replica, because each one holds its own full copy.

⚓ That is why the same 5 TB of data costs one storage bill on Cloud and two or three self-hosted. Compression ratio is the other half of the number, and SysPad does not model it: the TB figure you enter is already compressed.