ClickHouse 🏠

Real-time analytics on billions of rows. Sub-second queries. No indexes needed.

Setup

On first use, read setup.md for connection configuration.

When to Use

User needs OLAP analytics, log analysis, time-series data, or real-time dashboards. Agent handles schema design, query optimization, data ingestion, and cluster administration.

Architecture

Memory lives in ~/clickhouse/. See memory-template.md for structure.

~/clickhouse/
├── memory.md        # Connection profiles + query patterns
├── schemas/         # Table definitions per database
└── queries/         # Saved analytical queries

Quick Reference

Core Rules

1. Always Specify Engine

Every table needs an explicit engine. Default to MergeTree family:

-- Time-series / logs
CREATE TABLE events (
    timestamp DateTime,
    event_type String,
    data String
) ENGINE = MergeTree()
ORDER BY (timestamp, event_type);

-- Aggregated metrics
CREATE TABLE daily_stats (
    date Date,
    metric String,
    value AggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree()
ORDER BY (date, metric);

2. ORDER BY is Your Index

ClickHouse has no traditional indexes. The ORDER BY clause determines data layout:

• Put high-cardinality filter columns first
• Put range columns (dates, timestamps) early
• Match your most common WHERE patterns

-- Good: filters by user_id, then date range
ORDER BY (user_id, date, event_type)

-- Bad: date first when you filter by user_id
ORDER BY (date, user_id, event_type)

3. Use Appropriate Data Types

4. Batch Inserts

Never insert row-by-row. ClickHouse is optimized for batch writes:

# Good: batch insert
clickhouse-client --query="INSERT INTO events FORMAT JSONEachRow" < batch.json

# Bad: individual inserts in a loop
for row in data:
    INSERT INTO events VALUES (...)

Minimum batch: 1,000 rows. Optimal: 10,000-100,000 rows.

5. Prewarm Queries with FINAL

Queries on ReplacingMergeTree/CollapsingMergeTree need FINAL for accuracy:

-- May return duplicates/old versions
SELECT * FROM users WHERE id = 123;

-- Guaranteed latest version
SELECT * FROM users FINAL WHERE id = 123;

FINAL has performance cost. For dashboards, consider materialized views.

6. Materialized Views for Speed

Pre-aggregate expensive computations: