Database migrations without downtime

A schema migration can be perfectly valid SQL and still cause an outage. Production deployments are gradual: old application instances may continue serving traffic while new instances start, background jobs may run older code, and a large table can turn a simple alteration into a long lock.

The safest approach is to make application and schema changes compatible across a deployment window rather than requiring an instant switch.

Think in compatibility windows

During a rolling deployment, at least three states can exist:

1. The old application with the old schema
2. Old and new application versions with an intermediate schema
3. The new application with the final schema

Every step should work in the state where it runs. If new code requires a column that does not exist yet, or old code breaks as soon as a column disappears, deployment order becomes a single point of failure.

Use expand and contract

Split a breaking migration into compatible phases.

Expand: add the new structure without removing the old one.

Migrate: deploy code that can work with both forms, then backfill existing data.

Contract: after all readers and writers have moved, remove the old structure in a later release.

Renaming full_name to display_name, for example, should not be one migration:

Release A: add nullable display_name
Release B: write both columns, read display_name with fallback
Backfill: copy historical values in controlled batches
Release C: read only display_name
Release D: stop writing full_name
Release E: remove full_name

The extra steps buy rollback safety. Releases A through D can usually be reversed without restoring a database backup.

Add constraints in stages

Adding a required column with a value for every existing row can force validation or rewriting work on a large table.

A safer sequence is:

1. Add the column as nullable.
2. Deploy writers that populate it for new records.
3. Backfill older records in bounded batches.
4. Verify that no missing values remain.
5. Add or validate the constraint.

The exact locking behavior depends on the database engine and version. Test the real statement against production-like table sizes; a migration that takes milliseconds on an empty development database proves very little.

Backfill without becoming the outage

A backfill competes with normal traffic for CPU, disk I/O, locks and replication capacity. Make it restartable and observable.

UPDATE customers
SET display_name = full_name
WHERE id > :last_id
  AND id <= :batch_end
  AND display_name IS NULL;

Prefer stable key ranges over a single enormous transaction. Record progress, pause between batches when needed, and make rerunning a batch harmless.

Watch query latency, lock waits, replication lag and transaction-log growth while the job runs. “The script is progressing” is not enough if customer traffic is degrading.

Build indexes with production traffic in mind

Index creation can consume significant I/O and may block writes depending on the database and command used. Where supported, use the engine's online or concurrent index-building mechanism and understand its limitations.

An index should support a known access pattern. Before creating one, capture the slow query and its execution plan. Afterward, verify that the optimizer uses the index and that write overhead remains acceptable.

Separate schema change from application startup

Running migrations automatically from every application replica creates concurrency and availability risks. Several instances may attempt the same migration, and a failed schema change can prevent all new replicas from becoming healthy.

Prefer a controlled migration job in the release pipeline:

build artifact
  → test migration against a recent schema copy
  → deploy compatible schema expansion
  → deploy application gradually
  → verify health and data
  → schedule later cleanup

Use database-level migration locks as additional protection, not as the entire deployment strategy.

Design rollback before execution

Rolling application code back is easy only when the schema remains backward compatible. Destructive migrations often cannot be undone reliably once new writes occur.

Before deploying, decide:

• Can the old application run against the expanded schema?
• What happens to data written in the new format after rollback?
• Is rollback safer than rolling forward with a fix?
• Does the migration require a verified backup or point-in-time recovery?
• How will you know that the new data path is correct?

Avoid “down” migrations that pretend dropped or transformed data can always be reconstructed. Recovery plans should reflect what is actually reversible.

Verify the migration as a product change

Schema success is not the same as feature success. Validate row counts, null rates, uniqueness, application errors and the affected user journey.

For critical transformations, compare old and new representations during the compatibility period. A shadow read can calculate both results and report differences without changing the response users receive.

A practical release checklist

Before applying a production migration:

1. Measure it against realistic data volume and database configuration.
2. Review locks, transaction duration and replication effects.
3. Ensure old and new application versions can coexist.
4. Make backfills bounded, restartable and observable.
5. Define abort thresholds before execution.
6. Confirm backups and recovery procedures where data is at risk.
7. Monitor the migration and customer-facing behavior together.
8. Delay destructive cleanup until the new path has proved stable.

The useful mental model

A database migration is a distributed system change because many application versions, workers and replicas interact with the schema over time.

Expand compatibility first, move traffic and data deliberately, then contract only when the old path is truly unused.