ClickHouse Overview

ClickHouse is a column-oriented database for analytical queries. It stores each column separately, compresses it, and executes queries with vectorised operations over large blocks of values.

The design target is scanning and aggregating billions of rows quickly. Everything else — updates, point lookups, transactions — is either absent or deliberately expensive.

Architecture

Column storage. A query touching 3 of 100 columns reads roughly 3% of the data. Values in a column are similar, so compression ratios of 10× or more are common, which reduces I/O further.

MergeTree. Data is written as immutable parts, sorted by the table's ORDER BY. A background process merges small parts into larger ones. This is an LSM-like design: inserts are cheap appends, and the ongoing merge work is the price. See MergeTree Architecture.

Sparse primary index. ClickHouse indexes every 8192nd row by default (index_granularity), not every row. The index is small enough to stay in memory even for enormous tables, and a lookup identifies the granule to scan rather than the exact row.

Vectorised execution. Operations process blocks of values at a time, which uses CPU cache and SIMD instructions efficiently.

Best use cases

• Event and log analytics: clickstream, application logs, telemetry, security events.
• Product and business intelligence over large fact tables.
• Time-series data at high volume, when you are prepared to build retention and rollups yourself.
• Any workload dominated by GROUP BY over hundreds of millions of rows with a bounded set of filter columns.

When not to use it

• As an OLTP database. No general-purpose transactions, no efficient single-row updates, no foreign keys.
• For point lookups by an arbitrary key. If your access is "fetch one row by id", the sparse index still scans a granule, and a row store does this far better.
• For frequently mutated data. UPDATE and DELETE are asynchronous mutations that rewrite parts.
• With small, frequent inserts. Every insert creates a part; thousands of tiny inserts per second overwhelm the merge scheduler. See Insert Strategies.

Data model

CREATE TABLE events
(
    event_time   DateTime      CODEC(Delta, ZSTD(1)),
    event_date   Date          MATERIALIZED toDate(event_time),
    tenant_id    UInt64,
    user_id      UInt64,
    event_type   LowCardinality(String),
    country      LowCardinality(String),
    duration_ms  UInt32,
    payload      String        CODEC(ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_time)
TTL event_date + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

Two habits carry most of the benefit: LowCardinality(String) for columns with a limited set of values, which turns them into dictionary-encoded integers; and explicit CODECs such as Delta for monotonically increasing values.

Consistency and transactions

Inserts are atomic per block. There are no multi-statement transactions, and no cross-table atomicity. Replication is eventually consistent by default; insert_quorum makes an insert wait for acknowledgement from a number of replicas.

Scaling model

• Vertically first. ClickHouse uses all available cores and is often fast enough on one large server for datasets most teams consider "big data".
• Replication through ReplicatedMergeTree with ClickHouse Keeper coordinating.
• Sharding through Distributed tables that fan queries out and merge results. See Distributed Tables.

Common mistakes