Run a Safe Database Migration

On a small development database every migration is instant. On a production table with millions of rows and constant traffic, the same statement can lock writes for minutes, rewrite the whole table, or break the old code still running during the deploy. Safe migrations are boring: small steps, short locks, compatible with both the old and new code, rehearsed on realistic data, and never destroying data without a backup and a yes from the owner.

When to use

• "Add a column", "rename this field", "add an index", "make this NOT NULL", "split this table", "backfill the new column".
• Reviewing a migration in a pull request.
• Running migrations as part of a deploy.

What you need

• The migration files and the tool the project uses (Django, Rails, Alembic, Prisma, Knex, TypeORM, Flyway, Liquibase, golang-migrate, sqlx, plain SQL).
• The database engine and version (SELECT version();), since safe operations differ by version.
• Table sizes and traffic for the tables involved. Get them read-only from a replica or with the owner's help.
• Access: run migrations locally and on staging yourself; production runs through the project's deploy pipeline or by the owner, only with their explicit go-ahead. Database credentials come from the project's secret store or the owner, never pasted into chat.

Method

1. See the real SQL. ORMs hide what will run. Print it:

   • Django: python manage.py sqlmigrate <app> <number>
   • Rails: run on a scratch database and read db/structure.sql changes, or the migration log
   • Alembic: alembic upgrade <from>:<to> --sql
   • Prisma: npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script or read the generated migration.sql
   • Plain SQL tools: read the file. Note whether the tool wraps each migration in a transaction (Django and Rails do on Postgres; CREATE INDEX CONCURRENTLY cannot run inside one).

2. Size the tables.

   SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid))
   FROM pg_stat_user_tables WHERE relname IN ('orders','order_items');
   

   Small tables (thousands of rows) tolerate almost anything. Large or hot tables need the safe patterns below.

3. Classify every statement with references/postgres-migration-hazards.md. For each: what lock it takes, whether it rewrites or scans the table, and the safe alternative. The most common dangers on Postgres:

   • creating an index without CONCURRENTLY (blocks writes for the whole build);
   • adding a foreign key or check constraint that validates immediately (scans the table under a lock): add it NOT VALID, then VALIDATE CONSTRAINT separately;
   • SET NOT NULL on an existing column (full scan under an exclusive lock): add a CHECK (col IS NOT NULL) NOT VALID constraint, validate it, then SET NOT NULL (Postgres 12 and later skip the scan when such a validated constraint exists);
   • changing a column type (usually rewrites the table);
   • adding a column with a volatile default such as now() or a random UUID (rewrites the table); a constant default is fine on Postgres 11 and later;
   • renaming or dropping a column or table the running code still uses.

4. Use expand and contract for anything that renames, retypes or removes. Never rename in place on a live system. Instead:

   1. add the new column or table (nullable, no rewrite);
   2. deploy code that writes to both old and new;
   3. backfill old rows into the new structure in batches;
   4. deploy code that reads from the new structure;
   5. stop writing the old one;
   6. in a later release, after a backup and with the owner's go-ahead, drop the old one. At every step, the code before and after works against the current schema, so a code rollback is always safe.

5. Set lock timeouts so a blocked migration fails fast instead of queueing everyone behind it.

   SET lock_timeout = '5s';         -- give up waiting for a lock after 5 seconds
   SET statement_timeout = '15min'; -- but allow the statement itself to run
   

   If the lock cannot be taken, the migration fails cleanly and you retry at a quieter time. Before running, check for long-running transactions that would block you:

   SELECT pid, now() - xact_start AS age, state, left(query, 80)
   FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC LIMIT 10;
   

6. Backfill in batches, outside the schema transaction. One UPDATE over millions of rows holds locks and bloats the table. Update a few thousand rows per transaction, pause briefly between batches, and make the job restartable:

   UPDATE orders SET currency = 'EUR'
   WHERE id IN (SELECT id FROM orders WHERE currency IS NULL ORDER BY id LIMIT 5000);
   -- repeat until 0 rows updated; sleep 50 to 200 ms between batches; watch replica lag
   

   Run it as a separate script or job, not inside the migration that altered the table.

7. Rehearse. Run the migration on a copy with production-like size (a restored backup with personal data masked, or generated data at scale) and time it. Run the full test suite against the migrated schema. Check the down migration too, and be honest about irreversible steps (a dropped column cannot be restored by a down migration; only by a backup).

8. Lint if available. An open-source migration linter catches many hazards automatically, for example squawk for Postgres SQL (squawk migrations/*.sql), or the Rails strong_migrations gem in Rails projects. Treat its warnings as blockers until explained.

9. Plan the production run with the owner. Put on a show_card: the statements, expected duration from the rehearsal, locks taken, timing window, backup status (a recent backup or snapshot exists and a restore has been tested), rollback steps, and how you will watch it. Proceed only on an explicit yes. Destructive steps (DROP, TRUNCATE, DELETE without a narrow WHERE, irreversible type changes) always need their own explicit yes, after a fresh backup.

10. Run and watch. During the migration: locks and waiting queries (pg_stat_activity, pg_locks), application error rate and latency, replication lag. Abort if waits pile up (cancel with SELECT pg_cancel_backend(<pid>); on the migration session, with the owner informed). Afterwards verify: the schema matches (\d+ table), indexes are valid (SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; returns none), row counts and spot checks of backfilled data are right, and the app works.

MySQL notes

• Many operations are online in InnoDB (ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE; MySQL 8 adds ALGORITHM=INSTANT for some column additions). Specify the algorithm and lock explicitly so the statement fails instead of silently taking a blocking path.
• For large tables, online schema change tools such as gh-ost copy the table in the background and swap it in.
• Set lock_wait_timeout low for migration sessions.

Output

For a review: each statement with its risk class, the problem, and the safe rewrite. For a migration you write: the migration files (split into safe steps), the backfill script, the rehearsal timing, and the production run plan. After running: what ran, how long, verification results, and any follow-up contract steps scheduled.

Checks before you finish

• You read the actual SQL, not just the ORM migration code.
• Every statement on a large table uses a safe pattern or has an explained, rehearsed duration.
• Renames, type changes and removals use expand and contract across releases.
• Lock timeouts are set; backfills are batched and restartable.
• A backup exists and the owner approved before anything ran on production; destructive steps had their own approval.

Pitfalls

• "It was instant on my laptop." Your laptop has 200 rows. Time it on realistic data.
• Renaming a column in one step. The old code still running during the deploy crashes on the missing column.
• Index builds without CONCURRENTLY. Writes queue up behind the build; the site stalls. A failed concurrent build leaves an invalid index to drop and retry.
• Backfill inside the migration transaction. Locks held for the whole update; replication lag spikes.
• No lock timeout. One long-running analytics query blocks your ALTER, and every request queues behind your ALTER.
• Down migrations that "restore" dropped data. They restore the column, empty. Only backups restore data.
• Editing a migration that has already run somewhere. Environments diverge. Write a new migration.