Clickhouse Best Practices

---
name: clickhouse-best-practices
description: MUST USE when reviewing ClickHouse schemas, queries, or configurations. Contains 28 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.3.0"
---

# 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](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.

## Langfuse-Specific Rules

- Use `packages/shared/src/server/queries/clickhouse-sql/event-query-builder.ts`
  for queries against the `events` table. Do not hand-roll `events` SQL unless
  you first confirm the query builder cannot express the query.
- Never use `FINAL` on the `events` table; it is designed so `FINAL` is not
  required and the keyword hurts performance.
- ClickHouse query attribution is stored in `system.query_log.log_comment` as
  JSON from `packages/shared/src/server/clickhouse/queryTags.ts`. Parse it with
  `JSONExtractString(log_comment, 'surface')`,
  `JSONExtractString(log_comment, 'route')`, and
  `JSONExtractString(log_comment, 'projectId')`. Known `surface` values are
  `trpc`, `publicapi`, `worker`, `mcp`, and `unknown`; ClickhouseWriter inserts
  use `projectId = "MULTI_PROJECT"`.
- Query attribution is propagated through OpenTelemetry baggage. Entry points
  call `contextWithLangfuseProps(...)` from
  `packages/shared/src/server/headerPropagation.ts`, setting ClickHouse
  `surface`, optional `route`, and optional `projectId`. The ClickHouse
  repository layer then reads baggage via `normalizeClickHouseQueryTags(...)`
  and writes it to `log_comment`. Prefer setting attribution at entry points
  rather than passing tags through every repository call.
- Any migration in `packages/shared/clickhouse/migrations/clustered/**` with
  more than one `ALTER` on the same table must end every metadata `ALTER`
  (`ADD/DROP/MODIFY COLUMN`, `ADD/DROP INDEX`) with `SETTINGS alter_sync = 2`,
  and every mutation-creating `ALTER` (`MATERIALIZE …`, `UPDATE`, `DELETE`)
  with `SETTINGS mutations_sync = 2`. The matching `unclustered/` file runs
  against plain `MergeTree` and does not need (and should not duplicate)
  these settings.
- Never use `CREATE OR REPLACE VIEW` (nor `CREATE OR REPLACE TABLE` /
  `EXCHANGE TABLES`) in ClickHouse migrations. The atomic replace requires
  `renameat2` filesystem support, which NFS-backed self-hosted deployments
  (e.g. ClickHouse data on AWS EFS) lack — the migration fails and the
  deployment aborts on startup (GitHub issue #14906). Redefine a plain view as
  two statements in the same migration file: `DROP VIEW IF EXISTS <name>
  [ON CLUSTER default];` then `CREATE VIEW <name> [ON CLUSTER default] AS …`.
  The migration runner passes `x-multi-statement=true` and golang-migrate
  splits files on `;` without parsing SQL, so keep semicolons out of comments
  and string literals. Keep every statement idempotent
  (`IF EXISTS`/`IF NOT EXISTS`) so a dirty, half-applied migration can be
  re-run after `migrate force`. Readers hitting the view inside the
  drop→create window fail transiently — acceptable for the `analytics_*`
  export views, so keep plain views off product hot paths.
- Never drop-and-recreate a materialized view whose source table receives live
  inserts: every row inserted between `DROP` and `CREATE` is silently and
  permanently missing from the target table. Change an MV's SELECT with
  `ALTER TABLE <mv> [ON CLUSTER default] MODIFY QUERY <select>`, which swaps
  the transformation without interrupting ingestion. When the change adds
  columns, `ALTER` the target table(s) first (`ADD COLUMN IF NOT EXISTS …`),
  then `MODIFY QUERY`; in `clustered/` files those target-table `ALTER`s must
  carry `SETTINGS alter_sync = 2` so no host applies the new MV query before
  its target replica has the new columns. `MODIFY QUERY` is only viable for
  TO-table MVs (all Langfuse MVs use `TO`).

---

## 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
- [ ] Clustered migration files with multiple ALTERs on the same table use `SETTINGS alter_sync = 2` (metadata) and `SETTINGS mutations_sync = 2` (`MATERIALIZE …`, `UPDATE`, `DELETE`); unclustered mirror has none
- [ ] No `CREATE OR REPLACE VIEW/TABLE` or `EXCHANGE TABLES` in migrations (breaks NFS/EFS self-hosting); plain views are redefined via `DROP VIEW IF EXISTS` + `CREATE VIEW` in the same file
- [ ] Materialized views are never dropped and recreated while their source table takes inserts; SELECT changes go through `ALTER TABLE <mv> MODIFY QUERY` after the target-table `ALTER`s

### 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]

### 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