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.
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 ALTERs 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:
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:
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:
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:
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
1---
2name: clickhouse-best-practices
3description: 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.
4license: Apache-2.0
5metadata:
6 source: https://skillmd.com/skills/langfuse/clickhouse-best-practices
7 author: ClickHouse Inc
8 version: "0.3.0"
9---
10
11# ClickHouse Best Practices
12
13Comprehensive guidance for ClickHouse covering schema design, query optimization, and data ingestion. Contains 28 rules across 3 main categories (schema, query, insert), prioritized by impact.
14
15> **Official docs:** [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices)
16
17## IMPORTANT: How to Apply This Skill
18
19**Before answering ClickHouse questions, follow this priority order:**
20
211. **Check for applicable rules** in the `rules/` directory
222. **If rules exist:** Apply them and cite them in your response using "Per `rule-name`..."
233. **If no rule exists:** Use the LLM's ClickHouse knowledge or search documentation
244. **If uncertain:** Use web search for current best practices
255. **Always cite your source:** rule name, "general ClickHouse guidance", or URL
26
27**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.
28
29## Langfuse-Specific Rules
30
31- Use `packages/shared/src/server/queries/clickhouse-sql/event-query-builder.ts`
32 for queries against the `events` table. Do not hand-roll `events` SQL unless
33 you first confirm the query builder cannot express the query.
34- Never use `FINAL` on the `events` table; it is designed so `FINAL` is not
35 required and the keyword hurts performance.
36- ClickHouse query attribution is stored in `system.query_log.log_comment` as
37 JSON from `packages/shared/src/server/clickhouse/queryTags.ts`. Parse it with
38 `JSONExtractString(log_comment, 'surface')`,
39 `JSONExtractString(log_comment, 'route')`, and
40 `JSONExtractString(log_comment, 'projectId')`. Known `surface` values are
41 `trpc`, `publicapi`, `worker`, `mcp`, and `unknown`; ClickhouseWriter inserts
42 use `projectId = "MULTI_PROJECT"`.
43- Query attribution is propagated through OpenTelemetry baggage. Entry points
44 call `contextWithLangfuseProps(...)` from
45 `packages/shared/src/server/headerPropagation.ts`, setting ClickHouse
46 `surface`, optional `route`, and optional `projectId`. The ClickHouse
47 repository layer then reads baggage via `normalizeClickHouseQueryTags(...)`
48 and writes it to `log_comment`. Prefer setting attribution at entry points
49 rather than passing tags through every repository call.
50- `packages/shared/clickhouse/migrations/canonical/**` is the single canonical
51 template tree rendered for clustered and unclustered installs. Put
52 `{CLICKHOUSE_CLUSTER_CLAUSE}` at every cluster-aware DDL position. Use
53 `{CLICKHOUSE_REPLICATION_PREFIX}` only for engines that deliberately differ
54 by mode; some tables intentionally stay non-replicated in both modes.
55- Every metadata `ALTER` (`ADD/DROP/MODIFY COLUMN`, `ADD/DROP INDEX`) in a new
56 canonical migration must include
57 `{CLICKHOUSE_CLUSTERED_ONLY: SETTINGS alter_sync = 2}`, and every
58 mutation-creating `ALTER` (`MATERIALIZE …`, `UPDATE`, `DELETE`) must include
59 `{CLICKHOUSE_CLUSTERED_ONLY: SETTINGS mutations_sync = 2}`.
60 This applies to a file holding a single `ALTER` too — the race is across
61 migration files, not within one. `alter_sync` defaults to `1`, so the
62 statement returns as soon as the initiating replica has bumped the table's
63 metadata version in Keeper; golang-migrate then opens the next file
64 immediately, and its first `ALTER` on that table can land on a replica still
65 on the previous version. ClickHouse refuses to queue it and aborts the whole
66 run with `code 517` because the replica metadata version is behind the common
67 metadata version. Note that `mutations_sync` does not substitute for `alter_sync`: it
68 governs when mutations finish, not metadata propagation. The renderer omits
69 these fragments for unclustered `MergeTree` migrations. Use
70 `{CLICKHOUSE_UNCLUSTERED_ONLY:...}` only for a deliberate mode-specific
71 difference. Do not retrofit synchronization settings into already-shipped
72 migrations merely to normalize them; the historical compatibility test
73 intentionally protects their existing output.
74- Never use `CREATE OR REPLACE VIEW` (nor `CREATE OR REPLACE TABLE` /
75 `EXCHANGE TABLES`) in ClickHouse migrations. The atomic replace requires
76 `renameat2` filesystem support, which NFS-backed self-hosted deployments
77 (e.g. ClickHouse data on AWS EFS) lack — the migration fails and the
78 deployment aborts on startup (GitHub issue #14906). Redefine a plain view as
79 two statements in the same migration file. First use
80 `DROP VIEW IF EXISTS <name> {CLICKHOUSE_CLUSTER_CLAUSE};`, then
81 `CREATE VIEW <name> {CLICKHOUSE_CLUSTER_CLAUSE} AS …`.
82 The migration runner passes `x-multi-statement=true` and golang-migrate
83 splits files on `;` without parsing SQL, so keep semicolons out of comments
84 and string literals. Keep every statement idempotent
85 (`IF EXISTS`/`IF NOT EXISTS`) so a dirty, half-applied migration can be
86 re-run after `migrate force`. Readers hitting the view inside the
87 drop→create window fail transiently — acceptable for the `analytics_*`
88 export views, so keep plain views off product hot paths.
89- Never drop-and-recreate a materialized view whose source table receives live
90 inserts: every row inserted between `DROP` and `CREATE` is silently and
91 permanently missing from the target table. Change an MV's SELECT with
92 `ALTER TABLE <mv> {CLICKHOUSE_CLUSTER_CLAUSE} MODIFY QUERY <select>`, which swaps
93 the transformation without interrupting ingestion. When the change adds
94 columns, `ALTER` the target table(s) first (`ADD COLUMN IF NOT EXISTS …`),
95 then `MODIFY QUERY`; those target-table `ALTER`s must carry the
96 clustered-only `alter_sync` template fragment so no host applies the new MV
97 query before its target replica has the new columns. `MODIFY QUERY` is only
98 viable for TO-table MVs (all Langfuse MVs use `TO`).
99
100---
101
102## Review Procedures
103
104### For Schema Reviews (CREATE TABLE, ALTER TABLE)
105
106**Read these rule files in order:**
107
1081. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable
1092. `rules/schema-pk-cardinality-order.md` - Column ordering in keys
1103. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion
1114. `rules/schema-types-native-types.md` - Proper type selection
1125. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing
1136. `rules/schema-types-lowcardinality.md` - LowCardinality usage
1147. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT
1158. `rules/schema-partition-low-cardinality.md` - Partition count limits
1169. `rules/schema-partition-lifecycle.md` - Partitioning purpose
117
118**Check for:**
119
120- [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality)
121- [ ] Data types match actual data ranges
122- [ ] LowCardinality applied to appropriate string columns
123- [ ] Partition key cardinality bounded (100-1,000 values)
124- [ ] ReplacingMergeTree has version column if used
125- [ ] 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`
126- [ ] 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
127- [ ] 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
128
129### For Query Reviews (SELECT, JOIN, aggregations)
130
131**Read these rule files:**
132
1331. `rules/query-join-choose-algorithm.md` - Algorithm selection
1342. `rules/query-join-filter-before.md` - Pre-join filtering
1353. `rules/query-join-use-any.md` - ANY vs regular JOIN
1364. `rules/query-index-skipping-indices.md` - Secondary index usage
1375. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY
138
139**Check for:**
140
141- [ ] Filters use ORDER BY prefix columns
142- [ ] JOINs filter tables before joining (not after)
143- [ ] Correct JOIN algorithm for table sizes
144- [ ] Skipping indices for non-ORDER BY filter columns
145
146### For Insert Strategy Reviews (data ingestion, updates, deletes)
147
148**Read these rule files:**
149
1501. `rules/insert-batch-size.md` - Batch sizing requirements
1512. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives
1523. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives
1534. `rules/insert-async-small-batches.md` - Async insert usage
1545. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks
155
156**Check for:**
157
158- [ ] Batch size 10K-100K rows per INSERT
159- [ ] No ALTER TABLE UPDATE for frequent changes
160- [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns
161- [ ] Async inserts enabled for high-frequency small batches
162
163---
164
165## Output Format
166
167Structure your response as follows:
168
169```
170## Rules Checked
171- `rule-name-1` - Compliant / Violation found
172- `rule-name-2` - Compliant / Violation found
173...
174
175## Findings
176
177### Violations
178- **`rule-name`**: Description of the issue
179 - Current: [what the code does]
180 - Required: [what it should do]
181 - Fix: [specific correction]
182
183### Compliant
184- `rule-name`: Brief note on why it's correct
185
186## Recommendations
187[Prioritized list of changes, citing rules]
188```
189
190---
191
192## Rule Categories by Priority
193
194| Priority | Category | Impact | Prefix | Rule Count |
195| -------- | --------------------- | -------- | ------------------- | ---------- |
196| 1 | Primary Key Selection | CRITICAL | `schema-pk-` | 4 |
197| 2 | Data Type Selection | CRITICAL | `schema-types-` | 5 |
198| 3 | JOIN Optimization | CRITICAL | `query-join-` | 5 |
199| 4 | Insert Batching | CRITICAL | `insert-batch-` | 1 |
200| 5 | Mutation Avoidance | CRITICAL | `insert-mutation-` | 2 |
201| 6 | Partitioning Strategy | HIGH | `schema-partition-` | 4 |
202| 7 | Skipping Indices | HIGH | `query-index-` | 1 |
203| 8 | Materialized Views | HIGH | `query-mv-` | 2 |
204| 9 | Async Inserts | HIGH | `insert-async-` | 2 |
205| 10 | OPTIMIZE Avoidance | HIGH | `insert-optimize-` | 1 |
206| 11 | JSON Usage | MEDIUM | `schema-json-` | 1 |
207
208---
209
210## Quick Reference
211
212### Schema Design - Primary Key (CRITICAL)
213
214- `schema-pk-plan-before-creation` - Plan ORDER BY before table creation (immutable)
215- `schema-pk-cardinality-order` - Order columns low-to-high cardinality
216- `schema-pk-prioritize-filters` - Include frequently filtered columns
217- `schema-pk-filter-on-orderby` - Query filters must use ORDER BY prefix
218
219### Schema Design - Data Types (CRITICAL)
220
221- `schema-types-native-types` - Use native types, not String for everything
222- `schema-types-minimize-bitwidth` - Use smallest numeric type that fits
223- `schema-types-lowcardinality` - LowCardinality for <10K unique strings
224- `schema-types-enum` - Enum for finite value sets with validation
225- `schema-types-avoid-nullable` - Avoid Nullable; use DEFAULT instead
226
227### Schema Design - Partitioning (HIGH)
228
229- `schema-partition-low-cardinality` - Keep partition count 100-1,000
230- `schema-partition-lifecycle` - Use partitioning for data lifecycle, not queries
231- `schema-partition-query-tradeoffs` - Understand partition pruning trade-offs
232- `schema-partition-start-without` - Consider starting without partitioning
233
234### Schema Design - JSON (MEDIUM)
235
236- `schema-json-when-to-use` - JSON for dynamic schemas; typed columns for known
237
238### Query Optimization - JOINs (CRITICAL)
239
240- `query-join-choose-algorithm` - Select algorithm based on table sizes
241- `query-join-use-any` - ANY JOIN when only one match needed
242- `query-join-filter-before` - Filter tables before joining
243- `query-join-consider-alternatives` - Dictionaries/denormalization vs JOIN
244- `query-join-null-handling` - join_use_nulls=0 for default values
245
246### Query Optimization - Indices (HIGH)
247
248- `query-index-skipping-indices` - Skipping indices for non-ORDER BY filters
249
250### Query Optimization - Materialized Views (HIGH)
251
252- `query-mv-incremental` - Incremental MVs for real-time aggregations
253- `query-mv-refreshable` - Refreshable MVs for complex joins
254
255### Insert Strategy - Batching (CRITICAL)
256
257- `insert-batch-size` - Batch 10K-100K rows per INSERT
258
259### Insert Strategy - Async (HIGH)
260
261- `insert-async-small-batches` - Async inserts for high-frequency small batches
262- `insert-format-native` - Native format for best performance
263
264### Insert Strategy - Mutations (CRITICAL)
265
266- `insert-mutation-avoid-update` - ReplacingMergeTree instead of ALTER UPDATE
267- `insert-mutation-avoid-delete` - Lightweight DELETE or DROP PARTITION
268
269### Insert Strategy - Optimization (HIGH)
270
271- `insert-optimize-avoid-final` - Let background merges work
272
273---
274
275## When to Apply
276
277This skill activates when you encounter:
278
279- `CREATE TABLE` statements
280- `ALTER TABLE` modifications
281- `ORDER BY` or `PRIMARY KEY` discussions
282- Data type selection questions
283- Slow query troubleshooting
284- JOIN optimization requests
285- Data ingestion pipeline design
286- Update/delete strategy questions
287- ReplacingMergeTree or other specialized engine usage
288- Partitioning strategy decisions
289
290---
291
292## Rule File Structure
293
294Each rule file in `rules/` contains:
295
296- **YAML frontmatter**: title, impact level, tags
297- **Brief explanation**: Why this rule matters
298- **Incorrect example**: Anti-pattern with explanation
299- **Correct example**: Best practice with explanation
300- **Additional context**: Trade-offs, when to apply, references