Change a busy table without blocking it
The change was reviewed. The package is right. The deploy starts at 2 a.m. and adds one index to Orders, and for the next eleven minutes every checkout in the application waits behind it, because a plain CREATE INDEX holds the table for as long as it takes to build. Nobody scheduled that outage. It came with the statement.
Every engine has gentler forms of the changes that hurt most: an index built online, a column added instantly, a constraint checked after it’s in place. OnlineApply asks for them. It’s off by default, for a reason you’ll see below, and it’s a setting you choose per environment and override per table, which makes it exactly the kind of production-window decision this course is about.
Off, Prefer, Require
Section titled “Off, Prefer, Require”"OnlineApply": "Prefer"| Value | What the deploy does |
|---|---|
Off (default) | The plain statements, as a deploy always has. No mode in the summary. |
Prefer | The online form wherever the engine’s version and edition allow, the plain form otherwise, and a record of which. |
Require | The online form for every change the engine supports online, or a refusal naming each statement that can’t. |
Set it in SchemaQuench.settings.json, on the command line (--OnlineApply=Prefer), or in an environment variable, and override it in any table’s JSON. A small lookup table rewrites in milliseconds, so "OnlineApply": "Off" there is reasonable even when production runs Prefer.
What each engine does
Section titled “What each engine does”The same change, deployed with Prefer to all four sandbox engines:
| Engine | What it did |
|---|---|
| SQL Server | Altering Column … (online), Creating index … (online): WITH (ONLINE = ON) |
| PostgreSQL | Add missing Index … (online, after this pass): CREATE INDEX CONCURRENTLY, outside the step’s transaction; a new check added NOT VALID and validated separately |
| MySQL | the new column (instant: ALGORITHM=INSTANT), where the package declares it; the index (online: ALGORITHM=INPLACE, LOCK=NONE) |
| MariaDB | the same, and the VARCHAR widenings instant as well |
On SQL Server and PostgreSQL, SchemaSmith picks the form from the version and edition. On MySQL and MariaDB it asks for the lightest form and lets the engine decide, falling back step by step. That’s also why Prefer puts a new MySQL column where your package says, after CustomerId, when Off has always put it at the end.
Some changes have no online form anywhere. A SQL Server check constraint scans the table to check it; a PostgreSQL column type change rewrites the table. Those run as written and say so: (not covered by OnlineApply), mode: notCovered.
Reading the receipt
Section titled “Reading the receipt”Every change row in the deployment summary carries the form it used:
mode | Meaning |
|---|---|
online | the engine’s online form |
instant | MySQL or MariaDB changed only metadata |
resumable | a SQL Server index build that can pause and resume |
unavailable | the plain form, where the online one couldn’t be used, with the reason in the log |
notCovered | no online form exists for this change |
No mode means nothing chose a form: the table was under Off.
Require is a promise or a refusal
Section titled “Require is a promise or a refusal”Require is a guardrail in the same family as this course’s validation scripts: the deploy stops by name before it does the thing you said it mustn’t. Each engine in the lab has one change it can’t make online:
OnlineApply is Require, and these indexes cannot be built online: [dbo].[Orders].[NCCI_Orders] (a columnstore index is built offline). Set the table's OnlineApply to Prefer to build them with the plain statement.PostgreSQL refuses an exclusion constraint, which can’t be built concurrently. MySQL refuses an INT to BIGINT change, which needs a table copy. MariaDB makes the same change online with ALGORITHM=COPY, LOCK=NONE, a copy that doesn’t block writes, and that contrast is worth remembering when someone says “online” as if every engine meant the same thing.
Where the refusal lands differs too. SQL Server and PostgreSQL refuse before any existing index, key or column on the table changes. MySQL and MariaDB can’t take DDL back, so they stop at the statement, and anything the deploy did earlier stays done.
When MySQL still hands the ALTER to pt-osc
Section titled “When MySQL still hands the ALTER to pt-osc”That MySQL refusal is a change plenty of MySQL shops already have a tool for: pt-online-schema-change. It builds a shadow copy of the table, keeps the copy in step with triggers while it fills, swaps it in, and pauses itself when replicas fall behind. OnlineApply doesn’t replace it. It tells you, by name, which change needs it.
The two work as a pair. Preview the release with --WhatIf and SchemaQuench prints the exact ALTER it would send. Hand that clause to pt-osc’s --alter and let it do the copy. Then deploy the package as usual: SchemaQuench reads the swapped table, finds it already matches the declaration, and has nothing left to change on it. The pt-osc coexistence demo walks all three steps on 2.5 million rows.
So a MySQL production environment can run Require and keep pt-osc for exactly what it’s for. Everything the engine can change without blocking writes goes out with the deploy. The change it can’t is refused by name, goes to pt-osc, and the next deploy finds it done.
What online costs
Section titled “What online costs”This is why it’s off by default:
- An online index build takes roughly twice the space and the time of a plain one.
- A PostgreSQL concurrent build that fails leaves an invalid index. SchemaSmith drops it and tries once more, and a second failure fails the deploy with nothing left behind:
The build stopped and left the index invalid; dropping it and building once more. - A SQL Server resumable build (
OnlineApplyMaxDuration) that runs out of time pauses and ends the deploy’s session. The next deploy resumes it or abandons it, whatever its own settings say. - A MySQL instant change spends one of the table’s row versions. Near the limit the log warns, and at the limit the next change rebuilds the table.
- On SQL Server the edition decides: Standard, Web and Express have no online operations.
Preferdeploys plain there with the reason on every line, andRequirerefuses up front.
Where it sits with expand/contract
Section titled “Where it sits with expand/contract”Course 3’s expand/contract makes each step of a change safe for the application: old and new code both work at every point. OnlineApply makes each step safe for the table’s locks. A perfectly sequenced expand step can still stall checkout for eleven minutes if its index builds offline, and an online build of a change that breaks running code is still an outage. You want both.
Check yourself: Production runs OnlineApply: Require. A release adds a nonclustered columnstore index to a busy SQL Server table, and the deploy refuses with exit 2. A teammate suggests switching production to Prefer so it goes through. What's the trade, and what would you do instead?
Prefer would build the columnstore index the plain way, holding the table for as long as the build takes. That’s the outage Require was there to prevent, and switching the whole environment would also loosen the guarantee for every other table.
Decide it for this one change: build the index in a window where a lock is acceptable, with "OnlineApply": "Prefer" on that one table for that release, then take the override back out. The refusal did its job. It turned a surprise lock into a scheduling decision someone gets to make.
A smith reworking a piece that’s already in service doesn’t pull it off the line if he can help it. He heats the part that needs shaping and leaves the rest working. OnlineApply is that habit, written into the deploy: shape the table while it’s in use, say which strokes needed the line stopped, and refuse the ones you said must never stop it.
Got a change that ran offline when you expected online? Email me at forgebarrett@schemasmith.com. More’s coming from the forge.
Until then, may your changes land while the shop stays open.
— Forge