clickhouse-best-practices

Files (37)

• AGENTS.md
• README.md
• SKILL.md
• metadata.json
• _sections.md · rules/
• _template.md · rules/
• agent-connect-mcp.md · rules/
• agent-discovery-schema.md · rules/
• agent-query-safety.md · rules/
• insert-async-small-batches.md · rules/
• insert-batch-size.md · rules/
• insert-format-native.md · rules/
• insert-mutation-avoid-delete.md · rules/
• insert-mutation-avoid-update.md · rules/
• insert-optimize-avoid-final.md · rules/
• query-index-skipping-indices.md · rules/
• query-join-choose-algorithm.md · rules/
• query-join-consider-alternatives.md · rules/
• query-join-filter-before.md · rules/
• query-join-null-handling.md · rules/
• query-join-use-any.md · rules/
• query-mv-incremental.md · rules/
• query-mv-refreshable.md · rules/
• schema-json-when-to-use.md · rules/
• schema-partition-lifecycle.md · rules/
• schema-partition-low-cardinality.md · rules/
• schema-partition-query-tradeoffs.md · rules/
• schema-partition-start-without.md · rules/
• schema-pk-cardinality-order.md · rules/
• schema-pk-filter-on-orderby.md · rules/
• schema-pk-plan-before-creation.md · rules/
• schema-pk-prioritize-filters.md · rules/
• schema-types-avoid-nullable.md · rules/
• schema-types-enum.md · rules/
• schema-types-lowcardinality.md · rules/
• schema-types-minimize-bitwidth.md · rules/
• schema-types-native-types.md · rules/

SKILL.md (first lines)

# 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](https://clickhouse.com/docs/best-practices)

## IMPORTANT: How to Apply This Skill

**Before answering ClickHouse questions, follow this priority order:**

1. **Check for applicable rules** in the `rules/` directory
2. **If rules exist:** Apply them and cite them in your response using "Per `rule-name`..."
3. **If no rule exists:** Use the LLM's ClickHouse knowledge or search documentation
4. **If uncertain:** Use web search for current best practices
5. **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:

1. `rules/agent-connect-mcp.md` - Connection setup (MCP + CLI), credential discovery, output format selection
2. `rules/agent-discovery-schema.md` - **CRITICAL**: 7-step schema discovery workflow
3. `rules/agent-query-safety.md` - **CRITICAL**: LIMIT, timeouts, progressive exploration

**Every agent session should follow this sequence:**

1. **Connect** — establish connection via MCP or CLI (see `agent-connect-mcp`)
2. **Discover** — databases → tables → columns + comments → sort keys → skip indexes → sample → EXPLAIN
3. **Plan** — use sort key and skip index knowledge to write efficient WHERE clauses
4. **Execute** — run queries with LIMIT and timeouts
5. **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:**

1. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable
2. `rules/schema-pk-cardinality-order.md` - Column ordering in keys
3. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion
4. `rules/schema-types-native-types.md` - Proper type selection
5. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing
6. `rules/schema-types-lowcardinality.md` - LowCardinality usage
7. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT
8. `rules/schema-partition-low-cardinality.md` - Partition count limits
9. `rules/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:**

1. `rules/query-join-choose-algorithm.md` - Algorithm selection
2. `rules/query-join-filter-before.md` - Pre-join filtering
3. `rules/query-join-use-any.md` - ANY vs regular JOIN
4. `rules/query-index-skipping-indices.md` - Secondary index usage
5. `rules/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:**

1. `rules/insert-batch-size.md` - Batch sizing requirements
2. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives
3. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives
4. `rules/insert-async-small-batches.md` - Async insert usage
5. `rules/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]

From ClickHouse/agent-skills, Apache-2.0.

More from ClickHouse/agent-skills