How to Write Database Migrations That Don't Take the Site Down
The migration that takes production down is almost never the complicated one. Complicated migrations get reviewed carefully. It's `ALTER TABLE users ADD COLUMN ... NOT NULL DEFAULT ...` on a 40-million-row table, written in ten seconds, that takes an `ACCESS EXCLUSIVE` lock and stalls every query touching that table until it finishes.
The underlying problem is that a migration and the code that depends on it deploy at different moments, and for a window they must both work against the same schema. Almost every safe-migration technique is a way of managing that window.
Every schema change is two deploys, not one
The expand-contract pattern: expand the schema so old and new code both work, deploy the code, then contract by removing what's now unused. Slower and dramatically less likely to page you.
- ›Adding a nullable column is safe. Adding `NOT NULL` without a default rewrites the table.
- ›Renaming a column is never safe in one step. Add the new one, write to both, backfill, switch reads, then drop.
- ›Dropping a column is safe only after every deployed version has stopped selecting it — including the one you might roll back to.
- ›Adding an index on a busy table needs `CREATE INDEX CONCURRENTLY`, which cannot run inside a transaction.
- ›Backfills belong in batches outside the migration. A single `UPDATE` over ten million rows holds locks and bloats the transaction log.
The approaches
Framework migrations (Django, Rails, Laravel, Alembic)
// best for: Full-stack frameworks where the ORM is the source of truth for the schema.
- Django
- Ruby on Rails
- Laravel
- FastAPI
- Flask
These generate migrations by diffing your models against the current schema, which is convenient and occasionally wrong in expensive ways. The generated migration reflects what changed, not how to apply it safely — Django will happily generate an `ALTER TABLE` that locks a large table, because it has no idea the table is large.
Always read generated migrations before running them. For anything on a table over a million rows, expect to hand-edit: split it into steps, add `CONCURRENTLY` where relevant, move the backfill into a separate data migration that batches.
Django's `RunPython` with `atomic = False` and Rails' `disable_ddl_transaction!` exist precisely for operations that can't run in a transaction. Needing them is normal, not a code smell.
The gotcha
Framework migrations run inside a transaction by default, and `CREATE INDEX CONCURRENTLY` errors out inside one. The fix is disabling the transaction for that migration — but then a failure leaves it partially applied, and you must handle re-running it yourself.
SQL-first migration tools (Flyway, dbmate, Drizzle Kit, golang-migrate)
// best for: Teams who want the exact SQL in version control and reviewed like code.
- PostgreSQL
- MySQL
- Docker
- GitHub Actions
- GitLab CI
You write the SQL. Nothing is generated, nothing is inferred, and the migration in the repository is byte-for-byte what runs in production. For teams that have been burned by a generated migration doing something surprising, that predictability is worth the extra typing.
It also makes review meaningful. A reviewer looking at `ALTER TABLE orders ADD COLUMN status text` can ask about locking, defaults, and backfill, because the operation is right there rather than expressed as a model diff.
The trade is drift: nothing stops the schema and the application's model definitions from diverging. Most teams add a CI check that applies migrations to a scratch database and compares the result against a committed schema dump.
-- Expand-contract, adding a NOT NULL column to a large table.
-- Migration 1: add nullable. Instant, no rewrite.
alter table orders add column status text;
-- Migration 2: backfill in batches, outside a long transaction.
update orders set status = 'legacy'
where status is null and id in (
select id from orders where status is null limit 5000
); -- repeat until 0 rows
-- Migration 3: only once the backfill is verified complete.
alter table orders alter column status set not null;The gotcha
Editing a migration that has already run somewhere. The tool tracks applied migrations by version, so an edited file is never re-applied — production keeps the old behaviour while the repository claims otherwise, and the divergence surfaces months later on a fresh environment. Always add a new migration.
Platform-managed schema (Supabase, PlanetScale, Convex)
// best for: Teams who want branching and review workflows around schema changes.
- Supabase
- PlanetScale
- Convex
- Xano
PlanetScale's branching model is the standout: you create a schema branch, make changes, and open a deploy request that runs the change online without locking, using Vitess under the hood. That genuinely removes the locking class of problem — at the cost of restrictions, notably no foreign key constraints in the traditional sense.
Supabase gives you migration files plus a dashboard that can apply changes directly. The dashboard is the hazard: a change made there doesn't exist in your repository, so the next environment built from migrations won't have it. Treat the SQL editor as read-only in any project with more than one environment.
Convex takes a different path again, deriving schema from TypeScript definitions with runtime validation.
The gotcha
Making schema changes through a hosted dashboard creates drift that is invisible until a fresh environment is provisioned and behaves differently. Pick one source of truth — migration files — and treat everything else as a viewer.
Common pitfalls
Adding NOT NULL with a default on a large table
On Postgres 11+ a constant default is metadata-only and fast, but a volatile default (`now()`, `gen_random_uuid()`) still rewrites the whole table under an exclusive lock. Check your version's behaviour, and default to the three-step expand-contract on anything large.
Migrations that depend on application code
A data migration importing your models breaks the moment those models change, because the migration must run against the schema as it was, not as it is. Write data migrations in raw SQL or against a frozen historical model.
No rollback path
A migration that drops a column cannot be undone — the data is gone. For destructive changes, deploy the code that stops using the column first, wait a full release cycle, then drop it. The gap is what makes rollback possible.
Running migrations from every instance on deploy
Ten containers starting simultaneously each run the migration. Most tools take an advisory lock so only one proceeds, but the others may block on startup past the health check timeout and get killed. Run migrations as a separate pre-deploy step.
Questions people actually ask
Should migrations run automatically on deploy?
As a distinct step that completes before new application instances start, yes. Inside application startup, no — that couples migration timing to container scheduling and makes a slow migration look like a failed health check.
How do I rename a column safely?
Four deploys: add the new column and write to both; backfill; switch reads to the new column; drop the old one. Tedious, and the only approach where any intermediate state is safe to roll back to.
Can I squash old migrations?
Yes, once every environment is past them. Replace the history with a single schema snapshot marked as already applied. Worth doing when the test suite spends real time replaying hundreds of migrations.