I just want to add that newer versions of MySQL can add indexes online. In fact, it looks like all DDL can be done online, even though some if it (like adding a column) may require the table to be rebuilt, which can take time.
Note that the master is still available for read and write during this. Replicas will lag though.
> Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates.
While the master may be technically up for writes during this period, it's not much a goer if your table is large and has any write traffic at all, as anything writing will stall for what may be an extended period.
It should never be an extended period. Everyone everywhere will advise to keep your transactions as short as possible.
Sure this is sometimes boring additional work - eg you don't delete 1M records with one statement, you break it into 1,000 statements each deleting 1,000 records.
Sucks, but keeps your db and your users happy.
BTW this is true for PostgreSQL and MySQL and Oracle and every db that allows concurrent DML.
No, the migration itself will cause transactions to stall.
For example, if you have a table with 1M user records, and you run a migration to add a column in MySQL, then any updates to the table will be stalled while the table is rewritten to add the extra column (which may take a while). This is independent of how many records it touches - even if the transaction only touched 1 record and would take 10ms to execute, if the migration takes 10 minutes it may be stalled for up to 10 minutes.
In Postgres you can add a nullable column, and the table will only be locked for a very short amount of time, independent of the size of the table.
Note that the master is still available for read and write during this. Replicas will lag though.