ClickHouse Migration Deep Dive

Overview

Plan and execute ClickHouse schema migrations: column changes, engine migrations, ORDER BY modifications, and versioned migration runners. ClickHouse ALTER operations behave unlike PostgreSQL/MySQL — most are asynchronous mutations that rewrite data parts in the background, and some changes (ORDER BY, engine) require full table recreation. This skill walks the safe path for each.

Prerequisites

• ClickHouse admin access
• Backup of production data (see clickhouse-prod-checklist)
• Test environment for validation

Instructions

Follow these steps in order. SQL and runner code for each step live in the linked reference files — keep them open while you work.

Step 1: Classify the operation

Decide whether your change is lightweight (instant, metadata only) or a heavyweight mutation (rewrites parts in the background):

-- Lightweight (instant): ADD COLUMN, RENAME COLUMN, COMMENT COLUMN
ALTER TABLE events ADD COLUMN country LowCardinality(String) DEFAULT '';

-- Heavyweight (mutation): MODIFY COLUMN, DROP COLUMN, DELETE, UPDATE
ALTER TABLE events MODIFY COLUMN properties String CODEC(ZSTD(3));

-- Always monitor mutation progress
SELECT database, table, mutation_id, is_done, parts_to_do
FROM system.mutations WHERE NOT is_done ORDER BY create_time;

Step 2: Run column operations

Use Edit/Write to author the ALTER TABLE statements, then apply them. Add/modify/drop columns, set materialized defaults, and attach codecs. Full DDL semantics and every column-operation variant: DDL & column operations.

Step 3: Recreate the table for ORDER BY / engine changes

ClickHouse has no MODIFY ORDER BY and no in-place engine change. Create a new table, INSERT ... SELECT the data, then atomically RENAME TABLE to swap. Full create → copy → swap → verify → drop recipe for both cases: table recreation.

Step 4: Wire a versioned migration runner

For repeatable, tracked migrations, drive numbered .sql files through a runner that records applied versions in a _migrations table and stops on first failure. Use Write to scaffold runner.ts and the sql/NNN-*.sql files, then run it with npm/node. Full runner, example migration files, and the operation downtime matrix: migration runner.

Output

Applying this skill produces:

• Executed ALTER statements or recreated-and-swapped tables, with mutations confirmed complete via system.mutations WHERE NOT is_done (empty result).

ClickHouse Multi-Environment Setup

Overview

Configure separate ClickHouse instances for development, staging, and production with proper secrets management, environment detection, and infrastructure-as-code. A single typed config module resolves the right instance from NODE_ENV, so application code never branches on environment and destructive operations are blocked outside dev/staging.

The full code for every step lives in references/implementation.md; worked end-to-end flows are in references/examples.md.

Prerequisites

• ClickHouse Cloud account or self-hosted instances per environment
• Secret management solution (Vault, AWS Secrets Manager, GCP Secret Manager)
• CI/CD pipeline with environment variables

Instructions

Step 1: Choose an environment strategy

Provision one instance per tier, isolated by data sensitivity:

Step 2: Build a typed config module

Create src/config/clickhouse.ts with one ClickHouseEnvConfig per environment, keyed by NODE_ENV. Fail fast in non-dev environments — require a password and enforce HTTPS at startup:

export function getConfig(): ClickHouseEnvConfig {
  const env = process.env.NODE_ENV ?? 'development';
  const config = configs[env];
  if (!config) throw new Error(`Unknown environment: ${env}`);
  if (env !== 'development') {
    if (!config.password) throw new Error(`CLICKHOUSE_PASSWORD not set for ${env}`);
    if (!config.url.startsWith('https://')) {
      throw new Error(`ClickHouse ${env} must use HTTPS`);
    }
  }
  return config;
}

Full per-environment config table (pool sizes, timeouts, compression): references/implementation.md.

Step 3: Add a client factory

A lazily-initialized singleton in src/clickhouse/client.ts builds the pool once per process from the resolved config. Full code: references/implementation.md.

Step 4: Wire secrets per environment

Dev uses a git-ignored .env.local; every other tier pulls from a secret manager (AWS Secrets Manager, GCP Secret Manager, or Vault) — never commit credentials. CLI recipes for all three: references/implemen

ClickHouse Observability

Overview

Set up comprehensive monitoring for ClickHouse using built-in system tables, Prometheus integration, Grafana dashboards, and alerting rules. The workflow layers four signal sources: system.* tables (always available, zero dependencies), a Prometheus scrape endpoint, application-level client instrumentation, and alert rules that fire on the failure modes that actually page an on-call — high error rate, latency creep, merge backlog, and resource exhaustion.

Deep configs live in references/ so this file stays a fast, followable map.

Prerequisites

• ClickHouse instance with system.* table access
• Prometheus (or compatible: Grafana Alloy, Victoria Metrics)
• Grafana for dashboards
• AlertManager or PagerDuty for alerts

Instructions

Step 1: Query system tables for a health snapshot

Start with zero dependencies — the system.* tables already hold everything. Run this for an instant server-health read:

SELECT
    (SELECT count() FROM system.processes) AS running_queries,
    (SELECT value FROM system.metrics WHERE metric = 'MemoryTracking') AS memory_bytes,
    (SELECT count() FROM system.merges) AS active_merges;

Query throughput, insert rates, and per-table part counts (the merge-health signal), plus a full table of which system.* table to poll at what frequency: system table queries & reference.

Step 2: Wire up Prometheus scraping

ClickHouse Cloud exposes a managed Prometheus endpoint (Basic auth with a Cloud API key); self-hosted uses the built-in :9363 /metrics endpoint enabled in config.xml. Write the scrape config to your prometheus.yml. Full Cloud + self-hosted scrape configs and the config.xml block: Prometheus scrape config & Grafana dashboards.

Step 3: Instrument the application client

Server metrics show what ClickHouse does; client metrics attribute latency, error codes, and insert volume to your own code. Wrap queries in a prom-client histogram/counter and expose /metrics. Full instrumentation + structured logging: application-level instrumentation.

Step 4: Build Grafana dashboard panels

Panels for QPS, P50/P95/P99 latency, error rate, and insert throughput are in the Grafana dashboards reference. Or import the official community dashboard: https://grafana.com/grafana/dashboards/23415.

Step 5: Load alert rules

Write Prometheus alert rules for the five production failure modes (error rate, latency, part count, memory, disk) to a rules file

ClickHouse Performance Tuning

Overview

Diagnose and fix ClickHouse performance issues using query analysis, proper indexing, projections, materialized views, and server settings tuning. Work top-down: measure first with system.query_log, then apply the single highest-leverage fix (usually the ORDER BY key), then re-measure to confirm.

Prerequisites

• ClickHouse tables with data (see clickhouse-core-workflow-a)
• Access to system.query_log and system.parts

Instructions

The tuning workflow is seven independent steps. Diagnose first, then reach for the fix that matches the bottleneck. Each step's full SQL lives in references/implementation.md — start there for the complete, copy-paste commands.

1. Diagnose slow queries — rank the last 24h of system.query_log by query_duration_ms, then inspect a suspect query with EXPLAIN PLAN / EXPLAIN PIPELINE.
2. ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix skips whole granules; a mismatched key forces a full scan.
3. Data skipping indexes — bloom_filter for high-cardinality lookups, set for low-cardinality columns, minmax for range filters on non-key columns.
4. Projections — automatic pre-aggregation ClickHouse picks transparently when a query matches the projection's shape.
5. Server settings — max_threads, external sort/group-by spill, async_insert, and friends, set per-query or per-session.
6. Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so dashboard reads hit milliseconds, not seconds.
7. Query patterns — PREWHERE, LIMIT BY, and avoiding FINAL.

The essential first move — find the slowest queries:

SELECT event_time, query_duration_ms, read_rows, read_bytes,
       substring(query, 1, 300) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 24 HOUR
  AND query_duration_ms > 1000   -- > 1 second
ORDER BY query_duration_ms DESC
LIMIT 20;

Output

Applying this workflow produces:

• A ranked list of the slowest queries with their read_rows / read_bytes cost.
• One or more concrete schema/query changes: a corrected ORDER BY key, added data skipping indexes, a projection, a materialized view, or tuned session settings.
• A before/after measurement from system.query_log proving the change reduced read_rows, read_bytes,

ClickHouse Production Checklist

Overview

Comprehensive go-live checklist for ClickHouse covering schema and engine design, server tuning, backup configuration, monitoring, security, application integration, and operational readiness. Walk the eight sections top to bottom; each is a gate that must be green before production traffic is allowed. Deep configuration — server XML, backup SQL, and verification queries — lives in references/ so this file stays a fast operational scan.

Prerequisites

Before starting the checklist, confirm the following are in place:

• A ClickHouse instance is provisioned (ClickHouse Cloud or self-hosted) and reachable from the environment you will run traffic from.
• Application integration code has been exercised end-to-end in a staging environment against a ClickHouse of the same major version.
• You have admin credentials able to read system.* tables and, for self-hosted, edit config.xml / users.xml.

Instructions

Work each section in order. Every box must be checked (or explicitly waived with a reason) before go-live.

1. Schema & Engine Design

• [ ] Tables use MergeTree family engines (not Memory, Log, or TinyLog)
• [ ] ORDER BY columns match primary filter/group patterns
• [ ] PARTITION BY is coarse (monthly or weekly, never by ID)
• [ ] TTL configured for data retention policy
• [ ] LowCardinality(String) used for low-cardinality columns
• [ ] CODEC(ZSTD) applied to large String/JSON columns
• [ ] ReplacingMergeTree used with FINAL or dedup logic if upserts needed

2. Server Configuration (Self-Hosted)

Set memory to ~80% of RAM, cap per-query memory and execution time, and size the merge pools to the core count. ClickHouse Cloud manages these for you.

<max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
<max_concurrent_queries>150</max_concurrent_queries>
<max_execution_time>300</max_execution_time>  <!-- 5 min: cap runaway scans -->

Full annotated config.xml / users.xml block and tuning rationale: server configuration reference.

3. Backup Configuration

• [ ] Backup schedule configured (daily minimum)
• [ ] Backup restore tested and documented
• [ ] Point-in-time recovery possible (incremental backups)
• [ ] Backup stored in different region/account from primary

Native BACKUP ... TO S3 syntax, incremental base+delta backups, and recovery drill guidance: backup & recovery r

ClickHouse Rate Limits & Concurrency

Overview

ClickHouse has no REST API rate limits like a SaaS product. Instead it enforces server-side concurrency limits, memory quotas, and per-user settings that control resource usage. This skill configures those server-side limits and pairs them with client-side controls so an application stays within them under load.

Prerequisites

• ClickHouse admin access (or Cloud console) to create quotas and settings profiles.
• The @clickhouse/client Node package for the client-side patterns.
• A rough target for peak concurrent queries and per-query memory.

Instructions

Work top-down: cap resources at the server, then make the client respect the cap.

Step 1: Know the server-side limits

The defaults you tune most often:

ClickHouse Cloud's management API (not the query interface) is separately limited to 10 requests per 10 seconds. Full table in references/implementation.md.

Step 2: Cap resources per user (essential skeleton)

Bind a quota and a settings profile to each application user:

CREATE SETTINGS PROFILE IF NOT EXISTS app_profile
    SETTINGS
        max_memory_usage = 5000000000,       -- 5GB per query
        max_execution_time = 30,             -- 30s timeout
        max_concurrent_queries_for_user = 10 -- 10 parallel queries
    TO app_user;

The full quota (CREATE QUOTA … FOR INTERVAL 1 HOUR MAX …) plus verification queries are in references/implementation.md.

Step 3: Make the client respect the cap

Four client-side patterns keep the app inside the server limits — connection pooling, an app-level concurrency queue (p-queue), retry-with-backoff on TOO_MANY_SIMULTANEOUS_QUERIES, and insert buffering to avoid TOO_MANY_PARTS. Each is a drop-in TypeScript snippet in references/implementation.md, with the concurrency queue as the smallest starting point:

import PQueue from 'p-queue';
const queryQueue = new PQueue({ concurrency: 5, timeout: 30_000, throwOnTimeout: true });
const rateLimitedQuery = <T>(sql: string) =>
  queryQueue.add(async () => (await client.query({ query: