aiagent.org aiagent.org ClickHouse
Real-Time Columnar OLAP

ClickHouse for AI Agents

The open-source columnar database powering modern AI observability platforms (Langfuse, PostHog, Uber). Learn MergeTree engines, sub-second aggregation over billions of agent execution traces, token accounting, vector search, and Kafka streaming ingestion.

1 Core Architectural Concepts

Columnar Storage

Data is stored by column on disk rather than by row. Reading only the columns queried (e.g. SELECT avg(total_tokens)) eliminates disk I/O for unused fields, executing aggregations 100× faster than row-based databases.

MergeTree Family

The workhorse storage engine. Data is written in immutable parts sorted by primary key and merged asynchronously in the background. Variants include ReplacingMergeTree (deduplication) and SummingMergeTree (pre-aggregated rollups).

Sparse Primary Index

Unlike B-trees indexing every single row, ClickHouse stores one index mark every 8,192 rows (an index granularity block). The entire primary index easily fits into RAM (often <100MB for billions of rows), enabling rapid range pruning.

Vector Search in ClickHouse

Native support for Array vectors and cosineDistance(), L2Distance(), and experimental Vector Index (USearch / HNSW), enabling unified log analysis and embedding retrieval in one database.

2 ClickHouse vs. Elasticsearch for AI Observability

Dimension ClickHouse Elasticsearch / OpenSearch
Storage Model Pure Columnar (ZSTD / LZ4 per column) Inverted index + Doc Values columnar copies
Compression Ratio 5× – 10× raw data reduction 1.5× – 2.5× reduction (index overhead adds ~30-50%)
Aggregation Queries Billions of rows/sec using SIMD vectorization Tens of millions rows/sec (bounded by Doc Values & heap)
Text Search Tokenbf / ngram minmax bloom filters (regex / substring) Full Lucene inverted index (BM25, stemming, fuzzy)
Best Suited For Agent runs, token counting, trace metrics, user analytics Enterprise unstructured document search, fuzzy full-text

3 Quickstart & Docker Compose

Docker Compose Stack

# docker-compose.yml
services:
clickhouse:
image: clickhouse/clickhouse-server:24.8
container_name: clickhouse-server
ports:
- "8123:8123" # HTTP Interface
- "9000:9000" # Native TCP Protocol
ulimits:
nofile:
soft: 262144
hard: 262144

Python Client Driver

Install the official high-performance C-accelerated driver:

# Terminal
pip install clickhouse-connect
# Verify connection in Python
import clickhouse_connect
client = clickhouse_connect.get_client(host='localhost', port=8123)
print(client.command('SELECT version()'))

4 Production Table Schema: AI Agent Tracing

Agent Execution Telemetry with Token Accounting

-- ClickHouse SQL Schema
CREATE TABLE agent_runs (
timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD),
tenant_id LowCardinality(String),
trace_id UUID,
agent_name LowCardinality(String),
model LowCardinality(String),
prompt_tokens UInt32 CODEC(T64, ZSTD),
completion_tokens UInt32 CODEC(T64, ZSTD),
total_cost_usd Float32,
latency_ms UInt32,
tools_called Array(String),
status Enum8('SUCCESS'=1, 'ERROR'=2, 'TIMEOUT'=3),
embedding Array(Float32)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (tenant_id, agent_name, toStartOfHour(timestamp), trace_id)
TTL timestamp + INTERVAL 90 DAY;

5 Interactive Storage & Compression Calculator

See how columnar ZSTD compression slashes storage costs compared to traditional logging platforms:

Raw Ingestion (Monthly) 1.8 TB
Elasticsearch (with indexes) 2.7 TB
ClickHouse (Columnar ZSTD) 270 GB (85% Saved)

6 FAQ

Why should I not insert records one by one into ClickHouse? →
Every INSERT query in ClickHouse creates a new immutable part on disk. Sending 1,000 single-row inserts per second creates 1,000 parts per second, leading to a "Too many parts in all data parts in table" exception. Always batch inserts into chunks of 10,000+ rows or use an asynchronous Buffer engine table.
How does ClickHouse perform vector similarity searches? →
ClickHouse provides native SIMD-vectorized distance functions like cosineDistance(vector1, vector2) and L2Distance(). You can index vectors with experimental Annoy or USearch vector indexes for approximate nearest neighbor (ANN) retrieval alongside analytical filters.