As I look into database migration dangers for (edited to add: now defunct) a book I’m working on, I find a lot of great existing stuff that I had no idea existed.
Andrew Kane of Instacart has a bunch of wonderful guides, including one on “strong migrations” — migrations that run without downtime on MySQL and Postgres. It sums up some wonderful previous articles about downtime-free migrations on Postgres with high data volume, how to create indices without downtime on Postgres, and no-downtime migrations in general.
It turns out there was a whole world of information that was left behind by all the previous schmucks who froze gigantic tables with huge migrations. Heck, for some MySQL operations they wrote complex tools with ominous names to handle the fact that you can’t safely add an index to a large table, period.
And I’m ignoring lots of other great information on databases generally and migrations specifically. I mean, the Rails Migration Guide is an amazing intro, and even that didn’t exist when I started learning Rails, (cough) years ago.
If, like me, you started as a complete ignoramus about databases, there has never been a better time to learn this stuff!
(Why is it hard to migrate your database without downtime? Turns out that SQL databases lock everything down while they make the change — so if you change the type of a column in a table so big it takes five minutes to modify the rows… You may wind up with five minutes where all the queries on that DB fail. There’s more to it than that. I wrote an article, if you’re curious. Or you could read some of the other ones.)