# Cluster Operations

> Cluster management: distributed tables, ON CLUSTER DDL, node lifecycle, resharding, load balancing, and Keeper migration.

- Skill: `terrylica/cluster-operations` (Agent Skill)
- Install (CLI): `npx skillmds@latest add terrylica/cluster-operations`
- Raw SKILL.md: https://api.skillmd.com/api/skills/terrylica/cluster-operations/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: terrylica (https://skillmd.com/u/terrylica)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/terrylica/cluster-operations

---


# Cluster Operations

## Distributed Tables
- `CREATE TABLE dist ENGINE = Distributed(cluster, db, local_table, sharding_key)`
- Sharding key: `rand()` for even distribution, `cityHash64(user_id)` for user affinity
- Reads: query all shards in parallel; Writes: route to correct shard or write locally

## ON CLUSTER DDL
- `ALTER TABLE t ON CLUSTER '{cluster}' ADD COLUMN col Type` — propagate schema to all replicas
- `CREATE TABLE t ON CLUSTER '{cluster}' AS template_db.template_table` — clone across shards
- `distributed_ddl_output_mode`: `throw` (fail on error), `null` (ignore), `none`, `active`
- Check status: `SELECT * FROM system.distributed_ddl_queue`

## Load Balancing and Read Routing
- `load_balancing`: `random`, `in_order`, `first_or_random`, `nearest_hostname`
- `max_replica_delay_for_distributed_queries` — skip lagging replicas
- `fallback_to_stale_replicas_for_distributed_queries=1` — use stale when all delayed

## Adding Nodes
1. Install ClickHouse on new node
2. Configure Keeper/ZooKeeper connection
3. Update cluster config (`remote_servers`) on all nodes
4. Create local tables on new node
5. ReplicatedMergeTree: data syncs automatically; non-replicated: copy or re-insert

## Removing Nodes
1. Stop writes, wait for replication queue to drain
2. `SYSTEM DROP REPLICA` for replicated tables
3. Remove from cluster config, restart remaining nodes

## Resharding
- No native online resharding — create new distributed table with new sharding scheme
- `INSERT INTO new_dist SELECT * FROM old_dist` or `clickhouse-copier` for large migrations

## Cluster Recovery
- `SYSTEM RESTART REPLICA ON CLUSTER '{cluster}'` — restart replication across all nodes
- `SYSTEM SYNC REPLICA ON CLUSTER '{cluster}'` — force sync from ZooKeeper
- `SYSTEM FETCH PARTS ON CLUSTER '{cluster}'` — pull missing parts from other replicas

## Monitoring Clusters
- `system.clusters` — topology; `system.distributed_ddl_queue` — DDL status; `system.replicas` — replication
- Cross-shard queries: use Distributed table or `remote()` function

## Keeper Migration (ZooKeeper to ClickHouse Keeper)
1. Deploy ClickHouse Keeper alongside ZooKeeper
2. Snapshot data: `clickhouse-keeper-converter` or `zk-dump.sh`
3. Configure Keeper with converted snapshot
4. Update `zookeeper` config, restart one node at a time
5. Verify replication recovers, then remove ZooKeeper