ClickHouse Best Practices
Guidance for ClickHouse covering schema design, query optimization, and data ingestion. Contains 28 atomic rules across 3 categories (schema, query, insert), prioritized by impact. Extended with 15 reference files covering cluster management, backups, monitoring, and integrations.
Official docs: ClickHouse Best Practices
⚠️ Security Considerations
Credential Placeholders
Example credentials in documentation (password123, AKIAIOSFODNN7EXAMPLE) are placeholders only. Never use these in production. Use proper secret management:
- Environment variables
- Secret managers (AWS Secrets Manager, HashiCorp Vault, etc.)
- Kubernetes secrets (for K8s deployments)
- ClickHouse named collections with external configuration
Installation & Operations
For installation and operational procedures:
- Follow official documentation links provided in reference files
- Prefer package managers (
apt, yum, helm) over direct downloads
- Use versioned artifacts instead of
latest in production
- Test procedures in non-production environments first
IMPORTANT: How to Apply This Skill
Before answering ClickHouse questions, follow this priority order:
- Check for applicable rules in the
rules/ directory
- If rules exist: Apply them and cite them in your response using "Per
rule-name..."
- If no rule exists: Check
references/ for deeper topic coverage
- If neither covers it: Use general ClickHouse knowledge or search documentation
- Always cite your source: rule name, reference file, or URL
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.
Review Procedures
For Schema Reviews (CREATE TABLE, ALTER TABLE)
Read these rule files in order:
rules/schema-pk-plan-before-creation.md — ORDER BY is immutable
rules/schema-pk-cardinality-order.md — Column ordering in keys
rules/schema-pk-prioritize-filters.md — Filter column inclusion
rules/schema-pk-filter-on-orderby.md — Query filter alignment
rules/schema-types-native-types.md — Proper type selection
rules/schema-types-minimize-bitwidth.md — Numeric type sizing
rules/schema-types-lowcardinality.md — LowCardinality usage
rules/schema-types-avoid-nullable.md — Nullable vs DEFAULT
rules/schema-types-enum.md — Enum for finite value sets
rules/schema-partition-low-cardinality.md — Partition count limits
rules/schema-partition-lifecycle.md — Partitioning purpose
rules/schema-partition-query-tradeoffs.md — Partition pruning trade-offs
rules/schema-partition-start-without.md — Start without partitioning
rules/schema-json-when-to-use.md — JSON type usage
Check for:
For Query Reviews (SELECT, JOIN, aggregations)
Read these rule files:
rules/query-join-choose-algorithm.md — Algorithm selection
rules/query-join-use-any.md — ANY vs regular JOIN
rules/query-join-filter-before.md — Pre-join filtering
rules/query-join-consider-alternatives.md — Dictionaries/denormalization
rules/query-join-null-handling.md — join_use_nulls setting
rules/query-index-skipping-indices.md — Secondary index usage
rules/query-mv-incremental.md — Incremental materialized views
rules/query-mv-refreshable.md — Refreshable materialized views
Check for:
For Insert Strategy Reviews (data ingestion, updates, deletes)
Read these rule files:
rules/insert-batch-size.md — Batch sizing requirements
rules/insert-async-small-batches.md — Async insert usage
rules/insert-format-native.md — Native format for performance
rules/insert-mutation-avoid-update.md — UPDATE alternatives
rules/insert-mutation-avoid-delete.md — DELETE alternatives
rules/insert-optimize-avoid-final.md — OPTIMIZE TABLE risks
Check for:
Output Format
Structure review responses as follows:
## Rules Checked
- `rule-name-1` — Compliant / Violation found
- `rule-name-2` — Compliant / Violation found
...
## Findings
### Violations
- **`rule-name`**: Description of the issue
- Current: [what the code does]
- Required: [what it should do]
- Fix: [specific correction]
### Compliant
- `rule-name`: Brief note on why it's correct
## Recommendations
[Prioritized list of changes, citing rules]
Rule Categories by Priority
| Priority |
Category |
Impact |
Prefix |
Count |
| 1 |
Primary Key Selection |
CRITICAL |
schema-pk- |
4 |
| 2 |
Data Type Selection |
CRITICAL |
schema-types- |
5 |
| 3 |
JOIN Optimization |
CRITICAL |
query-join- |
5 |
| 4 |
Insert Batching |
CRITICAL |
insert-batch- |
1 |
| 5 |
Mutation Avoidance |
CRITICAL |
insert-mutation- |
2 |
| 6 |
Partitioning Strategy |
HIGH |
schema-partition- |
4 |
| 7 |
Skipping Indices |
HIGH |
query-index- |
1 |
| 8 |
Materialized Views |
HIGH |
query-mv- |
2 |
| 9 |
Async Inserts |
HIGH |
insert-async- |
2 |
| 10 |
OPTIMIZE Avoidance |
HIGH |
insert-optimize- |
1 |
| 11 |
JSON Usage |
MEDIUM |
schema-json- |
1 |
Quick Reference
Schema Design — Primary Key (CRITICAL)
schema-pk-plan-before-creation — Plan ORDER BY before table creation (immutable)
schema-pk-cardinality-order — Order columns low-to-high cardinality
schema-pk-prioritize-filters — Include frequently filtered columns
schema-pk-filter-on-orderby — Query filters must use ORDER BY prefix
Schema Design — Data Types (CRITICAL)
schema-types-native-types — Use native types, not String for everything
schema-types-minimize-bitwidth — Use smallest numeric type that fits
schema-types-lowcardinality — LowCardinality for <10K unique strings
schema-types-enum — Enum for finite value sets with validation
schema-types-avoid-nullable — Avoid Nullable; use DEFAULT instead
Schema Design — Partitioning (HIGH)
schema-partition-low-cardinality — Keep partition count 100-1,000
schema-partition-lifecycle — Use partitioning for data lifecycle, not queries
schema-partition-query-tradeoffs — Understand partition pruning trade-offs
schema-partition-start-without — Consider starting without partitioning
Schema Design — JSON (MEDIUM)
schema-json-when-to-use — JSON for dynamic schemas; typed columns for known
Query Optimization — JOINs (CRITICAL)
query-join-choose-algorithm — Select algorithm based on table sizes
query-join-use-any — ANY JOIN when only one match needed
query-join-filter-before — Filter tables before joining
query-join-consider-alternatives — Dictionaries/denormalization vs JOIN
query-join-null-handling — join_use_nulls=0 for default values
Query Optimization — Indices (HIGH)
query-index-skipping-indices — Skipping indices for non-ORDER BY filters
Query Optimization — Materialized Views (HIGH)
query-mv-incremental — Incremental MVs for real-time aggregations
query-mv-refreshable — Refreshable MVs for complex joins
Insert Strategy — Batching (CRITICAL)
insert-batch-size — Batch 10K-100K rows per INSERT
Insert Strategy — Async (HIGH)
insert-async-small-batches — Async inserts for high-frequency small batches
insert-format-native — Native format for best performance
Insert Strategy — Mutations (CRITICAL)
insert-mutation-avoid-update — ReplacingMergeTree instead of ALTER UPDATE
insert-mutation-avoid-delete — Lightweight DELETE or DROP PARTITION
Insert Strategy — Optimization (HIGH)
insert-optimize-avoid-final — Let background merges work
Quick Decision Guides
Which Table Engine?
Need to store data?
├── < 1M rows, dimension → Memory
└── ≥ 1M rows → MergeTree family
├── Deduplication? → ReplacingMergeTree(version)
├── Changelog? → CollapsingMergeTree(sign)
├── Pre-aggregation? → AggregatingMergeTree()
├── Replication? → ReplicatedMergeTree(...)
└── Default → MergeTree()
See references/table-engines.md for complete reference.
Common Issues & Quick Fixes
| Issue |
Quick Fix |
| Too many parts |
OPTIMIZE TABLE table FINAL (see insert-optimize-avoid-final) |
| Slow query |
EXPLAIN SELECT ... to check index usage |
| Mutation stuck |
Check system.mutations, consider alternatives per insert-mutation-avoid-update |
| Replication lag |
Check system.replication_queue, ZooKeeper |
| OOM on query |
Increase max_memory_usage, optimize query |
See references/debugging.md for detailed troubleshooting.
Deep Reference Files
For topics beyond the 28 rules, see the references/ directory:
Schema & Table Design
references/core-concepts.md — Architecture, data model, internals
references/schema-design.md — Database engines, migrations, version control
references/table-design.md — ORDER BY, partitioning, column selection
references/table-engines.md — Complete MergeTree family reference
Query & Performance
references/sql-reference.md — Complete SQL dialect, data types
references/query-optimization.md — EXPLAIN, JOINs, projections, skip indexes
references/advanced-features.md — Materialized views, mutations, TTL, dictionaries
Operations & Cluster
references/debugging.md — Query debugging, merges, mutations, replication
references/cluster-management.md — Distributed tables, replication, sharding
references/backup-restore.md — Backup strategies, disaster recovery
references/monitoring.md — Query monitoring, health checks, system queries
Integration & Best Practices
references/integrations.md — Kafka, S3, PostgreSQL, MySQL, BI tools
references/best-practices.md — Complete checklist and anti-patterns
references/external.md — Altinity KB links, official docs
references/system-queries.md — Ready-to-use queries for operations
Version: 1.4.0
Rules: Synced with ClickHouse/agent-skills (Apache-2.0)
References: Altinity Knowledge Base (200+ articles) + ClickHouse Official Docs
1---
2name: clickhouse
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.
4metadata:
5 source: https://skillmd.com/skills/duyet/clickhouse
6---
7
8# ClickHouse Best Practices
9
10Guidance for ClickHouse covering schema design, query optimization, and data ingestion. Contains 28 atomic rules across 3 categories (schema, query, insert), prioritized by impact. Extended with 15 reference files covering cluster management, backups, monitoring, and integrations.
11
12> **Official docs:** [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices)
13
14---
15
16## ⚠️ Security Considerations
17
18### Credential Placeholders
19Example credentials in documentation (`password123`, `AKIAIOSFODNN7EXAMPLE`) are placeholders only. Never use these in production. Use proper secret management:
20- Environment variables
21- Secret managers (AWS Secrets Manager, HashiCorp Vault, etc.)
22- Kubernetes secrets (for K8s deployments)
23- ClickHouse named collections with external configuration
24
25### Installation & Operations
26For installation and operational procedures:
27- Follow official documentation links provided in reference files
28- Prefer package managers (`apt`, `yum`, `helm`) over direct downloads
29- Use versioned artifacts instead of `latest` in production
30- Test procedures in non-production environments first
31
32---
33
34## IMPORTANT: How to Apply This Skill
35
36**Before answering ClickHouse questions, follow this priority order:**
37
381. **Check for applicable rules** in the `rules/` directory
392. **If rules exist:** Apply them and cite them in your response using "Per `rule-name`..."
403. **If no rule exists:** Check `references/` for deeper topic coverage
414. **If neither covers it:** Use general ClickHouse knowledge or search documentation
425. **Always cite your source:** rule name, reference file, or URL
43
44**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.
45
46---
47
48## Review Procedures
49
50### For Schema Reviews (CREATE TABLE, ALTER TABLE)
51
52**Read these rule files in order:**
53
541. `rules/schema-pk-plan-before-creation.md` — ORDER BY is immutable
552. `rules/schema-pk-cardinality-order.md` — Column ordering in keys
563. `rules/schema-pk-prioritize-filters.md` — Filter column inclusion
574. `rules/schema-pk-filter-on-orderby.md` — Query filter alignment
585. `rules/schema-types-native-types.md` — Proper type selection
596. `rules/schema-types-minimize-bitwidth.md` — Numeric type sizing
607. `rules/schema-types-lowcardinality.md` — LowCardinality usage
618. `rules/schema-types-avoid-nullable.md` — Nullable vs DEFAULT
629. `rules/schema-types-enum.md` — Enum for finite value sets
6310. `rules/schema-partition-low-cardinality.md` — Partition count limits
6411. `rules/schema-partition-lifecycle.md` — Partitioning purpose
6512. `rules/schema-partition-query-tradeoffs.md` — Partition pruning trade-offs
6613. `rules/schema-partition-start-without.md` — Start without partitioning
6714. `rules/schema-json-when-to-use.md` — JSON type usage
68
69**Check for:**
70- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)
71- [ ] Data types match actual data ranges
72- [ ] LowCardinality applied to appropriate string columns
73- [ ] Partition key cardinality bounded (100-1,000 values)
74- [ ] ReplacingMergeTree has version column if used
75
76### For Query Reviews (SELECT, JOIN, aggregations)
77
78**Read these rule files:**
79
801. `rules/query-join-choose-algorithm.md` — Algorithm selection
812. `rules/query-join-use-any.md` — ANY vs regular JOIN
823. `rules/query-join-filter-before.md` — Pre-join filtering
834. `rules/query-join-consider-alternatives.md` — Dictionaries/denormalization
845. `rules/query-join-null-handling.md` — join_use_nulls setting
856. `rules/query-index-skipping-indices.md` — Secondary index usage
867. `rules/query-mv-incremental.md` — Incremental materialized views
878. `rules/query-mv-refreshable.md` — Refreshable materialized views
88
89**Check for:**
90- [ ] Filters use ORDER BY prefix columns
91- [ ] JOINs filter tables before joining (not after)
92- [ ] Correct JOIN algorithm for table sizes
93- [ ] Skipping indices for non-ORDER BY filter columns
94
95### For Insert Strategy Reviews (data ingestion, updates, deletes)
96
97**Read these rule files:**
98
991. `rules/insert-batch-size.md` — Batch sizing requirements
1002. `rules/insert-async-small-batches.md` — Async insert usage
1013. `rules/insert-format-native.md` — Native format for performance
1024. `rules/insert-mutation-avoid-update.md` — UPDATE alternatives
1035. `rules/insert-mutation-avoid-delete.md` — DELETE alternatives
1046. `rules/insert-optimize-avoid-final.md` — OPTIMIZE TABLE risks
105
106**Check for:**
107- [ ] Batch size 10K-100K rows per INSERT
108- [ ] No ALTER TABLE UPDATE for frequent changes
109- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns
110- [ ] Async inserts enabled for high-frequency small batches
111
112---
113
114## Output Format
115
116Structure review responses as follows:
117
118```
119## Rules Checked
120- `rule-name-1` — Compliant / Violation found
121- `rule-name-2` — Compliant / Violation found
122...
123
124## Findings
125
126### Violations
127- **`rule-name`**: Description of the issue
128 - Current: [what the code does]
129 - Required: [what it should do]
130 - Fix: [specific correction]
131
132### Compliant
133- `rule-name`: Brief note on why it's correct
134
135## Recommendations
136[Prioritized list of changes, citing rules]
137```
138
139---
140
141## Rule Categories by Priority
142
143| Priority | Category | Impact | Prefix | Count |
144|----------|----------|--------|--------|-------|
145| 1 | Primary Key Selection | CRITICAL | `schema-pk-` | 4 |
146| 2 | Data Type Selection | CRITICAL | `schema-types-` | 5 |
147| 3 | JOIN Optimization | CRITICAL | `query-join-` | 5 |
148| 4 | Insert Batching | CRITICAL | `insert-batch-` | 1 |
149| 5 | Mutation Avoidance | CRITICAL | `insert-mutation-` | 2 |
150| 6 | Partitioning Strategy | HIGH | `schema-partition-` | 4 |
151| 7 | Skipping Indices | HIGH | `query-index-` | 1 |
152| 8 | Materialized Views | HIGH | `query-mv-` | 2 |
153| 9 | Async Inserts | HIGH | `insert-async-` | 2 |
154| 10 | OPTIMIZE Avoidance | HIGH | `insert-optimize-` | 1 |
155| 11 | JSON Usage | MEDIUM | `schema-json-` | 1 |
156
157---
158
159## Quick Reference
160
161### Schema Design — Primary Key (CRITICAL)
162
163- `schema-pk-plan-before-creation` — Plan ORDER BY before table creation (immutable)
164- `schema-pk-cardinality-order` — Order columns low-to-high cardinality
165- `schema-pk-prioritize-filters` — Include frequently filtered columns
166- `schema-pk-filter-on-orderby` — Query filters must use ORDER BY prefix
167
168### Schema Design — Data Types (CRITICAL)
169
170- `schema-types-native-types` — Use native types, not String for everything
171- `schema-types-minimize-bitwidth` — Use smallest numeric type that fits
172- `schema-types-lowcardinality` — LowCardinality for <10K unique strings
173- `schema-types-enum` — Enum for finite value sets with validation
174- `schema-types-avoid-nullable` — Avoid Nullable; use DEFAULT instead
175
176### Schema Design — Partitioning (HIGH)
177
178- `schema-partition-low-cardinality` — Keep partition count 100-1,000
179- `schema-partition-lifecycle` — Use partitioning for data lifecycle, not queries
180- `schema-partition-query-tradeoffs` — Understand partition pruning trade-offs
181- `schema-partition-start-without` — Consider starting without partitioning
182
183### Schema Design — JSON (MEDIUM)
184
185- `schema-json-when-to-use` — JSON for dynamic schemas; typed columns for known
186
187### Query Optimization — JOINs (CRITICAL)
188
189- `query-join-choose-algorithm` — Select algorithm based on table sizes
190- `query-join-use-any` — ANY JOIN when only one match needed
191- `query-join-filter-before` — Filter tables before joining
192- `query-join-consider-alternatives` — Dictionaries/denormalization vs JOIN
193- `query-join-null-handling` — join_use_nulls=0 for default values
194
195### Query Optimization — Indices (HIGH)
196
197- `query-index-skipping-indices` — Skipping indices for non-ORDER BY filters
198
199### Query Optimization — Materialized Views (HIGH)
200
201- `query-mv-incremental` — Incremental MVs for real-time aggregations
202- `query-mv-refreshable` — Refreshable MVs for complex joins
203
204### Insert Strategy — Batching (CRITICAL)
205
206- `insert-batch-size` — Batch 10K-100K rows per INSERT
207
208### Insert Strategy — Async (HIGH)
209
210- `insert-async-small-batches` — Async inserts for high-frequency small batches
211- `insert-format-native` — Native format for best performance
212
213### Insert Strategy — Mutations (CRITICAL)
214
215- `insert-mutation-avoid-update` — ReplacingMergeTree instead of ALTER UPDATE
216- `insert-mutation-avoid-delete` — Lightweight DELETE or DROP PARTITION
217
218### Insert Strategy — Optimization (HIGH)
219
220- `insert-optimize-avoid-final` — Let background merges work
221
222---
223
224## Quick Decision Guides
225
226### Which Table Engine?
227
228```
229Need to store data?
230├── < 1M rows, dimension → Memory
231└── ≥ 1M rows → MergeTree family
232 ├── Deduplication? → ReplacingMergeTree(version)
233 ├── Changelog? → CollapsingMergeTree(sign)
234 ├── Pre-aggregation? → AggregatingMergeTree()
235 ├── Replication? → ReplicatedMergeTree(...)
236 └── Default → MergeTree()
237```
238
239See `references/table-engines.md` for complete reference.
240
241### Common Issues & Quick Fixes
242
243| Issue | Quick Fix |
244|-------|-----------|
245| Too many parts | `OPTIMIZE TABLE table FINAL` (see `insert-optimize-avoid-final`) |
246| Slow query | `EXPLAIN SELECT ...` to check index usage |
247| Mutation stuck | Check `system.mutations`, consider alternatives per `insert-mutation-avoid-update` |
248| Replication lag | Check `system.replication_queue`, ZooKeeper |
249| OOM on query | Increase `max_memory_usage`, optimize query |
250
251See `references/debugging.md` for detailed troubleshooting.
252
253---
254
255## Deep Reference Files
256
257For topics beyond the 28 rules, see the `references/` directory:
258
259### Schema & Table Design
260- `references/core-concepts.md` — Architecture, data model, internals
261- `references/schema-design.md` — Database engines, migrations, version control
262- `references/table-design.md` — ORDER BY, partitioning, column selection
263- `references/table-engines.md` — Complete MergeTree family reference
264
265### Query & Performance
266- `references/sql-reference.md` — Complete SQL dialect, data types
267- `references/query-optimization.md` — EXPLAIN, JOINs, projections, skip indexes
268- `references/advanced-features.md` — Materialized views, mutations, TTL, dictionaries
269
270### Operations & Cluster
271- `references/debugging.md` — Query debugging, merges, mutations, replication
272- `references/cluster-management.md` — Distributed tables, replication, sharding
273- `references/backup-restore.md` — Backup strategies, disaster recovery
274- `references/monitoring.md` — Query monitoring, health checks, system queries
275
276### Integration & Best Practices
277- `references/integrations.md` — Kafka, S3, PostgreSQL, MySQL, BI tools
278- `references/best-practices.md` — Complete checklist and anti-patterns
279- `references/external.md` — Altinity KB links, official docs
280- `references/system-queries.md` — Ready-to-use queries for operations
281
282---
283
284**Version**: 1.4.0
285**Rules**: Synced with [ClickHouse/agent-skills](https://github.com/ClickHouse/agent-skills) (Apache-2.0)
286**References**: Altinity Knowledge Base (200+ articles) + ClickHouse Official Docs