How to Run Database Migrations Safely in a Production SaaS Application
- Author
- Vishal Maurya
- Published on
- Reading time
- 4 min read
Overview
A schema change that works on a local database can cause downtime in production. A column rename may break old application instances during a rolling deployment. An index build may block writes, and a data migration that updates millions of rows in one transaction can create locks or long-running replication lag.
Safe migrations are a coordination problem between the database, application versions, and deployment process.
1. Prefer Expand-and-Contract Changes
For a breaking schema change, avoid changing the database and application assumptions in one step when old and new application versions may run simultaneously.
A safer pattern is:
- Expand: add the new column or table while keeping the old schema usable.
- Deploy compatible code: write to both fields or read from the new field with a fallback, as appropriate.
- Backfill: populate existing rows in bounded batches.
- Switch reads: verify the new data path and monitor errors.
- Contract: remove the old field only after no deployed code depends on it.
Not every migration requires all five stages. The point is to preserve compatibility during transitions where multiple versions may coexist.
2. Understand Locks and Index Creation
DDL behavior depends on the database and statement. Before running a migration, check which locks it takes, how long it may run, and whether it blocks ordinary reads or writes. For PostgreSQL, concurrent index creation can reduce write blocking for suitable cases, but it has restrictions and cannot simply be placed inside every ordinary transaction-based migration.
Set appropriate lock and statement timeouts where supported, and test the migration against a realistic data volume. A migration that completes quickly on a small staging table may behave differently in production.
3. Backfill Large Tables in Batches
Avoid loading and updating an entire large table in one application-level migration if that creates excessive memory use, long transactions, or locks. Use bounded batches, checkpoint progress, and make the operation safe to resume.
A resumable backfill should know which rows remain and avoid overwriting values changed by live traffic. If the application is still writing the field, define how concurrent updates are reconciled.
Separate schema changes from long-running data transformations when the deployment framework and operational process allow it.
4. Keep Migrations Deterministic
A migration should not depend on a third-party API, current external state, or a code path that may change between environments. Use historical model definitions provided by the migration framework rather than importing a mutable current model where that can break old migrations.
For Django, data migrations should use the historical app registry supplied to the migration function. For other frameworks, follow their versioned migration conventions.
5. Plan for Failure and Rollback
A database rollback is not always the same as rolling back application code. Once users have written data in a new format, a reverse migration may lose information or fail to reconstruct the previous state.
Define the recovery path before deployment. Depending on the change, recovery may mean rolling forward with a corrective migration rather than reversing the schema. Take backups according to your recovery objectives and understand how to restore them before relying on them.
6. Avoid Running Migrations in Every Replica
If each application instance attempts to run migrations during startup, multiple replicas may race or delay readiness. A controlled release step or dedicated migration job usually makes ownership clearer. The exact mechanism depends on the deployment platform and migration tooling.
Ensure the deployment pipeline stops or alerts when a migration fails rather than continuing to route traffic to code that expects a schema change that did not complete.
7. Verify After Deployment
Monitor migration duration, database locks, replication lag, application errors, and query latency. Confirm that the new code path is actually using the intended schema and that the old path can be removed safely.
Conclusion
Production migrations are safest when they are compatible with rolling deployments, tested against realistic data, and designed to recover from partial progress. Treat schema changes as a release plan, not just a command to run.
If your SaaS application needs a risky schema change, large backfill, or safer migration pipeline, I can help review the rollout and implement a controlled migration strategy. Contact me.