database-clickhouse-weaviate

# 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): ```bash 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):** ```bash # 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`)