Most migration outages come from the same mistake: the schema and the code change in one deploy. For a few seconds, old instances run against the new schema, or new instances run against the old one, and queries fail. The fix is to never let the two depend on each other at the same time.
As an example, we will rename users.name to users.full_name on a busy table.
Add the new column
Add the column as nullable, with no default that would rewrite the table. This is fast and does not lock writes.
ALTER TABLE users ADD COLUMN full_name text;Write to both columns
Deploy code that writes every change to both name and full_name, but still reads from name. Old and new instances can run side by side, because the old ones never read full_name.
Backfill in batches
Copy the existing rows in small batches, so the backfill never holds a long lock or fills the write-ahead log.
UPDATE users
SET full_name = name
WHERE id IN (
SELECT id FROM users
WHERE full_name IS NULL
LIMIT 5000
);Run it in a loop until it updates zero rows. A durable workflow is a good fit for this, because it can pause and resume between batches.
Read from the new column
Once the backfill is done and both columns match, deploy code that reads from full_name. Keep writing to both for one more deploy, so you can roll back without losing data.
Drop the old column
When no running instance reads or writes name, stop writing to it, deploy, and then drop it.
Do not drop the column in the same deploy
Instances that are still draining run the previous release. If the column is gone before they stop, their queries fail.
ALTER TABLE users DROP COLUMN name;The rule behind the steps
Every deploy must work with the schema before it and the schema after it. If you keep that rule, the order of deploys and migrations no longer matters, and a rollback is always one click away.
Written by
Daniel Okafor
Staff engineer, Runtime
Daniel works on the runtime and the database layer. Daniel writes about cold starts, migrations, and measuring performance in production.
