clickhouse-best-practices

ClickHouse Best Practices

Comprehensive guidance for ClickHouse covering schema design, query optimization, and data ingestion. Contains 28 rules across 3 main categories (schema, query, insert), 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.

For Formal Reviews

When performing a formal review of schemas, queries, or data ingestion:

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 immutable
rules/schema_pk_cardinality_order.md - Column ordering in keys
rules/schema_pk_prioritize_filters.md - Filter column inclusion
rules/schema_types_native_types.md - Proper type selection
rules/schema_types_minimize_bitwidth.md - Numeric type sizing
rules/schema_types_lowcardinality.md - LowCardinality usage
rules/schema_types_avoid_nullable.md - Nullable vs DEFAULT
rules/schema_partition_low_cardinality.md - Partition count limits
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:

rules/query_join_choose_algorithm.md - Algorithm selection
rules/query_join_filter_before.md - Pre-join filtering
rules/query_join_use_any.md - ANY vs regular JOIN
rules/query_index_skipping_indices.md - Secondary index usage
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:

rules/insert_batch_size.md - Batch sizing requirements
rules/insert_mutation_avoid_update.md - UPDATE alternatives
rules/insert_mutation_avoid_delete.md - DELETE alternatives
rules/insert_async_small_batches.md - Async insert usage
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	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

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 - Skipping indices for non-ORDER BY filters

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

When to Apply

This skill activates when you encounter:

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.

01 前言 Front Matter

03 SKILL.md 正文