clickhouse-best-practices

Name: clickhouse-best-practices
Author: ClickHouse

6.8kinstalls32forks

How to install clickhouse-best-practices?

clickhouse/agent-skills/clickhouse-best-practices

$npx -y skills add clickhouse/agent-skills --skill clickhouse-best-practices

Installs into the current project.

Summary

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.

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Best Practices
2
3	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.
4
5	> Official docs: [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices)
6
7	## IMPORTANT: How to Apply This Skill
8
9	Before answering ClickHouse questions, follow this priority order:
10
11	1. Check for applicable rules in the `rules/` directory
12	2. If rules exist: Apply them and cite them in your response using "Per `rule-name`..."
13	3. If no rule exists: Use the LLM's ClickHouse knowledge or search documentation
14	4. If uncertain: Use web search for current best practices
15	5. Always cite your source: rule name, "general ClickHouse guidance", or URL
16
17	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.
18
19	---
20
21	## Agent Connectivity & Query Workflow
22
23	Before querying ClickHouse, agents must establish a connection and follow the discovery workflow:
24
25	1. `rules/agent-connect-mcp.md` - Connection setup (MCP + CLI), credential discovery, output format selection
26	2. `rules/agent-discovery-schema.md` - CRITICAL: 7-step schema discovery workflow
27	3. `rules/agent-query-safety.md` - CRITICAL: LIMIT, timeouts, progressive exploration
28
29	Every agent session should follow this sequence:
30
31	1. Connect — establish connection via MCP or CLI (see `agent-connect-mcp`)
32	2. Discover — databases → tables → columns + comments → sort keys → skip indexes → sample → EXPLAIN
33	3. Plan — use sort key and skip index knowledge to write efficient WHERE clauses
34	4. Execute — run queries with LIMIT and timeouts
35	5. Recover — on timeout/memory errors, narrow filters and retry (see `agent-query-safety`)
36
37	### Subagent architecture notes
38
39	If your system dispatches ClickHouse tasks to specialized subagents:
40	- Schema discovery + query execution: any model — the steps are procedural
41	- EXPLAIN analysis + query optimization: benefits from mid-tier reasoning
42	- Schema design review against all 28 rules: benefits from mid-tier reasoning
43
44	---
45
46	## Review Procedures
47
48	### For Schema Reviews (CREATE TABLE, ALTER TABLE)
49
50	Read these rule files in order:
51
52	1. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable
53	2. `rules/schema-pk-cardinality-order.md` - Column ordering in keys
54	3. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion
55	4. `rules/schema-types-native-types.md` - Proper type selection
56	5. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing
57	6. `rules/schema-types-lowcardinality.md` - LowCardinality usage
58	7. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT
59	8. `rules/schema-partition-low-cardinality.md` - Partition count limits
60	9. `rules/schema-partition-lifecycle.md` - Partitioning purpose
61
62	Check for:
63	- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)
64	- [ ] Data types match actual data ranges
65	- [ ] LowCardinality applied to appropriate string columns
66	- [ ] Partition key cardinality bounded (100-1,000 values)
67	- [ ] ReplacingMergeTree has version column if used
68
69	### For Query Reviews (SELECT, JOIN, aggregations)
70
71	Read these rule files:
72
73	1. `rules/query-join-choose-algorithm.md` - Algorithm selection
74	2. `rules/query-join-filter-before.md` - Pre-join filtering
75	3. `rules/query-join-use-any.md` - ANY vs regular JOIN
76	4. `rules/query-index-skipping-indices.md` - Secondary index usage
77	5. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY
78
79	Check for:
80	- [ ] Filters use ORDER BY prefix columns
81	- [ ] JOINs filter tables before joining (not after)
82	- [ ] Correct JOIN algorithm for table sizes
83	- [ ] Skipping indices for non-ORDER BY filter columns
84
85	### For Insert Strategy Reviews (data ingestion, updates, deletes)
86
87	Read these rule files:
88
89	1. `rules/insert-batch-size.md` - Batch sizing requirements
90	2. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives
91	3. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives
92	4. `rules/insert-async-small-batches.md` - Async insert usage
93	5. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks
94
95	Check for:
96	- [ ] Batch size 10K-100K rows per INSERT
97	- [ ] No ALTER TABLE UPDATE for frequent changes
98	- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns
99	- [ ] Async inserts enabled for high-frequency small batches
100
101	---
102
103	## Output Format
104
105	Structure your response as follows:
106
107	```

People who liked this also viewed

6 picks

Type

clickhouse-best-practices

6.8kinstalls32forks

View on GitHub

How to install clickhouse-best-practices?

clickhouse/agent-skills/clickhouse-best-practices

$npx -y skills add clickhouse/agent-skills --skill clickhouse-best-practices

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Best Practices
2
3	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.
4
5	> Official docs: [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices)
6
7	## IMPORTANT: How to Apply This Skill
8
9	Before answering ClickHouse questions, follow this priority order:
10
11	1. Check for applicable rules in the `rules/` directory
12	2. If rules exist: Apply them and cite them in your response using "Per `rule-name`..."
13	3. If no rule exists: Use the LLM's ClickHouse knowledge or search documentation
14	4. If uncertain: Use web search for current best practices
15	5. Always cite your source: rule name, "general ClickHouse guidance", or URL
16
17	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.
18
19	---
20
21	## Agent Connectivity & Query Workflow
22
23	Before querying ClickHouse, agents must establish a connection and follow the discovery workflow:
24
25	1. `rules/agent-connect-mcp.md` - Connection setup (MCP + CLI), credential discovery, output format selection
26	2. `rules/agent-discovery-schema.md` - CRITICAL: 7-step schema discovery workflow
27	3. `rules/agent-query-safety.md` - CRITICAL: LIMIT, timeouts, progressive exploration
28
29	Every agent session should follow this sequence:
30
31	1. Connect — establish connection via MCP or CLI (see `agent-connect-mcp`)
32	2. Discover — databases → tables → columns + comments → sort keys → skip indexes → sample → EXPLAIN
33	3. Plan — use sort key and skip index knowledge to write efficient WHERE clauses
34	4. Execute — run queries with LIMIT and timeouts
35	5. Recover — on timeout/memory errors, narrow filters and retry (see `agent-query-safety`)
36
37	### Subagent architecture notes
38
39	If your system dispatches ClickHouse tasks to specialized subagents:
40	- Schema discovery + query execution: any model — the steps are procedural
41	- EXPLAIN analysis + query optimization: benefits from mid-tier reasoning
42	- Schema design review against all 28 rules: benefits from mid-tier reasoning
43
44	---
45
46	## Review Procedures
47
48	### For Schema Reviews (CREATE TABLE, ALTER TABLE)
49
50	Read these rule files in order:
51
52	1. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable
53	2. `rules/schema-pk-cardinality-order.md` - Column ordering in keys
54	3. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion
55	4. `rules/schema-types-native-types.md` - Proper type selection
56	5. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing
57	6. `rules/schema-types-lowcardinality.md` - LowCardinality usage
58	7. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT
59	8. `rules/schema-partition-low-cardinality.md` - Partition count limits
60	9. `rules/schema-partition-lifecycle.md` - Partitioning purpose
61
62	Check for:
63	- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)
64	- [ ] Data types match actual data ranges
65	- [ ] LowCardinality applied to appropriate string columns
66	- [ ] Partition key cardinality bounded (100-1,000 values)
67	- [ ] ReplacingMergeTree has version column if used
68
69	### For Query Reviews (SELECT, JOIN, aggregations)
70
71	Read these rule files:
72
73	1. `rules/query-join-choose-algorithm.md` - Algorithm selection
74	2. `rules/query-join-filter-before.md` - Pre-join filtering
75	3. `rules/query-join-use-any.md` - ANY vs regular JOIN
76	4. `rules/query-index-skipping-indices.md` - Secondary index usage
77	5. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY
78
79	Check for:
80	- [ ] Filters use ORDER BY prefix columns
81	- [ ] JOINs filter tables before joining (not after)
82	- [ ] Correct JOIN algorithm for table sizes
83	- [ ] Skipping indices for non-ORDER BY filter columns
84
85	### For Insert Strategy Reviews (data ingestion, updates, deletes)
86
87	Read these rule files:
88
89	1. `rules/insert-batch-size.md` - Batch sizing requirements
90	2. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives
91	3. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives
92	4. `rules/insert-async-small-batches.md` - Async insert usage
93	5. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks
94
95	Check for:
96	- [ ] Batch size 10K-100K rows per INSERT
97	- [ ] No ALTER TABLE UPDATE for frequent changes
98	- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns
99	- [ ] Async inserts enabled for high-frequency small batches
100
101	---
102
103	## Output Format
104
105	Structure your response as follows:
106
107	```

People who liked this also viewed

6 picks

Type