A schema migration is a versioned script that changes database structure — adding a column, creating an index, changing a constraint — applied in order and recorded so every environment converges on the same shape. That much is mechanical. The difficulty is that production has data in it and users on it.
The rules that prevent incidents
- Forward-only in production. Down migrations are useful locally and dangerous live; recovering from a bad deploy is usually a new forward migration, not a rollback that discards data.
- One logical change per migration. Mixed migrations fail halfway and leave a state nobody planned for.
- Never edit an applied migration. Environments that already ran it will not re-run it, and they silently diverge.
- Separate schema changes from data backfills. Backfills are long-running and need batching; schema changes should be fast.
Expand and contract
The pattern that makes zero-downtime changes possible: never have a moment where the running code and the schema disagree. Renaming a column becomes four deploys rather than one.
-- 1. EXPAND: add the new column, nullable. Old code unaffected.
ALTER TABLE customer ADD COLUMN email_address citext;
-- 2. Deploy code that WRITES BOTH and reads the old one.
-- 3. Backfill in batches, off the critical path.
UPDATE customer SET email_address = email
WHERE email_address IS NULL AND id BETWEEN $1 AND $2;
-- 4. Deploy code that READS THE NEW one.
-- 5. CONTRACT: only now is the old column removable.
ALTER TABLE customer DROP COLUMN email;The lock that takes production down
In Postgres, adding a column with a volatile default, changing a column type, or adding a constraint without `NOT VALID` takes an ACCESS EXCLUSIVE lock and rewrites the table — every query queues behind it. On a large table that is an outage. Add the constraint as `NOT VALID`, then `VALIDATE CONSTRAINT` separately; create indexes `CONCURRENTLY`; never let a migration wait on a lock without a timeout.
Safe patterns worth memorising
-- Index without blocking writes (cannot run inside a transaction)
CREATE INDEX CONCURRENTLY idx_order_customer ON "order" (customer_id);
-- Constraint without a full-table scan under lock
ALTER TABLE "order" ADD CONSTRAINT total_non_negative
CHECK (total >= 0) NOT VALID;
ALTER TABLE "order" VALIDATE CONSTRAINT total_non_negative;
-- Never wait indefinitely for a lock
SET lock_timeout = '3s';Testing them properly
A migration that runs on an empty local database has not been tested. What matters is behaviour against production-scale data: how long it takes, what it locks, and whether the application keeps working while it runs. Restore a recent anonymised snapshot and run it there before it goes near production.
Where migrations meet deployment
Whether migrations run before, during or after the application deploy determines which combinations of code and schema exist simultaneously. Expand-and-contract works because it makes every intermediate combination valid — which is also why skipping a step is where teams get caught.
Frequently Asked Questions
Should migrations run automatically on deploy?
For additive changes, yes. For anything that locks or rewrites, run it deliberately with someone watching — automation is not the problem, unattended long locks are.
How do I undo a bad migration?
Usually by rolling forward with a corrective migration. If it dropped data, restore from backup — which is the argument for making destructive changes a separate, deliberate step long after the code stopped using the column.
Do migrations belong in the application repository?
Yes. Schema and the code that depends on it should version together, so a checkout of any commit describes a consistent system.
References
- PostgreSQL Documentation — ALTER TABLE — PostgreSQL
- PostgreSQL Documentation — Explicit Locking — PostgreSQL
About Jishu Labs
Jishu Labs is a software development company founded in 2016. We build custom software, AI/ML systems, and full-stack web and mobile applications for clients, and we make eight AI tools for software teams.