sluice

The scan-once optimization that walls you

Collapsing a table's index builds into one ALTER so InnoDB scans once is a real win — until a per-statement time limit. On PlanetScale a 12.8 GB table's four-index ALTER hit the ~900 s wall at 900,004 ms, and --resume re-issued the identical statement, so the build could never converge. The very property that makes the optimization efficient — one big statement — is what the wall kills.

Landed in sluice · 2026-08-19 · MySQL & Vitess

Observed — a real 30 GB PlanetScale us-east-1 → us-east-2 region move (7–8 hours, 4–5 restarts). Fixed in sluice v0.129.0 (ADR-0184); the combined-ALTER it conditionally splits is ADR-0080.

What happened #

sluice builds a table's secondary indexes in a deferred post-copy phase, and — following a standard MySQL optimization — emits the minimum set of statements: all of a table's combinable indexes collapse into one ALTER TABLE t ADD UNIQUE KEY …, ADD KEY …, ADD KEY … so InnoDB scans and rebuilds the table once instead of once per index. On an ordinary table that is a real, measured win.

On a large table on PlanetScale it was the opposite. The 12.8 GB audio_plays_daily table's four-index combined ALTER ran for exactly 900,004 milliseconds and was killed by the platform's statement-time limit (errno 3024) — four milliseconds over the ~900 s ceiling. And because --resume re-issued the same combined statement, every restart hit the same wall at the same place. The build could not converge; the operator restarted from scratch, re-copying 30 GB each time. That non-convergence was much of a 7–8 hour, 4–5 restart ordeal.

Why (the mechanism) #

Two facts combined into a trap. First, the “scan once” optimization concentrates all of the work into a single statement, and a single statement is precisely the unit the wall measures. The very property that makes the combined ALTER efficient — one big statement instead of several — is what makes it un-survivable under a per-statement time limit: you cannot get under the wall by being efficient inside one statement; you have to have fewer things in the statement.

Second, a resume that re-issues the identical failing statement is non-convergent by construction. sluice's resume already skips indexes that are already present — but the combined ALTER is one atomic statement that builds all four indexes or none, so after it is killed, zero indexes are present, and resume re-issues all four again. There is no state in which it makes progress.

What sluice does about it #

On a statement-time-limited target (the PlanetScale/Vitess flavor) and a table large enough to risk the wall, sluice now splits the deferred build into one ALTER per index instead of the combined form. Measured locally on an 8.4M-row table in the same shape: the combined build's longest statement was 220 s; per-index, the longest was 64 s, at the same 191 s total when the table fit in the buffer pool (roughly 1/N per statement, no total-time penalty; a table that doesn't fit in memory pays extra scans — the correct trade when the alternative is a hard failure). Each per-index ALTER stays well under the wall.

And because each index is now its own statement, resume works: the indexes that landed before the failure are present, resume skips them, and it finishes only the ones still missing instead of re-hitting a monolith. Every other target, and every ordinary table, keeps the combined ALTER unchanged — its single-scan win is real where the wall doesn't apply.

One elegant alternative didn't survive contact with the schema. Building each partition's shard separately and swapping them in with EXCHANGE PARTITION … WITHOUT VALIDATION recombines in 0.6 s, metadata-only — but MySQL requires every unique key to contain the partition column, which a table with a surrogate id primary key and an independent business UNIQUE(date, seller_id, pack_id, sample_id) cannot satisfy (ERROR 1503). And WITHOUT VALIDATION silently misplaces any out-of-range row — our own experiment lost ~458,000 high-id rows to a fixed-range assumption. The per-index split gets most of the benefit with none of those constraints.

The transferable lesson #

Under a per-statement time limit, a scan-minimizing “do it all in one statement” optimization is a liability, not a win. The efficiency you gained by consolidating work into one statement is invisible to a wall that only sees the statement — and a resume that re-issues the failing statement can never converge. When a platform caps statement time, the unit of progress has to be smaller than the cap, and the resume has to advance one small statement at a time.

Primary sources #