clickhouse-architect_skill

Overview

This skill provides prescriptive ClickHouse schema design and performance tuning for ClickHouse v24.4+ covering both cloud (SharedMergeTree) and self-hosted (ReplicatedMergeTree). It delivers clear recommendations for ORDER BY selection, compression codecs, partitioning, and performance accelerators like projections, materialized views, and dictionaries. Use it to design new tables, audit existing schemas, and prevent common anti-patterns.

How this skill works

The skill inspects query patterns, data cardinality, and column types to recommend an ORDER BY key (3–5 columns, lowest cardinality first), per-column compression codecs, and appropriate PARTITION BY expressions for lifecycle management. It suggests engine choice (SharedMergeTree, ReplicatedMergeTree, or MergeTree), adds accelerators (projections, materialized views, dictionaries) when beneficial, and produces audit checks for part counts, compression ratios, replication lag, and memory usage.

When to use it

• Design a new ClickHouse table schema and ORDER BY key
• Optimize compression settings per column for cost or read performance
• Define partitioning for TTL and lifecycle management
• Audit and remediate existing ClickHouse schemas for performance issues
• Add projections, materialized views, or dictionaries to accelerate queries

Best practices

• Limit ORDER BY to 3–5 columns and put lowest-cardinality columns first
• Partition by time units (month or week) for TTL; avoid high-cardinality partitions
• Choose codecs by data type: Gorilla for floats, DoubleDelta for timestamps, T64/Delta for integers
• Prefer LZ4 for read-heavy monotonic data and ZSTD when compression ratio matters
• Keep total partitions under ~1000 and parts per partition between 1–300
• Batch inserts (10k–100k rows) to avoid many small parts and set max_memory_usage limits

Example use cases

• Design a trades table for ClickHouse Cloud with ORDER BY(exchange, symbol, timestamp) and Gorilla compression for prices
• Audit a cluster to find tables with >300 parts/partition and recommend repartitioning or TTL changes
• Add a projection to support a frequent symbol+time query pattern to enable automatic selection by the engine
• Replace heavy JOINs with dictionaries for dimension lookups when dimension tables are <500k rows
• Choose ReplicatedMergeTree for multi-node clusters and SharedMergeTree for managed cloud deployments