Clickhouse Best Practices

1---2name: clickhouse-best-practices3description: MUST USE when reviewing ClickHouse schemas, queries, or configurations. Contains 28 rules that MUST be checked before providing recommendations. Always read relevant rule files and cite specific rules in responses.4license: Apache-2.05metadata:6  author: ClickHouse Inc7  version: "0.3.0"8---9 10# ClickHouse Best Practices11 12Comprehensive guidance for ClickHouse covering schema design, query optimization, and data ingestion. Contains 28 rules across 3 main categories (schema, query, insert), prioritized by impact.13 14> **Official docs:** [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices)15 16## IMPORTANT: How to Apply This Skill17 18**Before answering ClickHouse questions, follow this priority order:**19 201. **Check for applicable rules** in the `rules/` directory212. **If rules exist:** Apply them and cite them in your response using "Per `rule-name`..."223. **If no rule exists:** Use the LLM's ClickHouse knowledge or search documentation234. **If uncertain:** Use web search for current best practices245. **Always cite your source:** rule name, "general ClickHouse guidance", or URL25 26**Why rules take priority:** ClickHouse has specific behaviors (columnar storage, sparse indexes, merge tree mechanics) where general database intuition can be misleading. The rules encode validated, ClickHouse-specific guidance.27 28### For Formal Reviews29 30When performing a formal review of schemas, queries, or data ingestion:31 32---33 34## Review Procedures35 36### For Schema Reviews (CREATE TABLE, ALTER TABLE)37 38**Read these rule files in order:**39 401. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable412. `rules/schema-pk-cardinality-order.md` - Column ordering in keys423. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion434. `rules/schema-types-native-types.md` - Proper type selection445. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing456. `rules/schema-types-lowcardinality.md` - LowCardinality usage467. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT478. `rules/schema-partition-low-cardinality.md` - Partition count limits489. `rules/schema-partition-lifecycle.md` - Partitioning purpose49 50**Check for:**51- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)52- [ ] Data types match actual data ranges53- [ ] LowCardinality applied to appropriate string columns54- [ ] Partition key cardinality bounded (100-1,000 values)55- [ ] ReplacingMergeTree has version column if used56 57### For Query Reviews (SELECT, JOIN, aggregations)58 59**Read these rule files:**60 611. `rules/query-join-choose-algorithm.md` - Algorithm selection622. `rules/query-join-filter-before.md` - Pre-join filtering633. `rules/query-join-use-any.md` - ANY vs regular JOIN644. `rules/query-index-skipping-indices.md` - Secondary index usage655. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY66 67**Check for:**68- [ ] Filters use ORDER BY prefix columns69- [ ] JOINs filter tables before joining (not after)70- [ ] Correct JOIN algorithm for table sizes71- [ ] Skipping indices for non-ORDER BY filter columns72 73### For Insert Strategy Reviews (data ingestion, updates, deletes)74 75**Read these rule files:**76 771. `rules/insert-batch-size.md` - Batch sizing requirements782. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives793. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives804. `rules/insert-async-small-batches.md` - Async insert usage815. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks82 83**Check for:**84- [ ] Batch size 10K-100K rows per INSERT85- [ ] No ALTER TABLE UPDATE for frequent changes86- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns87- [ ] Async inserts enabled for high-frequency small batches88 89---90 91## Output Format92 93Structure your response as follows:94 95```96## Rules Checked97- `rule-name-1` - Compliant / Violation found98- `rule-name-2` - Compliant / Violation found99...100 101## Findings102 103### Violations104- **`rule-name`**: Description of the issue105  - Current: [what the code does]106  - Required: [what it should do]107  - Fix: [specific correction]108 109### Compliant110- `rule-name`: Brief note on why it's correct111 112## Recommendations113[Prioritized list of changes, citing rules]114```115 116---117 118## Rule Categories by Priority119 120| Priority | Category | Impact | Prefix | Rule Count |121|----------|----------|--------|--------|------------|122| 1 | Primary Key Selection | CRITICAL | `schema-pk-` | 4 |123| 2 | Data Type Selection | CRITICAL | `schema-types-` | 5 |124| 3 | JOIN Optimization | CRITICAL | `query-join-` | 5 |125| 4 | Insert Batching | CRITICAL | `insert-batch-` | 1 |126| 5 | Mutation Avoidance | CRITICAL | `insert-mutation-` | 2 |127| 6 | Partitioning Strategy | HIGH | `schema-partition-` | 4 |128| 7 | Skipping Indices | HIGH | `query-index-` | 1 |129| 8 | Materialized Views | HIGH | `query-mv-` | 2 |130| 9 | Async Inserts | HIGH | `insert-async-` | 2 |131| 10 | OPTIMIZE Avoidance | HIGH | `insert-optimize-` | 1 |132| 11 | JSON Usage | MEDIUM | `schema-json-` | 1 |133 134---135 136## Quick Reference137 138### Schema Design - Primary Key (CRITICAL)139 140- `schema-pk-plan-before-creation` - Plan ORDER BY before table creation (immutable)141- `schema-pk-cardinality-order` - Order columns low-to-high cardinality142- `schema-pk-prioritize-filters` - Include frequently filtered columns143- `schema-pk-filter-on-orderby` - Query filters must use ORDER BY prefix144 145### Schema Design - Data Types (CRITICAL)146 147- `schema-types-native-types` - Use native types, not String for everything148- `schema-types-minimize-bitwidth` - Use smallest numeric type that fits149- `schema-types-lowcardinality` - LowCardinality for <10K unique strings150- `schema-types-enum` - Enum for finite value sets with validation151- `schema-types-avoid-nullable` - Avoid Nullable; use DEFAULT instead152 153### Schema Design - Partitioning (HIGH)154 155- `schema-partition-low-cardinality` - Keep partition count 100-1,000156- `schema-partition-lifecycle` - Use partitioning for data lifecycle, not queries157- `schema-partition-query-tradeoffs` - Understand partition pruning trade-offs158- `schema-partition-start-without` - Consider starting without partitioning159 160### Schema Design - JSON (MEDIUM)161 162- `schema-json-when-to-use` - JSON for dynamic schemas; typed columns for known163 164### Query Optimization - JOINs (CRITICAL)165 166- `query-join-choose-algorithm` - Select algorithm based on table sizes167- `query-join-use-any` - ANY JOIN when only one match needed168- `query-join-filter-before` - Filter tables before joining169- `query-join-consider-alternatives` - Dictionaries/denormalization vs JOIN170- `query-join-null-handling` - join_use_nulls=0 for default values171 172### Query Optimization - Indices (HIGH)173 174- `query-index-skipping-indices` - Skipping indices for non-ORDER BY filters175 176### Query Optimization - Materialized Views (HIGH)177 178- `query-mv-incremental` - Incremental MVs for real-time aggregations179- `query-mv-refreshable` - Refreshable MVs for complex joins180 181### Insert Strategy - Batching (CRITICAL)182 183- `insert-batch-size` - Batch 10K-100K rows per INSERT184 185### Insert Strategy - Async (HIGH)186 187- `insert-async-small-batches` - Async inserts for high-frequency small batches188- `insert-format-native` - Native format for best performance189 190### Insert Strategy - Mutations (CRITICAL)191 192- `insert-mutation-avoid-update` - ReplacingMergeTree instead of ALTER UPDATE193- `insert-mutation-avoid-delete` - Lightweight DELETE or DROP PARTITION194 195### Insert Strategy - Optimization (HIGH)196 197- `insert-optimize-avoid-final` - Let background merges work198 199---200 201## When to Apply202 203This skill activates when you encounter:204 205- `CREATE TABLE` statements206- `ALTER TABLE` modifications207- `ORDER BY` or `PRIMARY KEY` discussions208- Data type selection questions209- Slow query troubleshooting210- JOIN optimization requests211- Data ingestion pipeline design212- Update/delete strategy questions213- ReplacingMergeTree or other specialized engine usage214- Partitioning strategy decisions215 216---217 218## Rule File Structure219 220Each rule file in `rules/` contains:221 222- **YAML frontmatter**: title, impact level, tags223- **Brief explanation**: Why this rule matters224- **Incorrect example**: Anti-pattern with explanation225- **Correct example**: Best practice with explanation226- **Additional context**: Trade-offs, when to apply, references227 228---229 230## Full Compiled Document231 232For the complete guide with all rules expanded inline: `AGENTS.md`233 234Use `AGENTS.md` when you need to check multiple rules quickly without reading individual files.