{"name":"clickhouse-best-practices","url":"https://skills.sh/clickhouse/agent-skills/clickhouse-best-practices","install":"npx skills add ClickHouse/agent-skills --skill clickhouse-best-practices","sdk":"clickhouse","key":"clickhouse/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.","hasContent":true,"content":"---\nname: clickhouse-best-practices\ndescription: 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.\nlicense: Apache-2.0\nmetadata:\n  author: ClickHouse Inc\n  version: \"0.4.0\"\n---\n\n# ClickHouse Best Practices\n\nComprehensive 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.\n\n> **Official docs:** [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices)\n\n## IMPORTANT: How to Apply This Skill\n\n**Before answering ClickHouse questions, follow this priority order:**\n\n1. **Check for applicable rules** in the `rules/` directory\n2. **If rules exist:** Apply them and cite them in your response using \"Per `rule-name`...\"\n3. **If no rule exists:** Use the LLM's ClickHouse knowledge or search documentation\n4. **If uncertain:** Use web search for current best practices\n5. **Always cite your source:** rule name, \"general ClickHouse guidance\", or URL\n\n**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.\n\n---\n\n## Agent Connectivity & Query Workflow\n\nBefore querying ClickHouse, agents must establish a connection and follow the discovery workflow:\n\n1. `rules/agent-connect-mcp.md` - Connection setup (MCP + CLI), credential discovery, output format selection\n2. `rules/agent-discovery-schema.md` - **CRITICAL**: 7-step schema discovery workflow\n3. `rules/agent-query-safety.md` - **CRITICAL**: LIMIT, timeouts, progressive exploration\n\n**Every agent session should follow this sequence:**\n\n1. **Connect** — establish connection via MCP or CLI (see `agent-connect-mcp`)\n2. **Discover** — databases → tables → columns + comments → sort keys → skip indexes → sample → EXPLAIN\n3. **Plan** — use sort key and skip index knowledge to write efficient WHERE clauses\n4. **Execute** — run queries with LIMIT and timeouts\n5. **Recover** — on timeout/memory errors, narrow filters and retry (see `agent-query-safety`)\n\n### Subagent architecture notes\n\nIf your system dispatches ClickHouse tasks to specialized subagents:\n- **Schema discovery + query execution**: any model — the steps are procedural\n- **EXPLAIN analysis + query optimization**: benefits from mid-tier reasoning\n- **Schema design review against all 28 rules**: benefits from mid-tier reasoning\n\n---\n\n## Review Procedures\n\n### For Schema Reviews (CREATE TABLE, ALTER TABLE)\n\n**Read these rule files in order:**\n\n1. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable\n2. `rules/schema-pk-cardinality-order.md` - Column ordering in keys\n3. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion\n4. `rules/schema-types-native-types.md` - Proper type selection\n5. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing\n6. `rules/schema-types-lowcardinality.md` - LowCardinality usage\n7. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT\n8. `rules/schema-partition-low-cardinality.md` - Partition count limits\n9. `rules/schema-partition-lifecycle.md` - Partitioning purpose\n\n**Check for:**\n- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)\n- [ ] Data types match actual data ranges\n- [ ] LowCardinality applied to appropriate string columns\n- [ ] Partition key cardinality bounded (100-1,000 values)\n- [ ] ReplacingMergeTree has version column if used\n\n### For Query Reviews (SELECT, JOIN, aggregations)\n\n**Read these rule files:**\n\n1. `rules/query-join-choose-algorithm.md` - Algorithm selection\n2. `rules/query-join-filter-before.md` - Pre-join filtering\n3. `rules/query-join-use-any.md` - ANY vs regular JOIN\n4. `rules/query-index-skipping-indices.md` - Secondary index usage\n5. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY\n\n**Check for:**\n- [ ] Filters use ORDER BY prefix columns\n- [ ] JOINs filter tables before joining (not after)\n- [ ] Correct JOIN algorithm for table sizes\n- [ ] Skipping indices for non-ORDER BY filter columns\n\n### For Insert Strategy Reviews (data ingestion, updates, deletes)\n\n**Read these rule files:**\n\n1. `rules/insert-batch-size.md` - Batch sizing requirements\n2. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives\n3. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives\n4. `rules/insert-async-small-batches.md` - Async insert usage\n5. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks\n\n**Check for:**\n- [ ] Batch size 10K-100K rows per INSERT\n- [ ] No ALTER TABLE UPDATE for frequent changes\n- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns\n- [ ] Async inserts enabled for high-frequency small batches\n\n---\n\n## Output Format\n\nStructure your response as follows:\n\n```\n## Rules Checked\n- `rule-name-1` - Compliant / Violation found\n- `rule-name-2` - Compliant / Violation found\n...\n\n## Findings\n\n### Violations\n- **`rule-name`**: Description of the issue\n  - Current: [what the code does]\n  - Required: [what it should do]\n  - Fix: [specific correction]\n\n### Compliant\n- `rule-name`: Brief note on why it's correct\n\n## Recommendations\n[Prioritized list of changes, citing rules]\n```\n\n---\n\n## Rule Categories by Priority\n\n| Priority | Category | Impact | Prefix | Rule Count |\n|----------|----------|--------|--------|------------|\n| 1 | Primary Key Selection | CRITICAL | `schema-pk-` | 4 |\n| 2 | Data Type Selection | CRITICAL | `schema-types-` | 5 |\n| 3 | JOIN Optimization | CRITICAL | `query-join-` | 5 |\n| 4 | Insert Batching | CRITICAL | `insert-batch-` | 1 |\n| 5 | Mutation Avoidance | CRITICAL | `insert-mutation-` | 2 |\n| 6 | Partitioning Strategy | HIGH | `schema-partition-` | 4 |\n| 7 | Skipping Indices | HIGH | `query-index-` | 1 |\n| 8 | Materialized Views | HIGH | `query-mv-` | 2 |\n| 9 | Async Inserts | HIGH | `insert-async-` | 2 |\n| 10 | OPTIMIZE Avoidance | HIGH | `insert-optimize-` | 1 |\n| 11 | JSON Usage | MEDIUM | `schema-json-` | 1 |\n| 12 | Agent Schema Discovery | CRITICAL | `agent-discovery-` | 1 |\n| 13 | Agent Query Safety | CRITICAL | `agent-query-` | 1 |\n| 14 | Agent Connectivity + Formats | HIGH | `agent-connect-` | 1 |\n\n---\n\n## Quick Reference\n\n### Schema Design - Primary Key (CRITICAL)\n\n- `schema-pk-plan-before-creation` - Plan ORDER BY before table creation (immutable)\n- `schema-pk-cardinality-order` - Order columns low-to-high cardinality\n- `schema-pk-prioritize-filters` - Include frequently filtered columns\n- `schema-pk-filter-on-orderby` - Query filters must use ORDER BY prefix\n\n### Schema Design - Data Types (CRITICAL)\n\n- `schema-types-native-types` - Use native types, not String for everything\n- `schema-types-minimize-bitwidth` - Use smallest numeric type that fits\n- `schema-types-lowcardinality` - LowCardinality for <10K unique strings\n- `schema-types-enum` - Enum for finite value sets with validation\n- `schema-types-avoid-nullable` - Avoid Nullable; use DEFAULT instead\n\n### Schema Design - Partitioning (HIGH)\n\n- `schema-partition-low-cardinality` - Keep partition count 100-1,000\n- `schema-partition-lifecycle` - Use partitioning for data lifecycle, not queries\n- `schema-partition-query-tradeoffs` - Understand partition pruning trade-offs\n- `schema-partition-start-without` - Consider starting without partitioning\n\n### Schema Design - JSON (MEDIUM)\n\n- `schema-json-when-to-use` - JSON for dynamic schemas; typed columns for known\n\n### Query Optimization - JOINs (CRITICAL)\n\n- `query-join-choose-algorithm` - Select algorithm based on table sizes\n- `query-join-use-any` - ANY JOIN when only one match needed\n- `query-join-filter-before` - Filter tables before joining\n- `query-join-consider-alternatives` - Dictionaries/denormalization vs JOIN\n- `query-join-null-handling` - join_use_nulls=0 for default values\n\n### Query Optimization - Indices (HIGH)\n\n- `query-index-skipping-indices` - Skipping indices for non-ORDER BY filters\n\n### Query Optimization - Materialized Views (HIGH)\n\n- `query-mv-incremental` - Incremental MVs for real-time aggregations\n- `query-mv-refreshable` - Refreshable MVs for complex joins\n\n### Insert Strategy - Batching (CRITICAL)\n\n- `insert-batch-size` - Batch 10K-100K rows per INSERT\n\n### Insert Strategy - Async (HIGH)\n\n- `insert-async-small-batches` - Async inserts for high-frequency small batches\n- `insert-format-native` - Native format for best performance\n\n### Insert Strategy - Mutations (CRITICAL)\n\n- `insert-mutation-avoid-update` - ReplacingMergeTree instead of ALTER UPDATE\n- `insert-mutation-avoid-delete` - Lightweight DELETE or DROP PARTITION\n\n### Insert Strategy - Optimization (HIGH)\n\n- `insert-optimize-avoid-final` - Let background merges work\n\n### Agent Integration - Discovery (CRITICAL)\n\n- `agent-discovery-schema` - Always discover schema before querying\n\n### Agent Integration - Safety (CRITICAL)\n\n- `agent-query-safety` - LIMIT, timeouts, progressive exploration\n\n### Agent Integration - Connectivity + Formats (HIGH)\n\n- `agent-connect-mcp` - MCP + CLI setup, credential discovery, output format selection\n\n---\n\n## When to Apply\n\nThis skill activates when you encounter:\n\n- AI agent connecting to ClickHouse (MCP, CLI, HTTP)\n- Agent workflow design for ClickHouse\n- Schema discovery or exploration requests\n\n- `CREATE TABLE` statements\n- `ALTER TABLE` modifications\n- `ORDER BY` or `PRIMARY KEY` discussions\n- Data type selection questions\n- Slow query troubleshooting\n- JOIN optimization requests\n- Data ingestion pipeline design\n- Update/delete strategy questions\n- ReplacingMergeTree or other specialized engine usage\n- Partitioning strategy decisions\n\n---\n\n## Rule File Structure\n\nEach rule file in `rules/` contains:\n\n- **YAML frontmatter**: title, impact level, tags\n- **Brief explanation**: Why this rule matters\n- **Incorrect example**: Anti-pattern with explanation\n- **Correct example**: Best practice with explanation\n- **Additional context**: Trade-offs, when to apply, references\n\n---\n\n## Full Compiled Document\n\nFor the complete guide with all rules expanded inline: `AGENTS.md`\n\nUse `AGENTS.md` when you need to check multiple rules quickly without reading individual files.\n","contentSource":"https://raw.githubusercontent.com/clickhouse/agent-skills/main/skills/clickhouse-best-practices/SKILL.md","contentFetchedAt":"2026-07-28T23:15:41.604Z"}