ClickHouse Migrations#

Langfuse manages ClickHouse schema changes with golang-migrate-based SQL migration files under packages/shared/clickhouse/migrations/. Every schema change is expressed as a pair of numbered .up.sql / .down.sql files (e.g., 0001_traces.up.sql, 0001_traces.down.sql) 1 2.

Directory Layout: Clustered vs. Unclustered#

The migrations directory contains two parallel subdirectories 3:

The migration runner up.sh reads the CLICKHOUSE_CLUSTER_ENABLED environment variable to select the correct path:

• CLICKHOUSE_CLUSTER_ENABLED=false → runs migrations/unclustered/ with x-migrations-table-engine=MergeTree 4
• Any other value → runs migrations/clustered/ with x-migrations-table-engine=ReplicatedMergeTree and x-cluster-name=${CLICKHOUSE_CLUSTER_NAME} 5

The only material difference between paired files (e.g., clustered/0020_analytics_observations.up.sql vs unclustered/0020_analytics_observations.up.sql) is the presence of ON CLUSTER default in the clustered DDL and the use of ReplicatedReplacingMergeTree vs ReplacingMergeTree table engines 6 7.

Companion scripts down.sh and drop.sh follow the same topology-selection pattern for rollbacks and full teardown.

View Migration Patterns#

Two distinct patterns are used for migrating ClickHouse views, depending on whether the view is being created from scratch or updated in-place.

Pattern 1: CREATE VIEW + DROP VIEW (initial creation)#

When a view is first introduced, the up migration uses a plain CREATE VIEW and the down migration uses DROP VIEW IF EXISTS. Example: the analytics_observations view (migration 0020):

• Up — 0020_analytics_observations.up.sql: CREATE VIEW analytics_observations AS SELECT ...
• Down — 0020_analytics_observations.down.sql: DROP VIEW IF EXISTS analytics_observations;

Clustered variants add ON CLUSTER default 8.

Pattern 2: CREATE OR REPLACE VIEW (in-place column additions)#

When an existing view is updated (e.g., to add a column), both the up and down migrations use CREATE OR REPLACE VIEW. The down migration simply re-declares the prior column list — no DROP is involved. This avoids downtime from a destructive drop/recreate cycle.

Example: migration 0036 adds ingested_sdks Map(String, UInt64) to analytics_scores 9:

• Up — CREATE OR REPLACE VIEW analytics_scores [ON CLUSTER default] AS SELECT ..., sumMap(...) AS ingested_sdks ...
• Down — CREATE OR REPLACE VIEW analytics_scores [ON CLUSTER default] AS SELECT ... (omits ingested_sdks, restoring the previous definition)

The CREATE OR REPLACE pattern is used consistently for both clustered and unclustered variants; the only difference is the ON CLUSTER default clause 9.

Key Reference Files#

Note: Some analytics views (e.g., analytics_events_core) are defined in dev-tables.sh rather than numbered migration files, and require manual CREATE OR REPLACE VIEW per region for production deployments 9.

Adding a New Migration#

1. Create XXXX_<description>.up.sql and XXXX_<description>.down.sql in both clustered/ and unclustered/ with the appropriate engine/cluster clauses.
2. For view additions: use CREATE VIEW in .up.sql and DROP VIEW IF EXISTS in .down.sql.
3. For view modifications: use CREATE OR REPLACE VIEW in both .up.sql and .down.sql; the down file should restore the previous column set.
4. Run up.sh (or down.sh to roll back) with the required environment variables (CLICKHOUSE_URL, CLICKHOUSE_MIGRATION_URL, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD, CLICKHOUSE_CLUSTER_ENABLED) 10.