clickhouse-io
ClickHouse database patterns, query optimization, analytics, and data engineering best practices for high-performance analytical workloads. Use when writing ClickHouse schemas or queries, or when an analytical query is too slow.
By affaan-m · 2,844 installs
npx skills add affaan-m/ecc --skill clickhouse-io
Source repository · Upstream listing
ClickHouse Analytics Patterns
ClickHouse specific patterns for high performance analytics and data engineering.
When to Activate
Designing ClickHouse table schemas (MergeTree engine selection)
Writing analytical queries (aggregations, window functions, joins)
Optimizing query performance (partition pruning, projections, materialized views)
Ingesting large volumes of data (batch inserts, Kafka integration)
Migrating from PostgreSQL/MySQL to ClickHouse for analytics
Implementing real time dashboards or time series analytics
Overview
ClickHouse is a column oriented database management system (DBMS) for online analytical processing (OLAP). It's optimized for fast analytical queries on large datasets.
Key Features:
Column oriented storage
Data compression
Parallel query execution
Distributed queries
Real time analytics
Table Design Patterns
MergeTree Engine (Most Common)
ReplacingMergeTree (Deduplication)
AggregatingMergeTree (Pre aggregation)
Query Optimization Patterns
Efficient Filtering
Aggregations
Window Functions
Data Insertion Patterns
Bulk Insert (Recommended)
Streaming Insert
Materialized Views
Real time Aggregations
Performance Monitoring
Query Performance
Table Statistics
Common Analytics Queries
Time Series Analysis
Funnel Analysis
Cohort Analysis
Data Pipeline Patterns
ETL Pattern
Change Data Capture (CDC)
Best Practices
1. Partitioning Strategy
Partition by time (usually month or day)
Avoid too many partitions (performance impact)
Use DATE type for partition key
2. Ordering Key
Put most frequently filtered columns first
Consider cardinality (high cardinality first)
Order impacts compression
3. Data Types
Use smallest appropriate type (UInt32 vs UInt64)
Use LowCardinality for repeated strings
Use Enum for categorical data
4. Avoid
SELECT (specify columns)
FINAL (merge data before query instead)
Too many JOINs (denormalize for analytics)
Small frequent inserts (batch instead)
5. Monitoring
Track query performance
Monitor disk usage
Check merge operations
Review slow query log
Remember : ClickHouse excels at analytical workloads. Design tables for your query patterns, batch inserts, and leverage materialized views for real time aggregations.