Notes from the terminalDatabase migrations that do not wake you up
Changing the structure of a database while the application is running is one of the few tasks that can turn an ordinary afternoon into an incident. The good news is that nearly all of the risk comes from doing too much at once.
Split every change into steps
Renaming a column looks like one operation. On a live system it is at least four: add the new column, write to both, move the old data, and finally stop using the old one. Each step can be deployed and rolled back on its own.
-- step 1: add, nullable, no default that rewrites the table
ALTER TABLE users ADD COLUMN display_name text;
-- step 2: application writes both columns (deploy)
-- step 3: backfill in small batches
UPDATE users SET display_name = name
WHERE id BETWEEN 1 AND 10000 AND display_name IS NULL;
-- step 4: application reads the new column (deploy)
-- step 5, much later: drop the old columnBe suspicious of locks
The dangerous statements are the ones that take a strong lock on a large table. Before running anything, find out what lock it needs and how long it may hold it. Set a short lock timeout, so that a migration that cannot get its lock fails quickly instead of queueing behind a long query and blocking everyone else.
Backfill slowly
Updating millions of rows in a single statement is a good way to fill the disk with logs and stall replication. Work in batches, pause between them, and make the job safe to restart from where it stopped.
Test on a copy of real data
A migration that takes a second on a developer laptop can take an hour in production. Run it against a recent copy of the real database and write down how long it took. That number is part of the plan.
Know the way back
Before you start, decide how you would undo each step. Some steps cannot be undone, such as dropping a column. Those go last, after enough time has passed to be sure that nothing depends on the old data.
A migration is finished when the old way is gone and nobody noticed.