Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples.
TL;DR: On MySQL 8.4, adding, dropping or renaming a column and changing a default are metadata-only, and an index or NOT NULL builds while writes continue. Other type changes, including a VARCHAR that crosses 255 bytes, a CHECK and a foreign key with checks on copy the table and hold back writes until the copy ends. Every ALTER TABLE , instant ones included, first waits for an exclusive metadata lock, and new queries on the table queue behind it.
On MySQL 8.4, most ALTER TABLE statements do not hold the table for their whole run. Adding, dropping or renaming a column and changing a default only edit metadata, and adding an index or a NOT NULL builds while reads and writes continue. Among the common changes, the ones that copy the table, and hold back writes until the copy ends, are a data type change (including a VARCHAR that grows past 255 bytes), a new CHECK and a foreign key added with checks on.…