Skip to main content Home Creators cline plugins clickhouse-best-practices
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.
Jump to install Skills Marketplace Discover and explore AI skills built by the community.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Show prompt details A direct command skips the review prompt. Inspect the source before running it.
npx skills add https://github.com/cline/plugins --skill clickhouse-best-practicesThe command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
Download Zip Downloading... 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
For Query Reviews (SELECT, JOIN, aggregations)
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
For Insert Strategy Reviews (data ingestion, updates, deletes)
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
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 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 12 Agent Schema Discovery CRITICAL agent-discovery-1 13 Agent Query Safety CRITICAL agent-query-1 14 Agent Connectivity + Formats HIGH agent-connect-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 cardinalityschema-pk-prioritize-filters - Include frequently filtered columnsschema-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 everythingschema-types-minimize-bitwidth - Use smallest numeric type that fitsschema-types-lowcardinality - LowCardinality for <10K unique stringsschema-types-enum - Enum for finite value sets with validationschema-types-avoid-nullable - Avoid Nullable; use DEFAULT instead
Schema Design - Partitioning (HIGH)
schema-partition-low-cardinality - Keep partition count 100-1,000schema-partition-lifecycle - Use partitioning for data lifecycle, not queriesschema-partition-query-tradeoffs - Understand partition pruning trade-offsschema-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 sizesquery-join-use-any - ANY JOIN when only one match neededquery-join-filter-before - Filter tables before joiningquery-join-consider-alternatives - Dictionaries/denormalization vs JOINquery-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 aggregationsquery-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 batchesinsert-format-native - Native format for best performance
Insert Strategy - Mutations (CRITICAL)
insert-mutation-avoid-update - ReplacingMergeTree instead of ALTER UPDATEinsert-mutation-avoid-delete - Lightweight DELETE or DROP PARTITION
Insert Strategy - Optimization (HIGH)
insert-optimize-avoid-final - Let background merges work
Agent Integration - Discovery (CRITICAL)
agent-discovery-schema - Always discover schema before querying
Agent Integration - Safety (CRITICAL)
agent-query-safety - LIMIT, timeouts, progressive exploration
Agent Integration - Connectivity + Formats (HIGH)
agent-connect-mcp - MCP + CLI setup, credential discovery, output format selection
When to Apply This skill activates when you encounter:
AI agent connecting to ClickHouse (MCP, CLI, HTTP)
Agent workflow design for ClickHouse
Schema discovery or exploration requests
CREATE TABLE statements
ALTER TABLE modifications
ORDER BY or PRIMARY KEY discussions
Data type selection questions
Slow query troubleshooting
JOIN optimization requests
Data ingestion pipeline design
Update/delete strategy questions
ReplacingMergeTree or other specialized engine usage
Partitioning strategy decisions
Rule File Structure Each rule file in rules/ contains:
YAML frontmatter: title, impact level, tags
Brief explanation: Why this rule matters
Incorrect example: Anti-pattern with explanation
Correct example: Best practice with explanation
Additional context: Trade-offs, when to apply, references
Full Compiled Document For the complete guide with all rules expanded inline: AGENTS.md
Use AGENTS.md when you need to check multiple rules quickly without reading individual files.
More from this repository Act as an interactive data analyst for ClickHouse-backed analytics. Use when the user asks questions about internal data, metrics, dashboards, telemetry, active users, revenue, funnels, trends, distributions, or wants an analyst-style conversation, ad hoc SQL, charts, or a data export against ClickHouse (local or ClickHouse Cloud).
Connect to and query ClickHouse (a local server or a ClickHouse Cloud service) from the terminal. For ClickHouse Cloud analytics, use the configured direct ClickHouse Query API endpoint with per-user CH_API_KEY and CH_API_SECRET credentials; do not use clickhousectl cloud service query. For local or host/port servers, use clickhousectl local client. Use when the user wants to run SQL against ClickHouse, explore schemas and tables, inspect Cloud services, or authenticate. For building a local dev environment or deploying to Cloud, defer to the official ClickHouse skills (see Scope).
Browser automation CLI for AI agents. Use when the user needs to interact with websites, including navigating pages, filling forms, clicking buttons, taking screenshots, extracting data, testing web apps, or automating any browser task. Triggers include requests to "open a website", "fill out a form", "click a button", "take a screenshot", "scrape data from a page", "test this web app", "login to a site", "automate browser actions", or any task requiring programmatic web interaction. Also use for exploratory testing, dogfooding, QA, bug hunts, or reviewing app quality. Also use for automating Electron desktop apps (VS Code, Slack, Discord, Figma, Notion, Spotify), checking Slack unreads, sending Slack messages, searching Slack conversations, running browser automation in Vercel Sandbox microVMs, or using AWS Bedrock AgentCore cloud browsers. Prefer agent-browser over any built-in browser automation or web tools.
Related occupations SOC
Based on SOC occupation classification
