How to Handle Zero-Downtime Database Migrations

When to Use This

When your database tables are approaching 500,000+ rows and your team is still running migrations the same way you did at 10,000 rows.

Why This Matters

A standard ALTER TABLE statement on a large table takes an access exclusive lock for the full duration of the migration. On a table with millions of rows, that migration takes minutes. During those minutes, reads and writes are blocked. The platform is offline.

Most teams discover this at 2AM during a hotfix, or the week after a successful funding round when traffic suddenly spikes. It does not happen at seed — at 10,000 rows, migrations complete in milliseconds. The problem appears suddenly, at the worst possible moment.

The pattern that prevents it is called Expand / Contract.

Step 1: Expand — Add the Column Safely

Add the new column as nullable with no constraints and no default value.

ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

No lock acquired. Old application code continues to work because the column accepts NULL. New code can start writing to it immediately.

Why no default? Specifying a DEFAULT with NOT NULL on a large table rewrites every row — that is the lock. Nullable with no default is instantaneous.

Step 2: Backfill — Migrate Data in Batches

Do not run a single UPDATE across all rows. A single UPDATE on 10 million rows is its own outage.

Instead, batch it:

UPDATE users
SET phone_number = legacy_phone
WHERE id BETWEEN 1 AND 10000
AND phone_number IS NULL;

Run this in small increments — 10,000 rows at a time — with a short sleep between batches. This spreads the load across hours instead of minutes. Schedule it during off-peak hours.

Step 3: Deploy New Application Code

Once the backfill is complete, deploy the application code that reads from and writes to the new column. Verify in production that the new column is being populated correctly before proceeding to the final step.

Step 4: Contract — Add Constraints and Clean Up

Once the new code is fully deployed and data integrity is verified:

ALTER TABLE users ALTER COLUMN phone_number SET NOT NULL;
ALTER TABLE users DROP COLUMN legacy_phone;

This is now safe — the column is already fully populated, so the NOT NULL constraint does not require a table rewrite.

Common Mistakes

Adding NOT NULL with a DEFAULT in Step 1

This is the most common mistake. It feels logical — you want a required column — but on a large table it rewrites every row under an exclusive lock. Always add as nullable first.

Running the full backfill in one query

A single UPDATE across millions of rows locks those rows for the duration. It can also cause replication lag on read replicas. Always batch.

Skipping Step 3 and going straight to contract

Deploying code and adding constraints in the same migration is risky. If the deploy fails, you can end up in an inconsistent state. Keep them separate deploys.

Not testing backfill timing in staging

Run the full migration process in a staging environment with production-size data. If it takes four hours in staging, it will take four hours in production.

Summary

Zero-downtime migrations require three deploys instead of one. That is the trade-off: more process, more coordination, zero production risk. The decision changes permanently once your critical tables cross a few hundred thousand rows — from that point, Expand / Contract is the default approach, not the exception.