/clickhouse-migrations

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 by the cluster that owns the object)

Only the members of NodeRole in posthog/clickhouse/client/connection.py are valid: ALL, DATA, INGESTION_EVENTS, INGESTION_SMALL, INGESTION_MEDIUM, ENDPOINTS, LOGS, AI_EVENTS, AUX, BATCH_EXPORTS, OPS, SESSIONS. A name that is not in that enum fails at import, which aborts migration discovery and takes every job that runs migrations down with it.

Pick the role by which cluster owns the object, not by its type alone:

• [NodeRole.DATA]: anything on the main cluster — sharded tables, non-sharded replicated tables, distributed read tables, views, dictionaries. The default, and right for most migrations.
• [NodeRole.INGESTION_SMALL]: writable tables, Kafka tables, materialized views on the ingestion layer
• [NodeRole.OPS], [NodeRole.LOGS], [NodeRole.AUX], [NodeRole.AI_EVENTS], [NodeRole.SESSIONS], [NodeRole.BATCH_EXPORTS]: objects that live on a satellite cluster. A table for one of those on DATA lands on the wrong nodes and leaves the intended cluster without it, so check where the object is read and written before defaulting to DATA. Dev runs the same satellite clusters as US/EU prod — never branch on CLOUD_DEPLOYMENT to give dev a different layout.
• [NodeRole.ALL]: rarely used

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)

Kafka engine — kafka_engine() returns the ENGINE clause only, so every table appends its own SETTINGS. Start from this baseline:

SETTINGS kafka_skip_broken_messages = 100,
         kafka_num_consumers = {kafka_num_consumers(1)},
         kafka_thread_per_consumer = 1,
         kafka_poll_timeout_ms = 10000,
         kafka_max_block_size = 100000

• kafka_skip_broken_messages defaults to 0, where one malformed message stops the consumer for good.
• kafka_poll_timeout_ms is 10000 for WarpStream, which does not support fetch.min.bytes.
• kafka_max_block_size defaults to about a million rows, so 100000 is a reduction; lower for wide rows, not below ~1000.
• kafka_num_consumers is per node, so the role's node count multiplies it; the total must not exceed the topic's partitions.
• Columns and settings are fixed at creation: drop and recreate the table with its MV, no SYNC. ALTER TABLE <mv> MODIFY QUERY covers an MV-SELECT-only change. Never for kafka_events_json_ws / events_json_ws_mv — see Critical rules.

posthog/clickhouse/migrations/AGENTS.md has the sizing rules and the rest.

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
• Never write CODEC(ZSTD(1)) on a column — the server already compresses every column with ZSTD, so it buys nothing. Declare a CODEC only where it beats that default, and check the ORDER BY first: Delta/DoubleDelta need the column near-sorted in storage order (a leading sort-key prefix), and lose on a column the key only buckets or omits. T64/Gorilla are ordering-independent. Put it on the storage table only — a CODEC on a Distributed or Kafka table is inert metadata that drifts from the sharded table it fronts.
• Never write a DROP COLUMN migration yourself — DROP COLUMN can get stuck in ClickHouse and block releases. Column removal is a two-step process: (1) the ClickHouse team drops the column directly on the cluster, then (2) you write a migration with the matching DROP COLUMN so the codebase schema stays in sync. Never initiate the drop from a migration without the ClickHouse team having done step 1 first.
• Never drop or recreate kafka_events_json_ws or events_json_ws_mv — these tables are a no-go zone. The MV definition differs significantly between US prod, EU prod, and dev (dozens of environment-specific mat_* columns) and those differences are not reflected in the repo. Dropping and recreating from repo SQL would destroy the environment-specific schema and break event ingestion. Any change must go through the ClickHouse team.

PR scope

A PR that contains a ClickHouse migration must be migration-only. Do not mix migration files with feature code, API changes, model changes, or frontend changes in the same PR. Migration-related files are:

• The migration file itself (posthog/clickhouse/migrations/0NNN_*.py)
• SQL definition files the migration depends on (e.g. posthog/clickhouse/sql/*.py, table engine helpers)
• Tests that directly exercise the migration or the SQL definitions it touches

If you need both a schema change and application code that uses the new schema, ship the migration first in its own PR and merge it before the application-code PR.

Local setup parity

No table should exist only in the cloud. Every table created via migration must also exist in a local dev environment.

Some migrations are cloud-guarded and skipped in local/hobby dev. Gate on posthog.run_mode, never on settings.CLOUD_DEPLOYMENT directly, because the clickhouse-migrations-use-run-mode semgrep rule blocks raw comparisons:

from posthog.run_mode import RunMode, run_mode

operations = (
    []
    if not run_mode().is_deployed_cloud  # US/EU/DEV
    else [...]
)

run_mode().is_cloud also counts E2E, so it is wrong for a migration. That one matches posthog.cloud_utils.is_cloud.

Call run_mode() where you need it rather than assigning a module-level constant. posthog/clickhouse/test/test_migrations.py re-imports every migration under a patched posthog.settings.CLOUD_DEPLOYMENT to check each deployment's branch for stray ON CLUSTER, and a cached value would silently skip that coverage.

If you create a new table inside such a guard, you must also add its SQL function to posthog/clickhouse/schema.py in the appropriate tuple so the table gets created locally:

The only exception is tables whose definition intentionally differs per environment and is not tracked in the repo (e.g. the no-go zone events_json_ws_mv table).

Dictionary credentials: when a dictionary uses a SOURCE(CLICKHOUSE(...)), resolve the source user/password via get_clickhouse_creds(ClickHouseUser.DICT_READER) and interpolate them into the USER/PASSWORD clause — do not hardcode default/CLICKHOUSE_USER or omit credentials. This keeps dictionary auth on the dedicated low-privilege dict_reader user, decoupled from default; it falls back to default creds when the env vars are unset. See posthog/models/exchange_rate/sql.py for the pattern.

Declarative schema (HCL) must agree

Some roles are also declared in posthog/clickhouse/hcl/, and the multinode migration smoke re-introspects the migrated cluster and fails on any drift between the live schema and the committed golden. A table added by a migration on a managed role therefore needs the HCL updated in the same PR, or CI fails at check_live_hcl with a DRIFT: block naming your new objects.

The data role is managed for the local-multi composition, so a new table on NodeRole.DATA counts. After writing the migration:

HCL=posthog/clickhouse/hcl
# add the table to the layer that composes it, e.g. $HCL/roles/data/local/tables.hcl
bash $HCL/gen-golden.sh && bash $HCL/gen-sql.sh   # refresh generated artifacts
bash $HCL/check.sh                                # must exit 0

Read posthog/clickhouse/hcl/README.md before editing a layer — it covers which layer to pick, abstract column lists, and the codegen path that writes the migration for you.

Testing

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