Clickhouse

---
name: clickhouse
description: "ClickHouse technology expert covering ALL versions. Deep expertise in columnar storage, MergeTree engine family, materialized views, distributed queries, and analytical query optimization. WHEN: \"ClickHouse\", \"clickhouse-client\", \"clickhouse-server\", \"MergeTree\", \"ReplacingMergeTree\", \"AggregatingMergeTree\", \"CollapsingMergeTree\", \"materialized view\", \"distributed table\", \"ClickHouse Cloud\", \"clickhouse-local\", \"ClickHouse Keeper\"."
license: MIT
---

# ClickHouse

This skill covers ClickHouse across all supported versions (24.8 LTS through 25.12 LTS). It covers ClickHouse internals -- columnar storage, the MergeTree engine family, distributed query execution, materialized views, projections, dictionaries, replication via ClickHouse Keeper, and analytical query optimization. For version-specific detail, see the matching file under `references/versions/`.

## When to Use This Skill vs. Version-Specific Guidance

**Use this agent when the question spans versions or is version-agnostic:**
- "How does MergeTree storage work in ClickHouse?"
- "Design a schema for a high-volume event analytics workload"
- "Tune a ClickHouse cluster for maximum query throughput"
- "Set up ReplicatedMergeTree with ClickHouse Keeper"
- "Compare AggregatingMergeTree vs. materialized views for pre-aggregation"
- "Troubleshoot 'too many parts' errors"
- "Optimize a slow GROUP BY query on a billion-row table"

**See the matching version reference when the question is version-specific:**
- "ClickHouse 25.12 LTS new features" --> `references/versions/25.12-lts.md`
- "ClickHouse 25.3 LTS lightweight deletes improvements" --> `references/versions/25.3-lts.md`
- "ClickHouse 24.8 LTS SharedMergeTree for Cloud" --> `references/versions/24.8-lts.md`

## How to Approach Tasks

When you receive a request:

1. **Classify** the request:
   - **Architecture/internals** -- Load `references/architecture.md`
   - **Performance diagnostics** -- Load `references/diagnostics.md`
   - **Configuration/operations** -- Load `references/best-practices.md`
   - **Version-specific feature** -- See the matching `references/versions/<v>.md` file
   - **Comparison with other databases** -- see the `overview` skill

2. **Determine version** -- Ask if unclear. Behavior differs across versions (e.g., lightweight deletes matured in 24.x+, SharedMergeTree only in ClickHouse Cloud, Variant type in 25.x+).

3. **Analyze** -- Apply ClickHouse-specific reasoning. Reference columnar storage characteristics, MergeTree merge mechanics, partition pruning, primary key sparse indexing, and distributed query execution as relevant.

4. **Recommend** -- Provide actionable guidance with specific server settings, SQL DDL/DML, clickhouse-client commands, or config.xml/users.xml parameters.

5. **Verify** -- Suggest validation steps (EXPLAIN PIPELINE, system.query_log, system.parts, system.merges, system.metrics).

## Core Expertise

### Columnar Storage Model

ClickHouse stores data in a column-oriented format. Each column is stored independently in separate files, enabling:

- **Compression efficiency** -- Similar values in a column compress extremely well (10:1 to 100:1 typical for analytical data). ClickHouse uses LZ4 by default with optional ZSTD, Delta, DoubleDelta, Gorilla, T64, and FPC codecs.
- **Vectorized execution** -- Queries process data in batches of column vectors (default 8192 rows per block), exploiting CPU SIMD instructions.
- **I/O minimization** -- Only columns referenced in the query are read from disk. A query touching 5 of 200 columns reads ~2.5% of the data.
- **Cache-friendly access** -- Sequential column data fits in CPU cache lines efficiently.

**Column encoding best practices:**
```sql
CREATE TABLE events (
    event_time    DateTime64(3) CODEC(DoubleDelta, LZ4),   -- timestamps: DoubleDelta
    user_id       UInt64        CODEC(Delta, LZ4),          -- monotonic IDs: Delta
    event_type    LowCardinality(String),                    -- low-cardinality strings
    url           String        CODEC(ZSTD(3)),              -- high-entropy strings: ZSTD
    response_code UInt16        CODEC(T64, LZ4),             -- small integers: T64
    latitude      Float64       CODEC(Gorilla, LZ4),         -- IEEE 754 floats: Gorilla
    payload       String        CODEC(ZSTD(1))               -- large text/JSON: ZSTD
) ENGINE = MergeTree()
ORDER BY (event_type, user_id, event_time);
```

### MergeTree Engine Family

MergeTree is the foundation of ClickHouse's storage. All production tables should use a MergeTree variant.

**How MergeTree works:**
1. Data is inserted in **parts** (immutable sorted chunks, typically millions of rows each)
2. Each part contains column files, a primary index (sparse), skip indexes, partition metadata, and checksums
3. Background **merges** combine smaller parts into larger parts, maintaining sort order
4. The **primary key** defines the sort order within parts and powers the sparse index (one index entry per `index_granularity` rows, default 8192)
5. **Partitioning** physically separates data (e.g., by month) to enable partition-level operations (DROP PARTITION, DETACH/ATTACH)

**MergeTree variants:**

| Engine | Purpose | Key Behavior |
|---|---|---|
| `MergeTree` | General-purpose analytical storage | Insert-only; parts are merged maintaining sort order |
| `ReplacingMergeTree` | Deduplication by sorting key | Keeps the latest version (by `ver` column) during merges; not guaranteed without FINAL |
| `AggregatingMergeTree` | Pre-aggregated rollups | Merges rows with same sorting key using aggregate function states |
| `SummingMergeTree` | Sum-based rollups | Sums numeric columns for rows with same sorting key during merges |
| `CollapsingMergeTree` | Mutable state via sign column | Uses Sign column (+1/-1) to cancel old rows and insert new ones |
| `VersionedCollapsingMergeTree` | Collapsing with out-of-order inserts | Adds Version column to handle out-of-order inserts correctly |
| `GraphiteMergeTree` | Graphite metric storage | Applies Graphite retention and aggregation rules during merges |

**Replicated variants:** Prefix any engine with `Replicated` (e.g., `ReplicatedMergeTree`, `ReplicatedAggregatingMergeTree`) for multi-replica support via ClickHouse Keeper.

### Primary Key and Sparse Index

ClickHouse's primary key is fundamentally different from RDBMS primary keys:

- **Not a uniqueness constraint** -- Duplicate values are allowed
- **Defines sort order** -- Data within each part is sorted by the primary key columns
- **Sparse index** -- One index entry per `index_granularity` rows (default 8192), not per row
- **Granule** -- The atomic unit of data reading; a contiguous block of `index_granularity` rows

**Primary key column ordering matters enormously:**
```sql
-- GOOD: low-cardinality first, then higher cardinality, then time
ORDER BY (tenant_id, event_type, event_time)

-- BAD: high-cardinality first destroys index effectiveness
ORDER BY (event_time, user_id, event_type)
```

**Design rules:**
1. Place the lowest-cardinality column first (the column most queries filter on)
2. Each subsequent column should increase in cardinality
3. Time columns typically go last in the ORDER BY (unless time-range is the primary access pattern)
4. The ORDER BY (sorting key) can be a superset of the PRIMARY KEY -- additional ORDER BY columns improve compression without expanding the sparse index

### Partitioning

Partitioning physically separates data within a table:

```sql
CREATE TABLE events (
    event_date Date,
    event_type String,
    user_id UInt64,
    data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)   -- monthly partitions
ORDER BY (event_type, user_id, event_date);
```

**Partitioning rules:**
- Use **coarse** partitions: monthly or weekly. Daily is acceptable for very high volumes. Hourly is almost always wrong.
- Target **~1,000 or fewer active partitions** per table. More than that causes excessive file descriptors and metadata overhead.
- Partition pruning eliminates entire partitions from scans when the WHERE clause matches the partition expression.
- Partition operations: `ALTER TABLE ... DROP PARTITION`, `DETACH PARTITION`, `ATTACH PARTITION`, `REPLACE PARTITION`, `FREEZE PARTITION` (for backups).
- **Do NOT over-partition.** A common mistake is partitioning by a high-cardinality column, creating millions of tiny partitions.

### Materialized Views

Materialized views in ClickHouse are insert-time triggers, not periodic refreshes:

```sql
-- Source table
CREATE TABLE raw_events (
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    duration_ms UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

-- Destination table for the materialized view
CREATE TABLE hourly_stats (
    hour DateTime,
    event_type LowCardinality(String),
    count AggregateFunction(count),
    avg_duration AggregateFunction(avg, UInt32),
    uniq_users AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (event_type, hour);

-- Materialized view: triggers on each INSERT into raw_events
CREATE MATERIALIZED VIEW hourly_stats_mv TO hourly_stats AS
SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    countState() AS count,
    avgState(duration_ms) AS avg_duration,
    uniqState(user_id) AS uniq_users
FROM raw_events
GROUP BY hour, event_type;

-- Query the materialized view target table using -Merge combinators
SELECT
    hour,
    event_type,
    countMerge(count) AS total_events,
    avgMerge(avg_duration) AS avg_duration_ms,
    uniqMerge(uniq_users) AS unique_users
FROM hourly_stats
GROUP BY hour, event_type
ORDER BY hour DESC;
```

**Key principles:**
- Materialized views process data at INSERT time, not retroactively
- Use `AggregateFunction` types with `-State` suffix in the MV and `-Merge` suffix in queries
- Materialized views can chain: MV_A --> MV_B --> MV_C
- Multiple MVs on one source table is common and efficient
- If the MV query fails, the INSERT into the source table still succeeds (data is not lost)

### Projections

Projections are inline materialized views stored within the same table:

```sql
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    country LowCardinality(String),
    duration_ms UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

-- Add a projection for queries that filter by country
ALTER TABLE events ADD PROJECTION events_by_country (
    SELECT * ORDER BY (country, event_type, event_time)
);

-- Add an aggregating projection
ALTER TABLE events ADD PROJECTION hourly_summary (
    SELECT
        toStartOfHour(event_time) AS hour,
        event_type,
        count() AS cnt,
        avg(duration_ms) AS avg_dur
    GROUP BY hour, event_type
);

-- Materialize projections for existing data
ALTER TABLE events MATERIALIZE PROJECTION events_by_country;
ALTER TABLE events MATERIALIZE PROJECTION hourly_summary;
```

**Projections vs. Materialized Views:**
- Projections are simpler (no separate target table) but less flexible
- Projections are automatically maintained and consistent with the base table
- Materialized views allow cross-table transformations, more complex aggregations, and different engines
- The query optimizer automatically selects the best projection if applicable

### Dictionaries

Dictionaries are ClickHouse's mechanism for key-value lookups from external data sources:

```sql
CREATE DICTIONARY geo_dict (
    ip_range_start UInt32,
    ip_range_end   UInt32,
    country_code   String,
    city           String
)
PRIMARY KEY ip_range_start
SOURCE(CLICKHOUSE(TABLE 'geo_data' DB 'default'))
LAYOUT(IP_TRIE())
LIFETIME(MIN 3600 MAX 7200);

-- Usage in queries
SELECT
    event_time,
    user_ip,
    dictGet('geo_dict', 'country_code', toIPv4(user_ip)) AS country
FROM events;
```

**Dictionary layouts:**
| Layout | Use Case | Memory Model |
|---|---|---|
| `flat` | Integer keys 0..N | Array indexed by key |
| `hashed` | Integer or string keys, fits in RAM | Hash table |
| `range_hashed` | Key + date/time range lookups | Hash table with range index |
| `cache` | Large datasets, tolerates misses | LRU cache, queries source on miss |
| `complex_key_hashed` | Composite keys | Hash table with composite key |
| `ip_trie` | IP address/CIDR lookups | Trie structure |
| `direct` | Always reads from source | No cache; every lookup queries source |

### Distributed Queries and Sharding

ClickHouse supports horizontal scaling via sharding. The `Distributed` engine acts as a proxy:

```sql
-- Local table on each shard
CREATE TABLE events_local ON CLUSTER my_cluster (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

-- Distributed table spanning all shards
CREATE TABLE events_distributed ON CLUSTER my_cluster AS events_local
ENGINE = Distributed(my_cluster, default, events_local, xxHash64(user_id));
```

**Sharding strategies:**
- **Hash-based** (`xxHash64(user_id)`): Even distribution, good for JOIN locality
- **Random** (`rand()`): Even distribution, no locality guarantees
- **Expression-based** (`toYYYYMM(event_time)`): Time-based sharding

**Distributed query execution flow:**
1. Query hits any node (the **initiator**)
2. Initiator rewrites the query and sends subqueries to each shard
3. Each shard executes locally and streams partial results back
4. Initiator merges partial results and returns the final result

**Key settings for distributed queries:**
```sql
SET distributed_product_mode = 'global';     -- for distributed JOINs
SET max_parallel_replicas = 3;               -- parallel reads from replicas
SET prefer_localhost_replica = 1;            -- prefer local shard if initiator is also a shard
```

### Replication with ClickHouse Keeper

ClickHouse Keeper (clickhouse-keeper) is the native replacement for Apache ZooKeeper, providing consensus for replicated tables:

**Replication model:**
- Each replica independently pulls data from other replicas
- The replication log is stored in ClickHouse Keeper (ZooKeeper path)
- Replicas are eventually consistent -- each replica independently fetches missing parts
- Inserts can go to any replica; the insert is logged in Keeper and other replicas fetch the part
- `ReplicatedMergeTree` is the replicated engine prefix

**ClickHouse Keeper configuration (config.xml):**
```xml
<keeper_server>
    <tcp_port>9181</tcp_port>
    <server_id>1</server_id>
    <log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
    <snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>
    <coordination_settings>
        <operation_timeout_ms>10000</operation_timeout_ms>
        <session_timeout_ms>30000</session_timeout_ms>
        <raft_logs_level>warning</raft_logs_level>
    </coordination_settings>
    <raft_configuration>
        <server>
            <id>1</id>
            <hostname>keeper1.example.com</hostname>
            <port>9234</port>
        </server>
        <server>
            <id>2</id>
            <hostname>keeper2.example.com</hostname>
            <port>9234</port>
        </server>
        <server>
            <id>3</id>
            <hostname>keeper3.example.com</hostname>
            <port>9234</port>
        </server>
    </raft_configuration>
</keeper_server>
```

**Replication monitoring:**
```sql
SELECT
    database, table, replica_name, is_leader,
    absolute_delay, queue_size, inserts_in_queue, merges_in_queue
FROM system.replicas
WHERE is_session_expired = 0
ORDER BY absolute_delay DESC;
```

### Data Types

ClickHouse provides rich type support optimized for analytics:

**Numeric types:** `UInt8`, `UInt16`, `UInt32`, `UInt64`, `UInt128`, `UInt256`, `Int8`..`Int256`, `Float32`, `Float64`, `Decimal32/64/128/256`

**String types:** `String` (arbitrary bytes), `FixedString(N)`, `LowCardinality(String)` (dictionary-encoded, major performance gain for <10K distinct values)

**Date/time types:** `Date`, `Date32`, `DateTime`, `DateTime64(precision, timezone)`

**Compound types:** `Array(T)`, `Tuple(T1, T2, ...)`, `Map(K, V)`, `Nested(col1 T1, col2 T2, ...)`

**Special types:** `Nullable(T)` (avoid when possible -- adds storage and processing overhead), `UUID`, `IPv4`, `IPv6`, `Enum8/Enum16`, `JSON` (semi-structured), `SimpleAggregateFunction`, `AggregateFunction`

**Performance tip:** Prefer `LowCardinality(String)` over plain `String` for columns with fewer than ~10,000 distinct values. It provides 5-10x compression improvement and 2-5x query speedup through dictionary encoding.

### ClickHouse SQL Extensions

See `references/operations.md#clickhouse-sql-extensions` — array functions, window functions, CTEs, FINAL modifier, SAMPLE clause, parameterized views, and FORMAT clause syntax.

### INSERT Optimization

ClickHouse is optimized for bulk inserts, not single-row writes:

**Rules of thumb:**
- **Batch size:** Insert 10,000-1,000,000 rows per INSERT statement
- **Insert frequency:** No more than ~1 insert per second per table (across all clients)
- **Async inserts:** Enable `async_insert = 1` for high-frequency small inserts; the server buffers and batches them
- **Buffer tables:** `Buffer` engine absorbs high-frequency inserts and flushes to the target MergeTree table periodically

```sql
-- Async insert settings
SET async_insert = 1;
SET wait_for_async_insert = 0;           -- don't wait for flush (fire-and-forget)
SET async_insert_max_data_size = 10485760;  -- flush buffer at 10MB
SET async_insert_busy_timeout_ms = 1000;    -- flush every 1 second

-- Bulk insert from file
clickhouse-client --query "INSERT INTO events FORMAT CSVWithNames" < events.csv
clickhouse-client --query "INSERT INTO events FORMAT Parquet" < events.parquet

-- Insert from S3
INSERT INTO events
SELECT * FROM s3('https://bucket.s3.amazonaws.com/data/*.parquet', 'Parquet');

-- Insert from another table
INSERT INTO events_archive
SELECT * FROM events WHERE event_date < today() - 90;
```

### Backup and Restore

See `references/operations.md#backup-and-restore` — BACKUP/RESTORE SQL commands, S3 backup targets, incremental backups, and alternatives like clickhouse-backup and FREEZE PARTITION.

## Troubleshooting Playbooks

### "Too Many Parts" Error

**Symptom:** Inserts fail with "Too many parts (N). Merges are processing significantly slower than inserts."

**Root cause:** Each INSERT creates a new part. If inserts arrive faster than merges can consolidate, part count grows unboundedly.

**Diagnostic:**
```sql
SELECT database, table, count() AS part_count, sum(rows) AS total_rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY part_count DESC;
```

**Resolution:**
1. **Reduce insert frequency** -- Batch inserts to at least 10K-100K rows per INSERT
2. **Enable async inserts** -- `SET async_insert = 1` to let the server batch
3. **Increase merge throughput** -- `background_pool_size` (default 16), `background_merges_mutations_concurrency_ratio`
4. **Check for stuck merges** -- `SELECT * FROM system.merges`
5. **Temporary relief** -- `OPTIMIZE TABLE events FINAL` (blocks until merges complete, resource-intensive)

### Slow Queries

**Diagnostic sequence:**
```sql
-- 1. Check query execution plan
EXPLAIN PIPELINE SELECT ... ;
EXPLAIN PLAN actions=1 SELECT ... ;

-- 2. Find slow queries in the log
SELECT query, query_duration_ms, read_rows, read_bytes, memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 5000
ORDER BY query_duration_ms DESC
LIMIT 20;

-- 3. Check if primary key is being used (look for granules scanned vs total)
SELECT query, ProfileEvents['SelectedMarks'] AS marks_selected,
       ProfileEvents['SelectedRows'] AS rows_selected
FROM system.query_log
WHERE query_id = 'your-query-id';
```

**Common causes and fixes:**
- **Full table scan** -- Query does not match the primary key prefix. Reorder ORDER BY or add a projection.
- **Excessive memory** -- Large GROUP BY or JOIN. Use `max_bytes_before_external_group_by` or `max_bytes_before_external_sort`.
- **Distributed query amplification** -- Suboptimal distributed JOIN. Use `GLOBAL IN` / `GLOBAL JOIN` or co-locate data on the same sharding key.
- **Missing codec** -- Large string columns without ZSTD. Add compression codecs.
- **Nullable overhead** -- Replace `Nullable(T)` with default values where possible.

### Replication Lag

**Diagnostic:**
```sql
SELECT database, table, replica_name, is_leader,
       absolute_delay, queue_size,
       inserts_in_queue, merges_in_queue,
       last_queue_update
FROM system.replicas
ORDER BY absolute_delay DESC;
```

**Resolution:**
1. Check Keeper health: `SELECT * FROM system.zookeeper WHERE path = '/clickhouse'`
2. Check network between replicas
3. Increase `background_schedule_pool_size` for replication tasks
4. Check for large parts that are slow to transfer: `SELECT * FROM system.replication_queue WHERE is_currently_executing = 1`

### OOM (Out of Memory)

**Prevention settings:**
```sql
SET max_memory_usage = 10000000000;                    -- 10GB per query
SET max_memory_usage_for_user = 20000000000;           -- 20GB per user
SET max_bytes_before_external_group_by = 5000000000;   -- spill GROUP BY to disk at 5GB
SET max_bytes_before_external_sort = 5000000000;       -- spill ORDER BY to disk at 5GB
SET max_rows_to_read = 1000000000;                     -- safety limit
```

**Diagnostic:**
```sql
SELECT query, memory_usage, peak_memory_usage
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND exception LIKE '%MEMORY_LIMIT_EXCEEDED%'
ORDER BY event_time DESC
LIMIT 10;
```

## Version Matrix

| Version | Type | Release | Status (April 2026) |
|---|---|---|---|
| 25.12 LTS | Long-Term Support | Dec 2025 | Current LTS -- actively maintained |
| 25.3 LTS | Long-Term Support | Mar 2025 | Supported LTS -- security and bug fixes |
| 24.8 LTS | Long-Term Support | Aug 2024 | Supported LTS -- security fixes until Aug 2026 |

**Version naming convention:** ClickHouse uses `YY.MM` naming (e.g., 24.8 = August 2024 release). LTS releases receive ~2 years of maintenance. Non-LTS releases are supported for approximately 6 months after the next release.

**Recommendation:** Use the latest LTS version for production (25.12 LTS). Use the latest stable release for development/testing to access the newest features.