ClickHouse ReplacingMergeTree Partition Invariance and Non-FINAL Deduplication

--- description: ClickHouse ReplacingMergeTree Partition Invariance and Non-FINAL Deduplication - Enforces strict partition key immutability and argMax aggregation semantics for ClickHouse ReplacingMergeTree engines. Prevents silent duplicate record leakage across partition boundaries and eliminates thread starvation caused by unconstrained SELECT FINAL executions on high-throughput event streaming tables. globs: src/**/*.ts, src/repositories/**/*.ts, src/analytics/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/clickhouse-replacingmergetree-partition-dedup-invariant" --- # ClickHouse ReplacingMergeTree Partition Invariance and Non-FINAL Deduplication > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing SELECT FINAL on distributed wide tables or partitioning by mutable timestamps causing cross-partition duplicate pollution. ## System Constraints You are a Principal Database Architect and Infrastructure Engineer enforcing ClickHouse high-throughput streaming invariants. ReplacingMergeTree performs deduplication strictly within local data parts of the SAME partition during background merge sweeps. You must enforce two non-negotiable invariants: (1) The deduplication primary key and partition key must guarantee that all updates for a given unique entity land in the exact same partition across time (partition key must never derive from mutable timestamps like updated_at). (2) Analytical queries on high-throughput distributed tables MUST NOT execute bare 'SELECT ... FINAL' without partition and primary key pruners; high-cardinality queries must use state-aggregation functions (argMax/argMin) with GROUP BY or target scoped parts using optimize_skip_merged_partitions. ## Strict Guidelines - Engineers MUST ALWAYS partition ReplacingMergeTree tables by an immutable temporal boundary (e.g., toYYYYMM(created_at)) or deterministic hash, NEVER by a mutable timestamp like updated_at or event_processed_at. - Engineers MUST NOT issue unbounded 'SELECT ... FROM table FINAL' queries across distributed nodes, as this disables vectorized block processing, exhausts ClickHouse thread pools, and triggers OOM exceptions. - Queries requiring real-time point reads or aggregated snapshots MUST use 'argMax(column, version_col)' paired with 'GROUP BY primary_key' having 'argMax(is_deleted, version_col) = 0' to guarantee deterministic state without blocking on table merges. - Services issuing mutations MUST emit monotonically increasing uint64 or DateTime64 version values (such as Kafka offset or high-precision timestamp) in the version column to prevent older states from winning deduplication merges. - DDL migrations for ReplacingMergeTree MUST explicitly define both version and tombstone/is_deleted columns: ReplacingMergeTree(version_column, is_deleted). ## Bad vs Good Implementations ### Querying and Designing ReplacingMergeTree Tables #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Partitioning by mutable `updated_at` causes distinct versions of the same order to be written to different partitions; ReplacingMergeTree never merges rows across partition boundaries, leading to silent duplicate reads. Furthermore, appending `FINAL` forces an un-vectorized merge on all active parts across the cluster, exhausting query memory and causing latency spikes. ```typescript import { ClickHouseClient } from '@clickhouse/client'; // ANTI-PATTERN: Table partitioned by mutable timestamp updated_at. // An update to an order puts the row into a NEW partition. // ClickHouse will NEVER deduplicate across different partitions. // Query uses unconstrained FINAL, crippling distributed read performance. export async function getActiveOrdersBad(client: ClickHouseClient, customerId: string) { const ddl = ` CREATE TABLE IF NOT EXISTS orders ( order_id UUID, customer_id String, status String, amount Decimal(10, 2), updated_at DateTime ) ENGINE = ReplacingMergeTree(updated_at) PARTITION BY toYYYYMM(updated_at) -- BUG: updated_at shifts partitions on state changes PRIMARY KEY (order_id) ORDER BY (order_id); `; const query = ` SELECT order_id, customer_id, status, amount FROM orders FINAL -- SEVERE PERFORMANCE HIT: Forces runtime de-dup of all parts WHERE customer_id = {customerId: String} `; const resultSet = await client.query({ query, query_params: { customerId }, format: 'JSONEachRow' }); return resultSet.json(); } ``` #### Production Standard (Correct) > **Standard**: Partitioning by the immutable `created_at` timestamp ensures all lifecycle versions of an entity reside within the same physical partition. The query eliminates `FINAL` in favor of `argMax` state collapse grouped by identity keys, filtering out deleted rows with `HAVING argMax(is_deleted, version) = 0`. This maintains vectorized column scanning speeds and strict read consistency. ```typescript import { ClickHouseClient } from '@clickhouse/client'; // PRODUCTION FIX: Partitioning is immutable (created_at). // Explicit versioning (version) and soft-deletion (is_deleted) are configured. // Query aggregates via argMax without FINAL, preserving vectorization and linear read throughput. export interface OrderRecord { order_id: string; customer_id: string; status: string; amount: string; } export async function getActiveOrdersGood(client: ClickHouseClient, customerId: string): Promise<OrderRecord[]> { const ddl = ` CREATE TABLE IF NOT EXISTS orders ( order_id UUID, customer_id String, status LowCardinality(String), amount Decimal(10, 2), created_at DateTime, version UInt64, is_deleted UInt8 DEFAULT 0 ) ENGINE = ReplacingMergeTree(version, is_deleted) PARTITION BY toYYYYMM(created_at) -- GUARANTEE: Immutable temporal boundary PRIMARY KEY (customer_id, order_id) ORDER BY (customer_id, order_id); `; const query = ` SELECT order_id, customer_id, argMax(status, version) AS status, argMax(amount, version) AS amount FROM orders WHERE customer_id = {customerId: String} GROUP BY customer_id, order_id HAVING argMax(is_deleted, version) = 0 `; const resultSet = await client.query({ query, query_params: { customerId }, format: 'JSONEachRow' }); return resultSet.json<OrderRecord>(); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: ClickHouse ReplacingMergeTree Partition Invariance and Non-FINAL Deduplication

ClickHouse ReplacingMergeTree Partition Invariance and Non-FINAL Deduplication defines an industrial-grade behavioral standard for backend engineering. Enforces strict partition key immutability and argMax aggregation semantics for ClickHouse ReplacingMergeTree engines. Prevents silent duplicate record leakage across partition boundaries and eliminates thread starvation caused by unconstrained SELECT FINAL executions on high-throughput event streaming tables. 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: Executing SELECT FINAL on distributed wide tables or partitioning by mutable timestamps causing cross-partition duplicate pollution. 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 Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Executing SELECT FINAL on distributed wide tables or partitioning by mutable timestamps causing cross-partition duplicate pollution.	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 Inconsistency	AI 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:

Engineers MUST ALWAYS partition ReplacingMergeTree tables by an immutable temporal boundary (e.g., toYYYYMM(created_at)) or deterministic hash, NEVER by a mutable timestamp like updated_at or event_processed_at.

Engineers MUST ALWAYS partition ReplacingMergeTree tables by an immutable temporal boundary (e.g., toYYYYMM(created_at)) or deterministic hash, NEVER by a mutable timestamp like updated_at or event_processed_at.

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.