What makes a schema migration safe to run against a live production database?
Four things make the difference between a migration that is safe and one that is a problem at three in the morning.
The first one is that it runs inside a transaction. If the migration has several steps and one of them fails, then the whole thing is undone and the database is left as it was. A migration that is a list of statements with no transaction around them can leave you half way.
The second one is that it does not lock the table for long. A change that rewrites the table holds a lock for as long as the rewrite takes, and on a big table that is an outage. So the work is broken into batches.
The third one is that it does not break the code that is running. The old application has to keep working while the change is going in, so the change has to be backwards compatible. That means adding rather than changing, and filling in later rather than all at once.
The fourth one is that it can be run twice. If the migration is interrupted half way and somebody runs it again, it has to finish the job rather than fail or do the work twice. That is the next question, and it is also the last one.
a safe migration
-----------------
runs inside a transaction
takes a short lock
does not break the running application
can be run twice without doing the work twice
The next two questions in this topic look at the second one in practice and at the last one.