Index of every page on this site: llms.txt
All skills
clickhouse avatar

/clickhouse-best-practices

@19b3242 official
by clickhouseclickhouse/agent-skills572 stars
37
Run it yourself

MUST USE when reviewing ClickHouse schemas, queries, native full-text search, or configurations. Contains 36 rules that MUST be checked before providing recommendations. Always read relevant rule files and cite specific rules in responses.

  • 44 files
  • 211.7 KB
  • Apache-2
  • Updated 3 days ago
  • GitHub
Use this Skill: https://skilld.dev/gh/clickhouse/agent-skills/clickhouse-best-practices

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈67 tokens for metadata: the name and description. ≈3.1k when used: this file. ≈51k more on demand in 43 files.

Description uses 3.3% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈267 characters, or 3.3%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

ClickHouse Best Practices

Comprehensive guidance for ClickHouse covering schema design, query optimization, data ingestion, and AI agent connectivity. Contains 36 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:

  1. Check for applicable rules in the rules/ directory
  2. If rules exist: Apply rules that match the deployed ClickHouse version and cite them using "Per rule-name...". Verify evolving features against version-specific official documentation; do not override newer authoritative evidence with an older rule.
  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 relevant 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 Text Search and Native Text-Index Reviews

Read the relevant files directly:

  1. rules/query-index-text-native.md - Native postings, version baseline, granularity, legacy indexes
  2. rules/query-index-text-semantics.md - Full search operation map, correctness, phrases, and capability boundaries
  3. rules/query-index-text-analysis.md - Tokenizers, languages, normalization, stemming, and stop words
  4. rules/query-index-text-plan.md - Direct read, hints, fallback, and EXPLAIN evidence
  5. rules/query-index-text-lifecycle.md - Existing-part materialization and migration

For any full-text-search question, establish the search contract (tokens, substrings, phrases, language, fields, ranking), deployed version/build, and indexed expression. Native text indexes store row-ID postings: their large granularity does not restrict them to part-level decisions. Eligible predicates can be exact, candidate hints, or ordinary pruning; preserve the original match semantics.

Read these references for the relevant feature details:

  • Full feature and analysis guide — supported operations, data shapes, Map/JSON correlation, language tokenizers, parameters, settings, limitations, and operational choices.
  • Version and evidence ledger — release gates, stable versus development capabilities, and resolved source conflicts.

Do not promise native BM25/fuzzy ranking, universal language segmentation, same-element matches across arrays, phrase support for every tokenizer, or direct read for every supported predicate. Consult the operation map before designing a query. Check plan actions and historical-part coverage before recommending performance changes.

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]

### 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 Secondary and Text Indices HIGH query-index- 6
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 cardinality
  • schema-pk-prioritize-filters - Include frequently filtered columns
  • schema-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 everything
  • schema-types-minimize-bitwidth - Use smallest numeric type that fits
  • schema-types-lowcardinality - LowCardinality for <10K unique strings
  • schema-types-enum - Enum for finite value sets with validation
  • schema-types-avoid-nullable - Avoid Nullable; use DEFAULT instead

Schema Design - Partitioning (HIGH)

  • schema-partition-low-cardinality - Keep partition count 100-1,000
  • schema-partition-lifecycle - Use partitioning for data lifecycle, not queries
  • schema-partition-query-tradeoffs - Understand partition pruning trade-offs
  • schema-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 sizes
  • query-join-use-any - ANY JOIN when only one match needed
  • query-join-filter-before - Filter tables before joining
  • query-join-consider-alternatives - Dictionaries/denormalization vs JOIN
  • query-join-null-handling - join_use_nulls=0 for default values

Query Optimization - Indices (HIGH)

  • query-index-skipping-indices - Conventional skipping indices for non-ORDER BY filters
  • query-index-text-native - Native text index and posting-list behavior
  • query-index-text-semantics - Preserve token, substring, phrase, value, and field semantics
  • query-index-text-analysis - Choose language/tokenization and processing deliberately
  • query-index-text-plan - Verify direct read, hints, fallback, and actual reads
  • query-index-text-lifecycle - Materialize existing parts and plan index migrations

Query Optimization - Materialized Views (HIGH)

  • query-mv-incremental - Incremental MVs for real-time aggregations
  • query-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 batches
  • insert-format-native - Native format for best performance

Insert Strategy - Mutations (CRITICAL)

  • insert-mutation-avoid-update - ReplacingMergeTree instead of ALTER UPDATE
  • insert-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

  • Native full-text search, TYPE text, tokenizers, LIKE, or token-search predicates

  • 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.

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No third-party reports yet.

Signed by skilld at 19b3242. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub 3 days ago.

Activeupdated 3 days ago
metadata
{
  "author": "ClickHouse Inc",
  "version": "0.5.0"
}

README badge

README badge for clickhouse/agent-skills/clickhouse-best-practices

Provides 31 ClickHouse-specific rules organized by priority across schema design, query optimization, data ingestion, and agent connectivity. Use this skill to validate schemas, review queries, and establish safe agent workflows with proper connection setup, schema discovery, and query safety procedures.

Generated from the current SKILL.md.

When should I use this skill?
Use this skill when reviewing ClickHouse schemas, queries, or data ingestion strategies. It contains 31 rules covering primary key design, data types, JOINs, partitioning, and insert batching that you must check before providing ClickHouse recommendations.
Does this skill help with AI agent connectivity to ClickHouse?
Yes. The skill includes rules for MCP and CLI connection setup, schema discovery workflows, and query safety (LIMIT, timeouts, progressive exploration) specific to AI agents querying ClickHouse.
What should I do if a rule doesn't exist for my question?
Fall back to the LLM's ClickHouse knowledge, search the official ClickHouse documentation, or use web search. Always cite your source in the response.
Are the rules mandatory or advisory?
The rules are mandatory checks before answering ClickHouse questions. They encode ClickHouse-specific behaviors (columnar storage, merge tree mechanics, sparse indexes) where general database intuition can be misleading.
Can I use this skill for INSERT performance tuning?
Yes. The skill covers batch sizing (10K-100K rows), async inserts for high-frequency small batches, mutation avoidance (ReplacingMergeTree instead of ALTER UPDATE), and OPTIMIZE TABLE risks.

Generated from the current SKILL.md. These answers refresh after source changes.