ClickHouse

ClickHouse is an open-source, column-oriented database management system designed for real-time analytical queries (OLAP). While row-oriented databases (like PostgreSQL or MySQL) struggle to scan more than a few million rows per second, ClickHouse evaluates SQL aggregations over billions of rows per second per server, making it the standard engine for event analytics, metrics platforms, ad-tech tracking, and log analysis.

When to reach for it

• High-volume append-only event streams: Clickstream events, API telemetry, financial trade logs, server metrics, and IoT sensor readings.
• Large-scale aggregation queries: Computing counts, percentiles, distinct visitors, and multi-dimensional group-bys over billions of rows in under a second.
• Real-time interactive dashboards: Fast analytical queries directly powering customer-facing analytics rather than waiting for overnight batch jobs.
• High compression requirements: Reducing storage footprints for petabyte-scale append-only logs.

ClickHouse is not an OLTP database. It does not support fine-grained single-row point updates or multi-statement transactions. For transactional e-commerce checkouts and user account balances, use PostgreSQL.

Row-oriented vs columnar storage

The fundamental difference between OLTP databases and ClickHouse is on-disk data layout:

When a query computes SELECT avg(response_time_ms) FROM api_logs WHERE service = 'auth', a row store reads every byte of every log message from disk. ClickHouse reads only the response_time_ms and service files, reducing disk I/O by 95% or more.

The MergeTree engine family

The core storage engine in ClickHouse is MergeTree (and its variants). Similar to an LSM-tree, MergeTree structures data into immutable on-disk parts:

Specialized MergeTree engines

ClickHouse offers engine variants that process data during background merges:

• SummingMergeTree: Automatically sums numeric columns sharing the same primary key during merges, pre-aggregating counters.
• AggregatingMergeTree: Stores intermediate aggregation states (e.g. HyperLogLog sketches for uniqExact or quantile states), allowing real-time rollups without re-reading raw events.
• ReplacingMergeTree: Deduplicates rows sharing the same primary key, keeping the latest version (eventual deduplication during merges).

Columnar compression and codecs

Because a single column file contains only identical data types (e.g. an entire array of timestamps or an array of status codes), compression is extraordinarily efficient:

• General compression: LZ4 (default, ultra-fast decompression) or ZSTD (higher compression ratio for historical data).
• Specialized column codecs:
  • DoubleDelta: Compresses monotonic timestamps into tiny bit deltas.
  • Gorilla: Compresses floating-point metrics (time-series values).
  • T64 / Delta: Compresses bounded integer ranges.
  • LowCardinality(String): Automatically dictionary-encodes string columns with under 10,000 distinct values, replacing strings with 1-byte or 2-byte integer IDs.

These techniques routinely achieve 5× to 10× storage reduction compared to raw JSON or relational tables.

Ingestion architecture: why batching is mandatory

The most critical operational rule in ClickHouse is: never perform single-row inserts.

Always buffer and batch writes upstream:

Laying out the diagram…

• Insert in batches of 10,000 to 100,000 rows (or every 1–2 seconds).
• Buffer writes using Kafka consumers, Vector, FluentBit, or ClickHouse's native Buffer table engine.
• Let ClickHouse write one clean, pre-sorted part per batch.

Materialized views: streaming rollups

ClickHouse materialized views are not traditional database snapshots recomputed on a schedule. They are streaming insert-time triggers:

When a batch of rows inserts into the raw events table, ClickHouse automatically pipes the batch through the materialized view query and appends the aggregated output into a target table (typically an AggregatingMergeTree). The raw table can retain data for 7 days, while the rollup table keeps hourly averages and unique user counts for years with virtually zero storage overhead.

Numbers to quote

Scan throughputusing SIMD vectorized instruction sets

1–2 billion rows/sec per server

Batch insert ratewith batched 10k–100k row blocks

100,000 to 500,000 rows/sec per node

Primary index memorydue to 8,192-row sparse index granules

~1 MB RAM per 100 million rows

Data compression ratio

5× to 10× compared to uncompressed JSON

Query latency

10–100 ms for analytical group-bys over hundreds of millions of rows

ClickHouse vs Elasticsearch and Postgres

Where it bites

• Memory limits on high-cardinality GROUP BY: Grouping by a field with 100 million unique strings (such as a UUID) requires ClickHouse to hold the entire hash table in RAM. Without max_memory_usage limits, queries will crash with out-of-memory errors.
• Large joins across distributed tables: ClickHouse does not optimize complex distributed multi-table joins. Broadcast joins copy the entire right-side table to every node. Denormalize data or pre-join dimensions into the event stream before ingestion.
• Mutations are asynchronous: Running ALTER TABLE UPDATE or DELETE executes a heavyweight asynchronous rewrite of entire data parts. Use it only for GDPR deletions or compliance fixes, never for high-frequency row mutations.

Try it

Multi-tenant Analytics Query Platform HLD

Design an interactive analytics platform serving real-time aggregations over billions of events.