How MySQL 8.4 ALGORITHM=INSTANT Works
On production InnoDB tables with tens of millions of rows, an unguarded ALTER TABLE can saturate disk I/O, stall replication replicas, and trigger metadata lock pileups across PHP-FPM workers. When an operation supports ALGORITHM=INSTANT, MySQL 8.4 updates only the data dictionary metadata without touching existing data pages—completing in milliseconds regardless of table size.
DDL Operations Supported by INSTANT in MySQL 8.4
- Adding columns at any position: Unlike MySQL 8.0.12–8.0.28 (which restricted instant additions to the trailing column), MySQL 8.0.29+ and 8.4 use InnoDB row versioning to support
ADD COLUMN ... AFTER col_nameinstantly. - Dropping columns:
DROP COLUMNcompletes instantly unless the column is referenced by a primary key, secondary index, or foreign key constraint. - Renaming & default changes: Renaming a column without changing its type or modifying
SET DEFAULT/DROP DEFAULTexecutes as metadata-only.
Edge Cases That Force an INPLACE or COPY Rebuild
- The 64 Row-Version Ceiling: Each instant column add or drop increments
TOTAL_ROW_VERSIONSinINFORMATION_SCHEMA.INNODB_TABLES. Once a table hits 64 row versions, the next DDL forces a full table rebuild (OPTIMIZE TABLEresets this counter). - FULLTEXT Indexes & Compressed Tables: Tables containing a
FULLTEXTindex or configured withROW_FORMAT=COMPRESSEDcannot useALGORITHM=INSTANT. - Column Type or Nullability Changes: Switching
NULLtoNOT NULLor changing data types requires scanning existing rows.
Frequently Asked Questions
How do I enforce INSTANT inside a Laravel migration?
Instead of relying on Laravel's blueprint builder (which omits explicit algorithm clauses), execute a raw DB::statement() appending ALGORITHM=INSTANT, LOCK=NONE. If MySQL cannot satisfy it instantaneously, it fails immediately rather than silently locking production.
Can ALGORITHM=INSTANT still block queries?
Yes, briefly. MySQL still acquires an exclusive Metadata Lock (MDL) for a split second at the start and commit phase. If a long-running analytical query or unclosed transaction is open on the table, the DDL waits behind it and queues subsequent writes.