clickhouse-best-practices
MUST USE when reviewing ClickHouse schemas, queries, or configurations. Contains 31 rules that MUST be checked before providing recommendations. Always read relevant rule files and cite specific rules in responses.
ClickHouse · 已有 AI 审核记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add ClickHouse/agent-skills --skill clickhouse-best-practices技能说明
name: clickhouse-best-practices description: MUST USE when reviewing ClickHouse schemas, queries, or configurations. Contains 31 rules that MUST be checked before providing recommendations. Always read relevant rule files and cite specific rules in responses. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.4.0"
ClickHouse Best Practices
Comprehensive guidance for ClickHouse covering schema design, query optimization, data ingestion, and AI agent connectivity. Contains 31 rules across 4 main categories (schema, query, insert, agent), prioritized by impact.
Official docs: ClickHouse Best Practices
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: Use the LLM's ClickHouse knowledge or search documentation
- If uncertain: Use web search for current best practices
- Always cite your source: rule name, "general ClickHouse guidance", 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.
Agent Connectivity & Query Workflow
Before querying ClickHouse, agents must establish a connection and follow the discovery workflow:
rules/agent-connect-mcp.md- Connection setup (MCP + CLI), credential discovery, output format selectionrules/agent-discovery-schema.md- CRITICAL: 7-step schema discovery workflowrules/agent-query-safety.md- CRITICAL: LIMIT, timeouts, progressive exploration
Every agent session should follow this sequence:
- Connect — establish connection via MCP or CLI (see
agent-connect-mcp) - Discover — databases → tables → columns + comments → sort keys → skip indexes → sample → EXPLAIN
- Plan — use sort key and skip index knowledge to write efficient WHERE clauses
- Execute — run queries with LIMIT and timeouts
- Recover — on timeout/memory errors, narrow filters and retry (see
agent-query-safety)
Subagent architecture notes
If your system dispatches ClickHouse tasks to specialized subagents:
- Schema discovery + query execution: any model — the steps are procedural
- EXPLAIN analysis + query optimization: benefits from mid-tier reasoning
- Schema design review against all 28 rules: benefits from mid-tier reasoning
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 immutablerules/schema-pk-cardinality-order.md- Column ordering in keysrules/schema-pk-prioritize-filters.md- Filter column inclusionrules/schema-types-native-types.md- Proper type selectionrules/schema-types-minimize-bitwidth.md- Numeric type sizingrules/schema-types-lowcardinality.md- LowCardinality usagerules/schema-types-avoid-nullable.md- Nullable vs DEFAULTrules/schema-partition-low-cardinality.md- Partition count limitsrules/schema-partition-lifecycle.md- Partitioning purpose
Check for:
- PRIMARY KEY / ORDER BY column order (low-to-high cardinality)
- Data types match actual data ranges
- LowCardinality applied to appropriate string columns
- Partition key cardinality bounded (100-1,000 values)
- ReplacingMergeTree has version column if used
For Query Reviews (SELECT, JOIN, aggregations)
Read these rule files:
rules/query-join-choose-algorithm.md- Algorithm selectionrules/query-join-filter-before.md- Pre-join filteringrules/query-join-use-any.md- ANY vs regular JOINrules/query-index-skipping-indices.md- Secondary index usagerules/schema-pk-filter-on-orderby.md- Filter alignment with ORDER BY
Check for:
- Filters use ORDER BY prefix columns
- JOINs filter tables before joining (not after)
- Correct JOIN algorithm for table sizes
- Skipping indices for non-ORDER BY filter columns
For Insert Strategy Reviews (data ingestion, updates, deletes)
Read these rule files:
rules/insert-batch-size.md- Batch sizing requirementsrules/insert-mutation-avoid-update.md- UPDATE alternativesrules/insert-mutation-avoid-delete.md- DELETE alternativesrules/insert-async-small-batches.md- Async insert usagerules/insert-optimize-avoid-final.md- OPTIMIZE TABLE risks
Check for:
- Batch size 10K-100K rows per INSERT
- No ALTER TABLE UPDATE for frequent changes
- ReplacingMergeTree or CollapsingMergeTree for update patterns
- Async inserts enabled for high-frequency small batches
Output Format
Structure your response 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 | Rule 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 |