ClickHouse Migrations

Read posthog/clickhouse/migrations/AGENTS.md for comprehensive patterns, cluster setup, examples, and ingestion layer details.

Quick reference

Migration structure

operations = [ run_sql_with_exceptions( SQL_FUNCTION(), node_roles=[...], sharded=False, # True for sharded tables is_alter_on_replicated_table=False # True for ALTER on replicated tables ), ]

Node roles (choose based on table type)

• [NodeRole.DATA] : Sharded tables (data nodes only)

• [NodeRole.DATA, NodeRole.COORDINATOR] : Non-sharded data tables, distributed read tables, replicated tables, views, dictionaries

• [NodeRole.INGESTION_SMALL] : Writable tables, Kafka tables, materialized views on ingestion layer

Table engines quick reference

MergeTree engines:

• AggregatingMergeTree(table, replication_scheme=ReplicationScheme.SHARDED) for sharded tables

• ReplacingMergeTree(table, replication_scheme=ReplicationScheme.REPLICATED) for non-sharded

• Other variants: CollapsingMergeTree , ReplacingMergeTreeDeleted

Distributed engine:

• Sharded: Distributed(data_table="sharded_events", sharding_key="sipHash64(person_id)")

• Non-sharded: Distributed(data_table="my_table", cluster=settings.CLICKHOUSE_SINGLE_SHARD_CLUSTER)

Critical rules

• NEVER use ON CLUSTER clause in SQL statements

• Always use IF EXISTS / IF NOT EXISTS clauses

• When dropping and recreating replicated table in same migration, use DROP TABLE IF EXISTS ... SYNC

• If a function generating SQL has on_cluster param, always set on_cluster=False

• Use sharded=True when altering sharded tables

• Use is_alter_on_replicated_table=True when altering non-sharded replicated tables

Testing

Delete entry from infi_clickhouse_orm_migrations table to re-run a migration.