QueryKings

Online schema change in MySQL: picking the right mechanism

"Can we add this column without downtime?" is the question behind most schema-change anxiety in MySQL shops. The honest answer is "usually, yes — if you pick the right mechanism for this specific change on this specific table." There are four realistic options, and choosing between them is a decision tree, not a preference.

Option 1: INSTANT DDL (8.0)

Since 8.0.12, some ALTER TABLE operations are metadata-only — no table rebuild, no row copying, done in milliseconds regardless of table size:

ALTER TABLE big_table ADD COLUMN flags TINYINT NULL, ALGORITHM=INSTANT;

8.0.12 covered adding a column at the end; 8.0.29 extended it to adding a column anywhere and dropping columns. Renaming a column, changing a default, and extending a VARCHAR within the same length-byte class are also instant.

Always state ALGORITHM=INSTANT explicitly: if the operation cannot be done instantly, MySQL errors instead of silently falling back to something expensive. That error is your decision point, not a failure.

Two caveats: each instant column-add increments a per-table counter with a limit (64 row versions) before a rebuild is required, and tables carrying instant-added columns need care on downgrades and physical-restore paths. Neither outweighs the benefit; both belong in your runbook.

Option 2: native INPLACE online DDL

Most remaining operations — adding an index, adding a column with a rebuild — support ALGORITHM=INPLACE, LOCK=NONE: the table stays readable and writable while InnoDB does the work, with concurrent DML buffered and applied at the end.

The trap is not locking during the operation but at its edges: online DDL needs brief exclusive metadata locks at the start and finish. On a busy table, one long-running query (a report, a stuck transaction) blocks the MDL, and everything else queues behind it. A "non-blocking" index add can take a site down this way. Mitigations:

  • Set a short lock_wait_timeout for the DDL session (say, 5–15 s) so the ALTER gives up rather than queueing the world.
  • Run during a low-traffic window and check information_schema.innodb_trx for long transactions first.
  • On replicas, remember the ALTER replicates and runs for the same duration there, stalling replication for the entire rebuild of a large table. That replication stall is usually what actually rules out native DDL on big tables.

Option 3: gh-ost

gh-ost copies the table to a ghost twin, tails the binlog to apply concurrent changes, and swaps names at the end. Because it reads the binlog instead of using triggers, it adds no synchronous overhead to production writes, and it is throttleable and pausable mid-flight — you can stop a migration at 80% during an incident and resume later.

Requirements: row-based binlog format and no foreign keys referencing the table (gh-ost does not support FK-linked tables). The cut-over needs a brief lock, typically sub-second. For large tables on FK-free schemas, this is our default.

Option 4: pt-online-schema-change

pt-osc does the same copy-and-swap using triggers on the source table to mirror writes. Triggers add synchronous write overhead while the migration runs, and add a failure mode if the tooling dies. But pt-osc handles foreign keys (with explicit, well-understood methods for rewiring them) and works on setups without row-based replication. Where FKs exist, it is usually the only online option.

The decision tree we actually use

  1. Is the operation INSTANT-capable on your version? Do that. Done.
  2. Small table (rebuild in seconds) or quiet hours? Native INPLACE with a short lock_wait_timeout, watching replica lag.
  3. Large table, no foreign keys? gh-ost, throttled against replica lag.
  4. Large table with foreign keys? pt-osc, tested on a replica first — or a scheduled maintenance window, which is sometimes genuinely cheaper than the operational risk.

The changes that are never "just an ALTER"

Three categories deserve extra caution regardless of mechanism:

  • Narrowing a column type (BIGINT to INT, shortening a VARCHAR) can fail partway through on out-of-range data, after hours of copying. Validate the data range first with a SELECT MAX(...) or a WHERE LENGTH(col) > n check.
  • Changing a column's character set or collation rewrites every row and can silently break index usage on joins to other tables that were not converted. Convert related columns together, in one planned pass.
  • Adding a NOT NULL column with no default on a huge table forces a rebuild even on 8.0. Add it nullable, backfill in chunks, then tighten the constraint — three cheap operations instead of one expensive one.

Process beats tooling

Whatever the mechanism: rehearse the exact change against a production-sized copy and record the duration; check disk headroom (copy-based tools need roughly the table's size again, plus binlog growth); pick an explicit go/no-go metric for lag while it runs; and know how to abort cleanly — gh-ost panics safely, pt-osc drops its triggers, but only if the runbook says who does what. Schema changes stop being scary exactly when they stop being improvised.