Clickhouse Best Practices

1---
2name: clickhouse-best-practices
3description: 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.0
5metadata:
6  author: ClickHouse Inc
7  version: "0.3.0"
8---
9 
10# ClickHouse Best Practices
11 
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 Skill
17 
18**Before answering ClickHouse questions, follow this priority order:**
19 
201. **Check for applicable rules** in the `rules/` directory
212. **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 documentation
234. **If uncertain:** Use web search for current best practices
245. **Always cite your source:** rule name, "general ClickHouse guidance", or URL
25 
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 Reviews
29 
30When performing a formal review of schemas, queries, or data ingestion:
31 
32---
33 
34## Review Procedures
35 
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 immutable
412. `rules/schema-pk-cardinality-order.md` - Column ordering in keys
423. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion
434. `rules/schema-types-native-types.md` - Proper type selection
445. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing
456. `rules/schema-types-lowcardinality.md` - LowCardinality usage
467. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT
478. `rules/schema-partition-low-cardinality.md` - Partition count limits
489. `rules/schema-partition-lifecycle.md` - Partitioning purpose
49 
50**Check for:**
51- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)
52- [ ] Data types match actual data ranges
53- [ ] LowCardinality applied to appropriate string columns
54- [ ] Partition key cardinality bounded (100-1,000 values)
55- [ ] ReplacingMergeTree has version column if used
56 
57### For Query Reviews (SELECT, JOIN, aggregations)
58 
59**Read these rule files:**
60 
611. `rules/query-join-choose-algorithm.md` - Algorithm selection
622. `rules/query-join-filter-before.md` - Pre-join filtering
633. `rules/query-join-use-any.md` - ANY vs regular JOIN
644. `rules/query-index-skipping-indices.md` - Secondary index usage
655. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY
66 
67**Check for:**
68- [ ] Filters use ORDER BY prefix columns
69- [ ] JOINs filter tables before joining (not after)
70- [ ] Correct JOIN algorithm for table sizes
71- [ ] Skipping indices for non-ORDER BY filter columns
72 
73### For Insert Strategy Reviews (data ingestion, updates, deletes)
74 
75**Read these rule files:**
76 
771. `rules/insert-batch-size.md` - Batch sizing requirements
782. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives
793. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives
804. `rules/insert-async-small-batches.md` - Async insert usage
815. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks
82 
83**Check for:**
84- [ ] Batch size 10K-100K rows per INSERT
85- [ ] No ALTER TABLE UPDATE for frequent changes
86- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns
87- [ ] Async inserts enabled for high-frequency small batches
88 
89---
90 
91## Output Format
92 
93Structure your response as follows:
94 
95```
96## Rules Checked
97- `rule-name-1` - Compliant / Violation found
98- `rule-name-2` - Compliant / Violation found
99...
100 
101## Findings
102 
103### Violations
104- **`rule-name`**: Description of the issue
105  - Current: [what the code does]
106  - Required: [what it should do]
107  - Fix: [specific correction]
108 
109### Compliant
110- `rule-name`: Brief note on why it's correct
111 
112## Recommendations
113[Prioritized list of changes, citing rules]
114```
115 
116---
117 
118## Rule Categories by Priority
119 
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 Reference
137 
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 cardinality
142- `schema-pk-prioritize-filters` - Include frequently filtered columns
143- `schema-pk-filter-on-orderby` - Query filters must use ORDER BY prefix
144 
145### Schema Design - Data Types (CRITICAL)
146 
147- `schema-types-native-types` - Use native types, not String for everything
148- `schema-types-minimize-bitwidth` - Use smallest numeric type that fits
149- `schema-types-lowcardinality` - LowCardinality for <10K unique strings
150- `schema-types-enum` - Enum for finite value sets with validation
151- `schema-types-avoid-nullable` - Avoid Nullable; use DEFAULT instead
152 
153### Schema Design - Partitioning (HIGH)
154 
155- `schema-partition-low-cardinality` - Keep partition count 100-1,000
156- `schema-partition-lifecycle` - Use partitioning for data lifecycle, not queries
157- `schema-partition-query-tradeoffs` - Understand partition pruning trade-offs
158- `schema-partition-start-without` - Consider starting without partitioning
159 
160### Schema Design - JSON (MEDIUM)
161 
162- `schema-json-when-to-use` - JSON for dynamic schemas; typed columns for known
163 
164### Query Optimization - JOINs (CRITICAL)
165 
166- `query-join-choose-algorithm` - Select algorithm based on table sizes
167- `query-join-use-any` - ANY JOIN when only one match needed
168- `query-join-filter-before` - Filter tables before joining
169- `query-join-consider-alternatives` - Dictionaries/denormalization vs JOIN
170- `query-join-null-handling` - join_use_nulls=0 for default values
171 
172### Query Optimization - Indices (HIGH)
173 
174- `query-index-skipping-indices` - Skipping indices for non-ORDER BY filters
175 
176### Query Optimization - Materialized Views (HIGH)
177 
178- `query-mv-incremental` - Incremental MVs for real-time aggregations
179- `query-mv-refreshable` - Refreshable MVs for complex joins
180 
181### Insert Strategy - Batching (CRITICAL)
182 
183- `insert-batch-size` - Batch 10K-100K rows per INSERT
184 
185### Insert Strategy - Async (HIGH)
186 
187- `insert-async-small-batches` - Async inserts for high-frequency small batches
188- `insert-format-native` - Native format for best performance
189 
190### Insert Strategy - Mutations (CRITICAL)
191 
192- `insert-mutation-avoid-update` - ReplacingMergeTree instead of ALTER UPDATE
193- `insert-mutation-avoid-delete` - Lightweight DELETE or DROP PARTITION
194 
195### Insert Strategy - Optimization (HIGH)
196 
197- `insert-optimize-avoid-final` - Let background merges work
198 
199---
200 
201## When to Apply
202 
203This skill activates when you encounter:
204 
205- `CREATE TABLE` statements
206- `ALTER TABLE` modifications
207- `ORDER BY` or `PRIMARY KEY` discussions
208- Data type selection questions
209- Slow query troubleshooting
210- JOIN optimization requests
211- Data ingestion pipeline design
212- Update/delete strategy questions
213- ReplacingMergeTree or other specialized engine usage
214- Partitioning strategy decisions
215 
216---
217 
218## Rule File Structure
219 
220Each rule file in `rules/` contains:
221 
222- **YAML frontmatter**: title, impact level, tags
223- **Brief explanation**: Why this rule matters
224- **Incorrect example**: Anti-pattern with explanation
225- **Correct example**: Best practice with explanation
226- **Additional context**: Trade-offs, when to apply, references
227 
228---
229 
230## Full Compiled Document
231 
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.
235