Database migrations are one of the most anxiety inducing operations in software engineering. One wrong column drop, one locked table, one incompatible schema change, and your production application goes offline while your users stare at error pages. We have run thousands of migrations across production systems handling millions of requests, and downtime has never been acceptable.
The good news: zero downtime migrations are not a luxury reserved for companies with 50 person platform teams. They are a set of patterns and practices that any team can adopt. The bad news: they require discipline, and most teams learn that discipline the hard way, after a migration takes the site down at the worst possible moment.
Why Migrations Cause Downtime
Before solving the problem, it helps to understand why it exists. Migrations cause downtime for three reasons:
Table locks. Operations like adding a column with a default value, creating an index, or altering a column type can lock the entire table in some database engines. While the lock is held, all reads and writes to that table queue up. On a table with millions of rows, an ALTER TABLE can hold a lock for minutes. Your application times out, users see errors, and monitoring lights up.
Schema incompatibility. If you deploy a new application version that expects a column that does not exist yet (or no longer expects a column that still exists), every query touching that column fails. The window between "migration runs" and "new code deploys" is a danger zone where your schema and application are out of sync.
Data migrations on large tables. Backfilling data across millions of rows is not instantaneous. If you try to UPDATE 10 million rows in a single transaction, you lock the table, exhaust memory, and potentially crash the database.
The Expand and Contract Pattern
The most reliable zero downtime migration strategy is expand and contract. Instead of making breaking changes in a single step, you split every migration into phases:
Phase 1, Expand. Add the new structure alongside the old one. Add a new column, create a new table, build a new index. Do not remove or rename anything yet. Your application continues using the old structure without interruption.
Phase 2, Migrate. Update the application code to write to both the old and new structures. Backfill existing data from old to new. This phase can take hours or days for large datasets, and that is fine, because nothing is locked and nothing is broken.
Phase 3, Contract. Once all data is migrated and the application is fully using the new structure, remove the old columns, tables, or indexes in a cleanup migration. This is a separate deployment, often days or weeks after the expand phase.
This pattern turns one risky migration into three safe ones. Each phase is independently deployable, independently reversible, and never requires a maintenance window.
Practical Examples
Adding a Required Column
The naive approach, adding a NOT NULL column with a default, locks the table on PostgreSQL for large datasets (though PostgreSQL 11+ handles this better for constant defaults). The safe approach:
1. Add the column as nullable with no default
2. Deploy application code that writes to the new column for all new records
3. Backfill existing rows in batches of 1,000 to 10,000
4. Once all rows are populated, add the NOT NULL constraint
5. Remove fallback code that handled null values
Renaming a Column
Never rename a column directly. Instead:
1. Add a new column with the desired name
2. Deploy code that writes to both columns
3. Backfill old rows to populate the new column
4. Deploy code that reads from the new column only
5. Drop the old column in a later migration
Creating an Index on a Large Table
A standard CREATE INDEX locks the table. Use CREATE INDEX CONCURRENTLY in PostgreSQL. It takes longer to build but does not block reads or writes. Always use this in production. The tradeoff of slower index creation versus zero user impact is not even a real tradeoff.
Changing a Column Type
This is one of the most dangerous migrations. Altering a column type rewrites the entire table in many databases. The safe path:
1. Add a new column with the desired type
2. Set up a trigger or application logic to keep both columns in sync
3. Backfill the new column
4. Switch reads to the new column
5. Drop the old column
Batch Processing for Data Migrations
When you need to backfill or transform millions of rows, never do it in a single transaction. A single UPDATE that touches 5 million rows will hold locks, consume memory, and potentially trigger out of memory kills.
Instead, process in batches:
- Batch size: 1,000 to 10,000 rows per transaction, depending on row size and database capacity
- Throttling: Add a small delay (100 to 500 ms) between batches to avoid overwhelming the database
- Progress tracking: Log which batch you are on so you can resume if the process is interrupted
- Idempotency: Write your backfill so it can safely run multiple times without corrupting data
For the systems we manage through our ongoing management service, we run data migrations as background jobs with monitoring, automatic retry, and Slack notifications on completion. This turns a nerve wracking manual operation into a boring, automated one.
Backward Compatible Deployments
Zero downtime migrations only work if your application code is backward compatible with both the old and new schema during the transition period. This means:
Your application must handle missing columns gracefully. If the migration has not run yet, the old columns must still work. If the migration has run but the new code is not deployed, the new columns must have sensible defaults.
Use feature flags for schema dependent code. If a new feature requires a new table or column, gate the feature behind a flag. Deploy the migration, verify it succeeded, then enable the flag. This decouples schema changes from feature releases.
Never deploy code that depends on a migration in the same release as the migration. Deploy the migration first, verify it, then deploy the application code. Or better yet, write code that works with both the old and new schema, deploy the code first, then run the migration.
This is closely related to how you structure your CI/CD pipeline. Your deployment pipeline should have separate stages for migrations and application code, with health checks between them.
Migration Testing Before Production
Every migration should be tested against a copy of production data before it touches the real thing. Not a toy dataset with 100 rows. A copy of production, with production scale data.
Why this matters: A migration that runs in 200 milliseconds on your development database with 500 rows might take 45 minutes on production with 5 million rows. You will not discover this in development. You will discover it in production, at the worst possible time.
Our standard process:
1. Restore a recent production backup to a staging environment
2. Run the migration against staging and measure execution time
3. Run the full test suite against the migrated schema
4. Verify application functionality with the new schema
5. Only then schedule the production migration
This adds 30 minutes to the migration process. It prevents hours of downtime and data loss incidents.
Rollback Planning
Every migration needs a rollback plan documented before it runs. Not "we will figure it out if something goes wrong." A specific, tested set of steps.
For expand phase migrations (adding columns, tables, indexes), the rollback is simple: drop what you added. For contract phase migrations (dropping old columns), the rollback is harder because you have already deleted structure. This is why the contract phase should only happen after extended validation that the new structure is working correctly.
Keep the old structure around longer than you think you need to. An unused column costs almost nothing. A premature column drop that triggers a rollback costs hours of downtime and engineering time.
Tools That Help
For PostgreSQL, several tools make zero downtime migrations easier:
- pg_repack for repacking tables without locks
- CREATE INDEX CONCURRENTLY for non blocking index creation
- Advisory locks for coordinating migrations across multiple application instances
- pgroll for managing expand and contract migrations with built in versioning
For application level migration management, frameworks like Prisma, Knex, and Django have migration systems built in. The important thing is that your migration tool supports running individual migrations, rolling back individual migrations, and tracking which migrations have been applied.
The Bottom Line
Zero downtime migrations are not optional for production systems. They are a fundamental part of system architecture that separates professional operations from amateur ones. The expand and contract pattern handles 95% of cases. Batch processing handles the remaining 5%. Testing against production scale data catches the surprises.
If your team is still running migrations with maintenance windows, or worse, running them live and hoping for the best, the patterns in this post will eliminate that risk. And if you want someone who has done this hundreds of times to handle it for you, let us know.