Schema Migrations You Can Roll Back

The Problem#

Database changes are harder to roll back than code. If a deploy renames a column and old app instances still write the previous shape, the rollback can break data or take the site down.

Why It Matters#

Most production deploys are rolling deploys. For a period of time, old code and new code run together. A safe migration must support both versions until the rollout is complete and verified.

Large companies and small teams both use the same expand-contract idea because it works with real deployment behavior: add compatibility first, switch behavior second, delete old shape last.

Project Example#

To rename users.full_name to users.display_name, do not rename it in one migration. Add the new column, dual-write both columns, backfill data, read from the new column, then remove the old column in a later release.

Implementation Example#

ALTER TABLE users ADD COLUMN display_name text;

UPDATE users
SET display_name = full_name
WHERE display_name IS NULL
LIMIT 1000;

In practice, the backfill should run in batches from a job, not as one giant transaction. After the backfill, deploy code that reads display_name with a fallback to full_name, then later remove the fallback.

Implementation Checklist#

• Expand first: add nullable columns, tables, or indexes.
• Deploy code that writes both old and new shapes.
• Backfill in small batches.
• Switch reads after validation.
• Contract later: remove old fields only after all code is migrated.
• Keep old workers compatible during the transition.
• Add metrics for rows remaining to backfill.
• Make every step independently deployable and reversible.

Production Notes#

Avoid table rewrites on hot tables during peak hours. Adding a column is usually cheap; adding a default, constraint, or index may not be. For Postgres, use CREATE INDEX CONCURRENTLY and validate constraints separately when possible.

For multi-service systems, publish a migration plan so every service owner knows when the old field becomes read-only and when it will be removed.

Common Mistakes#

• Running table rewrites during peak traffic.
• Adding NOT NULL constraints before backfill.
• Combining schema change, data migration, and feature rollout in one deploy.
• Forgetting old worker versions during rolling deploys.
• Deleting the old column before downstream consumers have migrated.
• Running a backfill without rate limits.

Summary#

Safe migrations are staged. Expand, backfill, switch, validate, and contract so code rollback remains possible.