clickhouse-patterns

When to Use

Trigger this skill when handling large-scale analytical queries, building OLAP pipelines, or optimizing ClickHouse performance.

Core Capabilities

Query Optimization

• Query only necessary columns — ClickHouse is columnar, fewer columns = less IO
• Use PREWHERE instead of WHERE for coarse filtering
• Avoid JOIN, use Dictionaries or wide tables instead
• Date filters must hit the primary key sort order

Materialized Views

• Use Materialized Views for pre-aggregation, queries return in seconds
• Use Refreshable materialized views for periodic full rebuilds
• Avoid chained materialized views, high maintenance cost

Data Ingestion

• Prefer large batch low-frequency writes over small batch high-frequency
• Use INSERT SELECT for table-to-table migration
• Use Kafka / ClickHouse Kafka Engine for real-time ingestion

Engine Selection

• Default MergeTree with date partitioning + sort key
• ReplacingMergeTree for deduplication
• AggregatingMergeTree for pre-aggregation