ClickHouse

What it is

An Apache-2.0 licensed column-oriented database for analytical queries. It stores each column separately, compresses it, and executes queries with vectorised operations over large blocks.

Architecture

Data is written as immutable parts, sorted by the table's ORDER BY, and merged in the background — an LSM-like design. A sparse primary index holds one entry per 8192-row granule, so it stays in memory even for enormous tables and identifies which granules to scan. Replication is per table via ReplicatedMergeTree with ClickHouse Keeper coordinating; sharding is expressed with Distributed tables.

Best use cases

• Event and log analytics: clickstream, application logs, telemetry, security events.
• Business intelligence over large fact tables.
• High-volume time series, if you are prepared to build retention and rollups with TTL and materialized views.
• Any workload dominated by GROUP BY over hundreds of millions of rows.

When not to use it

• As an OLTP database: no general transactions, no cheap single-row updates, no foreign keys.
• For point lookups by arbitrary key — the sparse index still reads a whole granule.
• For frequently mutated data; updates and deletes are asynchronous part rewrites.
• With small, frequent inserts, which overwhelm the merge scheduler.

Data model

Typed columns with LowCardinality for bounded value sets and explicit codecs (Delta, ZSTD) for compression. The ORDER BY defines physical layout and therefore which queries are fast; it cannot be changed in place.

Consistency and transactions

Inserts are atomic per block. No multi-statement transactions. Replication is eventually consistent unless insert_quorum is set; select_sequential_consistency restricts reads to quorum-confirmed data.

Scaling model

Vertical first — ClickHouse uses all cores and is often fast enough on one large server. Then replication for availability, and sharding via Distributed tables for capacity.

Replication

ReplicatedMergeTree with ClickHouse Keeper. Replicas are equal; any accepts inserts. Keeper losing quorum makes replicated tables read-only, so Keeper availability is cluster availability.

Backup and recovery

Native BACKUP/RESTORE to object storage with incremental support, or FREEZE for hard-link snapshots. Distributed backups are per shard and not point-in-time consistent across the cluster.

Monitoring

system.parts, system.merges, system.mutations, system.replicas, system.replication_queue and system.query_log. See ClickHouse Monitoring.

Common mistakes

Production checklist

See ClickHouse Production Checklist.

Related guides