You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ClickHouse cluster mode runs CREATE ... AS SELECT on every replica #6008
In self-hosted ClickHouse cluster mode (cluster: set on the gateway, non-Cloud), SQLMesh materializes full-refresh / CTAS models with a single CREATE TABLE … AS SELECT … ON CLUSTER <cluster>. Because the statement is ON CLUSTER, every replica executes the whole statement — SELECT and INSERT included. On an N-replica cluster this causes:
N× the source reads/compute for one result (each replica independently re-scans the same upstream source).
Deadlock/failure under insert_quorum > 0: the N concurrent quorum inserts never reach a clean quorum, so each blocks for insert_quorum_timeout (default 600s) and then fails with Code 319 UNKNOWN_STATUS_OF_INSERT, rolling the new table back to 0 rows.
The long CTAS occupies the distributed-DDL queue for its whole duration, head-of-line-blocking every other ON CLUSTER operation cluster-wide.
ClickHouse Cloud mode already avoids this: it creates the table EMPTY (schema only) then runs a plain single-node INSERT … SELECT that ReplicatedMergeTree replicates. Self-hosted cluster mode should do the same.
Root cause
In sqlmesh/core/engine_adapter/clickhouse.py, ClickhouseEngineAdapter._create_table, both the empty-CTAS split and the follow-up single-node insert are gated on engine_run_mode.is_cloud:
empty_ctas=(self.engine_run_mode.is_cloudandexpressionisnotNone),
...
if (self.engine_run_mode.is_cloudandtable_kind!="VIEW"andexpressionandnot (<limit0>)):
self._insert_append_query(table_name, expression, ...)
So cluster mode falls through to a full CREATE … AS SELECT ON CLUSTER.
Suggested fix
Broaden those two gates to is_cloud or is_cluster, so cluster mode also does empty-CREATE + single-node INSERT … SELECT and lets ReplicatedMergeTree replicate the parts. (A single writer is also fine under insert_quorum > 0 — it just waits for one peer to confirm, the normal path.)
Verified with a local monkeypatch broadening those gates: the build then did NewPart on one replica + DownloadPart on the others, produced the correct row count consistent across all replicas, read the source once, and no longer hit Code 319.
Gateway in cluster mode (cluster: set), models stored as ReplicatedMergeTree, server-side insert_quorum >= 2.
Run a full-refresh model whose query reads from an external source.
Observe via clusterAllReplicas('…','system.processes'): the SELECT pipeline runs on all replicas; after insert_quorum_timeout the CREATE fails with UNKNOWN_STATUS_OF_INSERT (code 319) and the table is left empty.
changed the title [-]ClickHouse cluster mode runs CREATE ... AS SELECT on every replica (N× source reads; deadlocks under insert_quorum)[/-][+]ClickHouse cluster mode runs CREATE ... AS SELECT on every replica[/+]on Sep 1, 2026
I tried reproducing this against the 3-replica cluster in the repo's docker compose, and I think the diagnosis holds but the suggested fix would break a common case.
Broadening the gates to is_cloud or is_cluster only works if the table replicates itself. With plain MergeTree, tables created ON CLUSTER are independent per node, so an empty CREATE followed by a single-node INSERT leaves the other replicas empty:
plain MergeTree, INSERT on one node -> 3 / 0 / 0 rows
ReplicatedMergeTree, INSERT on one node -> 3 / 3 / 3 rows
That matches what you saw, since your models are ReplicatedMergeTree. But SQLMesh's DEFAULT_TABLE_ENGINE is plain MergeTree — you only get a Replicated engine if you set storage_format yourself. So anyone on the default in cluster mode would go from "wasteful but correct" to one populated node and N-1 empty ones, which seems worse than the current behaviour.
I also checked whether a Replicated database engine would confuse this, since that's the setup in #5785. On 24.7 it replicates the DDL but not the data — a table declared MergeTree stays MergeTree on every node and a single-node insert still gives 3/0/0. So the engine name does seem to be a reliable signal, at least on that version.
If that's right, gating on the resolved engine rather than on cluster mode would cover both cases: cloud, or cluster where the engine is Replicated*/Shared*. storage_format is already in kwargs at _create_table, and resolving it is the same couple of lines _build_table_properties_exp already uses, so it looks doable without much plumbing.
Two caveats. I only tested 24.7 (what the repo pins), and this area has moved between versions, so newer servers may behave differently. And I don't have a multi-replica cluster with insert_quorum set, so I can't confirm the fix actually clears the code 319 deadlock — that part still needs your setup.
Happy to work on this if nobody else has picked it up.
Activity
[-]ClickHouse cluster mode runs CREATE ... AS SELECT on every replica (N× source reads; deadlocks under insert_quorum)[/-][+]ClickHouse cluster mode runs CREATE ... AS SELECT on every replica[/+]cookieark7 commentedon Sep 6, 2026
I tried reproducing this against the 3-replica cluster in the repo's docker compose, and I think the diagnosis holds but the suggested fix would break a common case.
Broadening the gates to is_cloud or is_cluster only works if the table replicates itself. With plain MergeTree, tables created ON CLUSTER are independent per node, so an empty CREATE followed by a single-node INSERT leaves the other replicas empty:
That matches what you saw, since your models are ReplicatedMergeTree. But SQLMesh's DEFAULT_TABLE_ENGINE is plain MergeTree — you only get a Replicated engine if you set storage_format yourself. So anyone on the default in cluster mode would go from "wasteful but correct" to one populated node and N-1 empty ones, which seems worse than the current behaviour.
I also checked whether a Replicated database engine would confuse this, since that's the setup in #5785. On 24.7 it replicates the DDL but not the data — a table declared MergeTree stays MergeTree on every node and a single-node insert still gives 3/0/0. So the engine name does seem to be a reliable signal, at least on that version.
If that's right, gating on the resolved engine rather than on cluster mode would cover both cases: cloud, or cluster where the engine is Replicated*/Shared*. storage_format is already in kwargs at _create_table, and resolving it is the same couple of lines _build_table_properties_exp already uses, so it looks doable without much plumbing.
Two caveats. I only tested 24.7 (what the repo pins), and this area has moved between versions, so newer servers may behave differently. And I don't have a multi-replica cluster with insert_quorum set, so I can't confirm the fix actually clears the code 319 deadlock — that part still needs your setup.
Happy to work on this if nobody else has picked it up.