[QUESTION] cluster migrations

[LHLHLHE]

I noticed that the MergeTree engine is always applied to the migration table, which is why migration data is not replicated to other nodes, even with "migration_cluster": "cluster" in the database settings.

https://github.com/jayvynl/django-clickhouse-backend/blob/52ced959768c5b0ccc770208f104b31b2b0f9587/clickhouse_backend/patch/migrations.py#L33

Is this done on purpose? I would like to be able to track the migration history on all nodes in the cluster.

@jayvynl

[jayvynl]

Yes, it's intentional.

Think of this situation. There is a cluster of two node A and node B. A Model of MergeTree engine is defined as:

class Student(models.ClickhouseModel):
    name = models.StringField()
    address = models.StringField()
    score = models.Int8Field()

    class Meta:
        engine = models.MergeTree()

DATABASES = {
    "default": {
        "ENGINE": "clickhouse_backend.backend",
        "OPTIONS": {
            "migration_cluster": "cluster",
        }
    },
    "B": {
        "ENGINE": "clickhouse_backend.backend",
        "PORT": 9001,
        "OPTIONS": {
            "migration_cluster": "cluster",
        }
    }
}

First, apply migrations on the default database, Student table is created on Node A.

python manage.py runmigrations

Then, apply migrations on Node B.

python manage.py runmigrations --database B

If migrations table is created on cluster, django will see that Student have already been created, so Student is not created in node B.

Remember, models with plain MergeTree engine will only be created in the node which you have runmigrations. If you want data replication, you should use Replicated engine. If you want data distribution, you should use Distributed engine.

If you want to query all tables created in the cluster, use the following SQL:

select app, name, applied from clusterAllReplicas('your cluster name', currentDatabase(), 'django_migrations') where not deleted

[LHLHLHE]

I understand this, but I would like to be able to check migrations in node B when node A is down

[PierreF]

I did hit similar issue (actually unable to apply RunSQL migration that use CREATE TABLE ... ON CLUSTER with a 4 nodes Clickhouse cluster that is accessed though a load-balancer, i.e. application can't connect to one specific Clickhouse node).

After thinking a bit I came to the following question: Should we run python manage.py migration --database clickhouse-node1, python manage.py migration --database clickhouse-node2, ... ? I means, is the migration process assume we always run python manage.py migration against all Clickhouse nodes (regardless of whether the migration create a clustered table or local only table) ?

That would raise some concern about requirement to run migration multiple time and that migration runner needs to known the list of Clickhouse nodes addresses. But doing so should:

• solve my issue with Clickhouse behind a load-balancer. Since I would do RunSQL without ON CLUSTER and apply to to every node by calling manage.py migration on every node.
• solve this issue, since all nodes will have the same django_migrations content (and thanks to Meta.cluster and this condition, clustered table would only be created once)

Without running migration on every nodes, since running python manage.py migration only update the local django_migration table, I don't see how to avoid issue if a node is lost. In that case, cluster migrations applied from this node will be lost and they will be re-tried the next time you call python manage.py migration.

Does my thinking makes sense and is it the clickhouse-backend's expectation that we call python manage.py migration on all our Clickhouse nodes ? Does it make sense to propose an option to create django_migrations as replicated table which could allow user that only use clustered table (no local-only table) to run the migration only once ?

[jayvynl]

Maybe you are right, migration table should be distributed, add an host field to track node running migration.

[avacaru]

Having the same issue, on a cluster with 2 shards and 2 replicas each, the django_migrations table is created on all nodes, but only one of the nodes has the actual migration data.

The next time the migrations run, depending on which node the load balancer steers the traffic to, the migrations fail with:

DB::Exception: There was an error on [my-clickhouse-server-0:9000]: Code: 57. DB::Exception: Table my_schema.my_table already exists. (TABLE_ALREADY_EXISTS) (version 24.3.8.13 (official build))

That is because my models use the ReplicatedMergeTree engine and the Meta.cluster = 'my_cluster'.

An idea for the django clickhouse settings would be to create a distributed table for django_migrations if migrations_cluster is set. Plus, an extra setting for the migrations_replication_path to allow the configuration of a zookeper path with macros like /clickhouse/tables/{shard}/{database}/{table} for the django_migrations table?

Manual creation of the tables:

CREATE TABLE r_django_migrations ON CLUSTER my_cluster (
    id Int64,
    app String,
    name String,
    applied DateTime(6, 'UTC'),
    deleted Bool
)
ENGINE ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}', '{replica}')
ORDER BY (app, name);

CREATE TABLE django_migrations ON CLUSTER my_cluster AS r_django_migrations
ENGINE Distributed('my_cluster', 'my_schema', 'r_django_migrations', rand());

[rcampos87]

I have a very similar issue where I have a cluster with 2 shards behind a load-balancer which makes running migrations inconsistent since the lb can connect to any of the shards.

I was able to modify django-clickhouse-backend's patch to create a distributed table along with a merge tree and its been working well in my case, making the tables being created on all shards via migration_cluster and the data is then distributed to shards.

[jayvynl]

The "migration_cluster" option simply indicates that django_migration tables will be created on a specific cluster. It does not mean that all tables — especially those using the MergeTree engine — should be created on that cluster. This option should not be relied upon for distributed table management.

If you want to use the Distributed engine, you must explicitly specify it in the model definition. Do not assume that setting a migration cluster will automatically enable or configure distribution.

Regarding whether to add an ON CLUSTER clause to the MergeTree engine: this is generally unnecessary, because the Distributed engine already provides the ability to create and manage tables across clusters. The only conceivable use case would be if you want to create individual local MergeTree tables on each node (e.g., one per node in a 4-node cluster) without using the Distributed engine. However, such a setup is uncommon and lacks strong justification in most real-world scenarios.

Importantly, if you are accessing a ClickHouse cluster through a load balancer, you should not use the MergeTree engine directly. Since MergeTree tables are local to each node, querying them via a load balancer without coordination can result in inconsistent or partial data retrieval — you might hit only one node at random. For consistent, cluster-wide queries, always use the Distributed engine, which ensures queries are broadcast and results are aggregated properly.

That said, if there is a valid reason for accessing MergeTree tables directly through a load balancer, please share your use case — we’re open to feedback, but the current recommendation stands: use Distributed for cross-node access.

[avacaru]

I 100% agree with you, @jayvynl! For the exact reasons you enumerated I believe that the django_migrations table should NOT be a MergeTree when the settings file indicates migration_cluster: some_cluster is set.

[rcampos87]

@jayvynl can I open a PR to your repo? you might understand my use case better, and might help others facing this issue.

[jayvynl]

@jayvynl can I open a PR to your repo? you might understand my use case better, and might help others facing this issue.

Yes, of course.

[jayvynl]

I 100% agree with you, @jayvynl! For the exact reasons you enumerated I believe that the django_migrations table should NOT be a MergeTree when the settings file indicates migration_cluster: some_cluster is set.

If a migration_cluster is configured, the migrations table should ideally be a distributed table. There are two main scenarios to consider. First, if you're using a load balancer, it's best to avoid regular MergeTree tables and instead use a distributed table to ensure consistency and proper query routing. Second, if no load balancer is used—such as in the example from my repository where each node has its own direct connection—it is acceptable to use a regular MergeTree table, as long as it's consistently managed across nodes.

In this case, how should we handle ordinary MergeTree tables? There are two possible approaches.

The first approach is to add a host field to the migration table to explicitly track on which node each MergeTree table was created.

The second approach is to avoid distinguishing between nodes altogether. Instead, when applying migrations, always include the ON CLUSTER clause in the table creation statement so that the same MergeTree table is created on all nodes uniformly.

Each method has trade-offs: the first offers more visibility and control but requires additional logic to manage host-specific records; the second simplifies management but may lead to redundant or unused tables on some nodes.

Which is better?