Failure: tiered_storage/alter table policy — disk utilization asserts encode a state ClickHouse does not guarantee

Affected tests:

• /tiered storage/normal/alter table policy — 75 failures in 2775 runs (2.7%) over 180 days
• /tiered storage/with minio/alter table policy — same block runs here too (minio is not in the with_s3amazon or with_s3gcs or with_cas condition)

Affected files:

• tiered_storage/tests/alter_table_policy.py

Description

Two asserts in the same block fail intermittently, with opposite causes:

line 147  check_disk_utilization("external", 30)   118 failures, 2025-07-10 → 2026-08-13
line 143  check_disk_utilization("jbod1", 10)       18 failures, 2025-09-17 → 2026-08-13

After the ALTER to jbods_with_external, the test reads system.disks and asserts a specific data distribution. That distribution is not deterministic. Three outcomes are all correct ClickHouse behaviour — the asserts only accept the middle one:

	What happened	jbod1	jbod2	external	Assert that breaks
A	background move relocated the 37MB and 24MB parts	5.06	9.07	67.42 (33.71%)	none
B	move relocated only the 37MB part — already enough for move_factor	14.13	25.17	42.24 (21.12%)	external ≥ 30
C	merge combined the 6 parts into one >10MB block, written straight to external	~0	0	81.54 (40.77%)	jbod1 ≥ 10

(MiB used; total is always 81.5 MiB. Values captured from CI and from local reruns.)

Not a regression — identical signature since 2025-07-10 across 24.8 / 25.3 / 25.8 / 26.1 / 26.3 / 26.4 / 26.5 and both architectures.

Evidence

1. The asserts contradict the documented behaviour.

"ClickHouse sorts existing parts by size from largest to smallest (in descending order) and selects parts with the total size that is sufficient to meet the move_factor condition. If the total size of all parts is insufficient, all parts will be moved."

"If the a size of a merged part estimated to be bigger than max_data_part_size_bytes then this part will be written to a next volume"

Moving only the 37MB part already satisfies move_factor → external stops at 38.99 MB = 18.59%, which is the single most common failure value in the CI database. And both quotes allow the source volume to end up empty, which is what jbod1 ≥ 10% forbids.

The SRS is silent on quantity — RQ.SRS-004.Configuration.StorageConfiguration.MoveFactor only says "the amount of free space which when exceeded causes data to be moved to the next volume".

2. The thresholds were copied from one observed run, not derived.

Check	Observed value	Threshold written
jbod1	12.646%	≥ 10
jbod2	0%	≥ 0
external (1st)	33.709%	≥ 30
external (2nd)	43.769%	≥ 39

Four thresholds, each the observed value rounded down by ~10-20%. ≥ 39 is not derivable from anything. jbod2 ≥ 0 is a no-op (the query wraps in greatest(..., 0)).

3. Upstream has no such asserts. tests/integration/test_multiple_disks/test.py::test_alter_policy checks only storage_policy in system.tables, that the invalid transition raises, and that the valid one succeeds. The disk-utilization asserts were added here on top.

4. Retrying does not help. A run with retries(timeout=60, delay=2) read the identical value on all 30 attempts — the system was already at rest at 21.12%, state B above.

5. The declared requirement is unaffected. The test declares only RQ.SRS-004.Volume.StoragePolicy.AddingToTable.AlterTable — "only when new policy have all the disks from the previous policy, otherwise an exception SHALL be raised" — covered by the four ALTERs (two valid, two expecting exception 36). None of the removed asserts touch it.

Solution

Replace the four asserts with checks derived from capacity and from documented placement rules.

1st block, after inserting 1+5+5+9MB:

with By("external disk utilization is above 5%%"):
    check_disk_utilization("external", 5)

81 MiB of data − 70 MiB of main capacity (jbod1 40 + jbod2 30) = 11 MiB must be on external = 5.5%

2nd block, after inserting 20MB:

with And("I check the new part is on the external volume"):
    output = node.query(
        f"SELECT disk_name, active FROM system.parts WHERE table = '{name}' FORMAT TabSeparated"
    ).output
    assert "external\t1" in output, error()

with By("external disk utilization is above 15%%"):
    check_disk_utilization("external", 15)

The 20MB part exceeds max_data_part_size_bytes = 10MB, so the main volume cannot take it — decided at insert time, no background involved. This is the same check the S3 branch already performs.

101 MiB of data − 70 MiB of main capacity = 31 MiB must be on external = 15.5%

Every floor is derived from capacity or from a documented placement rule, none from observation. The 2nd block now verifies through two independent sources: disk space (system.disks) and part placement (system.parts).

Unchanged: jbod1 ≥ 90% and jbod2 ≥ 70% in the earlier steps (insert placement, deterministic, never failed) and the four ALTERs.

Fix: https://github.com/Altinity/clickhouse-regression/commit/7ebefc26d8aaa2040d6914bf6a36621afcaa8c5e

Verification

30 consecutive runs of alter table policy on the local branch, 30 passes. External utilization observed across the 60 checks of each block (30 runs × 2 example tables):

1st block (floor 5%)		2nd block (floor 15%)
18.59%	×22	28.65%	×22
21.12%	×13	31.18%	×13
31.18%	×1	41.24%	×1
33.19%	×4	43.25%	×4
33.71%	×19	43.77%	×19
36.24%	×1	46.30%	×1

Six distinct resting states per block. The old thresholds (≥ 30 and ≥ 39) were set just below two of them — 35 of these 60 checks would have failed under the old thresholds, in each block. The 18.59% and 21.12% readings are states B and A reproducing live.

Margin against the derived floors: 18.59% vs 5% (3.7×) in the 1st block, 28.65% vs 15% (1.9×) in the 2nd. No retries, no waiting.

Note: the 58% hit rate here reflects a fast local machine landing in the non-golden states far more often than CI, where the same asserts failed 2.7% of the time.

References

• MergeTree — multiple block devices for data storage
• upstream test_alter_policy
• Failing run: https://github.com/Altinity/clickhouse-regression/actions/runs/31281037311/job/93565163470

Start with tiered_storage/tests/alter_table_policy.py and the alter table policy test blocks described in the issue; review how they query system.disks and system.parts. Replace the nondeterministic utilization expectations with checks based on documented capacity and placement rules, then verify the test passes repeatedly without retries.