Zero-downtime database schema migration: expand, backfill, and contract

A database schema change becomes dangerous when old and new application versions cannot operate at the same time. A reliable migration does not try to update the entire estate synchronously. It adds a compatible structure first, moves data and traffic next, and reserves removal of the old state for a separate release.

“Zero downtime” here means no planned service interruption. It is a target, not a guarantee: DDL locks, replication lag, or a faulty backfill still require a tested stop and recovery path.

Start with version compatibility

Before changing a table, identify the application versions that will coexist during a rolling deployment. The old version must continue to read the previous schema, while the new version must tolerate values that have not yet been populated.

Align the change with API contract versioning: a database is also a contract between processes, jobs, and analytical consumers. Renaming a column in one step breaks that contract just as surely as deleting a field from a public API.

For every step, record:

• the owner and execution window;
• maximum acceptable lock time;
• permitted replication lag;
• success and stop metrics;
• the reversal procedure;
• application versions compatible with the schema.

Step 1. Expand without removing the old structure

Add the new column, table, or index while keeping existing code functional. A new field initially permits NULL or uses a safe default. A NOT NULL constraint and removal of the old structure come later.

Test the exact database version on large tables. The same statement can be a metadata-only operation in one release and a full table rewrite in another. Build indexes online or concurrently when the platform supports it, and bound lock-wait time.

Step 2. Write to both models

The new application version begins populating both old and new representations. Keep this logic in one transactional path where possible instead of issuing two independent writes without checking both outcomes.

Dual writes introduce divergence risk. Count failures for each destination and compare values regularly. If an operation completes only partially, its retry must be idempotent.

Step 3. Backfill historical data

Run the backfill in small batches over a stable key. Each batch has a time limit, controls load, and can be repeated safely. Avoid one huge UPDATE that holds locks, expands the transaction log, and creates a replication spike.

Observe:

• processed rows per unit of time;
• CPU, I/O, and transaction-log growth;
• replication lag;
• lock duration and connection queues;
• mismatched and unprocessed records;
• impact on user-facing queries.

Feed these signals into the shared infrastructure observability model rather than leaving them only in the migration script’s log.

Step 4. Shift reads gradually

After data has been populated, let the new version read the new model for a small share of traffic. Compare it with the old read path in the background without returning two responses to the user. Group discrepancies by cause, application version, and data range.

Increase the share only while metrics remain stable. A feature flag should restore reads from the old model without another code deployment. If the two models have different semantics, define the source of truth for every stage in advance.

Step 5. Contract after an observation period

Remove the old column, table, and dual-write path only after:

1. every active application version reads the new model;
2. the backfill has completed and passed validation;
3. background jobs and analytics no longer use the old schema;
4. the agreed observation period has elapsed;
5. application rollback no longer requires the old format.

Removal is a separate migration and a separate release. That gap preserves reversibility during the riskiest part of cutover.

Prepare stop and recovery procedures

Rollback does not always mean reversing DDL. It is often safer to stop the backfill, return reads to the old model, and leave added objects in place until the incident is understood. Deleting them under pressure introduces another heavy operation.

The incident operations runbook should contain stop commands, owners, replication-lag thresholds, integrity checks, and restart conditions. A rehearsal against a production-like copy must reflect data volume, value distribution, and concurrent load.

A schema migration without planned downtime is a sequence of compatible states. Expand creates space for the new format, backfill moves history, controlled cutover validates the result, and contract removes the old state only after evidence shows that it is no longer needed.