Adding a column to a large MySQL table used to be an event: maintenance windows, third-party tools, a replica-swapping dance, and a prayer. MySQL 8.0 changed the game twice, first with ALGORITHM=INSTANT for a growing set of operations, then with the row-version machinery in 8.0.29 that made instant column changes far more general. But online DDL is not one thing. It is three algorithms with wildly different cost profiles, and picking wrong still takes tables hostage today. This is the decision tree I use.
Three algorithms, three cost profiles
Every ALTER TABLE resolves to one of three algorithms. COPY is the old world: create a new table with the desired structure, copy every row while holding locks that block writes, swap, drop the old one. Hours on a big table, double the disk, blocked writers the whole time. INPLACE rebuilds the table in place using temporary files and, crucially, an online alter log that records concurrent DML so reads and writes can continue during most of the rebuild under LOCK=NONE. You still pay a full rewrite of the table's data, the disk for the overlap, and the I/O for the whole operation, but the application keeps running. INSTANT changes only metadata in the data dictionary: no row is touched, no data is rewritten, and the operation finishes in milliseconds regardless of table size. When an operation supports INSTANT, the choice is made for you.
You can force the issue per statement with the ALGORITHM and LOCK clauses. ALGORITHM=INSTANT makes the statement fail loudly if the operation cannot be instant, which is exactly what you want in a migration script: better a failed deploy than a silent four-hour rebuild. If you omit ALGORITHM, MySQL picks the cheapest supported algorithm, which sounds helpful until an operation you assumed was instant quietly becomes an INPLACE rebuild on a 500 GB table.
What INSTANT covers, and the 64-change limit
The instant list has grown across 8.0 point releases: adding a column, at the end of the row since 8.0.12 and in any position since 8.0.29, dropping a column since 8.0.29, renaming a column, setting or dropping a column default, appending to an ENUM or SET definition, adding or dropping a virtual column, and renaming a table. Instant add-anywhere and instant drop work through row versions: InnoDB tags the table with the number of instant changes it has undergone and interprets old rows through the metadata rather than rewriting them.
That machinery has a hard limit: 64 row versions. After 64 instant add or drop column changes on one table, further instant changes fail with an error telling you the maximum row versions have been reached, and the table must be rebuilt to reset the count. Check where your tables stand:
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name LIKE 'app/%'
ORDER BY total_row_versions DESC;
-- pin the algorithm explicitly, fail loudly if unsupported:
ALTER TABLE orders
ADD COLUMN priority tinyint NOT NULL DEFAULT 0,
ALGORITHM=INSTANT;Three more INSTANT gotchas. Instant operations accept only LOCK=DEFAULT; the LOCK=NONE habit carried over from INPLACE work gets the statement rejected, which is why the example above pins the algorithm and nothing else. The row-version features need the DYNAMIC row format; REDUNDANT and COMPACT are excluded, and so is COMPRESSED, which mostly matters for ancient schemas carried forward from 5.x or tables somebody compressed by hand. And rebuilding the table with OPTIMIZE TABLE or an ALGORITHM=INPLACE alter resets total_row_versions to zero, so if a hot table is accumulating instant changes, a quiet rebuild every year or two is preventive maintenance, not superstition.
INPLACE: the real cost of "online"
When INSTANT is not available, INPLACE spans a wide cost range. Adding a secondary index is INPLACE but does not rebuild the table data; InnoDB builds just the new index while DML continues under LOCK=NONE, and on decent hardware this is routine. The expensive INPLACE operations are the full rebuilds that still run in place: a forced rebuild with ALGORITHM=INPLACE or an OPTIMIZE TABLE, changing the row format, a column change that INSTANT no longer covers. And some heavy operations do not get INPLACE at all: changing a column's data type and dropping a primary key without adding its replacement in the same statement force ALGORITHM=COPY, with writers blocked for the whole copy, and CONVERT TO CHARACTER SET is a COPY-class rewrite too. Rebuild-class work rewrites every row either way; what the algorithm decides is whether DML survives while it happens.
For a rebuild-class operation, budget three things. Disk: the operation needs roughly a full copy of the table in temporary space, plus the online alter log, which grows with concurrent write traffic and can itself become a space problem on a write-heavy table. I/O: it reads and writes every row, competing with production traffic, and on saturated storage that alone degrades latency. Time: hours, proportional to table size, not to how online the algorithm is. Online means available, not fast and not cheap.
Metadata locks: the part online DDL does not remove
Every algorithm, including INSTANT, must acquire an exclusive metadata lock on the table, briefly at the start and again at the end to commit the new definition. Briefly assumes the lock is available. If a long-running transaction or an open result set holds the table, the ALTER queues on the metadata lock, and here is the part that ruins days: while the ALTER waits, every new statement touching that table queues behind it. One idle transaction with an open cursor, one waiting ALTER, and thirty seconds later your entire application is stuck on "Waiting for table metadata lock."
The defenses are procedural, not algorithmic. Never run DDL while long transactions or report queries are running against the target table; check information_schema.innodb_trx for old transactions first. Set lock_wait_timeout low enough, minutes rather than the one-year default, that a stuck DDL fails fast instead of building a queue. And when a pileup happens, kill the blocker first: killing the ALTER just lets the next retry queue behind the same blocker. First, though, not always. If the blocker cannot be killed and will not finish soon, a report query with hours left or a transaction from a host nobody can reach, cancel the DDL itself; draining the queue beats standing on principle, and you can rerun the ALTER once the table is quiet. The full diagnostic playbook for these pileups is in my metadata lock diagnosis guide, and it is the companion every DDL runbook needs.
Watching progress, and when to reach for gh-ost
Long INPLACE operations expose progress through performance_schema stage events, provided you keep performance_schema reasonably configured; my baseline is in the low-overhead performance_schema setup:
SELECT event_name, work_completed, work_estimated,
ROUND(100 * work_completed / work_estimated, 1) AS pct
FROM performance_schema.events_stages_current
WHERE event_name LIKE 'stage/innodb/alter table%';When native DDL genuinely cannot do the job, the metadata-lock window has to be controllable rather than merely brief, or you need to throttle, pause, and cut over manually, the external tools still earn their place. gh-ost and pt-online-schema-change both copy the table in the background and swap at the end; gh-ost hooks the binary log instead of triggers, pauses when replication lag grows, and makes cut-over an explicit, testable step. The cutover still takes a metadata lock, no tool can swap tables without one, but it happens at a moment you choose, with the copy already done and a throttle you own. I reach for gh-ost on tables so hot that even a second of metadata lock queueing is an incident, and for changes where I want the option to throttle if the business day goes sideways. For everything else, native INSTANT-or-INPLACE with a lock_wait_timeout guardrail is simpler, and simpler wins at 3am.
Schema changes deserve an audit trail
Every DDL I have ever regretted failed the same way: nobody saw it coming. That is the operational gap the MySQL monitoring under construction at MonPG is aimed at, metadata-lock queues detected before they page you, DDL progress tracked as an event with a blast radius attached, row-version counts surfaced so the 64-change limit never arrives unannounced. To be precise about what exists right now: MonPG monitors PostgreSQL, the MySQL product is being built and has not shipped, and the PostgreSQL side already runs this evidence-first playbook on the MonPG platform, with the engine comparisons beside it. The rest of the series is on the blog. Until the MySQL product ships: pin ALGORITHM explicitly in every migration, set lock_wait_timeout, and check for old transactions before you alter anything you love.