clickhouse-schema-design_skill

Overview

This skill provides a compact set of 15+ actionable schema rules and patterns for ClickHouse table design. It is intended to be loaded whenever creating or modifying tables to achieve sub-second queries, strong compression, and automated data lifecycle. The guidance focuses on ORDER BY, PARTITION BY, data types, TTL, and verification/troubleshooting steps.

How this skill works

The skill inspects schema choices and recommends concrete changes: ideal ORDER BY ordering, partitioning strategy, smallest-fit data types, LowCardinality usage, and TTL rules for tiered storage or deletion. It also suggests engine selection (MergeTree variants), skip indexes for secondary lookups, and provides verification queries and remediation steps for common problems like too many parts or poor compression. Use it as a checklist before applying ALTER/CREATE operations.

When to use it

• Always when creating new ClickHouse tables
• Before altering table schema or changing ORDER BY/PARTITION BY
• When queries on large tables are slower than expected
• When compression ratios or storage growth are concerning
• When implementing automated retention or tiered storage with TTL

Best practices

• Choose ORDER BY columns in the order most commonly filtered: low-cardinality first, then time, then higher-cardinality keys
• Use PARTITION BY for management (partition size targets), not primary filtering; prefer monthly partitions for analytics
• Pick the smallest numeric and time types that fit needs; avoid nullable where possible and prefer DEFAULTs
• Use LowCardinality(String) for categories with <10k uniques and enums where appropriate
• Limit ORDER BY to 3–5 meaningful columns; make PRIMARY KEY a prefix of ORDER BY to shrink index
• Configure TTLs for deletion and tiered storage and verify with system tables before applying changes

Example use cases

• High-volume event table: ORDER BY (tenant_id, event_date, user_id), PARTITION BY toYYYYMM(event_date), TTL 12 months DELETE
• Multi-tenant analytics: partition by (tenant_id, toYYYYMM(date)) to isolate tenant data and meet partition size targets
• Dedup/upsert workload: use ReplacingMergeTree with deduplication key and materialized views for ingestion
• PII minimization: column-level TTL to delete email/ip after 30 days while retaining aggregated metrics
• Secondary lookups: add bloom_filter skip index on session_id or user_id for occasional direct lookups