Overview

ClickHouse is the fastest open-source analytical database for querying large volumes of data. Built by Yandex for web analytics, it’s now the backbone of analytics platforms at Cloudflare, Uber, Stripe, and thousands of other companies. If PostgreSQL is too slow for your analytics queries, ClickHouse is the answer.

Table Design

-- MergeTree: the base engine for most use cases
CREATE TABLE orders (
    order_id    UUID,
    customer_id String,
    created_at  DateTime,
    status      LowCardinality(String),  -- Optimized for low-cardinality columns
    amount      Decimal(10, 2),
    category    LowCardinality(String),
    items       Array(String)
)
ENGINE = MergeTree()
ORDER BY (created_at, customer_id)      -- Primary sort key (for range queries)
PARTITION BY toYYYYMM(created_at);      -- Partition by month

-- ReplacingMergeTree: deduplicate on primary key
CREATE TABLE user_events (
    user_id     String,
    event_type  String,
    occurred_at DateTime,
    value       Float64,
    version     UInt64  -- Higher version wins on dedup
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (user_id, event_type, occurred_at);

Query Performance

-- Count active users by day — scans billions of rows in seconds
SELECT
    toDate(created_at) AS day,
    uniqExact(customer_id) AS unique_customers,
    sum(amount) AS revenue,
    count() AS order_count,
    avg(amount) AS avg_order_value
FROM orders
WHERE created_at >= '2026-01-01'
  AND status = 'completed'
GROUP BY day
ORDER BY day;

-- Funnel analysis with ClickHouse array functions
SELECT
    countIf(has(steps, 'view')) AS views,
    countIf(has(steps, 'add_to_cart')) AS add_to_cart,
    countIf(has(steps, 'purchase')) AS purchases
FROM (
    SELECT
        session_id,
        groupArray(event_type) AS steps
    FROM events
    WHERE date = today()
    GROUP BY session_id
);

Materialized Views

-- Pre-aggregate revenue at insert time
CREATE MATERIALIZED VIEW daily_revenue_mv
ENGINE = SummingMergeTree()
ORDER BY (day, category)
AS SELECT
    toDate(created_at) AS day,
    category,
    sum(amount) AS revenue,
    count() AS order_count
FROM orders
GROUP BY day, category;

-- Querying the materialized view is instant
SELECT day, category, sum(revenue) AS revenue
FROM daily_revenue_mv
WHERE day >= today() - 30
GROUP BY day, category
ORDER BY day, revenue DESC;

Ingesting Data

from clickhouse_driver import Client

client = Client('localhost')

# Bulk insert (most efficient)
client.execute(
    'INSERT INTO orders (order_id, customer_id, created_at, status, amount) VALUES',
    [
        {'order_id': uuid4(), 'customer_id': 'cust-1', 'created_at': datetime.now(),
         'status': 'completed', 'amount': 99.99},
        # ... thousands more
    ]
)

ClickHouse vs PostgreSQL for Analytics

Query	PostgreSQL	ClickHouse
Count 1B rows	~30s	~0.1s
Group by on 100M rows	~20s	~0.5s
Time-series aggregation	~10s	~0.2s
Storage (1B rows)	~500GB	~50GB

ClickHouse isn’t a PostgreSQL replacement — use PostgreSQL for transactional data and ClickHouse for analytics. Many architectures sync PostgreSQL → ClickHouse via Kafka or ClickHouse’s PostgreSQL integration.

Best For

Real-time analytics dashboards where queries on billions of rows must return in under a second — ClickHouse’s columnar storage and vectorized execution are purpose-built for this
Time-series data — metrics, events, logs, and IoT data with time-based aggregations (hourly, daily, monthly rollups)
Log analytics replacing Elasticsearch for cost-sensitive teams — ClickHouse can store 10x more data in the same storage space and query it faster
Product analytics computing user funnels, retention cohorts, and session analytics across hundreds of millions of events
Ad-tech and gaming with extremely high-volume event ingestion (millions of inserts/second) and fast aggregation queries for reporting

When NOT to Use ClickHouse

Transactional workloads (frequent updates, deletes, row-level inserts) — use PostgreSQL; ClickHouse’s MergeTree engine is optimized for append-heavy patterns
Complex JOINs across many tables — ClickHouse JOIN performance degrades for wide joins; denormalize data or use a different OLAP database
Very small datasets — the operational complexity isn’t worth it under 10M rows; PostgreSQL or DuckDB serve these cases better

ClickHouse — Fast Open-Source Columnar Database for Analytics

✅ Pros

⚠️ Cons