Database Migrations at 2AM: A Zero-Downtime Playbook Written in Regret

Every rule in this playbook was purchased with an incident. Collecting them here so your receipts can be cheaper than mine.

The core insight that unlocks everything: during a deploy, old code and new code run at the same time against one database. There is no instant where the fleet switches. So every migration has to be compatible with both versions simultaneously, which means schema changes ship separately from the code that depends on them, always, no exceptions on Fridays especially.

The expand and contract rhythm follows from that. Adding a column: add it nullable or defaulted, deploy code that writes it, backfill old rows in batches, only then add constraints, and only after everything reads the new column do you even think about dropping the old one. Renaming a column: you don't. You add the new one, dual write, backfill, migrate readers, drop the old one weeks later. A rename is two migrations and a waiting period wearing a trench coat.

The locking landmines, learned individually. Adding an index takes a lock that blocks writes unless you say CONCURRENTLY, so say it. Adding a NOT NULL constraint scans the whole table under lock unless you add it as NOT VALID first and validate separately, so do that. Changing a column type rewrites the table, so mostly, don't, add a new column instead. And set a lock_timeout in every migration, a couple seconds, because a blocked migration queues behind long transactions and then everything queues behind the migration, and that pileup is the actual 2AM.

Last rule, run the migration against a prod sized copy first and time it. A migration that takes 40 milliseconds on staging and 40 minutes on prod is not the same migration. Ask me how I know.