Schema changes without downtime
Historically an ALTER TABLE rebuilt the whole table and blocked writes for the duration. Modern InnoDB online DDL lets many schema changes run while the table still serves reads and writes, which is essential for large production tables.
Algorithm choices
You can hint how an ALTER runs with the ALGORITHM option:
- INSTANT changes only metadata, such as adding a column at the end, completing in a moment with no data copy.
- INPLACE rebuilds within the storage engine without a full external copy, usually permitting concurrent reads and writes.
- COPY is the old behavior: build a full new table and block writes, used when no faster path exists.
Tracking concurrent changes
During an inplace rebuild, InnoDB buffers row changes made by other sessions in an online log and applies them at the end, so writes are not lost while the operation runs. A brief metadata lock is taken at the start and finish.
Operational cautions
- Even online ALTERs consume IO and can lag replicas, since the change replays on each.
- Some alterations still force a COPY or block writes, so always check the algorithm before running on a big table.
Key idea
Online DDL lets InnoDB run many ALTERs as INSTANT metadata changes or INPLACE rebuilds that buffer concurrent writes in an online log, avoiding the blocking full COPY of the old approach.