ClickHouse Schema Migrations

Rolling deploy safety

Apply expand-compatible migrations before deploying service versions that depend on new columns/tables. Heavy mutations and destructive contract changes are separate release steps.

Overview

ClickHouse schema changes are managed through embedded, versioned migrations that follow the same model as PostgreSQL. The runner is wired into the same CLI commands.

Scenario	What to use
Fresh install	frameworks cluster init clickhouse creates databases, applies the baseline schema, and runs migrations
Upgrading	frameworks cluster migrate applies pending migrations from pkg/database/sql/clickhouse/migrations/

cluster init clickhouse and cluster migrate are idempotent. Each migration records itself in a per-database _migrations ledger; re-running is a no-op.

Migration structure

Migrations are organized by target database, release version, and phase:

pkg/database/sql/clickhouse/migrations/
└── periscope/
    └── v0.2.31/
        ├── expand/
        │   ├── 001_add_filename_to_artifacts.sql
        │   └── 002_add_viewer_node_tracking.sql
        ├── postdeploy/
        │   └── 001_rebuild_artifact_projection.sql
        └── contract/
            └── 001_drop_legacy_artifact_view.sql

The first path segment after clickhouse/migrations/ must match a configured ClickHouse database name (today: periscope). The CLI embeds these files, applies only migrations whose database exists in the manifest, and records applied files in that database’s _migrations table.

Reconciliation model

The baseline schema in pkg/database/sql/clickhouse/<db>.sql is the cumulative current state at the latest release tag. Migrations advance the schema forward in subsequent releases.

On a fresh install, frameworks cluster init clickhouse:

1. Creates the configured databases.
2. Applies the baseline schema (DDL is IF NOT EXISTS, safe to re-run).
3. Runs every embedded migration up to the cluster’s resolved channel version. The migration SQL is idempotent (also IF NOT EXISTS), so DDL re-application no-ops on objects already created by the baseline; the _migrations ledger row is recorded by the same step.

On upgrades, frameworks cluster migrate runs only the migrations whose ledger row is missing. There is no blind ledger pre-seed: every recorded migration was produced by an actual SQL apply.

Release and recovery procedure

For a normal platform release, use the lifecycle command that owns ClickHouse, PostgreSQL/YugabyteDB, service, and transition ordering:

frameworks cluster release plan --version vX.Y.Z
frameworks cluster release apply --version vX.Y.Z --dry-run
frameworks cluster release apply --version vX.Y.Z --yes

It applies expand migrations before platform artifacts, interleaves declared release transitions, applies postdeploy migrations afterward, and always leaves contract migrations deferred.

Use the following lower-level flow only for migration development, diagnosis, or a release-specific recovery runbook:

1. Check release notes for required ClickHouse DDL.
2. Confirm the phase. Routine rolling upgrades apply expand migrations before deploying new binaries. postdeploy migrations cover heavy MODIFY COLUMN rewrites or ALTER TABLE … UPDATE/DELETE mutations and run after binaries are live. contract migrations are explicit cleanup with --yes required.
3. Apply migrations through the CLI:

frameworks cluster migrate validate
frameworks cluster migrate --phase expand

validate is fast and safe to run anywhere — it checks the embedded migration tree against the layout and per-phase safety rules.

4. Dry-run first to see what will change:

frameworks cluster migrate --phase expand --dry-run

Dry-run runs the role under ansible-playbook --check --diff. The role explicitly skips every mutating step (ledger table create, SQL apply, ledger insert) and only reports pending migrations.

5. Apply for real:

frameworks cluster migrate --phase expand --to-version v0.5.0

--to-version is optional; when omitted the cluster’s resolved channel version is used.

6. Deploy services only when the recovery runbook explicitly proves there are no unhandled release transitions:

frameworks cluster upgrade --all

7. Run any required postdeploy documented in the release notes:

frameworks cluster migrate --phase postdeploy --to-version v0.5.0

Direct cluster upgrade --all does not run declared release transitions and fails closed when one gates a downstream service. It is not the normal whole release path.

Moving the physical ClickHouse node

frameworks cluster clickhouse migrate moves the existing Periscope dataset to a new Replicated ClickHouse node. This is a data cutover, separate from the versioned schema-migration commands above. The service endpoints deliberately split readers from writers:

• periscope-ingest and periscope-metering follow write_endpoint;
• periscope-query follows read_endpoint;
• METERING_SOURCE_ID continues to identify the same logical dataset and must not change during the move.

Before the final sync, stop both Ingest and Metering. Kafka buffers analytics events while Ingest is stopped, and Metering’s PostgreSQL cursor retains its exact position. The cutover command requires both confirmations:

frameworks cluster clickhouse migrate cutover \
  --from <old-clickhouse-host> \
  --ingest-stopped \
  --metering-stopped

For the online endpoint sequence, first set write_endpoint to the new node, re-provision and resume Ingest, and let its Kafka lag drain. Then re-provision and resume Metering with the unchanged source ID. Finally set read_endpoint to the new node and re-provision Query. Do not resume Metering against the old source after Ingest has begun writing the destination, and do not decommission the old node until parity, service health, cursor progress, and Purser report consumption are verified.

Ledger details

The role creates <db>._migrations per ClickHouse database with this shape:

CREATE TABLE IF NOT EXISTS _migrations (
  version       LowCardinality(String),
  phase         LowCardinality(String),
  seq           UInt32,
  filename      String,
  checksum      FixedString(64),
  applied_at    DateTime64(3) DEFAULT now64()
) ENGINE = ReplacingMergeTree(applied_at)
ORDER BY (version, phase, seq);

ReplacingMergeTree collapses retried-insert duplicates on merge. Reads use argMax(checksum, applied_at) and surface uniqExact(checksum) so a crashed mid-write that left two distinct checksums under the same key is flagged as a hard failure, not silently resolved.

If a migration file’s content changes after it has been applied, the role fails on checksum mismatch. Treat applied migrations as immutable.

Writing migrations

Guidelines for contributors:

1. IF NOT EXISTS is mandatory on every CREATE TABLE / VIEW / DICTIONARY. This is what makes idempotent re-apply safe across both fresh installs (where the baseline already created the object) and existing clusters. The validator rejects migrations missing this guard.
2. Prefer expand-only changes in routine upgrades — add columns/tables, avoid drops, avoid type rewrites.
3. Use Nullable for new columns — old binaries ignore them, rollback is safe.
4. Update the baseline schema in the same release: edit pkg/database/sql/clickhouse/<db>.sql so a fresh install ends in the same state as an upgraded one. The migration runs both on fresh installs (no-op against the freshly applied baseline) and on upgrades (the actual schema change).
5. One migration per logical change — easier to track and debug.
6. Pick the correct phase:
   • expand: additive, safe alongside old + new binaries.
   • postdeploy: heavy MODIFY COLUMN rewrites, ALTER TABLE … UPDATE/DELETE mutations, materialized-view rebuilds.
   • contract: drops, renames, post-cleanup.
7. Large historical rewrites (long ALTER … UPDATE, MV rebuilds, replay from Kafka) need release runbooks with progress and verification, not incidental DDL inside a migration.

Example expand migration:

ALTER TABLE periscope.artifact_events
  ADD COLUMN IF NOT EXISTS filename Nullable(String) AFTER internal_name;

ALTER TABLE periscope.artifact_state_current
  ADD COLUMN IF NOT EXISTS filename Nullable(String) AFTER internal_name;

Replicated by default

The baseline provisions the analytics databases with a Replicated database engine (ENGINE = Replicated('/clickhouse/databases/<db>/{shard}', '{shard}', '{replica}')) and Replicated* table engines, backed by a Keeper ensemble. A Replicated database propagates DDL to every replica automatically, so migrations use plain CREATE/ALTER statements and must not add ON CLUSTER '{cluster}' — it is unnecessary here and can conflict with the database’s own DDL replication:

ALTER TABLE periscope.artifact_events
  ADD COLUMN IF NOT EXISTS filename Nullable(String) AFTER internal_name;

Keeper is required in every topology. The baseline creates the Replicated database and Replicated* tables unconditionally, and those engines cannot exist without Keeper. A singleton (dev / single-node) therefore still runs a one-node standalone clickhouse-keeper service — a separate systemd unit (/etc/clickhouse-keeper/keeper_config.xml) that the Ansible role configures and orders before clickhouse-server, not an in-process/embedded Keeper — and applies the exact same migration files with a single shard/replica; only the Keeper ensemble size differs from production. The role’s empty-clickhouse_keeper_nodes “bare” mode creates an ordinary Atomic database with no Keeper and is not compatible with this always-Replicated analytics baseline; do not use it for the analytics databases.

Rollback

• Nullable columns are safe to leave — older service versions simply won’t populate them.
• Don’t drop columns during rollback unless explicitly required.
• Re-deploying an older service version is usually sufficient.
• Do not rely on DDL rollback for normal recovery — keep expand changes compatible, redeploy the previous binary, and leave extra columns in place until the contract release.

Troubleshooting

“Unknown column” errors:

Code: 16. DB::Exception: Unknown column 'filename' in column list

Cause: Service deployed before migration was applied. Fix: Apply the migration, then restart the service.

Check ledger contents:

SELECT version, phase, seq, filename, checksum, applied_at
FROM periscope._migrations
ORDER BY version, phase, seq
FORMAT Vertical;

Check if a column exists:

SELECT name, type FROM system.columns
WHERE database = 'periscope' AND table = 'artifact_events' AND name = 'filename';