ClickHouse Database Expert

Comprehensive ClickHouse knowledge base for working with high-performance columnar OLAP databases.

When to Invoke This Skill

Use this skill when:

• Designing ClickHouse schemas (tables, partitions, ORDER BY)
• Choosing table engines (MergeTree family decision tree)
• Writing and optimizing ClickHouse SQL queries
• Managing ClickHouse clusters (replication, sharding)
• Debugging query performance or merge issues
• Setting up backups and monitoring
• Integrating ClickHouse with Kafka, S3, or other systems
• Operating ClickHouse on Kubernetes

What is ClickHouse?

ClickHouse is a columnar OLAP database designed for real-time analytics on large datasets.

Key Characteristics:

• Columnar storage: Read only needed columns (10-100x faster than row stores for analytical queries)
• MergeTree engine family: Automatic background merges for data organization
• SQL dialect with extensions: Arrays, tuples, lambdas, specialized functions
• Append-first design: Optimized for high-volume inserts, not point updates

Golden Rules

1. Always use MergeTree (except tiny dimensions → Memory engine)
2. Sort key = query filter: ORDER BY defines data layout on disk
3. Partition by time: For TTL and efficient DROP PARTITION operations
4. Avoid mutations: Use INSERT + new data instead of UPDATE/DELETE
5. Monitor merges: Background merges impact performance significantly

Quick Start Examples

Minimal Working Schema

-- Basic events table with best practices
CREATE TABLE events (
    timestamp DateTime,
    user_id UInt32,
    event_type LowCardinality(String),
    session_id UUID,
    metadata String,
    revenue Decimal(18, 2) DEFAULT 0
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)  -- Monthly partitions
ORDER BY (user_id, timestamp)     -- Data layout matches query pattern
SETTINGS index_granularity = 8192;

Common Query Patterns

-- Effective time range filter
SELECT * FROM events
WHERE timestamp >= today() AND timestamp < tomorrow();

-- Optimize JOIN (smaller table on RIGHT)
SELECT * FROM large_table RIGHT JOIN small_table ON large_table.id = small_table.id;

-- Check query plan
EXPLAIN SELECT * FROM events WHERE user_id = 123;

Monitoring Queries

-- Running queries
SELECT query_id, user, query, elapsed FROM system.processes ORDER BY elapsed DESC;

-- Table sizes
SELECT database, table, formatReadableSize(sum(bytes)) as size
FROM system.parts WHERE active = 1
GROUP BY database, table ORDER BY sum(bytes) DESC;

-- Active merges
SELECT table, elapsed, bytes_read_uncompressed FROM system.merges ORDER BY elapsed DESC;

Common Pitfalls

ClickHouse Architecture Overview

Data Model

• Append-first: No in-place updates (mutations are expensive)
• Parts and partitions: Parts merge into larger parts (background process)
• Two-level index: Sparse index (8192 rows/mark) + mark files

When ClickHouse Shines

✅ Wide tables (100+ columns), read few columns ✅ Time-series with time-based filters ✅ Aggregations over billions of rows ✅ Append-only workloads (events, logs, metrics)

When to Avoid ClickHouse

❌ Point updates/deletes (use row store like PostgreSQL) ❌ Heavy JOINs on non-sorted keys ❌ Complex transactions (no ACID support) ❌ Low-latency OLTP (use row store)

Key Topics by Reference

Schema & Table Design

Query & Performance

Operations & Cluster

Integration & Best Practices

Quick Decision Guides

Which Table Engine?

Need to store data?
├── < 1M rows, dimension → Memory
└── ≥ 1M rows → MergeTree family
    ├── Deduplication? → ReplacingMergeTree(version)
    ├── Changelog? → CollapsingMergeTree(sign)
    ├── Pre-aggregation? → AggregatingMergeTree()
    ├── Replication? → ReplicatedMergeTree(...)
    └── Default → MergeTree()

See references/table-engines.md for complete reference.

Common Issues & Quick Fixes

See references/debugging.md for detailed troubleshooting.

See Also

Core References

• references/core-concepts.md – Architecture, data model, MergeTree internals
• references/schema-design.md – Database engines, schema organization, migrations
• references/table-design.md – ORDER BY design, partitioning strategies, column selection
• references/table-engines.md – Complete guide to all MergeTree family engines
• references/sql-reference.md – Full SQL dialect with data types and functions

Query & Performance

• references/query-optimization.md – EXPLAIN, JOIN optimization, projections, skip indexes
• references/advanced-features.md – Materialized views, mutations, TTL, dictionaries
• references/debugging.md – Query debugging, merge issues, replication problems

Operations

• references/cluster-management.md – Distributed tables, replication, sharding
• references/kubernetes-operator.md – K8s deployment with Altinity operator
• references/backup-restore.md – Backup strategies and disaster recovery
• references/monitoring.md – Query monitoring, health checks, system queries

Integration & Best Practices

• references/integrations.md – Kafka, S3, PostgreSQL, MySQL, BI tools
• references/best-practices.md – Comprehensive checklist and anti-patterns
• references/external.md – Altinity KB (200+ articles) and official docs

System Queries

• references/system-queries.md – Ready-to-use queries for operations and monitoring

Version: 1.0.0 Sources: Altinity Knowledge Base (200+ articles) + ClickHouse Official Docs