ClickHouse — Columnar Database for Lightning-Fast Analytical Queries

ClickHouse is the fastest open-source columnar database. Billions of rows per second thanks to vectorized processing.

Why ClickHouse is Fast¶

Columnar storage + vectorized processing + compression.

CREATE TABLE events (
    event_date  Date,
    event_time  DateTime,
    user_id     UInt64,
    event_type  LowCardinality(String),
    revenue_czk Decimal(12, 2)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time)
TTL event_date + INTERVAL 1 YEAR;

Materialized Views¶

CREATE MATERIALIZED VIEW hourly_revenue
ENGINE = SummingMergeTree() ORDER BY (hour, region)
AS SELECT
    toStartOfHour(event_time) AS hour,
    region,
    count() AS events,
    sum(revenue_czk) AS revenue
FROM events GROUP BY hour, region;

Optimization¶

• ORDER BY — based on WHERE conditions
• LowCardinality — enum-like columns
• Approximate functions — uniqHLL12

Summary¶

ClickHouse is the king of analytical queries. MergeTree and materialized views for sub-second queries over billions of rows.