Zero-Downtime Database Schema Migration Strategies

A zero-downtime database schema migration changes the schema without failing application requests or exceeding the latency budget. Two things can go wrong: the DDL blocks queries, or the new schema breaks running application code.

Check native online DDL first. Use expand-and-contract to keep application versions compatible during deployment. For expensive table changes, consider an online schema change tool or a separate blue-green environment. These approaches can be combined.

The first principle: compatible intermediate states#

For changes that require data movement or multiple deployments:

1. Prepare the new structure while the current one remains usable.
2. Backfill existing data and keep concurrent writes consistent.
3. Switch application usage. Remove the old structure after its consumers and rollback window are gone.

Metadata-only changes may need no backfill. Larger changes also need bounded lock waits and throttled background work to avoid disrupting traffic.

1. Native online DDL#

Use the engine's native operation when its locking and resource costs fit the workload.

MySQL InnoDB online DDL offers several algorithms. INSTANT changes metadata for supported operations. INPLACE can still rebuild the table; concurrent writes depend on the operation. Specify the algorithm and applicable locking level to reject unsupported requests rather than allow a more disruptive fallback.

PostgreSQL documents lock levels and validation options for each ALTER TABLE operation. For index creation, use CONCURRENTLY to allow writes during the build:

CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON orders (customer_id);

Concurrent index creation does more work than a regular build. It cannot run inside a transaction block and can leave an invalid index after failure.

Online DDL still consumes resources and can wait on long-running transactions.

2. Expand-and-contract#

Expand-and-contract, also called parallel change, spreads a breaking change across compatible releases: add the new structure, migrate data and application usage, then remove the old structure. The Evolutionary Database Design describes deploying a compatible database change before the application update.

Example: add a required field to an existing table#

You need a required orders.fulfillment_status field. Existing orders need values derived from shipment records, so a single default won't work.

Readers must tolerate missing values during the backfill. Backfills must preserve concurrent application updates. Before enforcing NOT NULL, ensure all rows have values and every writer supplies the field, including application versions retained for rollback.

Keep backfill batches small and resumable. Throttle when application latency or replica lag rises. When replacing a column, use dual writes to keep both representations current through the rollback window, preferably in the same transaction.

Include background jobs, integrations, reports, and exports in the rollout. Updating the main application alone does not retire every schema dependency.

3. Online schema change tools#

For large MySQL tables that native DDL cannot change with acceptable blocking, online schema change tools:

1. Create a replacement table with the target schema.
2. Copy existing rows in chunks while production uses the original table.
3. Apply ongoing changes to the replacement.
4. Swap tables after synchronization completes.

gh-ost reads the MySQL binary log to capture changes without adding triggers to the source table. pt-online-schema-change uses triggers to propagate source writes to the replacement table. Their restrictions around keys, foreign keys, triggers, and replication differ.

Budget storage for the replacement table and indexes. Long-running transactions can delay the final swap, so test cutover under a representative workload.

4. Blue-green database deployment#

Blue-green deployment runs disruptive DDL on a synchronized copy of production. AWS demonstrates rebuilding a table to reclaim space on an Aurora MySQL green database while blue serves requests. After the rebuild, let replication catch up, validate, and switch traffic to green.

Cutover still briefly interrupts service, and switching back after new writes requires reconciliation.

Control the rollout with feature flags#

Feature flag rollout and rollback: prepare and validate, enable new reads, then turn the flag off if errors rise to restore old reads while both columns stay current.

Deploy the schema and new code before enabling the behavior that depends on them. Once the backfill is validated, use a feature flag to switch reads gradually. Monitor errors and latency.

If the new read path fails, turn the flag off. Keep both columns current so old reads still return correct data. This rolls back application behavior without reversing the schema migration. Remove the flag and old column only after the rollback window closes.

Put the migration into practice#

Set pause thresholds for lock waits, application latency, disk usage, and replica lag. Define which application versions remain safe to roll back to after each migration step.

Bytebase integrates with gh-ost for MySQL online schema changes. Track the DDL, application rollout, backfill, and cleanup as separate steps in a reviewed migration workflow.

References#

• MySQL: InnoDB and Online DDL
• PostgreSQL: ALTER TABLE
• PostgreSQL: CREATE INDEX and concurrent index builds
• Evolutionary Database Design
• gh-ost: GitHub's online schema migration tool
• Percona: pt-online-schema-change
• AWS: Deploy schema changes in Aurora MySQL with minimal downtime