Home › ClickHouse › ClickHouse schema and queries Windsurf rules

ClickHouse schema and queries Windsurf rules

Windsurf rules for ClickHouse: MergeTree ORDER BY design, coarse partitions, batched inserts, LowCardinality and types, materialized views, dedup semantics, and query checks.

WindsurfClickHouse

Copy clickhouse.md

How to use

Save this as .windsurf/rules/clickhouse.md in your repository root, commit it, and your agent will pick it up automatically. Edit the commands to match your project.

Note: Review and test before use. Provided as is, without warranty. See Terms.

---
trigger: model_decision
description: Apply when writing ClickHouse DDL, migrations, inserts or analytical queries
---

# ClickHouse

## Table design
- Pick the engine deliberately: `MergeTree` by default; `ReplacingMergeTree(version)` for upserts, `AggregatingMergeTree`/`SummingMergeTree` for rollups. Replicated/shared variants follow the cluster setup in the repo.
- `ORDER BY` (the primary key) follows the most common filters, low-cardinality columns first (e.g. `(tenant_id, event_type, toDate(ts), user_id)`). Changing it later means a new table; write the reasoning in the migration.
- `PARTITION BY` is coarse (usually `toYYYYMM(ts)`) and used for retention, not for query speed. Never partition by high-cardinality columns.
- Types: smallest fitting integer, `LowCardinality(String)` for repeated strings, `DateTime64(3, 'UTC')` for timestamps, `Decimal` for money, `Enum` for fixed sets. Avoid `Nullable` unless null has meaning; use defaults.
- Add `TTL` for retention and data-skipping indexes (`bloom_filter`, `minmax`) only for measured slow filters.

## Writes
- Insert in batches (thousands+ rows per insert) or use `async_insert=1`; never one row per insert from app code.
- `ALTER TABLE ... UPDATE/DELETE` are heavy mutations; use them rarely. Prefer lightweight deletes or ReplacingMergeTree patterns.
- ReplacingMergeTree dedups eventually during merges: queries that need exact results use `FINAL` (scoped with filters) or `argMax` aggregation; don't call `OPTIMIZE ... FINAL` from application code.

## Queries
- Select only needed columns; filter on the ORDER BY prefix; use `PREWHERE` only when it measurably helps.
- Prefer ClickHouse functions (`uniq`, `quantile`, `countIf`, `sumIf`, `arrayJoin`) over self-joins. Put the smaller table on the right side of a JOIN or use dictionaries for dimension lookups.
- Precompute heavy aggregations with materialized views writing into target tables (`TO` clause); remember they only see new inserts, so backfill explicitly.
- Check plans with `EXPLAIN indexes = 1` and read `system.query_log` for rows/bytes read before and after an optimization.
- Parameterize queries from apps (`{name:Type}` query parameters or the client's binding); never string-concatenate user input.

## Migrations
Schema changes are versioned migration files; `ON CLUSTER` used consistently where the repo uses a cluster.