ClickHouse 26.x — Columnar Database for Billion-Row Real-Time Analytics

Posted on: 4/27/2026 5:11:59 AM

Table of contents

1. Why Is ClickHouse So Fast?
2. ClickHouse 26.x — Notable Improvements
3. Designing an Analytics System with ClickHouse
4. ClickHouse vs Other Solutions
5. Real-World Case Studies 2026
6. Best Practices for Production
7. Conclusion
1. References

When your system needs to analyze billions of rows in seconds, traditional databases like MySQL or PostgreSQL start to choke. This is where ClickHouse shines — an open-source columnar database designed for real-time analytics, delivering speeds hundreds of times faster than conventional RDBMS. With the 26.x series (2026), ClickHouse continues to push boundaries with GA full-text search, materialized CTEs, WebAssembly UDFs, and an embedded observability stack. This article dives deep into its architecture, how ClickHouse achieves remarkable performance, and how to design a production-ready analytics system.

1. Why Is ClickHouse So Fast?

ClickHouse was built from the ground up for OLAP (Online Analytical Processing) — optimized for read-heavy queries on massive datasets rather than small OLTP transactions. Three architectural pillars drive its superiority:

96T events scanned in <2s (Cloudflare)

600K rows/sec ingestion (Goldsky blockchain)

3.2x faster RIGHT/FULL JOINs (v26.2)

96% fewer granules scanned with full-text index

1.1. Columnar Storage — Read Less, Return Faster

Unlike row-oriented databases (MySQL, PostgreSQL) that store entire rows contiguously on disk, ClickHouse stores each column separately. When querying SELECT avg(price) FROM orders WHERE date > '2026-01-01', ClickHouse only reads the price and date columns instead of full rows — reducing I/O by up to 100x on wide tables.

graph LR
    subgraph ROW["Row-Oriented (MySQL)"]
        R1["Row 1: id|name|price|date"]
        R2["Row 2: id|name|price|date"]
        R3["Row 3: id|name|price|date"]
    end
    subgraph COL["Column-Oriented (ClickHouse)"]
        C1["Column: id → 1,2,3,..."]
        C2["Column: name → A,B,C,..."]
        C3["Column: price → 10,20,30,..."]
        C4["Column: date → 01,02,03,..."]
    end
    Q["SELECT avg(price)
WHERE date > X"] -->|"Reads all rows"| ROW
    Q -->|"Reads only 2 cols"| COL
    style COL fill:#f8f9fa,stroke:#4CAF50,color:#2c3e50
    style ROW fill:#f8f9fa,stroke:#e94560,color:#2c3e50
    style Q fill:#e94560,stroke:#fff,color:#fff
    style C3 fill:#e8f5e9,stroke:#4CAF50,color:#2c3e50
    style C4 fill:#e8f5e9,stroke:#4CAF50,color:#2c3e50

Figure 1: Row-oriented vs Column-oriented — ClickHouse reads only the columns needed

1.2. Extremely Efficient Data Compression

Since data within the same column shares the same type (all numbers, all strings, all dates), compression ratios are very high. ClickHouse supports multiple codecs: LZ4 (default, fast), ZSTD (higher compression ratio), Delta + ZSTD for time series, and the new ALP codec (v26.3) for floating-point data. Real-world compression ratios typically reach 5-15x, meaning 1TB of raw data occupies just 70-200GB on disk.

-- Choose the right compression codec for each column
CREATE TABLE events (
    event_time DateTime CODEC(Delta, ZSTD(3)),    -- time series: Delta encoding + ZSTD
    user_id    UInt64   CODEC(T64, LZ4),           -- integer: T64 transform + LZ4
    price      Float64  CODEC(ALP),                -- floating-point: ALP codec (v26.3)
    url        String   CODEC(ZSTD(5)),            -- text: ZSTD high compression
    status     LowCardinality(String)              -- automatic dictionary encoding
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

1.3. Vectorized Query Execution

ClickHouse processes data in blocks (default 65,536 rows/block) rather than row by row. Each block is processed using SIMD instructions (SSE4.2, AVX2, AVX-512), fully leveraging CPU pipelines and cache. Result: query throughput is 10-100x higher than row-by-row processing.

Sparse Primary Index — Not Your Typical B-Tree

ClickHouse doesn't use B-Tree indexes like traditional RDBMS. Instead, it uses a sparse index — storing only the first value of each granule (default 8,192 rows). The index is tiny (often just a few MB for billion-row tables), so it always fits in RAM. During queries, ClickHouse skips entire granules that don't match the WHERE condition — this is called data skipping.

2. ClickHouse 26.x — Notable Improvements

February 2026 — ClickHouse 26.2

Full-text search GA: New index reduces granules scanned by 96% vs Bloom filters. QBit data type production-ready. RIGHT/FULL JOINs 3.2x faster. Embedded ClickStack for in-product observability. Time-based block flushing for streaming data.

April 2026 — ClickHouse 26.3 LTS

Materialized CTEs (experimental): reuse CTE results within the same query. WebAssembly UDFs: write custom functions in Rust/Go/C++ compiled to Wasm. ALP compression for floating-point. Asynchronous inserts as default. JOIN reordering for ANTI/SEMI/FULL.

April 2026 — clickhousectl CLI (Beta)

Official CLI unifying local + cloud management. Supports project scaffolding, version management, and AI agent integration — designed for the agentic era.

2.1. Full-Text Search — A Search Revolution in ClickHouse

Before v26.2, text search in ClickHouse relied on LIKE or Bloom filter indexes — slow and imprecise. From v26.2, ClickHouse features a true full-text index, using front-coding compression and adaptive posting lists optimized for object storage.

-- Create full-text index on log message column
CREATE TABLE logs (
    timestamp DateTime64(3),
    service   LowCardinality(String),
    level     Enum8('DEBUG'=0, 'INFO'=1, 'WARN'=2, 'ERROR'=3),
    message   String,
    INDEX idx_msg message TYPE full_text GRANULARITY 1
) ENGINE = MergeTree()
ORDER BY (service, timestamp);

-- Full-text query — much faster than LIKE
SELECT timestamp, service, message
FROM logs
WHERE hasToken(message, 'timeout') AND level >= 'ERROR'
ORDER BY timestamp DESC
LIMIT 100;

-- Comparison: Bloom filter scans ~2,400 granules, full-text index only ~96 granules
-- on the same dataset → approximately 25x faster

Can Full-text Search Replace Elasticsearch?

With full-text search GA, ClickHouse is encroaching on Elasticsearch's territory for log analytics. Advantages: no separate search cluster needed, much better compression (5-10x vs Elasticsearch), familiar SQL syntax. Limitations: no fuzzy search, complex relevance scoring, or synonym expansion — features Elasticsearch has matured. Verdict: great for log search and structured analytics, not yet a replacement for full-blown search engines.

2.2. Materialized CTEs — Avoid Redundant Computation

Before v26.3, CTEs in ClickHouse were just syntactic sugar — each reference re-executed the query. Materialized CTEs store intermediate results and reuse them, particularly useful for complex queries with multiple sub-aggregations.

-- Materialized CTE: compute once, use multiple times
WITH daily_stats AS MATERIALIZED (
    SELECT
        toDate(event_time) AS day,
        service,
        count() AS total_events,
        countIf(level = 'ERROR') AS error_count
    FROM logs
    WHERE event_time >= now() - INTERVAL 30 DAY
    GROUP BY day, service
)
SELECT
    s1.service,
    avg(s1.total_events) AS avg_daily_events,
    avg(s1.error_count) AS avg_daily_errors,
    -- Compare with last week
    (SELECT avg(total_events) FROM daily_stats s2
     WHERE s2.service = s1.service
       AND s2.day >= now() - INTERVAL 7 DAY) AS avg_last_7d
FROM daily_stats s1
GROUP BY s1.service;

2.3. WebAssembly UDFs — Unlimited Custom Functions

ClickHouse 26.3 lets you write User-Defined Functions in any language that compiles to WebAssembly — Rust, Go, C++, AssemblyScript. UDFs run in a secure without affecting server stability.

// Rust → WebAssembly UDF: compute Haversine distance
// Compile: cargo build --target wasm32-wasi --release
#[no_mangle]
pub extern "C" fn haversine(lat1: f64, lon1: f64, lat2: f64, lon2: f64) -> f64 {
    let r = 6371.0; // Earth radius in km
    let dlat = (lat2 - lat1).to_radians();
    let dlon = (lon2 - lon1).to_radians();
    let a = (dlat / 2.0).sin().powi(2)
        + lat1.to_radians().cos() * lat2.to_radians().cos()
        * (dlon / 2.0).sin().powi(2);
    r * 2.0 * a.sqrt().atan2((1.0 - a).sqrt())
}

-- Register Wasm UDF in ClickHouse
CREATE FUNCTION haversine_km AS 'haversine'
USING '/path/to/haversine.wasm'
ARGUMENTS (lat1 Float64, lon1 Float64, lat2 Float64, lon2 Float64)
RETURNS Float64;

-- Usage: find all stores within 5km radius
SELECT name, haversine_km(10.7769, 106.7009, lat, lon) AS distance_km
FROM stores
WHERE distance_km <= 5
ORDER BY distance_km;

3. Designing an Analytics System with ClickHouse

Below is a reference architecture for a production analytics system handling millions of events per second:

graph TB
    SOURCE["Data Sources
(Apps, IoT, Logs)"] --> KAFKA["Apache Kafka
/ Redpanda"]
    KAFKA --> CH_KAFKA["Kafka Engine Table
(ClickHouse)"]
    CH_KAFKA --> MV["Materialized View
(Transform + Aggregate)"]
    MV --> TARGET["MergeTree Table
(Final Storage)"]
    TARGET --> QUERY["Query Layer"]
    QUERY --> GRAFANA["Grafana
Dashboard"]
    QUERY --> API["Analytics API
(.NET / Node.js)"]
    QUERY --> AGENT["AI Agent
(MCP Server)"]
    SOURCE --> BATCH["Batch Ingestion
(Airflow / dbt)"]
    BATCH --> TARGET
    style TARGET fill:#e94560,stroke:#fff,color:#fff
    style KAFKA fill:#2c3e50,stroke:#e94560,color:#fff
    style MV fill:#16213e,stroke:#e94560,color:#fff
    style GRAFANA fill:#f8f9fa,stroke:#e94560,color:#2c3e50
    style API fill:#f8f9fa,stroke:#e94560,color:#2c3e50
    style AGENT fill:#f8f9fa,stroke:#e94560,color:#2c3e50

Figure 2: Analytics pipeline architecture — streaming via Kafka + batch, MergeTree storage, serving dashboards + API + AI agents

3.1. MergeTree Engine — The Heart of ClickHouse

MergeTree is the most important table engine. Inserted data is written to separate parts (immutable), then ClickHouse merges smaller parts into larger ones in the background — similar to LSM-trees. This design enables extremely fast inserts (append-only) and efficient queries (large, sorted parts).

-- Optimized analytics events table design
CREATE TABLE analytics_events (
    -- Partition key: by month, enables TTL and query pruning
    event_date    Date,
    event_time    DateTime64(3),

    -- Primary key columns: order matters!
    -- Place most commonly filtered columns first
    tenant_id     UInt32,
    event_type    LowCardinality(String),
    user_id       UInt64,

    -- Payload
    properties    String CODEC(ZSTD(3)),
    revenue       Decimal64(2) CODEC(Delta, ZSTD),
    country       LowCardinality(String),
    device        LowCardinality(String),

    -- Full-text search on description
    description   String,
    INDEX idx_desc description TYPE full_text GRANULARITY 1
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_time, user_id)
TTL event_date + INTERVAL 90 DAY DELETE
SETTINGS
    index_granularity = 8192,
    min_bytes_for_wide_part = 10485760;

ORDER BY Selection Principles

ORDER BY in ClickHouse doesn't just sort — it determines the primary index structure and data skipping efficiency. Rule of thumb: place columns with the lowest cardinality first (tenant_id, event_type), then higher cardinality columns (user_id). Time columns should be in the middle if you commonly query time ranges combined with other filters. Wrong ORDER BY can make queries 100x slower on the same dataset.

3.2. Materialized Views — Pre-compute, Query Instantly

Materialized Views in ClickHouse work like triggers: whenever data is inserted into the source table, the MV automatically transforms and writes to the target table. This is the core mechanism for real-time aggregation.

-- Target table for aggregated metrics
CREATE TABLE hourly_metrics (
    hour         DateTime,
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    event_count  AggregateFunction(count, UInt64),
    unique_users AggregateFunction(uniq, UInt64),
    total_revenue AggregateFunction(sum, Decimal64(2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (tenant_id, event_type, hour);

-- Materialized View: auto-aggregate on insert
CREATE MATERIALIZED VIEW mv_hourly_metrics
TO hourly_metrics AS
SELECT
    toStartOfHour(event_time) AS hour,
    tenant_id,
    event_type,
    countState() AS event_count,
    uniqState(user_id) AS unique_users,
    sumState(revenue) AS total_revenue
FROM analytics_events
GROUP BY hour, tenant_id, event_type;

-- Query: merge aggregated results (sub-millisecond on pre-aggregated data)
SELECT
    hour,
    countMerge(event_count) AS events,
    uniqMerge(unique_users) AS users,
    sumMerge(total_revenue) AS revenue
FROM hourly_metrics
WHERE tenant_id = 42
  AND hour >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

3.3. Streaming Ingestion from Kafka

ClickHouse has a built-in Kafka table engine, allowing direct consumption from Kafka topics without external connectors:

-- Kafka source table
CREATE TABLE kafka_events (
    event_time DateTime64(3),
    tenant_id  UInt32,
    event_type String,
    user_id    UInt64,
    properties String,
    revenue    Decimal64(2)
)
ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka-1:9092,kafka-2:9092,kafka-3:9092',
    kafka_topic_list = 'analytics-events',
    kafka_group_name = 'clickhouse-consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4,
    -- v26.2: time-based flushing instead of waiting for row count
    kafka_flush_interval_ms = 5000;

-- Pipe from Kafka → MergeTree via Materialized View
CREATE MATERIALIZED VIEW mv_kafka_to_events
TO analytics_events AS
SELECT
    event_time,
    toDate(event_time) AS event_date,
    tenant_id,
    event_type,
    user_id,
    properties,
    revenue,
    JSONExtractString(properties, 'country') AS country,
    JSONExtractString(properties, 'device') AS device,
    '' AS description
FROM kafka_events;

4. ClickHouse vs Other Solutions

Criteria	ClickHouse	Elasticsearch	Apache Druid	BigQuery
Model	Columnar OLAP	Inverted Index	Columnar OLAP	Serverless OLAP
Compression	5-15x (excellent)	1.5-3x	3-8x	Managed
Query latency	Sub-second	Sub-second	Sub-second	Seconds
Ingestion	Millions rows/sec	Hundreds of thousands docs/sec	Millions rows/sec	Streaming (limited)
SQL support	Full SQL + extensions	Yes (limited)	Yes (Druid SQL)	Full SQL
Full-text search	GA (v26.2)	Native, mature	No	Limited
JOIN performance	Good (v26.2+)	Very limited	Limited	Good
Operational cost	Low (self-host) / Medium (Cloud)	High (RAM-hungry)	High (complex ops)	Pay-per-query
Best for	Analytics, observability, time series	Search, logging	Real-time dashboards	Ad-hoc analytics

5. Real-World Case Studies 2026

5.1. Cloudflare — 1.61 Quadrillion Events/Day

Cloudflare uses ClickHouse to process 1.61 quadrillion events per day (1.61 × 10¹⁵) across over 300 data centers worldwide. A single query can scan 96 trillion events in under 2 seconds. Architecture: each data center has a local ClickHouse cluster, queries are fan-out/merged through a distributed layer.

5.2. Goldsky — 12x Faster Blockchain Analytics

Goldsky (blockchain data infrastructure) switched from a Kafka/Avro pipeline to reading directly from ClickHouse with Apache Arrow format, achieving a 12x speedup for historical backfills — from 50K to 600K rows/sec. The secret: leveraging columnar-to-columnar transfer (ClickHouse → Arrow) without per-row serialization/deserialization.

5.3. Hookdeck — Search Latency from 30s to 400ms

Hookdeck (webhook infrastructure) needed to search across billions of webhook payloads. Before ClickHouse: full-text queries took 30+ seconds. After migrating to ClickHouse with hashed payload values in typed bucket columns + iterative time-window scanning: latency dropped to under 400ms — 60x faster.

6. Best Practices for Production

6.1. Schema Design

Common Mistakes

1. Using UUID as primary key: UUIDs distribute randomly → data skipping becomes nearly useless. Use a composite key with low-cardinality columns first.
2. Too many small columns: ClickHouse opens a file descriptor per column × per part. Tables with 500+ columns and many parts cause significant overhead. Consolidate related fields into Nested or JSON.
3. Single-row INSERTs: Each INSERT creates a new part → enormous merge pressure. Always batch (minimum 1,000 rows/batch, ideally 10K-100K).
4. Missing TTL: Analytics data grows fast. Always set TTL according to your retention policy to automatically remove old data.

6.2. Performance Tuning

-- Check compression ratio
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'analytics'
GROUP BY table
ORDER BY sum(data_uncompressed_bytes) DESC;

-- Check how many granules queries are scanning
SELECT
    query,
    read_rows,
    read_bytes,
    result_rows,
    formatReadableSize(memory_usage) AS peak_memory,
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Async insert (default since v26.3)
-- ClickHouse automatically batches small inserts into larger batches
SET async_insert = 1;
SET wait_for_async_insert = 0;  -- don't wait, fire-and-forget

6.3. clickhousectl — The New CLI for the Agent Era

ClickHouse just released clickhousectl (beta, 04/2026) — the official CLI unifying local installation and cloud deployment management. The standout feature: built-in AI agent integration via MCP server, allowing AI agents to query and manage ClickHouse clusters through a standardized protocol.

# Install clickhousectl
curl -fsSL https://clickhouse.com/install-ctl | bash

# Manage local instance
clickhousectl install 26.3       # Install specific version
clickhousectl start              # Start server
clickhousectl status             # Check status

# Cloud connection
clickhousectl cloud login
clickhousectl cloud services     # List cloud services
clickhousectl cloud query "SELECT count() FROM analytics_events"

# Scaffold new project
clickhousectl init my-analytics  # Create project template

7. Conclusion

ClickHouse has cemented its position as the fastest database for real-time analytics at scales ranging from startups to Cloudflare (1.61 quadrillion events/day). With the 26.x series, ClickHouse isn't just faster — it's smarter. GA full-text search eliminates the need for Elasticsearch in log analytics, materialized CTEs simplify complex queries, WebAssembly UDFs unlock unlimited processing capabilities, and clickhousectl + MCP server pave the way for the agentic AI era.

If your system needs to analyze billions of rows with sub-second latency — whether for observability, product analytics, or IoT time series — ClickHouse deserves a spot on your evaluation shortlist. Combined with Kafka for streaming ingestion, Materialized Views for real-time aggregation, and Grafana for visualization, you get a complete analytics stack at significantly lower operational cost than Elasticsearch or expensive managed services.

References

#ClickHouse #Real-time Analytics #system design #Database #Performance

# ClickHouse 26.x — Columnar Database for Billion-Row Real-Time Analytics

When your system needs to analyze **billions of rows in seconds**, traditional databases like MySQL or PostgreSQL start to choke. This is where **ClickHouse** shines — an open-source columnar database designed for real-time analytics, delivering speeds hundreds of times faster than conventional RDBMS. With the **26.x** series (2026), ClickHouse continues to push boundaries with GA full-text search, materialized CTEs, WebAssembly UDFs, and an embedded observability stack. This article dives deep into its architecture, how ClickHouse achieves remarkable performance, and how to design a production-ready analytics system.

## 1. Why Is ClickHouse So Fast?

ClickHouse was built from the ground up for **OLAP** (Online Analytical Processing) — optimized for read-heavy queries on massive datasets rather than small OLTP transactions. Three architectural pillars drive its superiority:

96T events scanned in <2s (Cloudflare)

600K rows/sec ingestion (Goldsky blockchain)

3.2x faster RIGHT/FULL JOINs (v26.2)

96% fewer granules scanned with full-text index

### 1.1. Columnar Storage — Read Less, Return Faster

Unlike row-oriented databases (MySQL, PostgreSQL) that store entire rows contiguously on disk, ClickHouse stores **each column separately**. When querying `SELECT avg(price) FROM orders WHERE date > '2026-01-01'`, ClickHouse only reads the `price` and `date` columns instead of full rows — reducing I/O by up to **100x** on wide tables.

```
graph LR
    subgraph ROW["Row-Oriented (MySQL)"]
        R1["Row 1: id|name|price|date"]
        R2["Row 2: id|name|price|date"]
        R3["Row 3: id|name|price|date"]
    end
    subgraph COL["Column-Oriented (ClickHouse)"]
        C1["Column: id → 1,2,3,..."]
        C2["Column: name → A,B,C,..."]
        C3["Column: price → 10,20,30,..."]
        C4["Column: date → 01,02,03,..."]
    end
    Q["SELECT avg(price)  
WHERE date > X"] -->|"Reads all rows"| ROW
    Q -->|"Reads only 2 cols"| COL
    style COL fill:#f8f9fa,stroke:#4CAF50,color:#2c3e50
    style ROW fill:#f8f9fa,stroke:#e94560,color:#2c3e50
    style Q fill:#e94560,stroke:#fff,color:#fff
    style C3 fill:#e8f5e9,stroke:#4CAF50,color:#2c3e50
    style C4 fill:#e8f5e9,stroke:#4CAF50,color:#2c3e50

```
Figure 1: Row-oriented vs Column-oriented — ClickHouse reads only the columns needed

### 1.2. Extremely Efficient Data Compression

Since data within the same column shares the same type (all numbers, all strings, all dates), compression ratios are very high. ClickHouse supports multiple codecs: **LZ4** (default, fast), **ZSTD** (higher compression ratio), **Delta + ZSTD** for time series, and the new **ALP** codec (v26.3) for floating-point data. Real-world compression ratios typically reach **5-15x**, meaning 1TB of raw data occupies just 70-200GB on disk.

```sql
-- Choose the right compression codec for each column
CREATE TABLE events (
    event_time DateTime CODEC(Delta, ZSTD(3)),    -- time series: Delta encoding + ZSTD
    user_id    UInt64   CODEC(T64, LZ4),           -- integer: T64 transform + LZ4
    price      Float64  CODEC(ALP),                -- floating-point: ALP codec (v26.3)
    url        String   CODEC(ZSTD(5)),            -- text: ZSTD high compression
    status     LowCardinality(String)              -- automatic dictionary encoding
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

```

### 1.3. Vectorized Query Execution

ClickHouse processes data in **blocks** (default 65,536 rows/block) rather than row by row. Each block is processed using SIMD instructions (SSE4.2, AVX2, AVX-512), fully leveraging CPU pipelines and cache. Result: query throughput is **10-100x higher** than row-by-row processing.

#### Sparse Primary Index — Not Your Typical B-Tree

ClickHouse doesn't use B-Tree indexes like traditional RDBMS. Instead, it uses a **sparse index** — storing only the first value of each granule (default 8,192 rows). The index is tiny (often just a few MB for billion-row tables), so it always fits in RAM. During queries, ClickHouse skips entire granules that don't match the WHERE condition — this is called **data skipping**.

## 2. ClickHouse 26.x — Notable Improvements

February 2026 — ClickHouse 26.2

**Full-text search GA**: New index reduces granules scanned by 96% vs Bloom filters. **QBit data type** production-ready. RIGHT/FULL JOINs 3.2x faster. Embedded **ClickStack** for in-product observability. Time-based block flushing for streaming data.

April 2026 — ClickHouse 26.3 LTS

**Materialized CTEs** (experimental): reuse CTE results within the same query. **WebAssembly UDFs**: write custom functions in Rust/Go/C++ compiled to Wasm. **ALP compression** for floating-point. Asynchronous inserts as default. JOIN reordering for ANTI/SEMI/FULL.

April 2026 — clickhousectl CLI (Beta)

Official CLI unifying local + cloud management. Supports project scaffolding, version management, and AI agent integration — designed for the agentic era.

### 2.1. Full-Text Search — A Search Revolution in ClickHouse

Before v26.2, text search in ClickHouse relied on `LIKE` or Bloom filter indexes — slow and imprecise. From v26.2, ClickHouse features a true **full-text index**, using **front-coding compression** and **adaptive posting lists** optimized for object storage.

```sql
-- Create full-text index on log message column
CREATE TABLE logs (
    timestamp DateTime64(3),
    service   LowCardinality(String),
    level     Enum8('DEBUG'=0, 'INFO'=1, 'WARN'=2, 'ERROR'=3),
    message   String,
    INDEX idx_msg message TYPE full_text GRANULARITY 1
) ENGINE = MergeTree()
ORDER BY (service, timestamp);

-- Full-text query — much faster than LIKE
SELECT timestamp, service, message
FROM logs
WHERE hasToken(message, 'timeout') AND level >= 'ERROR'
ORDER BY timestamp DESC
LIMIT 100;

-- Comparison: Bloom filter scans ~2,400 granules, full-text index only ~96 granules
-- on the same dataset → approximately 25x faster

```

#### Can Full-text Search Replace Elasticsearch?

With full-text search GA, ClickHouse is encroaching on Elasticsearch's territory for **log analytics**. Advantages: no separate search cluster needed, much better compression (5-10x vs Elasticsearch), familiar SQL syntax. Limitations: no fuzzy search, complex relevance scoring, or synonym expansion — features Elasticsearch has matured. Verdict: great for log search and structured analytics, not yet a replacement for full-blown search engines.

### 2.2. Materialized CTEs — Avoid Redundant Computation

```sql
-- Materialized CTE: compute once, use multiple times
WITH daily_stats AS MATERIALIZED (
    SELECT
        toDate(event_time) AS day,
        service,
        count() AS total_events,
        countIf(level = 'ERROR') AS error_count
    FROM logs
    WHERE event_time >= now() - INTERVAL 30 DAY
    GROUP BY day, service
)
SELECT
    s1.service,
    avg(s1.total_events) AS avg_daily_events,
    avg(s1.error_count) AS avg_daily_errors,
    -- Compare with last week
    (SELECT avg(total_events) FROM daily_stats s2
     WHERE s2.service = s1.service
       AND s2.day >= now() - INTERVAL 7 DAY) AS avg_last_7d
FROM daily_stats s1
GROUP BY s1.service;

```

### 2.3. WebAssembly UDFs — Unlimited Custom Functions

ClickHouse 26.3 lets you write User-Defined Functions in any language that compiles to WebAssembly — Rust, Go, C++, AssemblyScript. UDFs run in a secure sandbox without affecting server stability.

```rust
// Rust → WebAssembly UDF: compute Haversine distance
// Compile: cargo build --target wasm32-wasi --release
#[no_mangle]
pub extern "C" fn haversine(lat1: f64, lon1: f64, lat2: f64, lon2: f64) -> f64 {
    let r = 6371.0; // Earth radius in km
    let dlat = (lat2 - lat1).to_radians();
    let dlon = (lon2 - lon1).to_radians();
    let a = (dlat / 2.0).sin().powi(2)
        + lat1.to_radians().cos() * lat2.to_radians().cos()
        * (dlon / 2.0).sin().powi(2);
    r * 2.0 * a.sqrt().atan2((1.0 - a).sqrt())
}

```

```sql
-- Register Wasm UDF in ClickHouse
CREATE FUNCTION haversine_km AS 'haversine'
USING '/path/to/haversine.wasm'
ARGUMENTS (lat1 Float64, lon1 Float64, lat2 Float64, lon2 Float64)
RETURNS Float64;

-- Usage: find all stores within 5km radius
SELECT name, haversine_km(10.7769, 106.7009, lat, lon) AS distance_km
FROM stores
WHERE distance_km <= 5
ORDER BY distance_km;

```

## 3. Designing an Analytics System with ClickHouse

Below is a reference architecture for a production analytics system handling millions of events per second:

```
graph TB
    SOURCE["Data Sources  
(Apps, IoT, Logs)"] --> KAFKA["Apache Kafka  
/ Redpanda"]
    KAFKA --> CH_KAFKA["Kafka Engine Table  
(ClickHouse)"]
    CH_KAFKA --> MV["Materialized View  
(Transform + Aggregate)"]
    MV --> TARGET["MergeTree Table  
(Final Storage)"]
    TARGET --> QUERY["Query Layer"]
    QUERY --> GRAFANA["Grafana  
Dashboard"]
    QUERY --> API["Analytics API  
(.NET / Node.js)"]
    QUERY --> AGENT["AI Agent  
(MCP Server)"]
    SOURCE --> BATCH["Batch Ingestion  
(Airflow / dbt)"]
    BATCH --> TARGET
    style TARGET fill:#e94560,stroke:#fff,color:#fff
    style KAFKA fill:#2c3e50,stroke:#e94560,color:#fff
    style MV fill:#16213e,stroke:#e94560,color:#fff
    style GRAFANA fill:#f8f9fa,stroke:#e94560,color:#2c3e50
    style API fill:#f8f9fa,stroke:#e94560,color:#2c3e50
    style AGENT fill:#f8f9fa,stroke:#e94560,color:#2c3e50

```
Figure 2: Analytics pipeline architecture — streaming via Kafka + batch, MergeTree storage, serving dashboards + API + AI agents

### 3.1. MergeTree Engine — The Heart of ClickHouse

**MergeTree** is the most important table engine. Inserted data is written to separate **parts** (immutable), then ClickHouse merges smaller parts into larger ones in the background — similar to LSM-trees. This design enables extremely fast inserts (append-only) and efficient queries (large, sorted parts).

```sql
-- Optimized analytics events table design
CREATE TABLE analytics_events (
    -- Partition key: by month, enables TTL and query pruning
    event_date    Date,
    event_time    DateTime64(3),

-- Primary key columns: order matters!
    -- Place most commonly filtered columns first
    tenant_id     UInt32,
    event_type    LowCardinality(String),
    user_id       UInt64,

-- Payload
    properties    String CODEC(ZSTD(3)),
    revenue       Decimal64(2) CODEC(Delta, ZSTD),
    country       LowCardinality(String),
    device        LowCardinality(String),

-- Full-text search on description
    description   String,
    INDEX idx_desc description TYPE full_text GRANULARITY 1
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_time, user_id)
TTL event_date + INTERVAL 90 DAY DELETE
SETTINGS
    index_granularity = 8192,
    min_bytes_for_wide_part = 10485760;

```

#### ORDER BY Selection Principles

**ORDER BY** in ClickHouse doesn't just sort — it determines the primary index structure and data skipping efficiency. Rule of thumb: place columns with the **lowest cardinality first** (tenant_id, event_type), then higher cardinality columns (user_id). Time columns should be in the middle if you commonly query time ranges combined with other filters. Wrong ORDER BY can make queries 100x slower on the same dataset.

### 3.2. Materialized Views — Pre-compute, Query Instantly

Materialized Views in ClickHouse work like **triggers**: whenever data is inserted into the source table, the MV automatically transforms and writes to the target table. This is the core mechanism for real-time aggregation.

```sql
-- Target table for aggregated metrics
CREATE TABLE hourly_metrics (
    hour         DateTime,
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    event_count  AggregateFunction(count, UInt64),
    unique_users AggregateFunction(uniq, UInt64),
    total_revenue AggregateFunction(sum, Decimal64(2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (tenant_id, event_type, hour);

-- Materialized View: auto-aggregate on insert
CREATE MATERIALIZED VIEW mv_hourly_metrics
TO hourly_metrics AS
SELECT
    toStartOfHour(event_time) AS hour,
    tenant_id,
    event_type,
    countState() AS event_count,
    uniqState(user_id) AS unique_users,
    sumState(revenue) AS total_revenue
FROM analytics_events
GROUP BY hour, tenant_id, event_type;

-- Query: merge aggregated results (sub-millisecond on pre-aggregated data)
SELECT
    hour,
    countMerge(event_count) AS events,
    uniqMerge(unique_users) AS users,
    sumMerge(total_revenue) AS revenue
FROM hourly_metrics
WHERE tenant_id = 42
  AND hour >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

```

### 3.3. Streaming Ingestion from Kafka

ClickHouse has a built-in Kafka table engine, allowing direct consumption from Kafka topics without external connectors:

```sql
-- Kafka source table
CREATE TABLE kafka_events (
    event_time DateTime64(3),
    tenant_id  UInt32,
    event_type String,
    user_id    UInt64,
    properties String,
    revenue    Decimal64(2)
)
ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka-1:9092,kafka-2:9092,kafka-3:9092',
    kafka_topic_list = 'analytics-events',
    kafka_group_name = 'clickhouse-consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4,
    -- v26.2: time-based flushing instead of waiting for row count
    kafka_flush_interval_ms = 5000;

-- Pipe from Kafka → MergeTree via Materialized View
CREATE MATERIALIZED VIEW mv_kafka_to_events
TO analytics_events AS
SELECT
    event_time,
    toDate(event_time) AS event_date,
    tenant_id,
    event_type,
    user_id,
    properties,
    revenue,
    JSONExtractString(properties, 'country') AS country,
    JSONExtractString(properties, 'device') AS device,
    '' AS description
FROM kafka_events;

```

## 4. ClickHouse vs Other Solutions

| Criteria | ClickHouse | Elasticsearch | Apache Druid | BigQuery |
| --- | --- | --- | --- | --- |
| **Model** | Columnar OLAP | Inverted Index | Columnar OLAP | Serverless OLAP |
| **Compression** | 5-15x (excellent) | 1.5-3x | 3-8x | Managed |
| **Query latency** | Sub-second | Sub-second | Sub-second | Seconds |
| **Ingestion** | Millions rows/sec | Hundreds of thousands docs/sec | Millions rows/sec | Streaming (limited) |
| **SQL support** | Full SQL + extensions | Yes (limited) | Yes (Druid SQL) | Full SQL |
| **Full-text search** | GA (v26.2) | Native, mature | No | Limited |
| **JOIN performance** | Good (v26.2+) | Very limited | Limited | Good |
| **Operational cost** | Low (self-host) / Medium (Cloud) | High (RAM-hungry) | High (complex ops) | Pay-per-query |
| **Best for** | Analytics, observability, time series | Search, logging | Real-time dashboards | Ad-hoc analytics |

## 5. Real-World Case Studies 2026

### 5.1. Cloudflare — 1.61 Quadrillion Events/Day

Cloudflare uses ClickHouse to process **1.61 quadrillion events per day** (1.61 × 10¹⁵) across over 300 data centers worldwide. A single query can scan **96 trillion events in under 2 seconds**. Architecture: each data center has a local ClickHouse cluster, queries are fan-out/merged through a distributed layer.

### 5.2. Goldsky — 12x Faster Blockchain Analytics

Goldsky (blockchain data infrastructure) switched from a Kafka/Avro pipeline to reading directly from ClickHouse with Apache Arrow format, achieving a **12x speedup** for historical backfills — from 50K to 600K rows/sec. The secret: leveraging columnar-to-columnar transfer (ClickHouse → Arrow) without per-row serialization/deserialization.

### 5.3. Hookdeck — Search Latency from 30s to 400ms

## 6. Best Practices for Production

### 6.1. Schema Design

#### Common Mistakes

**1. Using UUID as primary key:** UUIDs distribute randomly → data skipping becomes nearly useless. Use a composite key with low-cardinality columns first.  
**2. Too many small columns:** ClickHouse opens a file descriptor per column × per part. Tables with 500+ columns and many parts cause significant overhead. Consolidate related fields into Nested or JSON.  
**3. Single-row INSERTs:** Each INSERT creates a new part → enormous merge pressure. Always batch (minimum 1,000 rows/batch, ideally 10K-100K).  
**4. Missing TTL:** Analytics data grows fast. Always set TTL according to your retention policy to automatically remove old data.

### 6.2. Performance Tuning

```sql
-- Check compression ratio
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'analytics'
GROUP BY table
ORDER BY sum(data_uncompressed_bytes) DESC;

-- Check how many granules queries are scanning
SELECT
    query,
    read_rows,
    read_bytes,
    result_rows,
    formatReadableSize(memory_usage) AS peak_memory,
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Async insert (default since v26.3)
-- ClickHouse automatically batches small inserts into larger batches
SET async_insert = 1;
SET wait_for_async_insert = 0;  -- don't wait, fire-and-forget

```

### 6.3. clickhousectl — The New CLI for the Agent Era

ClickHouse just released **clickhousectl** (beta, 04/2026) — the official CLI unifying local installation and cloud deployment management. The standout feature: built-in **AI agent integration** via MCP server, allowing AI agents to query and manage ClickHouse clusters through a standardized protocol.

```bash
# Install clickhousectl
curl -fsSL https://clickhouse.com/install-ctl | bash

# Manage local instance
clickhousectl install 26.3       # Install specific version
clickhousectl start              # Start server
clickhousectl status             # Check status

# Cloud connection
clickhousectl cloud login
clickhousectl cloud services     # List cloud services
clickhousectl cloud query "SELECT count() FROM analytics_events"

# Scaffold new project
clickhousectl init my-analytics  # Create project template

```

## 7. Conclusion

ClickHouse has cemented its position as the **fastest database for real-time analytics** at scales ranging from startups to Cloudflare (1.61 quadrillion events/day). With the 26.x series, ClickHouse isn't just faster — it's smarter. GA full-text search eliminates the need for Elasticsearch in log analytics, materialized CTEs simplify complex queries, WebAssembly UDFs unlock unlimited processing capabilities, and clickhousectl + MCP server pave the way for the agentic AI era.

### References

- [ClickHouse April 2026 Newsletter — clickhouse.com](https://clickhouse.com/blog/202604-newsletter)
- [ClickHouse March 2026 Newsletter — clickhouse.com](https://clickhouse.com/blog/202603-newsletter)
- [How to Choose a Database for Real-Time Analytics in 2026 — clickhouse.com](https://clickhouse.com/resources/engineering/how-to-choose-a-database-for-real-time-analytics-in-2026)
- [Real-Time Analytics Platforms: A Practical Comparison — clickhouse.com](https://clickhouse.com/resources/engineering/real-time-analytics-platforms-a-practical-comparison)
- [ClickHouse Expands Strategic Collaboration with Google Cloud — FinancialContent](https://www.financialcontent.com/article/bizwire-2026-4-22-clickhouse-expands-strategic-collaboration-with-google-cloud-bringing-deeper-integration-cloud-flexibility-and-next-generation-performance)
- [ClickHouse Builds a CLI to Make Its Databases Agent-Native — Futurum Group](https://futurumgroup.com/insights/clickhouse-builds-a-cli-to-make-its-databases-agent-native/)
- [ClickHouse 26.2 Release Highlights — TipRanks](https://www.tipranks.com/news/private-companies/clickhouse-highlights-new-26-2-release-with-cloud-and-observability-enhancements)

Core Web Vitals 2026 — Optimizing LCP, INP and CLS for Faster Websites

Cloudflare Pages — Free Full-Stack Deployment on the Global Edge

Disclaimer: The opinions expressed in this blog are solely my own and do not reflect the views or opinions of my employer or any affiliated organizations. The content provided is for informational and educational purposes only and should not be taken as professional advice. While I strive to provide accurate and up-to-date information, I make no warranties or guarantees about the completeness, reliability, or accuracy of the content. Readers are encouraged to verify the information and seek independent advice as needed. I disclaim any liability for decisions or actions taken based on the content of this blog.