AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: clickhouse-olap-query-optimization

ClickHouse OLAP Analytics & Partitioned MergeTree Standard

Production-grade ClickHouse architecture for billion-row high-throughput analytics. Forbids row-by-row mutating updates (ALTER TABLE UPDATE), mandates immutable partitioned ReplacingMergeTree engines, and pre-aggregated Materialized Views.

Prohibited Anti-Pattern: AI writes row-by-row OLTP-style updates and unindexed full-table joins that freeze ClickHouse background merge threads.

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: ClickHouse OLAP Analytics & Partitioned MergeTree Standard - Production-grade ClickHouse architecture for billion-row high-throughput analytics. Forbids row-by-row mutating updates (ALTER TABLE UPDATE), mandates immutable partitioned ReplacingMergeTree engines, and pre-aggregated Materialized Views.
globs: **/*clickhouse*.ts, **/*clickhouse*.py, **/*.sql
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/clickhouse-olap-query-optimization"
---

# ClickHouse OLAP Analytics & Partitioned MergeTree Standard

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: AI writes row-by-row OLTP-style updates and unindexed full-table joins that freeze ClickHouse background merge threads.

## System Constraints
You are a ClickHouse database architect. ClickHouse is an append-only columnar OLAP database, NOT an OLTP database. Never issue frequent 'ALTER TABLE UPDATE/DELETE' mutations. Use ReplacingMergeTree with version columns, optimize sorting keys (ORDER BY), and leverage Materialized Views for real-time aggregations.

## Strict Guidelines
- Never use ClickHouse as an OLTP database; avoid row-by-row mutations ('ALTER TABLE UPDATE/DELETE') as they rewrite entire data parts on disk.
- Use the ReplacingMergeTree(version) engine for deduplication and append-only state updates, querying with 'FINAL' or argMax() aggregations.
- Choose low-cardinality primary sorting keys for 'ORDER BY' (e.g. tenant_id, toStartOfHour(timestamp), event_id) to maximize sparse index skipping.
- Pre-calculate high-cardinality metrics using Materialized Views with AggregatingMergeTree or SummingMergeTree.

## Bad vs Good Implementations
### ReplacingMergeTree with Version Column vs Costly ALTER TABLE UPDATE

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Mutations in ClickHouse are asynchronous heavy part-rewrites; running them in real-time pipelines causes write amplification and server freezes.
```sql
-- ANTI-PATTERN: Using OLTP update mutations in ClickHouse
-- Freezes disk I/O and creates heavy background mutation queues!
ALTER TABLE user_events UPDATE is_processed = 1 WHERE event_id = 'evt_123';
ALTER TABLE user_events DELETE WHERE timestamp < now() - INTERVAL 90 DAY;
```

#### Production Standard (Correct)
> **Standard**: Append-only inserts with versioning and native TTL enable zero-mutation data expiration and real-time state resolution.
```sql
-- PRODUCTION STANDARD: Append-only ReplacingMergeTree with version & TTL
CREATE TABLE user_events (
    tenant_id UInt32,
    event_time DateTime CODEC(DoubleDelta, ZSTD(1)),
    event_id UUID,
    is_processed UInt8,
    version UInt64,
    payload String CODEC(ZSTD(3))
) ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, toStartOfHour(event_time), event_id)
TTL event_time + INTERVAL 90 DAY DELETE;

-- Query latest state cleanly with argMax or FINAL
SELECT 
    tenant_id, 
    argMax(is_processed, version) as current_status
FROM user_events 
WHERE tenant_id = 42 
GROUP BY tenant_id;
```

### Pre-aggregated Materialized Views for Sub-Millisecond Dashboards

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Scanning raw data repeatedly consumes immense CPU and RAM on every dashboard query.
```sql
-- ANTI-PATTERN: Scanning 100M raw rows for every dashboard refresh
SELECT toStartOfMinute(event_time) as minute, count(), avg(latency)
FROM raw_http_logs
WHERE event_time >= now() - INTERVAL 1 DAY
GROUP BY minute;
```

#### Production Standard (Correct)
> **Standard**: Materialized views aggregate data upon insertion, delivering 1000x faster dashboard queries.
```sql
-- PRODUCTION STANDARD: AggregatingMergeTree Materialized View
CREATE TABLE http_metrics_hourly (
    metric_hour DateTime,
    total_requests SimpleAggregateFunction(sum, UInt64),
    latency_state AggregateFunction(avg, Float64)
) ENGINE = AggregatingMergeTree()
ORDER BY metric_hour;

CREATE MATERIALIZED VIEW mv_http_metrics_hourly TO http_metrics_hourly AS
SELECT
    toStartOfHour(event_time) as metric_hour,
    count() as total_requests,
    avgState(latency) as latency_state
FROM raw_http_logs
GROUP BY metric_hour;

-- Sub-millisecond query reading only aggregated parts
SELECT 
    metric_hour,
    sum(total_requests),
    avgMerge(latency_state)
FROM http_metrics_hourly
GROUP BY metric_hour;
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

ClickHouse Query Analyzer MCP
Analyze query EXPLAIN plans and partition pruning in ClickHouse.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

ClickHouse Schema Designer
Design high-performance ReplacingMergeTree and AggregatingMergeTree schemas.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: ClickHouse OLAP Analytics & Partitioned MergeTree Standard

ClickHouse OLAP Analytics & Partitioned MergeTree Standard defines an industrial-grade behavioral standard for backend engineering. Production-grade ClickHouse architecture for billion-row high-throughput analytics. Forbids row-by-row mutating updates (ALTER TABLE UPDATE), mandates immutable partitioned ReplacingMergeTree engines, and pre-aggregated Materialized Views. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: AI writes row-by-row OLTP-style updates and unindexed full-table joins that freeze ClickHouse background merge threads. Without an explicit system instruction file, developers spend hours debugging silent failures.
Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftAI writes row-by-row OLTP-style updates and unindexed full-table joins that freeze ClickHouse background merge threads.Critical
Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production.
Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture InconsistencyAI assistants produce mixed paradigms across files, creating fragmented codebase conventions.Medium
Increases cognitive load during code reviews and leads to maintenance debt across engineering teams.
Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.
Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

1

Never use ClickHouse as an OLTP database

Never use ClickHouse as an OLTP database; avoid row-by-row mutations ('ALTER TABLE UPDATE/DELETE') as they rewrite entire data parts on disk.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Use the ReplacingMergeTree(version) engine for deduplication and append-only state updates, querying with 'FINAL' or argMax() aggregations.

Use the ReplacingMergeTree(version) engine for deduplication and append-only state updates, querying with 'FINAL' or argMax() aggregations.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Choose low-cardinality primary sorting keys for 'ORDER BY' (e.g. tenant_id, toStartOfHour(timestamp), event_id) to maximize sparse index skipping.

Choose low-cardinality primary sorting keys for 'ORDER BY' (e.g. tenant_id, toStartOfHour(timestamp), event_id) to maximize sparse index skipping.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Pre-calculate high-cardinality metrics using Materialized Views with AggregatingMergeTree or SummingMergeTree.

Pre-calculate high-cardinality metrics using Materialized Views with AggregatingMergeTree or SummingMergeTree.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is ClickHouse OLAP Analytics & Partitioned MergeTree Standard critical for AI coding assistants?

Production-grade ClickHouse architecture for billion-row high-throughput analytics. Forbids row-by-row mutating updates (ALTER TABLE UPDATE), mandates immutable partitioned ReplacingMergeTree engines, and pre-aggregated Materialized Views. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (AI writes row-by-row OLTP-style updates and unindexed full-table joins that freeze ClickHouse background merge threads.) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

ClickHouse