The ALTER TABLE that looked free and rebuilt the whole table

A while back I watched a one-line migration cause a production incident on Aurora MySQL. The statement was, near enough:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Adding a foreign key. About as routine as a migration gets, and everyone reads it as a cheap metadata change — you're declaring a relationship, not touching the data. On InnoDB that assumption is wrong in a way that stays hidden until the table is big. Adding a foreign key only runs as an online, in-place operation when foreign_key_checks is disabled. With the default settings — checks on — the only algorithm InnoDB will use is ALGORITHM=COPY: a full rebuild of the table, with a lock that blocks every write to it for as long as the rebuild runs. Nothing writes to the table until it finishes.

The table was large — not so much in row count as on disk, which is the dimension that actually matters here, because COPY physically rewrites every page. The rebuild ran for about thirty minutes, every write to the table blocked behind it the whole time, connections backed up, and the feature went down. One line of SQL.

What makes that incident worth writing about isn't that the problem was hard. It's that it was entirely catchable and nobody caught it. The developer who wrote the migration didn't know adding a foreign key rebuilds the table, the review didn't flag it, and the DBAs didn't catch it before it shipped. The migration looked identical to dozens of safe ones — and the gap between "free" and "thirty-minute outage" on Aurora is not visible in the SQL unless you already know which corner you're standing near. The rules have a lot of those corners. Here are the ones that have bitten me or people I know.

INSTANT is narrower than you think, and it moves between versions

ALGORITHM=INSTANT is the good case: no rebuild, no lock, a metadata-only change that completes in milliseconds regardless of table size. What qualifies depends on your version, which is the first trap. On Aurora 3.04 and earlier (MySQL 8.0.28 and below) INSTANT covers a deliberately narrow set:

• Adding a column at the end of the table (with a DEFAULT, or nullable with none)
• Changing a column's DEFAULT
• Appending values to an ENUM or SET (at the end, with no change in storage size)
• Adding or dropping a virtual generated column

Note "at the end". On those versions, add the same column with AFTER customer_id and you've asked for a positional insert, which INSTANT can't do — you drop to INPLACE or COPY. Same column, same type, one clause different, completely different locking behaviour.

MySQL 8.0.29 widened this a lot: INSTANT learned to add a column at any position and to drop columns. The trap on Aurora is that there is no 8.0.29 — Aurora went straight from 8.0.28 (3.04) to 8.0.32 (3.05) — so those improvements only reach you at Aurora 3.05. Read the 8.0.29 release notes, assume your 3.04 cluster can do an instant positional add or drop, and you'll be wrong: on 3.04 those still fall back to INPLACE or a rebuild.

A few things feel like they should be instant but never are, on any version: changing a data type (INT→BIGINT, say), changing a character set, changing the primary key. Those always rebuild the table.

Make Aurora prove it

With the eligibility rules shifting between versions like that, the reliable habit is to stop reasoning about them in your head and make the server tell you. Force the algorithm:

ALTER TABLE orders
  ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending',
  ALGORITHM=INSTANT;

If Aurora can't satisfy ALGORITHM=INSTANT, the statement fails immediately with an error before it acquires any lock. That's the whole point: a fast failure in CI or staging is infinitely better than discovering the fallback on production at peak. I now treat an unqualified ALTER as a smell — if you believe it's instant, say so in the SQL and let the engine fail you fast if you're wrong.

DROP COLUMN is not instant until later than you'd think

Here's one that catches people who've read the MySQL 8.0 release notes but not the Aurora ones. INSTANT support for DROP COLUMN arrived in MySQL 8.0.29 — but, per the version jump above, the first Aurora release that has it is 3.05 (8.0.32), because Aurora skipped 8.0.29 through 8.0.31. On Aurora 3.04 (8.0.28) and earlier — still extremely common in production — ALTER TABLE ... DROP COLUMN runs under INPLACE. Online, yes, but it's a full background rebuild that burns I/O and lags replicas for the duration. On a 50-million-row table that's not a quick cleanup; it's a scheduled maintenance task.

The COPY cliff

When you end up on ALGORITHM=COPY — data-type changes, primary-key changes, row-format changes, and adding a foreign key with the default foreign_key_checks enabled (the one that started this post) — the lock is held for the whole rebuild. The duration tracks the table's size on disk, not just its row count, because COPY rewrites every page — so treat the row-count bands below as a crude proxy, and expect wide rows or large blobs to push them slower. The rough shape on a mid-range instance:

The threshold I use: past about a million rows, a COPY operation is no longer a migration, it's an outage with a git blame. Reach for gh-ost or pt-osc.

The foreign-key case from the start of this post had a fix that was genuinely a one-liner: wrap the statement in SET foreign_key_checks=0; … SET foreign_key_checks=1; and InnoDB adds the constraint in place, no rebuild. The only thing you give up is validation of the existing rows against the new constraint, so you do it knowing the data is already consistent — which, for a relationship the application had been enforcing all along, it was. Thirty seconds of work, no outage.

That's the part that's stuck with me. The fix wasn't hard and it wasn't obscure. The thirty-minute outage happened because catching it depended on a specific person remembering a specific rule at the moment they read the diff, and that day nobody did.

Why I built a checker for this

None of the above is secret. Every one of these is catchable — it's all in the MySQL and Aurora docs if you go looking, across a dozen pages, and a DBA who has the rule loaded will spot it in a diff. That's exactly the problem. Catching it depends on a human holding the right rule in their head at the right moment, for every migration, forever. The dangerous migration and the safe one look the same in the pull request. The reviewer would need to keep all of it in mind at once — the version-specific eligibility, the 8.0.28-to-8.0.32 jump in what INSTANT can do, the foreign_key_checks behaviour, the row count and on-disk size of the actual table — and do it on a Friday afternoon on the fortieth diff of the week. People miss things. The developer missed it and so did the DBAs, and they weren't being careless; they were being human.

A tool doesn't get tired and doesn't skip the boring forty-first diff. It can apply every rule to every statement, every time, which is the one thing this kind of checking actually needs. So I built migracheck, and it fails the pull-request check when a migration would lock a table, lag a replica, or block rollback. It's the review I wish that foreign key had gone through.

How it works

The hard part is that you can't do this by reading the SQL alone. A static linter can match ADD CONSTRAINT ... FOREIGN KEY with a regex, but it has no idea whether your orders table is ten thousand rows or two hundred gigabytes on disk — and that, not the syntax, is the entire difference between "fine" and "thirty-minute outage". The check needs to know what your database actually looks like.

Importantly, migracheck never connects to your database. You don't wire it into production or hand it a credential. Instead someone on your side — usually a DBA — runs a read-only query to produce a metadata snapshot: table sizes, row counts, indexes, the foreign keys in and out, column types, and the Aurora version you're really on. We ship the SQL for it (the CLI can run it too), it reads catalogue and statistics tables rather than your row data, and a read replica is a perfectly good place to run it. You load that snapshot into migracheck and refresh it when the schema drifts. The tool only ever sees the snapshot you chose to give it — it has no network path to your database and no idea what's in your rows.

Then in CI, for each migration it parses the script into individual statements, works out what each one does and which tables it touches, and narrows that snapshot down to just those tables plus anything with a foreign key into them (which keeps the context small and the check fast). That slice — the statements and the piece of your real schema they affect — goes to an LLM running against a system prompt that encodes the rules from this post: the per-version INSTANT eligibility, the Aurora 3.04-to-3.05 jump in what INSTANT covers, the COPY duration thresholds by size, the foreign_key_checks behaviour. It comes back with a finding per statement: a severity, what the problem is, and what to do instead. You choose which severities fail the build.

I used an LLM rather than a hand-written rules engine on purpose. The rules interact — whether an ALTER is safe depends on the operation and the Aurora version and the state of the actual table — migrations get written a dozen different ways, and the output that's actually useful is a sentence a developer can act on, not a rule ID. A model with your schema in front of it handles that combination well; a pile of regexes handles it badly.

It runs as a GitHub Action or a GitLab CI component, and there's a free tier for open-source projects and trying it out.

If you've got an Aurora DDL war story of your own, I'd genuinely like to hear it.