ClickHouse

When to use: ClickHouse queries, Goose migrations, chdb test schema, or telemetry storage paths.

ClickHouse queries

ClickHouse adapter stack remains SQL-oriented in packages/platform/db-clickhouse.

All ClickHouse queries must use parameterized bindings ({name:Type} syntax with query_params) — never interpolate user-supplied values directly into SQL strings.

ClickHouse migrations (Goose)

Install goose (if not already installed):

brew install goose

Migration files live in packages/platform/db-clickhouse/clickhouse/migrations/:

• unclustered/ — single-node deployments (local dev, default)
• clustered/ — distributed deployments (LAT_CLICKHOUSE_CLUSTER_ENABLED=true)

Goose tracks applied migrations automatically in the goose_db_version table. The repo also keeps packages/platform/db-clickhouse/clickhouse/.migration-lock, regenerated by ch:create, solely to force git conflicts when developers create migrations in parallel.

Migration execution safety (agents)

Same rule as Postgres: do not run ch:* or ch:schema:dump unless the user explicitly asked in this conversation.

Commands (run from repo root):

# Apply all pending migrations
pnpm --filter @platform/db-clickhouse ch:up

# Roll back last migration
pnpm --filter @platform/db-clickhouse ch:down

# Show migration status
pnpm --filter @platform/db-clickhouse ch:status

# Create a new migration (creates the next sequential file in both unclustered/ and clustered/)
pnpm --filter @platform/db-clickhouse ch:create <migration_name>

# Roll back ALL migrations (equivalent to drop)
pnpm --filter @platform/db-clickhouse ch:drop

# Reset ClickHouse volume and re-migrate (nuclear option)
pnpm --filter @platform/db-clickhouse ch:reset

# Seed sample span data
pnpm --filter @platform/db-clickhouse ch:seed

Creating migrations

1. ch:create <name> — creates the next sequential migration (for example 00016_name.sql) in both unclustered/ and clustered/, and updates clickhouse/.migration-lock
2. Fill in both files (see rules below)
3. Commit both migration files plus clickhouse/.migration-lock

Migration file rules

• Each migration is a single .sql file with -- +goose Up and -- +goose Down sections
• Always include -- +goose NO TRANSACTION (ClickHouse does not support transactions)
• ClickHouse migration history is append-only in this repository. Do not edit existing Goose migration files; add a new migration in both unclustered/ and clustered/ instead.
• For additive changes to existing tables, prefer ordinary ALTER TABLE or additive projection migrations with sensible defaults unless the change truly requires a table rebuild.
• unclustered/: use standard table engines (e.g. ReplacingMergeTree)
• clustered/: add ON CLUSTER default and use Replicated* engines

Clustered migration reliability (replica lag / Code 517)

In clustered ClickHouse, replicas can temporarily lag DDL metadata propagation. A migration can fail with:

• code: 517
• Code: 517
• doesn't catchup with latest ALTER query updates

Use these authoring rules to reduce failures:

• Keep migrations idempotent (IF EXISTS / IF NOT EXISTS) so retries are safe.
• Prefer additive schema changes over destructive rewrites.
• Keep DDL batches small; avoid chaining many dependent ALTER statements in one migration.
• For tightly-coupled changes on the same table in replicated clusters, prefer one ALTER TABLE ... with multiple actions over multiple dependent ALTER statements.
• If statement B depends on metadata introduced by statement A, prefer splitting them into separate migration files.
• Avoid coupling view rebuilds and many base-table changes in one large migration when possible.
• Run one migration runner per environment (never concurrent ch:up against the same cluster).

Execution safety:

• packages/platform/db-clickhouse/clickhouse/scripts/up.sh retries transient replica lag errors from goose ... up.
• In clustered mode, migration sessions set alter_sync, distributed_ddl_task_timeout, and replication_wait_for_inactive_replica_timeout to improve DDL convergence.
• Retry tuning env vars:
  • LAT_CLICKHOUSE_MIGRATION_MAX_RETRIES (default 20)
  • LAT_CLICKHOUSE_MIGRATION_RETRY_DELAY_SECONDS (default 5)
  • LAT_CLICKHOUSE_MIGRATION_MAX_RETRY_DELAY_SECONDS (default 30)
• Clustered DDL tuning env vars:
  • LAT_CLICKHOUSE_MIGRATION_ALTER_SYNC (default 2)
  • LAT_CLICKHOUSE_MIGRATION_DISTRIBUTED_DDL_TASK_TIMEOUT_SECONDS (default 300)
  • LAT_CLICKHOUSE_MIGRATION_REPLICA_WAIT_TIMEOUT_SECONDS (default 300)