clickhouse-migration-deep-dive

Execute ClickHouse schema migrations — ALTER TABLE operations, data migration between engines, versioned migration runners, and zero-downtime schema changes. Use when modifying ClickHouse schemas, migrating data between tables, or implementing versioned migration workflows. Trigger: "clickhouse migration", "clickhouse ALTER TABLE", "clickhouse schema change", "migrate clickhouse", "clickhouse add column", "clickhouse schema migration".

Jump to install

Repository: ComeOnOliver/skillshub
Last source activity: March 23, 2026 at 17:43
Detected SKILL.md language: English
Stars: 58
Forks: 20

Install options

The review-first prompt is selected by default. You can switch to a direct command or download a local copy.

Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.

A direct command skips the review prompt. Inspect the source before running it.

npx skills add https://github.com/ComeOnOliver/skillshub --skill clickhouse-migration-deep-dive

The command stays on one line. Scroll horizontally to inspect it before copying.

Prefer a local copy? Download the files currently available to SkillsMP.

Review the source files

Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.

SKILL.md

Source instructions · Read-only preview

Back to search Back to installation

before-you-build

Review product and feature risk before an AI coding agent starts implementation.

2026-06-24

x-twitter-scraper

Use Xquik for X data and confirmation-gated X actions: tweet search, user lookup, follower export, media download, monitors, webhooks, MCP, and SDK workflows.

2026-06-24

canton-network-repos

Canton Network open-source ecosystem guide covering DAML SDK, Canton runtime, and Splice applications. Use when working with Canton Network, DAML smart contracts, or building decentralized applications.

2026-04-03

View all from this repository

ClickHouse Migration Deep Dive

Overview

Plan and execute ClickHouse schema migrations: column changes, engine migrations, ORDER BY modifications, and versioned migration runners.

Prerequisites

ClickHouse admin access

Backup of production data (see clickhouse-prod-checklist)

Test environment for validation

Instructions

Step 1: Understanding ClickHouse DDL

ClickHouse ALTER operations are mutations — they run asynchronously and rewrite data parts in the background. This is fundamentally different from PostgreSQL/MySQL where ALTER is often instant or blocking.

-- Lightweight operations (instant, metadata only) ALTER TABLE events ADD COLUMN country LowCardinality(String) DEFAULT ''; ALTER TABLE events RENAME COLUMN old_name TO new_name; ALTER TABLE events COMMENT COLUMN user_id 'Unique user identifier'; -- Heavyweight operations (mutations — rewrite parts in background) ALTER TABLE events MODIFY COLUMN properties String CODEC(ZSTD(3)); ALTER TABLE events DROP COLUMN deprecated_field; ALTER TABLE events DELETE WHERE user_id = 0; ALTER TABLE events UPDATE email = '' WHERE created_at < '2024-01-01'; -- Check mutation progress SELECT database, table, mutation_id, command, is_done, parts_to_do, create_time FROM system.mutations is_done create_time;

ClickHouse Migration Deep Dive

Overview

Plan and execute ClickHouse schema migrations: column changes, engine migrations, ORDER BY modifications, and versioned migration runners.

Prerequisites

ClickHouse admin access

Backup of production data (see clickhouse-prod-checklist)

Test environment for validation

Instructions

Step 1: Understanding ClickHouse DDL

// src/clickhouse/migrations/runner.ts import { createClient } from '@clickhouse/client'; import { readFileSync, readdirSync } from 'fs'; import { join } from 'path'; const client = createClient({ url: process.env.CLICKHOUSE_HOST! }); async function runMigrations() { // Create migration tracking table await client.command({ query: ` CREATE TABLE IF NOT EXISTS _migrations ( version String, name String, applied_at DateTime DEFAULT now(), checksum String ) ENGINE = ReplacingMergeTree(applied_at) ORDER BY version `, }); // Get applied migrations const rs = await client.query({ query: 'SELECT version FROM _migrations FINAL', format: 'JSONEachRow', }); const applied = new Set((await rs.json<{ version: string }>()).map((r) => r.version)); // Read migration files const migrationsDir = join(__dirname, 'sql'); const files = readdirSync(migrationsDir) .filter((f) => f.endsWith('.sql')) .sort(); // 001-create-events.sql, 002-add-country.sql, etc. for (const file of files) { const version = file.split('-')[0]; // "001" if (applied.has(version)) { console.log(` [SKIP] ${file} (already applied)`); continue; } const sql = readFileSync(join(migrationsDir, file), 'utf-8'); console.log(` [APPLY] ${file}...`); try { // Split on semicolons to handle multi-statement files const statements = sql.split(';').filter((s) => s.trim()); for (const stmt of statements) { await client.command({ query: stmt }); } // Record migration await client.insert({ table: '_migrations', values: [{ version, name: file, checksum: '' }], format: 'JSONEachRow', }); console.log(` [OK] ${file}`); } catch (err) { console.error(` [FAIL] ${file}: ${(err as Error).message}`); throw err; // Stop on first failure } } console.log('Migrations complete.'); } runMigrations();

name	clickhouse-migration-deep-dive
description	Execute ClickHouse schema migrations — ALTER TABLE operations, data migration between engines, versioned migration runners, and zero-downtime schema changes. Use when modifying ClickHouse schemas, migrating data between tables, or implementing versioned migration workflows. Trigger: "clickhouse migration", "clickhouse ALTER TABLE", "clickhouse schema change", "migrate clickhouse", "clickhouse add column", "clickhouse schema migration".
allowed-tools	Read, Write, Edit, Bash(npm:), Bash(node:), Bash(kubectl:*)
version	1.0.0
license	MIT
author	Jeremy Longshore <jeremy@intentsolutions.io>
tags	["saas","database","analytics","clickhouse","olap"]
compatible-with	claude-code

name	clickhouse-migration-deep-dive
description	Execute ClickHouse schema migrations — ALTER TABLE operations, data migration between engines, versioned migration runners, and zero-downtime schema changes. Use when modifying ClickHouse schemas, migrating data between tables, or implementing versioned migration workflows. Trigger: "clickhouse migration", "clickhouse ALTER TABLE", "clickhouse schema change", "migrate clickhouse", "clickhouse add column", "clickhouse schema migration".
allowed-tools	Read, Write, Edit, Bash(npm:), Bash(node:), Bash(kubectl:*)
version	1.0.0
license	MIT
author	Jeremy Longshore <jeremy@intentsolutions.io>
tags	["saas","database","analytics","clickhouse","olap"]
compatible-with	claude-code

Operation	Downtime?	Notes
ADD COLUMN	None	Instant metadata change
DROP COLUMN	None	Mutation runs in background
MODIFY COLUMN type	None*	Mutation rewrites — can be slow on large tables
Change ORDER BY	Brief	Requires table recreation + RENAME
Change ENGINE	Brief	Requires table recreation + RENAME
ADD INDEX	None	MATERIALIZE runs in background
ALTER TTL	None	Takes effect on next merge

Error	Cause	Solution
`Cannot ALTER: table has mutations`	Mutation queue full	Wait or cancel: `KILL MUTATION WHERE ...`
`Column already exists`	Re-running migration	Use `IF NOT EXISTS`
`Cannot convert type`	Incompatible type change	Create new column, backfill, drop old
`Not enough disk space`	Mutation doubles data temporarily	Free space, then retry

clickhouse-migration-deep-dive

Install options

Review the source files