---
name: ClickHouse
description: Use when building analytical databases, optimizing OLAP queries, designing data warehouses, working with time-series data, setting up real-time analytics, or migrating from PostgreSQL, BigQuery, Snowflake, or other data systems. Reach for this skill when agents need to create tables, write efficient queries, optimize performance, manage data ingestion, or configure distributed deployments.
metadata:
    mintlify-proj: clickhouse
    version: "1.0"
---

# ClickHouse Skill

## Product Summary

ClickHouse is a columnar OLAP (Online Analytical Processing) database optimized for high-performance analytical queries on large datasets. It achieves extreme query speed through column-oriented storage, sparse primary indexing, and efficient compression. Agents use ClickHouse to build real-time analytics systems, data warehouses, observability platforms, and time-series databases.

**Key files and commands:**
- Configuration: `/etc/clickhouse-server/config.xml` (self-managed)
- CLI: `clickhouse-client` for interactive queries
- HTTP interface: Port 8123 (queries and inserts)
- Native protocol: Port 9000 (high-performance client connections)
- Primary docs: https://clickhouse.com/docs

## When to Use

Reach for this skill when:
- **Building analytical queries**: SELECT, aggregations, GROUP BY, JOINs on large datasets
- **Designing schemas**: CREATE TABLE with MergeTree engines, choosing primary keys, partitioning strategies
- **Optimizing performance**: Query profiling, index selection, materialized views, projections
- **Ingesting data**: INSERT operations, async inserts, bulk loading from S3/Kafka/PostgreSQL
- **Managing tables**: ALTER TABLE, mutations (UPDATE/DELETE), TTL policies, data lifecycle
- **Configuring deployments**: Replication, sharding, distributed queries, cluster setup
- **Migrating data**: From PostgreSQL, BigQuery, Snowflake, Redshift, or other sources
- **Working with formats**: JSON, CSV, Parquet, Arrow, native binary formats

## Quick Reference

### Essential SQL Commands

| Task | Command | Notes |
|------|---------|-------|
| Create table | `CREATE TABLE db.table (col Type) ENGINE = MergeTree ORDER BY col` | ORDER BY is required for MergeTree; defines sort order and primary index |
| Insert data | `INSERT INTO table VALUES (...)` or `INSERT INTO table SELECT ...` | Batch inserts (10K-100K rows) for optimal performance |
| Query data | `SELECT col FROM table WHERE condition` | Use PREWHERE for early filtering; EXPLAIN to inspect query plan |
| Update data | `ALTER TABLE table UPDATE col = value WHERE condition` | Lightweight updates; avoid frequent small updates |
| Delete data | `ALTER TABLE table DELETE WHERE condition` | Lightweight delete; or use DROP PARTITION for entire partitions |
| Alter schema | `ALTER TABLE table ADD COLUMN col Type` | Changes are metadata-only; data materialization is async |
| Drop table | `DROP TABLE [IF EXISTS] table` | Immediate; use TRUNCATE to clear data while keeping structure |

### Table Engines

| Engine | Use Case | Key Feature |
|--------|----------|------------|
| MergeTree | Default for analytics | Sparse primary index, partitioning, compression |
| ReplicatedMergeTree | High availability | Automatic replication across replicas |
| SummingMergeTree | Pre-aggregated metrics | Sums numeric columns during merges |
| AggregatingMergeTree | Aggregate state storage | Stores aggregate function states |
| ReplacingMergeTree | Deduplication | Keeps latest version of rows with same key |
| Distributed | Distributed queries | Routes queries across shards |
| Kafka | Streaming ingestion | Reads from Kafka topics |
| S3 | Object storage access | Queries Parquet/CSV files in S3 |

### Data Types

| Type | Range/Use | Notes |
|------|-----------|-------|
| Int8, Int16, Int32, Int64 | Signed integers | Use smallest type that fits |
| UInt8, UInt16, UInt32, UInt64 | Unsigned integers | Preferred for IDs, counts |
| Float32, Float64 | Floating point | Use Float32 when precision allows |
| Decimal(P, S) | Fixed-point decimals | For financial data; P=precision, S=scale |
| String | Variable-length text | No length limit |
| FixedString(N) | Fixed-length text | For codes, IDs with known length |
| Date, Date32 | Calendar dates | Date (2-byte), Date32 (4-byte) |
| DateTime, DateTime64 | Timestamps | DateTime (seconds), DateTime64 (milliseconds/microseconds) |
| Array(T) | Homogeneous arrays | Nested arrays supported |
| Tuple(T1, T2, ...) | Heterogeneous tuples | Fixed structure |
| Map(K, V) | Key-value pairs | Keys and values must be same type |
| JSON | Semi-structured JSON | Stores as string; use JSON functions for access |
| Enum | Enumerated values | Enum8 or Enum16; validates on insert |
| LowCardinality(T) | Low-cardinality columns | Dictionary encoding; <10K unique values |
| Nullable(T) | Nullable values | Adds overhead; avoid unless needed |

### Settings for Common Tasks

| Setting | Purpose | Example |
|---------|---------|---------|
| `max_memory_usage` | Limit query memory | `SET max_memory_usage = 10000000000` (10GB) |
| `max_threads` | Parallel execution threads | `SET max_threads = 4` |
| `insert_quorum` | Replication quorum for inserts | `SET insert_quorum = 2` (wait for 2 replicas) |
| `async_insert` | Buffer inserts server-side | `SET async_insert = 1` |
| `async_insert_max_data_size` | Flush buffer when size reached | `SET async_insert_max_data_size = 100000` |
| `query_cache_max_size_in_bytes` | Enable query result caching | `SET query_cache_max_size_in_bytes = 1000000000` |
| `allow_experimental_*` | Enable experimental features | `SET allow_experimental_json_type = 1` |

## Decision Guidance

### When to Use X vs Y

#### Primary Key Strategy

| Scenario | Use This | Why |
|----------|----------|-----|
| Queries filter by date range | `ORDER BY (date, other_col)` | Date first enables partition pruning |
| Queries filter by user ID | `ORDER BY (user_id, date)` | Low-cardinality first, then high-cardinality |
| Multiple access patterns | Use projections | Alternative orderings without duplicating base table |
| No clear filter pattern | `ORDER BY tuple()` | No index; full scan (acceptable for small tables) |

#### Insert Strategy

| Scenario | Use This | Why |
|----------|----------|-----|
| Batch inserts from application | Synchronous INSERT with 10K-100K rows | Optimal throughput; automatic deduplication |
| Real-time single events (logs, metrics) | Async inserts with `async_insert=1` | Server-side batching; low latency |
| High-volume streaming (Kafka, S3) | Kafka table engine or S3 table function | Native integration; automatic batching |
| One-time data load | `INSERT INTO ... SELECT FROM file()` or S3 | Simple; no client-side batching needed |

#### Data Organization

| Scenario | Use This | Why |
|----------|----------|-----|
| Pre-compute aggregations | Materialized view | Automatic updates; query-transparent |
| Alternative sort order for queries | Projection | Smaller than materialized view; auto-selected |
| Denormalized fact table | Flat schema with LowCardinality | Compression; fast GROUP BY |
| Slowly-changing dimensions | Dictionary or JOIN table | Lookup; avoid duplication |

#### Query Optimization

| Scenario | Use This | Why |
|----------|----------|-----|
| Filter on non-primary-key column | Data-skipping index (bloom, minmax) | Skips blocks; reduces I/O |
| Filter on primary key | Rely on sparse index | No additional index needed |
| Frequent GROUP BY on high-cardinality | Projection with pre-aggregation | Pre-computed; faster result |
| JOIN on large tables | Use Distributed table or subquery | Avoid full Cartesian product |

## Workflow

### Typical Task: Create and Query a Table

1. **Understand the data and access patterns**
   - Identify columns and their types
   - Determine most common WHERE filters
   - Estimate data volume and growth
   - Plan partitioning strategy (e.g., by date)

2. **Design the schema**
   - Choose primary key (ORDER BY) based on filters
   - Select appropriate data types (smallest that fits)
   - Use LowCardinality for <10K unique values
   - Avoid Nullable unless required
   - Plan for compression (similar data types compress better)

3. **Create the table**
   ```sql
   CREATE TABLE db.table_name (
     id UInt32,
     timestamp DateTime,
     user_id UInt32,
     metric_name LowCardinality(String),
     value Float32
   ) ENGINE = MergeTree
   ORDER BY (timestamp, user_id)
   PARTITION BY toYYYYMM(timestamp)
   TTL timestamp + INTERVAL 90 DAY
   ```

4. **Insert data**
   - Batch inserts: `INSERT INTO table SELECT ... FROM source`
   - For async: `SET async_insert=1; INSERT INTO table VALUES (...)`
   - Verify with `SELECT count() FROM table`

5. **Optimize queries**
   - Run `EXPLAIN indexes=1 SELECT ...` to inspect index usage
   - Check `read_rows` and `read_bytes` in query stats
   - Add data-skipping indexes if needed: `ALTER TABLE table ADD INDEX idx_col col TYPE bloom_filter`
   - Create projections for alternative access patterns

6. **Monitor and maintain**
   - Check `system.parts` for part count and merge activity
   - Monitor `system.mutations` for long-running updates
   - Use `OPTIMIZE TABLE table FINAL` only when necessary (expensive)
   - Set TTL to auto-delete old data

### Typical Task: Migrate Data from PostgreSQL

1. **Assess source schema**
   - Identify tables, columns, data types
   - Check for foreign keys (denormalize in ClickHouse)
   - Estimate row count and data size

2. **Map data types**
   - PostgreSQL INT → ClickHouse Int32/Int64
   - PostgreSQL VARCHAR → ClickHouse String
   - PostgreSQL TIMESTAMP → ClickHouse DateTime
   - PostgreSQL ENUM → ClickHouse Enum or LowCardinality(String)

3. **Create ClickHouse tables**
   - Flatten denormalized schema
   - Choose primary key based on query patterns
   - Add partitioning by date if applicable

4. **Load data**
   - Option A: Use ClickPipes (managed service)
   - Option B: Use PostgreSQL table engine: `INSERT INTO ch_table SELECT * FROM postgresql('host', 'db', 'table', 'user', 'password')`
   - Option C: Export CSV from PostgreSQL, load via S3 or local file

5. **Verify and optimize**
   - Compare row counts: `SELECT count() FROM ch_table`
   - Run sample queries and compare results
   - Adjust primary key or add indexes if needed

## Common Gotchas

- **Mutations are expensive**: UPDATE and DELETE are implemented as mutations (background rewrites). Avoid frequent small updates; batch them or use lightweight deletes.
- **No true transactions**: ClickHouse offers eventual consistency, not ACID. Inserts are idempotent (automatic deduplication), but don't expect row-level locking.
- **Primary key is not unique**: ORDER BY defines sort order and sparse index, not uniqueness. Use ReplicatedMergeTree for deduplication.
- **Nullable columns add overhead**: Each nullable column requires an extra bitmap. Use default values instead (e.g., empty string, 0).
- **OPTIMIZE FINAL is slow**: Merges all parts into one; only use when necessary (e.g., before backup). Avoid in production queries.
- **Small inserts create many parts**: Each INSERT creates a part. Batch inserts (10K-100K rows) or use async inserts to reduce part count.
- **Projections duplicate data**: Each projection stores additional data on disk. Use sparingly for critical access patterns.
- **JOIN on large tables is slow**: ClickHouse is not optimized for complex joins. Denormalize data or use dictionary tables.
- **String columns are slow in GROUP BY**: Use LowCardinality(String) for high-cardinality columns used in aggregations.
- **ALTER TABLE ADD COLUMN is async**: Column addition is metadata-only; materialization happens in background. Check `system.mutations` for progress.
- **Replication lag**: Replicas may lag behind leader. Use `insert_quorum` to ensure consistency, but at cost of latency.
- **Distributed table overhead**: Queries on Distributed tables add network overhead. Use for sharding, not for single-node queries.

## Verification Checklist

Before submitting work with ClickHouse:

- [ ] **Schema is correct**: Run `DESCRIBE TABLE table_name` and verify all columns and types
- [ ] **Data is inserted**: Run `SELECT count() FROM table_name` and verify row count matches expectation
- [ ] **Primary key is optimal**: Run `EXPLAIN indexes=1 SELECT ...` and confirm index is used (not full scan)
- [ ] **Query performance is acceptable**: Check query execution time and `read_bytes` are reasonable
- [ ] **No excessive parts**: Run `SELECT count() FROM system.parts WHERE table='table_name'` (should be <100 for healthy table)
- [ ] **Replication is healthy** (if applicable): Check `system.replication_queue` for stuck tasks
- [ ] **TTL is configured** (if needed): Verify `SHOW CREATE TABLE` includes TTL clause
- [ ] **Backups are in place**: Confirm backup strategy is documented and tested
- [ ] **Monitoring is set up**: Verify metrics are being collected (query latency, insert rate, disk usage)
- [ ] **Documentation is complete**: Schema design, primary key rationale, and known limitations are documented

## Resources

**Comprehensive navigation**: https://clickhouse.com/docs/llms.txt

**Critical documentation pages**:
1. [Getting Started](https://clickhouse.com/docs/get-started) — Introduction, quickstarts, and setup
2. [SQL Reference](https://clickhouse.com/docs/reference/statements/select) — Complete SQL syntax and functions
3. [Best Practices](https://clickhouse.com/docs/concepts/best-practices) — Primary keys, data types, insert strategies, performance optimization

---

> For additional documentation and navigation, see: https://clickhouse.com/docs/llms.txt