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