The first time I ran an instant ADD COLUMN on a two-billion-row InnoDB table, it finished before I could switch terminal tabs. The first time I assumed a change would be instant and it was not, the ALTER ran for four hours and the replica fell twenty minutes behind. Both experiences came from the same feature: ALGORITHM=INSTANT, added in MySQL 8.0.12 and significantly expanded in 8.0.29.
Instant DDL is genuinely excellent. It is also full of edges that the syntax does not warn you about. The same ALTER TABLE statement can be a metadata flip on one table and a full table copy on another, and MySQL will happily run either one unless you tell it not to. These are my field notes on what actually qualifies, where the row-version limit bites, and how to verify the algorithm before the statement starts.
What ALGORITHM=INSTANT actually does
A normal ALTER TABLE in InnoDB has three possible algorithms. COPY rebuilds the table into a new copy while blocking writes. INPLACE modifies the table without a full copy, often allowing concurrent DML, but can still rebuild the clustered index and can still take hours on a big table. INSTANT changes only metadata in the data dictionary. No rows are touched, no index is rebuilt, and the statement finishes in milliseconds regardless of table size.
The mechanism matters for understanding the limits. When you instantly add a column, InnoDB does not rewrite existing rows to include it. Instead it records a new row version in the table metadata. Rows written before the change stay in the old format; rows written after carry the new one. When a query reads an old-format row, InnoDB fills in the missing column from the default recorded in metadata. Instant DROP COLUMN works the same way in reverse: the column stops being visible, but the bytes stay in old rows until something rewrites them.
This is why instant DDL is cheap at ALTER time and slightly deferred at read time. It is also why there is a hard cap on how many times you can do it, which we will get to.
The operations that qualify in 8.0 and 8.4
The instant-capable list is shorter than most people assume. As of MySQL 8.0.29 and carried forward into 8.4, these operations can use ALGORITHM=INSTANT:
- Adding a column. From 8.0.12 only as the last column; from 8.0.29 at any position. The column cannot be auto-increment, and the table cannot use ROW_FORMAT=COMPRESSED or have a FULLTEXT index.
- Dropping a column. Only from 8.0.29, with the same table restrictions, and not if the column is part of an index or referenced by a foreign key.
- Setting or dropping a column default. Metadata only, always cheap.
- Modifying an ENUM or SET definition. Only when appending members at the end and only when the storage size does not change.
- Adding or dropping a virtual generated column. Stored generated columns do not qualify.
- Renaming a table.
- Changing the index type.
Notice what is missing: changing a column data type, extending a VARCHAR, adding an index, changing NULL to NOT NULL, changing character sets. Some of those are INPLACE, some are COPY, none are INSTANT. Extending VARCHAR length is a particularly common trap because it is INPLACE only within the same length-byte boundary; crossing from 255 bytes to 256 bytes of encoded length forces a COPY, and with utf8mb4 that boundary arrives at 63 characters.
The 64 row-version limit
Every instant ADD COLUMN or DROP COLUMN since 8.0.29 creates a new row version for the table. InnoDB allows at most 64 of them. Once a table reaches 64 row versions, the next instant ADD or DROP is refused, and the change requires INPLACE or COPY, which rebuilds the table and resets the counter to zero.
Sixty-four sounds like plenty until you meet a table that a busy team has been evolving for three years with weekly migrations. The counter does not decay on its own. It only resets when something rebuilds the table: an ALTER with ALGORITHM=INPLACE that rebuilds, an ALTER with ALGORITHM=COPY, or OPTIMIZE TABLE. You can check where a table stands before planning a migration:
SELECT NAME,
TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME LIKE 'shop/%'
ORDER BY TOTAL_ROW_VERSIONS DESC;My working rule: any table above 50 row versions gets a scheduled rebuild during the next maintenance window, on our terms, rather than discovering the limit mid-deploy when a migration that worked in staging refuses to run in production. Staging tables are usually rebuilt more recently than production tables, so their counters are lower. That asymmetry is exactly how this failure hides.
When INSTANT silently becomes INPLACE or COPY
Here is the behavior that causes incidents: if you write ALTER TABLE without an ALGORITHM clause, MySQL picks the cheapest algorithm the operation supports. That sounds helpful, and usually is. But it means an ALTER that was instant last month can silently become a multi-hour INPLACE this month because the table gained a FULLTEXT index, or the column being dropped got indexed, or the row-version counter hit 64.
The fallback is silent in the worst sense. The statement does not warn you; it just takes ten thousand times longer and holds metadata locks at the start and end while doing so. On a replica applying the binlog serially, that same statement replays as a single long operation and lag climbs for its entire duration. I wrote more about how MySQL DDL locking compares to PostgreSQL's approach in MySQL online DDL vs PostgreSQL DDL locks; the short version is that MySQL's per-operation algorithm matrix demands more pre-flight checking, not less.
Multi-clause ALTER statements have their own trap. If you combine an instant-capable clause with a non-instant clause in one statement, the whole statement uses the more expensive algorithm. Splitting one ALTER into two can turn a table rebuild plus metadata flip into a rebuild you schedule and an instant change you run anytime.
Check before you ALTER, every time
The fix for silent fallback is to make the algorithm explicit. When you specify ALGORITHM=INSTANT and the operation does not qualify, MySQL refuses with an error instead of quietly doing something expensive:
ALTER TABLE orders
ADD COLUMN promo_code VARCHAR(32) NULL DEFAULT NULL,
ALGORITHM = INSTANT;
-- If this errors with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON,
-- you just learned about the fallback in a test, not in an outage.
ALTER TABLE orders
DROP COLUMN legacy_flags,
ALGORITHM = INSTANT;An error at migration time is a gift. It converts "the deploy hung for three hours" into "the migration failed fast and we chose INPLACE with a LOCK=NONE clause for the weekend window." I put ALGORITHM and LOCK clauses on every production ALTER for exactly this reason, and I make migration review reject any ALTER that omits them. The same discipline applies to LOCK=NONE: if the operation cannot run without blocking DML, better to find out as a refused statement.
Two more pre-flight checks are worth automating. First, the row-version query above, so the 64 limit never surprises you. Second, a check for FULLTEXT indexes and COMPRESSED row format on the target table, since both disqualify instant column changes entirely.
Operational habits that make instant DDL boring
Boring is the goal. The habits that get you there are small. Keep migrations single-purpose so instant clauses never share a statement with rebuild clauses. Track row versions as part of routine review, the way you track table growth. Rebuild high-version tables deliberately instead of reactively. Test every migration against a production-sized copy, because algorithm eligibility depends on table state, not just on the DDL text. And watch metadata lock waits during deploys, because even a true instant ALTER must acquire a brief exclusive metadata lock, and a long-running transaction holding the table can queue everything behind it.
That last one deserves emphasis. The most common "instant DDL took down the app" story is not the ALTER being slow. It is the ALTER waiting on a metadata lock behind an idle transaction, while every new query on the table queues behind the ALTER. The statement was instant; the lock queue was not.
Where MonPG fits
MonPG is a PostgreSQL monitoring platform, and I want to be straightforward about that: MonPG does not monitor MySQL today. We are actively building MySQL monitoring (coming soon), and schema-change observability is part of the design, because the failure modes in this post are visibility problems: row-version counters nobody checks, algorithm fallbacks nobody catches, metadata lock queues nobody sees until the app stalls. Writing these field notes is part of how we make sure the MySQL product covers what actually breaks. If your team also runs PostgreSQL, you can start there today, where query history, lock chains, and DDL-impact evidence are already in the product.