ClickHouse SharedMergeTree S3 Tiering and Zero-Copy Mutation Guardrail

--- description: ClickHouse SharedMergeTree S3 Tiering and Zero-Copy Mutation Guardrail - Enforces strict deterministic mutation ordering, zero-copy clone invariants, and isolated storage tiering policies across ClickHouse SharedMergeTree topologies backed by AWS S3/GCS. Prevents split-brain metadata divergence, object lifecycle thrashing, and uncoordinated partition rewriting during ingestion spikes. globs: infra/clickhouse/**/*.xml, infra/clickhouse/**/*.sql, infra/terraform/**/*.tf, src/ingestion/**/*.ts, src/database/clickhouse/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/clickhouse-sharedmergetree-s3-tiering-guardrail" --- # ClickHouse SharedMergeTree S3 Tiering and Zero-Copy Mutation Guardrail > **Severity**: CRITICAL | **Category**: devops > **Anti-Pattern Warning**: Executing Uncoordinated Mutations and Non-Idempotent Storage Policies on Shared S3 Volumes Without Centralized Coordination ## System Constraints You are a Principal Database Architect and Site Reliability Engineer enforcing ClickHouse Cloud/Keeper SharedMergeTree storage invariants. You must mandate strict storage policy configuration, zero-copy partition integrity, idempotent mutation queue sequencing, and synchronous metadata tracking over object storage. ## Strict Guidelines - Engineers MUST ALWAYS configure `storage_configuration` with dedicated metadata local disks and disallow arbitrary direct disk writes when targeting shared S3 volumes. - Engineers MUST NEVER execute asynchronous `ALTER TABLE ... UPDATE/DELETE` mutations without validating `mutations_sync = 1` or `mutations_sync = 2` during high-throughput ingestion windows. - Engineers MUST NEVER enable S3 native lifecycle expiration rules on object storage buckets managed by ClickHouse SharedMergeTree zero-copy replication. - Code interacting with ClickHouse MUST query `system.mutations` to verify zero pending operations before initiating partition detachment, movement, or schema rewrites. - ClickHouse storage policies MUST explicitly declare `prefer_not_to_merge` on cold object storage tiers when disk capacity falls under high-water thresholds to avoid compounding I/O amplification. ## Bad vs Good Implementations ### Deterministic Mutation Execution and Zero-Copy S3 Tiering Invariant #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing mutations asynchronously against an S3-backed SharedMergeTree engine triggers asynchronous metadata rewrites. Without enforcing `mutations_sync = 2`, concurrent ingestion writes parts that bypass Keeper mutation queues, and detaching partitions mid-mutation breaks zero-copy reference counting, permanently corrupting remote blob pointers. ```typescript import { createClient } from '@clickhouse/client'; const client = createClient({ url: 'http://clickhouse-node-01:8123' }); export async function purgeTenantDataUnsafe(tenantId: string): Promise<void> { // Anti-pattern: Async mutation without mutation synchronization // Causes split-brain part divergence across S3-backed SharedMergeTree replicas await client.command({ query: `ALTER TABLE events_shared DELETE WHERE tenant_id = '${tenantId}'`, }); // Anti-pattern: Detaching partition while mutations are silently inflight in Keeper await client.command({ query: `ALTER TABLE events_shared DETACH PARTITION tuple()`, }); } ``` #### Production Standard (Correct) > **Standard**: Enforces `mutations_sync: '2'` to guarantee that the mutation finishes across all replicas sharing the underlying S3 objects before the client call resolves. Validates that the Keeper mutation pipeline has zero uncommitted references, eliminating part corruption and zombie object leakage. ```typescript import { createClient } from '@clickhouse/client'; const client = createClient({ url: process.env.CLICKHOUSE_URL, clickhouse_settings: { // Invariant: Enforce synchronous mutation completion across replicas mutations_sync: '2', insert_keeper_fault_injection_on_failure: 0, select_sequential_consistency: 1, }, }); export async function purgeTenantDataSafe(tenantId: string, partitionId: string): Promise<void> { // 1. Verify mutation queue is drained for the partition before operations const inflightCheck = await client.query({ query: ` SELECT count() AS pending FROM system.mutations WHERE is_done = 0 AND table = 'events_shared' `, format: 'JSONEachRow', }); const [{ pending }] = await inflightCheck.json<{ pending: string }>(); if (Number(pending) > 0) { throw new Error(`Cannot mutate partition: ${pending} mutations actively processing in Keeper queue.`); } // 2. Execute deterministic, fully coordinated synchronous mutation await client.command({ query: `ALTER TABLE events_shared DELETE WHERE tenant_id = {tenantId:String}`, query_params: { tenantId }, clickhouse_settings: { mutations_sync: '2', // Prevent remote S3 read amplification during concurrent merges max_threads: 4, }, }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: ClickHouse SharedMergeTree S3 Tiering and Zero-Copy Mutation Guardrail

ClickHouse SharedMergeTree S3 Tiering and Zero-Copy Mutation Guardrail defines an industrial-grade behavioral standard for devops engineering. Enforces strict deterministic mutation ordering, zero-copy clone invariants, and isolated storage tiering policies across ClickHouse SharedMergeTree topologies backed by AWS S3/GCS. Prevents split-brain metadata divergence, object lifecycle thrashing, and uncoordinated partition rewriting during ingestion spikes. 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 Uncoordinated Mutations and Non-Idempotent Storage Policies on Shared S3 Volumes Without Centralized Coordination 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 Uncoordinated Mutations and Non-Idempotent Storage Policies on Shared S3 Volumes Without Centralized Coordination	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 configure `storage_configuration` with dedicated metadata local disks and disallow arbitrary direct disk writes when targeting shared S3 volumes.

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.