# Clickhouse Best Practices

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

- Skill: `langfuse/clickhouse-best-practices` (Agent Skill, multi-file: 32 files)
- Install (CLI): `npx skillmds@latest add langfuse/clickhouse-best-practices`
- Raw SKILL.md: https://api.skillmd.com/api/skills/langfuse/clickhouse-best-practices/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: Apache-2.0
- Author: langfuse (https://skillmd.com/u/langfuse)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/langfuse/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](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.
- `packages/shared/clickhouse/migrations/canonical/**` is the single canonical
  template tree rendered for clustered and unclustered installs. Put
  `{CLICKHOUSE_CLUSTER_CLAUSE}` at every cluster-aware DDL position. Use
  `{CLICKHOUSE_REPLICATION_PREFIX}` only for engines that deliberately differ
  by mode; some tables intentionally stay non-replicated in both modes.
- Every metadata `ALTER` (`ADD/DROP/MODIFY COLUMN`, `ADD/DROP INDEX`) in a new
  canonical migration must include
  `{CLICKHOUSE_CLUSTERED_ONLY: SETTINGS alter_sync = 2}`, and every
  mutation-creating `ALTER` (`MATERIALIZE …`, `UPDATE`, `DELETE`) must include
  `{CLICKHOUSE_CLUSTERED_ONLY: SETTINGS mutations_sync = 2}`.
  This applies to a file holding a single `ALTER` too — the race is across
  migration files, not within one. `alter_sync` defaults to `1`, so the
  statement returns as soon as the initiating replica has bumped the table's
  metadata version in Keeper; golang-migrate then opens the next file
  immediately, and its first `ALTER` on that table can land on a replica still
  on the previous version. ClickHouse refuses to queue it and aborts the whole
  run with `code 517` because the replica metadata version is behind the common
  metadata version. Note that `mutations_sync` does not substitute for `alter_sync`: it
  governs when mutations finish, not metadata propagation. The renderer omits
  these fragments for unclustered `MergeTree` migrations. Use
  `{CLICKHOUSE_UNCLUSTERED_ONLY:...}` only for a deliberate mode-specific
  difference. Do not retrofit synchronization settings into already-shipped
  migrations merely to normalize them; the historical compatibility test
  intentionally protects their existing output.
- 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. First use
  `DROP VIEW IF EXISTS <name> {CLICKHOUSE_CLUSTER_CLAUSE};`, then
  `CREATE VIEW <name> {CLICKHOUSE_CLUSTER_CLAUSE} 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> {CLICKHOUSE_CLUSTER_CLAUSE} 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`; those target-table `ALTER`s must carry the
  clustered-only `alter_sync` template fragment 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
- [ ] Every metadata ALTER in a new canonical migration includes `{CLICKHOUSE_CLUSTERED_ONLY: SETTINGS alter_sync = 2}` — including files with a single ALTER, since the next migration file is what breaks — and every `MATERIALIZE …` / `UPDATE` / `DELETE` includes the corresponding `mutations_sync` fragment; `mutations_sync` is not a substitute for `alter_sync`; do not normalize already-shipped migration output; both rendered modes pass `prepareMigrations.test.ts`
- [ ] 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