A change applies in milliseconds in tests but stalls the deploy on a production-sized table. How would you catch that beforehand?

## Why a green test says nothing about elapsed time A change unit tested against empty tables measures one thing: that the statement is accepted. Most of what makes a schema change expensive is **proportional to the number of rows**, and an empty table has none. So the test finishes in milliseconds, and the same statement against a table with hundreds of millions of rows can run for hours, hold a lock the whole time, and turn a routine deploy into an outage. The failure has a characteristic shape: the change passes review, passes CI, passes in every pre-production environment whose tables are small, and then stalls the first deploy that touches a real table. ## What actually scales with row count - **Table rewrites.** Some changes cause the engine to write out a new copy of every row — changing a column's type, and on some engines adding a column with a default value, while other engines record the default as metadata and finish instantly. The difference between those two behaviours is the difference between a second and an hour. - **Index builds.** Building an index reads the whole table. Whether that blocks writes for the duration depends on whether the engine offers a concurrent build and whether the change used it. - **Constraint validation.** Adding a not-null or a check constraint, or a foreign key, usually scans every existing row to prove the constraint already holds. - **Data changes bundled into the chain.** A single statement that rewrites a column's values across the whole table is one transaction that grows with the table, holds locks, and inflates the engine's undo and replication traffic. - **Lock queuing.** The change may only need a brief exclusive lock, but it has to wait for existing transactions to finish before it gets it — and while it waits, everything queued behind it waits too. On a busy table the wait, not the work, is what takes the system down. ## How to catch it before the deploy 1. **Restore a snapshot with realistic volume.** Not the shape of production — the *size*. Row counts, cardinality and index sizes on the biggest tables are what the rehearsal needs. 2. **Apply the pending increment to it**, exactly as the deploy would: the same units, the same runner, the same account. 3. **Measure, do not eyeball.** Record elapsed time per unit, what lock each unit took and for how long, and whether concurrent reads and writes were blocked while it ran. A rehearsal with no concurrent workload against the table understates the last two, so drive a small read/write load during the run. 4. **Compare against a budget.** Give the deploy an explicit ceiling — for example, no single unit may hold a blocking lock for more than a few seconds — and fail the rehearsal when a unit exceeds it. 5. **Rehearse the abort.** If you cancel the unit mid-run, does the engine roll it back cleanly and quickly, or does it leave a partially built object and a long undo behind? | What to record | Why it matters | |---|---| | Elapsed time per change unit | Tells you whether the deploy fits its window | | Lock kind and hold time | Predicts whether traffic stalls, not just whether it is slow | | Concurrent errors during the run | Shows what callers actually experience | | Growth of undo/redo and replication lag | A long single transaction can hurt replicas after it commits | ## What to do when the rehearsal fails The rehearsal is only useful if a red result changes the change. The usual remedies, in rough order of preference: - **Split the unit.** Take the change apart into a cheap metadata step and an expensive data step, so only the cheap step sits in the deploy path. - **Use the online form** where the engine offers one — a build that does not block writes costs more total work and much less availability. - **Add the constraint unvalidated, validate separately.** Many engines let you enforce a constraint for new rows immediately and scan the existing rows in a later, interruptible step. - **Move the long data work out of the deploy chain entirely**, into a separate resumable job that is not gating the release. - **Batch anything that touches every row** so that no single transaction spans the table. ## The two mistakes to avoid The first is treating this as a timeout problem — raising the deploy timeout so the change is "allowed" to take forty minutes leaves the lock in place for forty minutes and simply relabels the outage. The second is inferring volume behaviour from a scaled-down copy: cost is not linear in row count once an index no longer fits in memory, so a rehearsal on one per cent of the data can be off by far more than a factor of a hundred. If the rehearsal cannot be full size, say so explicitly and treat its numbers as a lower bound rather than a prediction.