Clickhouse Architect

ClickHouse Architect

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

Prescriptive schema design, compression selection, and performance optimization for ClickHouse (v24.4+). Covers both ClickHouse Cloud (SharedMergeTree) and self-hosted (ReplicatedMergeTree) deployments.

Self-Evolving Skill: This skill improves through use. If instructions are wrong, parameters drifted, or a workaround was needed — fix this file immediately, don't defer. Only update for real, reproducible issues.

When to Use This Skill

Use this skill when:

• Designing ClickHouse table schemas with ORDER BY key selection
• Selecting compression codecs for column types
• Configuring partition keys for data lifecycle management
• Adding performance accelerators (projections, indexes, dictionaries)
• Auditing and optimizing existing ClickHouse schemas

Core Methodology

Schema Design Workflow

Follow this sequence when designing or reviewing ClickHouse schemas:

1. Define ORDER BY key (3-5 columns, lowest cardinality first) 2. Select compression codecs per column type 3. Configure PARTITION BY for data lifecycle management 4. Add performance accelerators (projections, indexes) 5. Validate with audit queries (see scripts/) 6. Document with COMMENT statements — ClickHouse table and column COMMENTs are the single source of truth (SSoT) for what each column means, how it was computed, and what constraints apply. No external doc, skill, or wiki supersedes the COMMENT. See `references/schema-documentation.md`

ORDER BY Key Selection

The ORDER BY clause is the most critical decision in ClickHouse schema design.

Rules:

• Limit to 3-5 columns maximum (each additional column has diminishing returns)
• Place lowest cardinality columns first (e.g., tenant_id before timestamp)
• Include all columns used in WHERE clauses for range queries
• PRIMARY KEY must be a prefix of ORDER BY (or omit to use full ORDER BY)

Example:

-- Correct: Low cardinality first, 4 columns
CREATE TABLE trades (
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    timestamp DateTime64(3),
    trade_id UInt64,
    price Float64,
    quantity Float64
) ENGINE = MergeTree()
ORDER BY (exchange, symbol, timestamp, trade_id);

-- Wrong: High cardinality first (10x slower queries)
ORDER BY (trade_id, timestamp, symbol, exchange);

Compression Codec Quick Reference

When to use LZ4 over ZSTD: LZ4 provides 1.76x faster decompression. Use LZ4 for read-heavy workloads with monotonic sequences (timestamps, counters). Use ZSTD (default) when compression ratio matters or data patterns are unknown.

Note on codec combinations:

Delta/DoubleDelta + Gorilla combinations are blocked by default (allow_suspicious_codecs) because Gorilla already performs implicit delta compression internally—combining them is redundant, not dangerous. A historical corruption bug (PR #45615, Jan 2023) was fixed, but the blocking remains as a best practice guardrail.

Use each codec family independently for its intended data type:

-- Correct usage
price Float64 CODEC(Gorilla, ZSTD)              -- Floats: use Gorilla
timestamp DateTime64 CODEC(DoubleDelta, ZSTD)   -- Timestamps: use DoubleDelta
timestamp DateTime64 CODEC(DoubleDelta, LZ4)    -- Read-heavy: use LZ4

PARTITION BY Guidelines

PARTITION BY is for data lifecycle management, NOT query optimization.

Rules:

• Partition by time units (month, week) for TTL and data management
• Keep partition count under 1000 total across all tables
• Each partition should contain 1-300 parts maximum
• Never partition by high-cardinality columns

Example:

-- Correct: Monthly partitions for TTL management
PARTITION BY toYYYYMM(timestamp)

-- Wrong: Daily partitions (too many parts)
PARTITION BY toYYYYMMDD(timestamp)

-- Wrong: High-cardinality partition key
PARTITION BY user_id

Anti-Patterns Checklist (v24.4+)

DELETE Strategy Guide (v13.49.0+ Best Practices)

Choose the right DELETE strategy based on scope. Ranked fastest to slowest:

Anti-pattern: Lightweight `DELETE FROM` before INSERT

DELETE FROM sets _row_exists=0 masks instead of physically removing rows. These ghost rows:

• Persist until ClickHouse background merge (unpredictable timing)
• Show up in queries without FINAL as phantom data
• Cause false anomalies in monitoring/integrity checks
• Were the root cause of months of phantom Stathera gaps in production (opendeviationbar #269)

Use `DELETE FROM` only for: ad-hoc data correction where ghost rows don't matter (analytics cleanup, dev/test). Never use in write pipelines where INSERT follows DELETE.

All DELETE mutations should use: SETTINGS mutations_sync = 1 to block until completion (prevents INSERT-DELETE race conditions).

Partition-aware DELETE tip: If your partition key includes the columns you're filtering on (e.g., PARTITION BY (symbol, threshold, toYYYYMM(timestamp))), use DELETE IN PARTITION to scope the scan to a single partition instead of scanning all parts.

Table Engine Selection

Cloud (SharedMergeTree):

CREATE TABLE trades (...)
ENGINE = SharedMergeTree('/clickhouse/tables/{shard}/trades', '{replica}')
ORDER BY (exchange, symbol, timestamp);

Self-hosted (ReplicatedMergeTree):

CREATE TABLE trades (...)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/trades', '{replica}')
ORDER BY (exchange, symbol, timestamp);

Skill Delegation Guide

<!-- ADR: 2025-12-10-clickhouse-skill-delegation -->

This skill is the hub for ClickHouse-related tasks. When the user's needs extend beyond schema design, invoke the related skills below.

Delegation Decision Matrix

Typical Workflow Sequence

1. Schema Design (THIS SKILL) → Design ORDER BY, compression, partitioning 2. User Setup → clickhouse-cloud-management (if cloud credentials needed) 3. Client Config → clickhouse-pydantic-config (generate DBeaver JSON) 4. Validation → schema-e2e-validation (CI/CD schema contracts)

Example: Full Stack Request

User: "I need to design a trades table for ClickHouse Cloud and set up DBeaver to query it."

Expected behavior:

1. Use THIS skill for schema design 2. Invoke clickhouse-cloud-management for creating database user 3. Invoke clickhouse-pydantic-config for DBeaver configuration

Performance Accelerators

Projections

Create alternative sort orders that ClickHouse automatically selects:

ALTER TABLE trades ADD PROJECTION trades_by_symbol (
    SELECT * ORDER BY symbol, timestamp
);
ALTER TABLE trades MATERIALIZE PROJECTION trades_by_symbol;

Materialized Views

Pre-compute aggregations for dashboard queries:

CREATE MATERIALIZED VIEW trades_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (exchange, symbol, hour)
AS SELECT
    exchange,
    symbol,
    toStartOfHour(timestamp) AS hour,
    sum(quantity) AS total_volume,
    count() AS trade_count
FROM trades
GROUP BY exchange, symbol, hour;

Dictionaries

Replace JOINs with O(1) dictionary lookups for large-scale star schemas:

When to use dictionaries (v24.4+):

• Fact tables with 100M+ rows joining dimension tables
• Dimension tables 1k-500k rows with monotonic keys
• LEFT ANY JOIN semantics required

When JOINs are sufficient (v24.4+):

• Dimension tables <500 rows (JOIN overhead negligible)
• v24.4+ predicate pushdown provides 8-180x improvements
• Complex JOIN types (FULL, RIGHT, multi-condition)

Benchmark context: 6.6x speedup measured on Star Schema Benchmark (1.4B rows).

CREATE DICTIONARY symbol_info (
    symbol String,
    name String,
    sector String
)
PRIMARY KEY symbol
SOURCE(CLICKHOUSE(TABLE 'symbols'))
LAYOUT(FLAT())  -- Best for <500k entries with monotonic keys
LIFETIME(3600);

-- Use in queries (O(1) lookup)
SELECT
    symbol,
    dictGet('symbol_info', 'name', symbol) AS symbol_name
FROM trades;

Scripts

Execute comprehensive schema audit:

clickhouse-client --multiquery < scripts/schema-audit.sql

The audit script checks:

• Part count per partition (threshold: 300)
• Compression ratios by column
• Query performance patterns
• Replication lag (if applicable)
• Memory usage patterns

Additional Resources

Reference Files

External Documentation

• ClickHouse Best Practices
• Altinity Knowledge Base
• ClickHouse Blog

Python Driver Policy

<!-- ADR: 2025-12-10-clickhouse-python-driver-policy -->

Use `clickhouse-connect` (official) for all Python integrations.

# ✅ RECOMMENDED: clickhouse-connect (official, HTTP)
import clickhouse_connect

client = clickhouse_connect.get_client(
    host='localhost',
    port=8123,  # HTTP port
    username='default',
    password=''
)
result = client.query("SELECT * FROM trades LIMIT 1000")
df = client.query_df("SELECT * FROM trades")  # Pandas integration

Why NOT clickhouse-driver

Do NOT use `clickhouse-driver` despite its ~26% speed advantage for large exports. The maintenance risk outweighs performance gains:

• Single maintainer (mymarilyn) with no succession plan
• Issues accumulating without response
• Risk of abandonment breaks production code

Exception: Only consider clickhouse-driver if you have extreme performance requirements (exporting millions of rows) AND accept the maintenance risk.

ClickHouse COMMENT = Single Source of Truth

Every ClickHouse table and column MUST have a COMMENT that fully documents its meaning, computation method, and constraints. The COMMENT is the SSoT — no external document, skill, or wiki supersedes it.

Why

• ClickHouse COMMENTs have no length limit, support newlines, URLs, and unicode
• Zero performance impact on queries (pure metadata, never in data path)
• Visible via DESCRIBE table, SHOW CREATE TABLE, system.columns
• Survives schema migrations (preserved through ALTER operations)

What to Include in COMMENTs

• Column purpose in plain English
• Computation formula (if derived/computed)
• Unit (seconds, milliseconds, bps, ratio)
• Valid range or enum values
• Anti-patterns (what NOT to do with this column)
• GitHub issue link for provenance
• Source script that populates the column

Example

ALTER TABLE t COMMENT COLUMN session_label
'STRICT session label. 8 values: sydney_only, tokyo_only, ...
Only set when ENTIRE bar (open→close) falls within one session.
cross_session = bar spans boundary. Use WHERE is_pure_session=1.
GitHub: https://github.com/org/repo/issues/54
Source: scripts/populate-sessions/populate_v3.py';

Anti-Pattern

NEVER create a ClickHouse column without a COMMENT. A column without documentation is a column that will be misused.

Related Skills

---

Troubleshooting

Post-Execution Reflection

After this skill completes, check before closing:

1. Did the command succeed? — If not, fix the instruction or error table that caused the failure. 2. Did parameters or output change? — If the underlying tool's interface drifted, update Usage examples and Parameters table to match. 3. Was a workaround needed? — If you had to improvise (different flags, extra steps), update this SKILL.md so the next invocation doesn't need the same workaround.

Only update if the issue is real and reproducible — not speculative.

Skill: ClickHouse Architect

Anti-Patterns and Fixes

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

The "13 Deadly Sins" of ClickHouse with v24.4+ status and modern fixes.

Overview

Some traditional anti-patterns have been significantly improved in v24.4+:

Still Critical Anti-Patterns

1. Too Many Parts

Problem: More than 300 active parts per partition causes degraded performance.

Detection:

SELECT database, table, partition, count() AS parts
FROM system.parts
WHERE active = 1
GROUP BY database, table, partition
HAVING parts > 300;

Fix:

• Reduce PARTITION BY granularity (monthly instead of daily)
• Increase batch sizes for inserts
• Run OPTIMIZE TABLE ... FINAL during maintenance windows

2. Small Batch Inserts

Problem: Inserting fewer than 1,000 rows per batch creates too many parts.

Symptoms:

• Growing part count
• Slow inserts
• High CPU from merges

Fix:

# Buffer rows before inserting
BATCH_SIZE = 50000
buffer = []

for row in source:
    buffer.append(row)
    if len(buffer) >= BATCH_SIZE:
        client.insert('table', buffer)
        buffer = []

Target: 10,000-100,000 rows per batch.

3. High-Cardinality First ORDER BY

Problem: Placing high-cardinality columns first in ORDER BY makes queries 10x slower.

Bad Example:

-- Wrong: trade_id is unique (highest cardinality)
ORDER BY (trade_id, timestamp, symbol, exchange)

Fix:

-- Correct: lowest cardinality first
ORDER BY (exchange, symbol, timestamp, trade_id)

4. No Memory Limits

Problem: 78% of deployments don't configure memory limits, risking OOM kills.

Fix:

-- Set per-query limit
SET max_memory_usage = 10000000000;  -- 10GB

-- In users.xml or config
<max_memory_usage>10000000000</max_memory_usage>
<max_memory_usage_for_all_queries>50000000000</max_memory_usage_for_all_queries>

5. Denormalization Overuse

Problem: Pre-joining data into wide tables increases storage 10-100x and slows queries.

Bad Pattern:

-- Wide denormalized table
CREATE TABLE orders_denormalized (
    order_id UInt64,
    -- Order fields
    customer_name String,
    customer_email String,
    customer_address String,
    -- Product fields (repeated per order item!)
    product_name String,
    product_category String,
    ...
);

Fix: Use dictionaries for dimension lookups:

-- Fact table (normalized)
CREATE TABLE orders (
    order_id UInt64,
    customer_id UInt64,
    product_id UInt64,
    quantity UInt32,
    price Float64
);

-- Dictionary for customer lookup
CREATE DICTIONARY customers_dict (...)
SOURCE(CLICKHOUSE(TABLE 'customers'))
LAYOUT(FLAT());

-- Query with dictionary (6.6x faster than JOIN)
SELECT
    order_id,
    dictGet('customers_dict', 'name', customer_id) AS customer_name
FROM orders;

6. Over-Partitioning (Nuanced)

Problem: Too many partitions degrades performance when parts haven't merged.

The real metric is PARTS COUNT, not partition count. A table with 100K partitions but 1 merged part each is fine. A table with 10 partitions but 50K unmerged parts is broken. Always check system.parts WHERE active = 1 — that is the number that determines mutation speed, query latency, and merge pressure.

Partition Key Design for Time-Series with Compound Keys

For tables with ORDER BY like (symbol, threshold, first_agg_trade_id) where the last column is time-correlated (monotonic trade IDs, timestamps), time should NOT be in the partition key. The ORDER BY index already provides efficient time-range pruning within partitions — adding time to the partition key is redundant and harmful.

Best partition key: The dimensions you use for data lifecycle (DELETE/DROP scope) — typically the non-time columns that define your mutation boundaries.

-- CORRECT: Partition by dimensions used for DELETE/DROP scope.
-- ORDER BY index handles time-range pruning automatically.
PARTITION BY (symbol, threshold_decimal_bps)
ORDER BY (symbol, threshold_decimal_bps, ouroboros_mode, first_agg_trade_id)

-- WRONG: Time in partition key when ORDER BY already has a time-correlated column.
-- Creates N × days partitions, each with unmerged parts from streaming inserts.
PARTITION BY (symbol, threshold_decimal_bps, toYYYYMMDD(open_time_ms / 1000))

Why this matters — validated benchmark:

• Dimension-only partitions: 1,037 days/min (heavy symbols), 3,800 days/min (light symbols)
• Daily time partitions: 200 days/min — 5x slower on heavy, 18x slower on light
• Root cause: daily partitions create O(symbols × thresholds × days) partitions, each accumulating unmerged parts from streaming inserts. Mutations must scan all active parts.

When Time in Partition Key IS Correct

Time belongs in the partition key only when:

1. You need DROP PARTITION for bulk time-range cleanup (e.g., TTL replacement, purging old months) 2. The table is append-only with no DELETE operations targeting specific rows 3. ORDER BY does NOT already contain a time-correlated column

-- Append-only logs where you DROP entire months for retention
PARTITION BY toYYYYMM(timestamp)
ORDER BY (service, timestamp)

Critical: Post-Migration OPTIMIZE

After any partition key change (requires table recreation + data copy), the new table has 1 part per INSERT batch — potentially 50K+ unmerged parts. You MUST run `OPTIMIZE TABLE ... FINAL` and wait for completion before starting any services that run mutations. Mutations scan all active parts — 50K unmerged parts means 300s+ timeouts on every DELETE.

-- After migration: force merge BEFORE restarting services
OPTIMIZE TABLE db.table FINAL;  -- may take 10-30 min for large tables

-- Verify parts are merged
SELECT count() FROM system.parts WHERE database = 'db' AND table = 'table' AND active;
-- Target: ~1 part per partition (or low single digits)

Detection

-- Check active parts count per table (THE metric that matters)
SELECT
    database,
    table,
    count() AS active_parts,
    countDistinct(partition) AS partitions,
    round(count() / countDistinct(partition), 1) AS avg_parts_per_partition
FROM system.parts
WHERE active = 1
GROUP BY database, table
HAVING active_parts > 1000
ORDER BY active_parts DESC;

-- Drill down: which partitions have the most unmerged parts?
SELECT
    database,
    table,
    partition,
    count() AS parts_in_partition,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active = 1 AND database = 'your_db' AND table = 'your_table'
GROUP BY database, table, partition
HAVING parts_in_partition > 10
ORDER BY parts_in_partition DESC
LIMIT 20;

7. Missing Codecs

Problem: Not using specialized codecs wastes 5-10x storage.

Fix: Apply appropriate codecs:

timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD)
price Float64 CODEC(Gorilla, ZSTD)
count UInt64 CODEC(T64, ZSTD)

Improved in v24.4+ (Use with Caution)

8. Large JOINs (180x Improved)

v24.4+ Improvement: Predicate pushdown makes JOINs 180x faster in many cases.

Still Avoid For: Ultra-low-latency (<10ms) requirements.

CTE Range Joins: The v24.4+ improvement does NOT apply to range joins on CTEs (e.g., FROM cte_a JOIN cte_b ON b.rn BETWEEN a.rn + 1 AND a.rn + 101). ClickHouse cannot index into CTEs — these remain O(N×M) nested loop scans. For forward-looking array collection patterns, use window functions (groupArray() OVER (ROWS BETWEEN ...)) instead of self-joins. See rangebar-patterns AP-14 for benchmarks showing 11x speedup.

Signal Timing: When using lagInFrame() for pattern detection in CTE-based signal pipelines, verify that lag offsets match the intended bar. Off-by-one errors cause SQL signals to fire 1 bar late relative to event-driven backtesting engines. See rangebar-patterns AP-15 (Signal Timing Off-by-One) for the lagInFrame offset correction rules.

Better Alternative: Dictionaries for dimension lookups.

-- Now acceptable for most use cases
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.timestamp > now() - INTERVAL 1 DAY;

-- Still better: Dictionary lookup
SELECT o.*, dictGet('customers', 'name', customer_id)
FROM orders o
WHERE timestamp > now() - INTERVAL 1 DAY;

9. Mutations (1700x Improved)

v24.4+ Improvement: Lightweight updates are 1700x faster.

Traditional Mutations: Still slow, avoid for frequent operations.

Lightweight Updates:

-- Fast in v24.4+ (lightweight)
ALTER TABLE trades UPDATE status = 'processed' WHERE trade_id = 123;

-- Still slow (traditional mutation) — use a Python-computed literal instead of now()
-- e.g. cutoff = datetime.utcnow() - timedelta(days=90), then pass as a parameter
ALTER TABLE trades DELETE WHERE timestamp < %(cutoff)s
SETTINGS mutations_sync = 1;

WARNING: Lightweight DELETE FROM sets a _row_exists=0 mask that persists after

completion and re-applies to new parts during background merges. If you INSERT after

a lightweight DELETE, the mask will delete your new rows. Always use ALTER TABLE ... DELETE

(traditional mutation) when INSERTs will follow. Use SETTINGS mutations_sync = 1 to

ensure the DELETE completes before proceeding.

Better Pattern: Use TTL for deletions:

TTL timestamp + INTERVAL 90 DAY DELETE

Detection Query

Run to identify anti-patterns (parts count is the primary health metric):

-- Anti-pattern detection: parts count, avg parts per partition, total size
SELECT
    p.database,
    p.table,
    count() AS active_parts,
    countDistinct(p.partition) AS partitions,
    round(count() / countDistinct(p.partition), 1) AS avg_parts_per_partition,
    formatReadableSize(sum(p.bytes_on_disk)) AS total_size,
    -- Red flags
    multiIf(
        count() > 10000, 'CRITICAL: merge backlog or over-partitioned',
        count() / countDistinct(p.partition) > 50, 'WARNING: high parts/partition ratio',
        'OK'
    ) AS status
FROM system.parts p
WHERE p.active = 1
  AND p.database NOT IN ('system', 'INFORMATION_SCHEMA')
GROUP BY p.database, p.table
ORDER BY active_parts DESC;

Related References

• Schema Design Workflow
• Audit and Diagnostics
• Idiomatic Architecture

Skill: ClickHouse Architect

Audit and Diagnostics

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

Comprehensive guide to ClickHouse system tables and diagnostic queries.

System Tables Overview

Schema Health Queries

Part Count Analysis

Critical threshold: >300 parts per partition indicates problems.

SELECT
    database,
    table,
    partition,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size,
    CASE
        WHEN count() > 300 THEN 'CRITICAL'
        WHEN count() > 100 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM system.parts
WHERE active = 1
GROUP BY database, table, partition
HAVING parts > 10
ORDER BY parts DESC;

Compression Effectiveness

SELECT
    database,
    table,
    column,
    type,
    compression_codec,
    formatReadableSize(data_compressed_bytes) AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS ratio
FROM system.columns
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA')
    AND data_compressed_bytes > 0
ORDER BY data_uncompressed_bytes DESC
LIMIT 50;

Table Overview

SELECT
    database,
    name AS table,
    engine,
    partition_key,
    sorting_key,
    formatReadableSize(total_bytes) AS total_size,
    total_rows
FROM system.tables
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA')
ORDER BY total_bytes DESC;

Query Performance Queries

Slow Queries (Last 24 Hours)

SELECT
    type,
    query_kind,
    round(query_duration_ms / 1000, 2) AS duration_sec,
    formatReadableSize(memory_usage) AS memory,
    formatReadableSize(read_bytes) AS read_bytes,
    read_rows,
    substring(query, 1, 100) AS query_preview
FROM system.query_log
WHERE event_time > now() - INTERVAL 24 HOUR
    AND type = 'QueryFinish'
    AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 20;

Active Queries

SELECT
    query_id,
    user,
    round(elapsed, 2) AS elapsed_sec,
    formatReadableSize(memory_usage) AS memory,
    formatReadableSize(read_bytes) AS read_bytes,
    substring(query, 1, 100) AS query_preview
FROM system.processes
ORDER BY elapsed DESC;

Query Patterns Analysis

SELECT
    normalized_query_hash,
    count() AS query_count,
    avg(query_duration_ms) AS avg_ms,
    max(query_duration_ms) AS max_ms,
    sum(read_rows) AS total_rows_read,
    any(substring(query, 1, 100)) AS sample_query
FROM system.query_log
WHERE event_time > now() - INTERVAL 7 DAY
    AND type = 'QueryFinish'
GROUP BY normalized_query_hash
ORDER BY query_count DESC
LIMIT 20;

Index Effectiveness

Use EXPLAIN to analyze index usage:

EXPLAIN indexes = 1
SELECT * FROM your_table
WHERE your_conditions;

Key metrics:

Replication Diagnostics

Replication Status

SELECT
    database,
    table,
    is_readonly,
    is_session_expired,
    future_parts,
    parts_to_check,
    queue_size,
    inserts_in_queue,
    merges_in_queue,
    log_pointer,
    CASE
        WHEN is_readonly = 1 THEN 'CRITICAL: READONLY'
        WHEN queue_size > 100 THEN 'WARNING: LARGE QUEUE'
        ELSE 'OK'
    END AS status
FROM system.replicas
ORDER BY queue_size DESC;

Cross-Replica Check

SELECT
    hostName() AS host,
    database,
    table,
    total_rows,
    formatReadableSize(total_bytes) AS size
FROM clusterAllReplicas('your_cluster', system.tables)
WHERE database NOT IN ('system')
ORDER BY database, table, host;

Resource Monitoring

Disk Usage

SELECT
    name,
    path,
    formatReadableSize(free_space) AS free_space,
    formatReadableSize(total_space) AS total_space,
    round(100 * (1 - free_space / total_space), 2) AS used_percent
FROM system.disks;

Memory Metrics

SELECT
    metric,
    formatReadableSize(value) AS value
FROM system.metrics
WHERE metric LIKE '%Memory%'
ORDER BY value DESC;

Ongoing Merges

SELECT
    database,
    table,
    elapsed,
    progress,
    num_parts,
    formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
ORDER BY elapsed DESC;

ProfileEvents for Deep Analysis

Key ProfileEvents to monitor:

SELECT
    event,
    value
FROM system.events
WHERE event IN (
    'OSIOWaitMicroseconds',
    'OSCPUWaitMicroseconds',
    'SelectedParts',
    'SelectedRanges',
    'SelectedMarks'
)
ORDER BY value DESC;

Production Health Checks

Production-validated diagnostic queries for operational monitoring.

Parts Health Check

More important than per-partition count: total active parts per table. Target: below 10K.

-- Check active parts per table (target: <10K)
SELECT database, table, count() AS active_parts,
       countDistinct(partition) AS partitions
FROM system.parts WHERE active
GROUP BY database, table
HAVING active_parts > 3000
ORDER BY active_parts DESC;

Mutation Queue Check

Stuck mutations cause timeout cascades. Should be 0 in steady state.

-- Pending mutations (should be 0 in steady state)
SELECT count() AS pending,
       min(create_time) AS oldest
FROM system.mutations
WHERE NOT is_done AND database NOT IN ('system');

Background Merge Health

-- Are merges keeping up?
SELECT count() AS active_merges FROM system.merges;
-- If consistently >0 during writes, increase background_pool_size

Insert Throughput Check

Useful for comparing Arrow vs other insert formats and spotting latency regressions.

-- Last 20 inserts: check format and latency
SELECT query_kind, formatReadableSize(written_bytes),
       query_duration_ms, result_rows
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Insert'
ORDER BY event_time DESC LIMIT 20;

Post-Migration Checklist

After any PARTITION BY change, follow this sequence strictly:

1. OPTIMIZE TABLE ... FINAL (wait for completion) 2. Verify parts count < 10K using the parts health check above 3. Only then restart services that run mutations

Skipping step 2 risks mutation timeouts if the merge backlog has not cleared.

Automated Audit Script

Run the comprehensive audit:

clickhouse-client --multiquery < scripts/schema-audit.sql

Related References

• Schema Design Workflow
• Anti-Patterns and Fixes
• Idiomatic Architecture

Skill: ClickHouse Architect

Cache Schema Evolution

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

Patterns for managing schema changes in ClickHouse caches without manual invalidation. Covers version columns, content-based validation, and migration strategies.

Why Schema Evolution Matters

Caching computed results accelerates queries but introduces a hidden cost: stale data after schema changes.

Real-world case: rangebar-py v7.0 added 10 microstructure columns. Cached data from v6.x returned NULL for ofi, kyle_lambda_proxy, etc., breaking ML pipelines silently.

Pattern 1: Schema Version Column

Cosmos DB-style: Store a version identifier with each cached record, filter at read time.

Implementation

CREATE TABLE cache.range_bars (
    -- Primary data
    symbol LowCardinality(String),
    timestamp_ms Int64,
    open Float64,
    high Float64,
    low Float64,
    close Float64,
    volume Float64,

    -- Version column for evolution
    schema_version UInt16 DEFAULT 1,

    -- Or use application version string
    app_version String DEFAULT '',

    -- Computed timestamp for ReplacingMergeTree
    computed_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = ReplacingMergeTree(computed_at)
ORDER BY (symbol, timestamp_ms);

Read with Version Filter

import clickhouse_connect

CURRENT_SCHEMA_VERSION = 3  # Increment on breaking changes

def get_cached_bars(client, symbol: str, start_ms: int, end_ms: int):
    """Fetch bars, filtering stale versions."""
    return client.query_df(f"""
        SELECT * FROM cache.range_bars FINAL
        WHERE symbol = %(symbol)s
          AND timestamp_ms BETWEEN %(start)s AND %(end)s
          AND schema_version >= %(version)s
        ORDER BY timestamp_ms
    """, parameters={
        'symbol': symbol,
        'start': start_ms,
        'end': end_ms,
        'version': CURRENT_SCHEMA_VERSION,
    })

When to Increment Version

Pattern 2: ReplacingMergeTree + Version

Automatic invalidation: ClickHouse keeps the newest version during merge.

Implementation

CREATE TABLE cache.range_bars (
    symbol LowCardinality(String),
    timestamp_ms Int64,
    open Float64,
    high Float64,
    low Float64,
    close Float64,
    volume Float64,

    -- Microstructure features (added v7.0)
    ofi Float64 DEFAULT 0,
    vwap_close_deviation Float64 DEFAULT 0,
    kyle_lambda_proxy Float64 DEFAULT 0,

    -- Version for ReplacingMergeTree dedup
    record_version UInt64 DEFAULT 1,
    computed_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = ReplacingMergeTree(record_version)
ORDER BY (symbol, timestamp_ms);

Write with Incremented Version

def store_bars(client, df, schema_version: int = 1):
    """Store bars with explicit version for replacement."""
    df = df.copy()
    df['record_version'] = schema_version
    df['computed_at'] = pd.Timestamp.now()
    client.insert_df('cache.range_bars', df)

Query with FINAL

-- Force deduplication at query time
SELECT * FROM cache.range_bars FINAL
WHERE symbol = 'BTCUSDT'
  AND timestamp_ms BETWEEN 1704067200000 AND 1706745600000
ORDER BY timestamp_ms;

Caveat: FINAL is slow on large tables (100x overhead). Use partition-aware optimization:

SET do_not_merge_across_partitions_select_final = 1;
SELECT * FROM cache.range_bars FINAL WHERE ...;

Pattern 3: Content-Based Validation

Schema-agnostic: Validate cached data meets current requirements at read time.

Implementation

from typing import NamedTuple

# Single source of truth for required columns
REQUIRED_COLUMNS = frozenset({
    'timestamp_ms', 'open', 'high', 'low', 'close', 'volume',
})

MICROSTRUCTURE_COLUMNS = frozenset({
    'ofi', 'vwap_close_deviation', 'kyle_lambda_proxy',
    'trade_intensity', 'volume_per_trade', 'aggression_ratio',
})

class CacheValidation(NamedTuple):
    valid: bool
    missing_columns: set[str]
    reason: str

def validate_cached_df(df, include_microstructure: bool = False) -> CacheValidation:
    """Validate cached DataFrame has required columns."""
    required = REQUIRED_COLUMNS.copy()
    if include_microstructure:
        required |= MICROSTRUCTURE_COLUMNS

    present = set(df.columns)
    missing = required - present

    if missing:
        return CacheValidation(
            valid=False,
            missing_columns=missing,
            reason=f"Missing columns: {missing}",
        )

    # Check for NULL values in required columns
    null_cols = [c for c in required if df[c].isnull().any()]
    if null_cols:
        return CacheValidation(
            valid=False,
            missing_columns=set(null_cols),
            reason=f"NULL values in: {null_cols}",
        )

    return CacheValidation(valid=True, missing_columns=set(), reason="")

Usage with Fallback

def get_range_bars(symbol: str, start: str, end: str, include_microstructure: bool = False):
    """Get range bars with cache validation."""
    # Try cache first
    cached_df = cache.get_cached_bars(symbol, start, end)

    if cached_df is not None:
        validation = validate_cached_df(cached_df, include_microstructure)
        if validation.valid:
            return cached_df
        else:
            logger.warning(f"Cache invalid: {validation.reason}, recomputing...")

    # Recompute and cache
    df = compute_range_bars(symbol, start, end, include_microstructure)
    cache.store_bars(df)
    return df

Pattern 4: ALTER TABLE Migrations

Schema evolution without data loss: Add columns to existing tables.

Adding New Columns

-- Add microstructure columns (v7.0 migration)
ALTER TABLE cache.range_bars
    ADD COLUMN ofi Float64 DEFAULT 0,
    ADD COLUMN vwap_close_deviation Float64 DEFAULT 0,
    ADD COLUMN kyle_lambda_proxy Float64 DEFAULT 0;

Renaming Columns

-- Rename column (v7.2 migration)
ALTER TABLE cache.range_bars
    RENAME COLUMN aggregation_efficiency TO aggregation_density;

Changing Default Values

-- Modify default (doesn't affect existing rows)
ALTER TABLE cache.range_bars
    MODIFY COLUMN ofi Float64 DEFAULT nan;

Migration Script Pattern

-- Migration: v7.0 to v7.2
-- File: migrations/007_002_rename_efficiency.sql

-- Check if column exists (idempotent)
SELECT count() FROM system.columns
WHERE database = 'cache'
  AND table = 'range_bars'
  AND name = 'aggregation_efficiency';

-- Run only if exists:
ALTER TABLE cache.range_bars
    RENAME COLUMN aggregation_efficiency TO aggregation_density;

Python Migration Helper

def ensure_schema_version(client, target_version: int):
    """Apply migrations up to target version."""
    current = get_schema_version(client)

    migrations = {
        2: "ALTER TABLE cache.range_bars ADD COLUMN ofi Float64 DEFAULT 0",
        3: "ALTER TABLE cache.range_bars RENAME COLUMN efficiency TO density",
    }

    for version in range(current + 1, target_version + 1):
        if version in migrations:
            client.command(migrations[version])
            logger.info(f"Applied migration {version}")

    set_schema_version(client, target_version)

Decision Matrix: Which Pattern to Use

Anti-Patterns

1. Silent NULL Propagation

Problem: New columns default to NULL, ML models train on incomplete data.

-- WRONG: No default, returns NULL
ALTER TABLE cache.range_bars ADD COLUMN kyle_lambda Float64;

Fix: Always specify meaningful defaults or validate at read time.

-- CORRECT: Explicit default
ALTER TABLE cache.range_bars ADD COLUMN kyle_lambda Float64 DEFAULT 0;

2. Unbounded Cache Growth

Problem: Multiple schema versions accumulate without cleanup.

# WRONG: No TTL or version cleanup
def store_bars(df):
    df['version'] = CURRENT_VERSION
    client.insert_df('cache.range_bars', df)  # Old versions accumulate forever

Fix: Use ReplacingMergeTree or periodic cleanup.

-- Cleanup old versions (run periodically)
-- Pass cutoff_ts as a Python-computed UTC timestamp literal, not now()
ALTER TABLE cache.range_bars DELETE
WHERE schema_version < %(min_version)s
  AND computed_at < %(cutoff_ts)s
SETTINGS mutations_sync = 1;

3. Version-Only Without Content Check

Problem: Version matches but data is corrupted or incomplete.

# WRONG: Trust version blindly
if cached_df['schema_version'].iloc[0] >= CURRENT_VERSION:
    return cached_df  # May have NULL microstructure columns

Fix: Combine version filter with content validation.

# CORRECT: Version + content validation
if cached_df['schema_version'].iloc[0] >= CURRENT_VERSION:
    validation = validate_cached_df(cached_df, include_microstructure=True)
    if validation.valid:
        return cached_df

4. Breaking Changes Without Migration Path

Problem: Column rename breaks all existing queries.

-- WRONG: Rename without transition period
ALTER TABLE cache.range_bars
    RENAME COLUMN threshold_bps TO threshold_decimal_bps;
-- All queries using threshold_bps immediately fail

Fix: Add new column, migrate, then remove old.

-- CORRECT: Additive migration
ALTER TABLE cache.range_bars ADD COLUMN threshold_decimal_bps UInt32;
ALTER TABLE cache.range_bars UPDATE threshold_decimal_bps = threshold_bps WHERE 1;
-- Application migrates to new column
-- Later: ALTER TABLE cache.range_bars DROP COLUMN threshold_bps;

Complete Example: rangebar-py Cache

-- Production schema with all evolution patterns
CREATE TABLE IF NOT EXISTS rangebar_cache.range_bars (
    -- Primary key columns
    symbol LowCardinality(String),
    threshold_decimal_bps UInt32,
    timestamp_ms Int64,

    -- OHLCV (always present)
    open Float64,
    high Float64,
    low Float64,
    close Float64,
    volume Float64,

    -- Microstructure (v7.0+, DEFAULT for backward compat)
    ofi Float64 DEFAULT 0,
    vwap_close_deviation Float64 DEFAULT 0,
    kyle_lambda_proxy Float64 DEFAULT 0,
    trade_intensity Float64 DEFAULT 0,

    -- Evolution tracking
    cache_key String,                        -- Content hash
    rangebar_version String DEFAULT '',      -- Application version
    computed_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = ReplacingMergeTree(computed_at)
PARTITION BY (symbol, threshold_decimal_bps, toYYYYMM(toDateTime(timestamp_ms / 1000)))
ORDER BY (symbol, threshold_decimal_bps, timestamp_ms);

# Python client with full validation
from rangebar.constants import MICROSTRUCTURE_COLUMNS
from importlib.metadata import version as pkg_version

class RangeBarCache:
    # SSoT-OK: Version read from package metadata at runtime
    CURRENT_VERSION = pkg_version("rangebar")

    def get_cached_bars(self, symbol: str, start_ms: int, end_ms: int,
                        include_microstructure: bool = False) -> pd.DataFrame | None:
        """Get cached bars with schema validation."""
        df = self._query_bars(symbol, start_ms, end_ms)

        if df is None or df.empty:
            return None

        # Content-based validation
        if include_microstructure:
            for col in MICROSTRUCTURE_COLUMNS:
                if col not in df.columns:
                    logger.warning(f"Cache missing {col}, invalidating")
                    return None
                if df[col].isnull().all() or (df[col] == 0).all():
                    logger.warning(f"Cache has empty {col}, invalidating")
                    return None

        return df

    def store_bars(self, df: pd.DataFrame, symbol: str,
                   threshold_decimal_bps: int) -> int:
        """Store bars with version metadata."""
        df = df.copy()
        df['rangebar_version'] = self.CURRENT_VERSION
        df['computed_at'] = pd.Timestamp.now()

        # Add optional microstructure columns if present
        columns = ['timestamp_ms', 'open', 'high', 'low', 'close', 'volume']
        for col in MICROSTRUCTURE_COLUMNS:
            if col in df.columns:
                columns.append(col)

        return self.client.insert_df('rangebar_cache.range_bars', df[columns])

Validation Query

Check schema evolution health:

-- Check version distribution in cache
SELECT
    rangebar_version,
    count() AS row_count,
    min(computed_at) AS oldest,
    max(computed_at) AS newest
FROM rangebar_cache.range_bars
GROUP BY rangebar_version
ORDER BY newest DESC;

-- Check for NULL microstructure columns (indicates stale data)
SELECT
    symbol,
    threshold_decimal_bps,
    count() AS total_rows,
    countIf(ofi = 0 AND vwap_close_deviation = 0) AS missing_microstructure,
    round(missing_microstructure / total_rows * 100, 2) AS pct_missing
FROM rangebar_cache.range_bars
GROUP BY symbol, threshold_decimal_bps
HAVING pct_missing > 50
ORDER BY pct_missing DESC;

Related References

• Schema Design Workflow - Initial schema creation
• Anti-Patterns and Fixes - Common ClickHouse mistakes
• Idiomatic Architecture - ReplacingMergeTree patterns

Skill: ClickHouse Architect

Compression Codec Selection

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

Decision guide and benchmarks for selecting optimal ClickHouse compression codecs.

Quick Selection Guide

Note on Codec Combinations

Delta/DoubleDelta + Gorilla combinations are blocked by default via allow_suspicious_codecs.

Why blocked: Gorilla already performs implicit delta compression internally. Combining Delta/DoubleDelta with Gorilla is redundant—it adds overhead without compression benefit.

Historical context: A corruption bug existed in this combination (fixed in PR #45615, Jan 2023). The blocking (PR #45652) remains as a best practice guardrail, not because of danger.

Best practice: Use each codec family independently for its intended data type:

• DoubleDelta/Delta: Timestamps, monotonic sequences
• Gorilla: Float values (prices, gauges)

-- Correct usage
price Float64 CODEC(Gorilla, ZSTD)              -- Floats: use Gorilla
timestamp DateTime64 CODEC(DoubleDelta, ZSTD)   -- Timestamps: use DoubleDelta

Codec Reference

DoubleDelta

Best for: Monotonically increasing timestamps, sequence numbers

How it works: Stores difference of differences (second derivative)

Typical ratio: 10-50x for timestamps

timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD)
event_time DateTime CODEC(DoubleDelta, ZSTD)
sequence_id UInt64 CODEC(DoubleDelta, ZSTD)  -- If monotonic

Gorilla

Best for: Float values (prices, measurements, gauges)

How it works: XOR-based encoding for IEEE 754 floats

Typical ratio: 5-15x for financial data

Restriction: Float32/Float64 only

price Float64 CODEC(Gorilla, ZSTD)
temperature Float32 CODEC(Gorilla, ZSTD)
percentage Float64 CODEC(Gorilla, ZSTD)

T64

Best for: General integers, especially with ZSTD

How it works: Transform to 64-bit chunks, compress value distribution

Typical ratio: 3-8x

Note: Works best with ZSTD, not LZ4

count UInt64 CODEC(T64, ZSTD)
user_id UInt32 CODEC(T64, ZSTD)
quantity Int64 CODEC(T64, ZSTD)

Delta

Best for: Slowly changing integer values

How it works: Stores differences between consecutive values

Typical ratio: 5-20x for small deltas

version UInt32 CODEC(Delta, ZSTD)
revision Int32 CODEC(Delta, ZSTD)

LowCardinality

Best for: String columns with < 10,000 unique values

How it works: Dictionary encoding with integer references

Typical improvement: 4x query speed, 3-5x compression

status LowCardinality(String)
country LowCardinality(String)
exchange LowCardinality(String)

ZSTD

Best for: General-purpose compression, always as final codec

Levels: 1-22 (default 1, recommended 3 for balance)

-- Level 3 is good balance of speed/ratio
description String CODEC(ZSTD(3))
json_payload String CODEC(ZSTD(3))

LZ4

Best for: Speed-critical scenarios (slightly faster than ZSTD)

Trade-off: 10-20% worse compression than ZSTD

-- Only if decompression speed is critical
log_line String CODEC(LZ4)

Upcoming Codecs

ALP (Adaptive Lossless floating-Point)

Status: 🔄 In Development (PR #91362, Dec 2025)

Best for: Float columns with better compression than Gorilla

How it works: Adaptive encoding that exploits patterns in floating-point data

Current status: Not yet available in any ClickHouse release. PR #91362 is under active review (opened Dec 2, 2025). Issue #60533 tracks the feature request.

When available: ALP will provide an alternative to Gorilla for float compression, potentially with better ratios for certain data patterns.

-- Future syntax (not yet available)
price Float64 CODEC(ALP, ZSTD)  -- Once released

Codec Chaining

Chain specialized codecs with general-purpose compression:

Decision guide:

• ZSTD (default): Better compression ratio, safer when data patterns unknown
• LZ4: 1.76x faster decompression, use when read latency is critical

Benchmark Results

Typical compression ratios (higher is better):

Validation Query

Check compression effectiveness:

SELECT
    column,
    type,
    compression_codec,
    formatReadableSize(data_compressed_bytes) AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS ratio
FROM system.columns
WHERE database = 'your_database'
    AND table = 'your_table'
ORDER BY data_uncompressed_bytes DESC;

Migration

To change codec on existing column:

-- Add new column with desired codec
ALTER TABLE trades ADD COLUMN price_new Float64 CODEC(Gorilla, ZSTD);

-- Copy data
ALTER TABLE trades UPDATE price_new = price WHERE 1;

-- Swap columns
ALTER TABLE trades DROP COLUMN price;
ALTER TABLE trades RENAME COLUMN price_new TO price;

Related References

• Schema Design Workflow
• Anti-Patterns and Fixes

Evolution Log

Convention: Reverse chronological order (newest on top, oldest at bottom). Prepend new entries.

---

2026-02-26: Initial Evolution Log

Status: Skill is in use and maintained. Track improvements here.

Purpose

This evolution log tracks updates to the skill. Each entry should note:

• What changed (content, structure, tooling)
• Why it changed (bug fix, feature request, best practice)
• Files affected

How to Use

1. When updating SKILL.md or references, add an entry here with the date 2. Keep entries reverse-chronological (newest first) 3. Link to ADRs or GitHub issues when relevant 4. Reference specific line changes when helpful

---

Skill: ClickHouse Architect

Idiomatic Architecture

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

ClickHouse-native patterns that replace traditional database approaches.

Pattern Mapping

Note: Dictionary performance gains are context-dependent. See Dictionaries vs JOINs for decision framework.

Parameterized Views (23.1+)

Replace static views with flexible table functions.

Basic Example

-- Create parameterized view
CREATE VIEW trades_by_symbol AS
SELECT *
FROM trades
WHERE symbol = {symbol:String}
    AND timestamp >= {start_time:DateTime64}
    AND timestamp <= {end_time:DateTime64};

-- Query with parameters
SELECT * FROM trades_by_symbol(
    symbol = 'BTCUSDT',
    start_time = '2024-01-01 00:00:00',
    end_time = '2024-01-31 23:59:59'
);

With Nullable Parameters

CREATE VIEW trades_filtered AS
SELECT *
FROM trades
WHERE symbol = coalesce({symbol:Nullable(String)}, symbol)
    AND exchange = coalesce({exchange:Nullable(String)}, exchange)
    AND timestamp >= {start_time:DateTime64};

-- Query with optional filters
SELECT * FROM trades_filtered(
    symbol = NULL,  -- No symbol filter
    exchange = 'binance',
    start_time = '2024-01-01'
);

Array Parameters

CREATE VIEW trades_multi_symbol AS
SELECT *
FROM trades
WHERE symbol IN {symbols:Array(String)}
    AND timestamp >= {start_time:DateTime64};

-- Query with multiple symbols
SELECT * FROM trades_multi_symbol(
    symbols = ['BTCUSDT', 'ETHUSDT', 'SOLUSDT'],
    start_time = '2024-01-01'
);

Dictionaries vs JOINs (Context-Dependent)

Benchmark context: The "6.6x faster" claim comes from Star Schema Benchmark with 1.4 billion rows in the fact table.

v24.4+ JOIN Improvements

ClickHouse 24.4 introduced significant JOIN optimizations:

• Predicate pushdown: 8-180x faster (180x upper bound)
• Automatic OUTER→INNER conversion
• Enhanced equivalence class analysis

When to Use Dictionaries (v24.4+)

When to Use JOINs (v24.4+)

Create Dictionary

-- Source table
CREATE TABLE symbols (
    symbol String,
    name String,
    sector String,
    market_cap Float64
) ENGINE = MergeTree()
ORDER BY symbol;

-- Dictionary for fast lookups
CREATE DICTIONARY symbols_dict (
    symbol String,
    name String,
    sector String,
    market_cap Float64
)
PRIMARY KEY symbol
SOURCE(CLICKHOUSE(TABLE 'symbols'))
LAYOUT(FLAT())  -- Fastest for < 500k keys
LIFETIME(MIN 300 MAX 3600);

Use in Queries

-- Instead of JOIN
SELECT
    t.symbol,
    t.price,
    dictGet('symbols_dict', 'name', t.symbol) AS symbol_name,
    dictGet('symbols_dict', 'sector', t.symbol) AS sector
FROM trades t
WHERE timestamp > now() - INTERVAL 1 DAY;

Layout Selection

Limitations

• No duplicate keys: Silently deduplicated (last value wins)
• Memory-resident: FLAT/HASHED load entirely into RAM
• Update lag: LIFETIME controls refresh frequency

ReplacingMergeTree for Deduplication

Handle duplicates with eventual consistency at merge time.

Basic Deduplication

CREATE TABLE trades (
    trade_id UInt64,
    symbol String,
    timestamp DateTime64(3),
    price Float64,
    quantity Float64
) ENGINE = ReplacingMergeTree()
ORDER BY (symbol, trade_id);

-- Duplicates with same (symbol, trade_id) merged at merge time

Versioned Deduplication

CREATE TABLE trades (
    trade_id UInt64,
    symbol String,
    timestamp DateTime64(3),
    price Float64,
    quantity Float64,
    version UInt64  -- Higher version wins
) ENGINE = ReplacingMergeTree(version)
ORDER BY (symbol, trade_id);

Query-Time Deduplication

-- FINAL forces deduplication at query time (slower)
SELECT * FROM trades FINAL
WHERE symbol = 'BTCUSDT';

-- Partition-aware FINAL (faster for partitioned tables)
SET do_not_merge_across_partitions_select_final = 1;
SELECT * FROM trades FINAL
WHERE symbol = 'BTCUSDT';

Limitations

• Eventual consistency: Duplicates exist until merge
• FINAL is slow: 100x slower on large tables
• ORDER BY is key: Deduplication based on ORDER BY columns

Materialized Views for Pre-Aggregation

Pre-compute expensive aggregations in real-time.

Hourly Aggregation

-- Source table
CREATE TABLE trades (...) ENGINE = MergeTree() ...;

-- Materialized view for hourly stats
CREATE MATERIALIZED VIEW trades_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (exchange, symbol, hour)
AS SELECT
    exchange,
    symbol,
    toStartOfHour(timestamp) AS hour,
    sum(quantity) AS total_volume,
    sum(price * quantity) AS total_value,
    count() AS trade_count,
    min(price) AS low,
    max(price) AS high
FROM trades
GROUP BY exchange, symbol, hour;

-- Query pre-computed stats (instant)
SELECT * FROM trades_hourly_mv
WHERE symbol = 'BTCUSDT'
    AND hour >= now() - INTERVAL 7 DAY;

AggregatingMergeTree for Complex Aggregates

CREATE MATERIALIZED VIEW trades_stats_mv
ENGINE = AggregatingMergeTree()
ORDER BY (exchange, symbol, day)
AS SELECT
    exchange,
    symbol,
    toDate(timestamp) AS day,
    sumState(quantity) AS total_volume,
    avgState(price) AS avg_price,
    quantileState(0.5)(price) AS median_price
FROM trades
GROUP BY exchange, symbol, day;

-- Query with merge functions
SELECT
    exchange,
    symbol,
    day,
    sumMerge(total_volume) AS volume,
    avgMerge(avg_price) AS avg,
    quantileMerge(0.5)(median_price) AS median
FROM trades_stats_mv
GROUP BY exchange, symbol, day;

Warning: ReplacingMergeTree + Materialized View

Avoid putting AggregatingMergeTree on top of ReplacingMergeTree:

-- PROBLEMATIC: Duplicates may be aggregated before merge
CREATE MATERIALIZED VIEW stats_mv
ENGINE = SummingMergeTree()
AS SELECT ... FROM replacing_table GROUP BY ...;

The materialized view sees duplicates before ReplacingMergeTree merges them.

Solution: Use query-time aggregation with FINAL, or pre-deduplicate in a separate table.

Related References

• Schema Design Workflow
• Anti-Patterns and Fixes
• Audit and Diagnostics

Skill: ClickHouse Architect

Schema Design Workflow

<!-- ADR: 2025-12-09-clickhouse-architect-skill -->

Complete workflow for designing ClickHouse schemas from requirements to production.

Workflow Overview

Requirements → ORDER BY → Codecs → PARTITION BY → Accelerators → Validate

Step 1: Gather Requirements

Before designing the schema, understand:

Step 2: Define ORDER BY Key

The ORDER BY clause determines query performance more than any other factor.

Decision Process

1. List all columns used in WHERE clauses 2. Order by cardinality (lowest first) 3. Limit to 3-5 columns 4. Ensure range query columns are included

Example Walkthrough

Scenario: Trading data with queries filtering by exchange, symbol, and time ranges.

-- Query patterns:
-- 1. WHERE exchange = 'binance' AND symbol = 'BTCUSDT' AND timestamp > ...
-- 2. WHERE symbol = 'ETHUSDT' ORDER BY timestamp
-- 3. WHERE timestamp BETWEEN ... AND ... (rare)

-- Cardinality analysis:
-- exchange: ~10 values (LOW)
-- symbol: ~1000 values (MEDIUM)
-- timestamp: millions (HIGH)
-- trade_id: unique (HIGHEST)

-- Optimal ORDER BY:
ORDER BY (exchange, symbol, timestamp, trade_id)

Step 3: Select Data Types and Codecs

Match column types to their optimal codecs:

CREATE TABLE trades (
    -- Identifiers
    trade_id UInt64,

    -- Low-cardinality strings
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    side LowCardinality(String),

    -- Timestamps with specialized codec
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD),

    -- Float values with Gorilla compression
    price Float64 CODEC(Gorilla, ZSTD),
    quantity Float64 CODEC(Gorilla, ZSTD),

    -- Integer counters
    sequence_num UInt64 CODEC(T64, ZSTD)
) ENGINE = MergeTree()
ORDER BY (exchange, symbol, timestamp, trade_id);

Step 4: Configure PARTITION BY

Use PARTITION BY for data lifecycle management, not query optimization.

Guidelines

Compound Partition Keys for Financial Time-Series Data

When your table has compound keys like (symbol, threshold, timestamp), partition by lifecycle dimensions only — not time.

Key insight: If your ORDER BY contains a time-monotonic column (trade IDs, sequence numbers), you do NOT need time in the partition key. The primary key index provides efficient time-range pruning within partitions.

-- CORRECT: partition by lifecycle dimensions only
PARTITION BY (symbol, threshold)
ORDER BY (symbol, threshold, mode, trade_id)  -- trade_id is time-monotonic

-- WRONG: time in partition key
PARTITION BY (symbol, threshold, toYYYYMMDD(timestamp))
-- Creates 192K+ partitions, mutations scan all partition metadata

Why this matters for mutations and writes:

Time-based partitioning fragments data across thousands of partitions. Every ALTER TABLE DELETE or INSERT must coordinate across all of them. With lifecycle-only partitions, mutations target exactly the partitions they need (e.g., DELETE IN PARTITION (sym, thr) WHERE ...), and the ORDER BY index handles time-range pruning.

When to use compound lifecycle partitions:

• Table has categorical dimensions (symbol, threshold, mode) that define independent data lineages
• Mutations operate on specific (symbol, threshold) combinations
• TTL or DROP PARTITION operates on (symbol, threshold) groups
• Time-range queries are handled by a time-monotonic column in ORDER BY

TTL Integration

CREATE TABLE trades (
    ...
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (exchange, symbol, timestamp, trade_id)
TTL timestamp + INTERVAL 90 DAY DELETE;

Step 5: Add Performance Accelerators

When to Use Each

Projection Example

-- Add projection for queries sorted by symbol first
ALTER TABLE trades ADD PROJECTION trades_by_symbol (
    SELECT * ORDER BY symbol, exchange, timestamp
);
ALTER TABLE trades MATERIALIZE PROJECTION trades_by_symbol;

Skip Index Example

-- Bloom filter for rare text searches
ALTER TABLE trades ADD INDEX idx_trade_id trade_id TYPE bloom_filter GRANULARITY 4;

Step 6: Validate Schema

Run the audit script to verify:

clickhouse-client --multiquery < scripts/schema-audit.sql

Validation Checklist

• [ ] Part count < 300 per partition
• [ ] Compression ratio > 3x for numeric columns
• [ ] Query execution time meets SLA
• [ ] Memory usage within limits
• [ ] Replication lag (if applicable) < 10 seconds

Complete Example

-- Production-ready trading table
CREATE TABLE trades (
    -- Identifiers
    trade_id UInt64,

    -- Categorical (low cardinality)
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    side Enum8('buy' = 1, 'sell' = 2),

    -- Time series
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD),

    -- Numeric measurements
    price Float64 CODEC(Gorilla, ZSTD),
    quantity Float64 CODEC(Gorilla, ZSTD),
    quote_quantity Float64 CODEC(Gorilla, ZSTD),

    -- Metadata
    is_maker Bool,
    sequence_num UInt64 CODEC(T64, ZSTD)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (exchange, symbol, timestamp, trade_id)
TTL timestamp + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

-- Add projection for symbol-first queries
ALTER TABLE trades ADD PROJECTION trades_by_symbol (
    SELECT * ORDER BY symbol, exchange, timestamp
);
ALTER TABLE trades MATERIALIZE PROJECTION trades_by_symbol;

Related References

• Compression Codec Selection
• Anti-Patterns and Fixes
• Audit and Diagnostics

Skill: ClickHouse Architect

Schema Documentation for AI Understanding

<!-- ADR: 2025-12-09-clickhouse-schema-documentation -->

Schema comments and naming conventions help AI tools understand your ClickHouse schema. This reference provides evidence-based guidance on what works, ClickHouse-specific syntax, and when to graduate to more sophisticated approaches.

Evidence-Based Positioning

What the Research Shows

Key insight: Schema comments are the _essential baseline_, not the complete solution. For small-to-medium schemas, they're sufficient. For enterprise scale, invest in semantic layers (dbt, Cube, AtScale).

Sources: AtScale 2025 study, SNAILS (SIGMOD 2025), TigerData research

ClickHouse COMMENT Syntax

ClickHouse does NOT use standard SQL COMMENT ON syntax. Use the patterns below.

Table-Level Comments

-- At creation
CREATE TABLE trades (
    trade_id UInt64,
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    price Float64,
    quantity Float64,
    timestamp DateTime64(3)
) ENGINE = MergeTree()
ORDER BY (exchange, symbol, timestamp)
COMMENT 'Real-time trade events from crypto exchanges. Partitioned monthly.';

-- After creation
ALTER TABLE trades MODIFY COMMENT 'Updated: includes legacy data migration';

Column-Level Comments

ALTER TABLE trades
    COMMENT COLUMN trade_id 'Unique identifier from exchange API',
    COMMENT COLUMN symbol 'Trading pair (e.g., BTCUSDT). LowCardinality for <10k unique values',
    COMMENT COLUMN price 'Execution price in quote currency. Use Gorilla codec for floats',
    COMMENT COLUMN timestamp 'Event time from exchange. DoubleDelta codec for monotonic';

Query Comments from System Tables

-- Table comments
SELECT name, comment
FROM system.tables
WHERE database = 'default' AND name = 'trades';

-- Column comments
SELECT name, comment, type
FROM system.columns
WHERE database = 'default' AND table = 'trades'
ORDER BY position;

Naming Conventions (SNAILS Research)

The SNAILS study (SIGMOD 2025) found that schema identifier "naturalness" has statistically significant impact on LLM accuracy. Naming may matter as much as comments.

Naming Patterns

Anti-Patterns

Replication Considerations

Comment behavior varies by table engine:

Best practice: Apply column comments after table creation, before data ingestion. For Materialized Views, apply comments to the target table, not the view itself.

Integration with Schema Design Workflow

Add comments as Step 6 in the Schema Design Workflow:

1. Define ORDER BY key 2. Select compression codecs 3. Configure PARTITION BY 4. Add performance accelerators 5. Validate with audit queries 6. Document with COMMENT statements ← NEW

Complete Example

-- Step 1-5: Schema creation (see schema-design-workflow.md)
CREATE TABLE trades (
    trade_id UInt64,
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    price Float64 CODEC(Gorilla, ZSTD),
    quantity Float64 CODEC(Gorilla, ZSTD),
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (exchange, symbol, timestamp, trade_id)
COMMENT 'Real-time trade events. Source: exchange websocket feeds.';

-- Step 6: Add column comments for AI understanding
ALTER TABLE trades
    COMMENT COLUMN trade_id 'Unique identifier from exchange API. Not globally unique.',
    COMMENT COLUMN exchange 'Exchange name (binance, coinbase, etc.). ~20 values.',
    COMMENT COLUMN symbol 'Trading pair in BASE/QUOTE format (e.g., BTC/USDT).',
    COMMENT COLUMN price 'Execution price in quote currency units.',
    COMMENT COLUMN quantity 'Trade size in base currency units.',
    COMMENT COLUMN timestamp 'Exchange-reported execution time (UTC).';

When to Graduate Beyond Comments

Signs you need a semantic layer:

• Multiple teams with different terminology for same concepts
• Business users asking "what does this column mean?" repeatedly
• AI tools generating incorrect queries despite comments
• Schema sprawl making comments hard to maintain

Related References

• Schema Design Workflow - Step 1-5 of schema creation
• Audit and Diagnostics - Includes system.columns queries