The golden rule: separate deploy from migrate
Never deploy code and run migrations in the same step. Deploy the code first, verify it works with the old schema, then run the migration. If the migration fails, the code is already running fine.
Additive changes only
In a zero-downtime environment, the old and new code run simultaneously during deploys. This means:
- Safe: Add a column, add a table, add an index
- Unsafe: Rename a column, drop a column, change a column type
How to rename a column safely
Three deploys instead of one:
- Deploy 1: Add the new column. Write to both old and new. Read from old.
- Deploy 2: Backfill the new column from the old. Switch reads to the new column.
- Deploy 3: Drop the old column.
Yes, it is more work. But each step is independently safe and reversible.
Index creation: CONCURRENTLY or nothing
-- This locks the table for the entire build time
CREATE INDEX idx_users_email ON users (email);
-- This does not
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
On a table with 10M rows, the first statement locks writes for minutes. The second runs in the background. Always use CONCURRENTLY in production.
Migration testing checklist
- Run the migration against a copy of production data (not an empty test database)
- Check execution time — anything over 30 seconds needs a different approach
- Verify the rollback works — every migration should have a
downfunction - Test with the old version of the code running (simulate the overlap period)
The one thing I always forget
Default values on new columns. If you add a NOT NULL column without a default, every existing row fails the constraint. Add it as nullable first, backfill, then add the constraint. Or use a default value, but be aware that on large tables, Postgres rewrites every row when adding a column with a default (fixed in Postgres 11+ for non-volatile defaults).