# Zero-Downtime Database Schema Migration Strategies
> Compare zero-downtime schema migration strategies: native online DDL, expand-and-contract, online schema change tools, and blue-green deployments.
Tianzhou | 2026-09-09 | Source: https://www.bytebase.com/blog/zero-downtime-database-schema-migration/
---
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
> Break a potentially disruptive change into compatible intermediate states, keeping live reads and writes correct throughout the transition.
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](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl.html) 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](https://www.postgresql.org/docs/current/sql-altertable.html). For index creation, use `CONCURRENTLY` to allow writes during the build:
```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);
```
[Concurrent index creation](https://www.postgresql.org/docs/current/sql-createindex.html) 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](https://martinfowler.com/articles/evodb.html) 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.
```mermaid
%%{init: {"flowchart": {"wrappingWidth": 600}}}%%
flowchart TD
accTitle: Adding a required field without downtime
accDescr: Five steps: expand the schema, update writers, backfill existing rows, enforce the constraint, and complete the application rollout.
A["1. Expand
Add fulfillment_status
Allow NULL values"]
B["2. Update writers
Set status on new orders
Update it as orders progress"]
C["3. Backfill
Derive status from shipments
Process existing orders in batches"]
D["4. Enforce
Validate existing values
Add NOT NULL"]
E["5. Complete
Read from the new field
Remove fallback logic"]
A --> B --> C --> D --> E
```
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](https://github.com/github/gh-ost) reads the MySQL binary log to capture changes without adding triggers to the source table. [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html) 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](https://aws.amazon.com/blogs/database/deploy-schema-changes-in-an-amazon-aurora-mysql-database-with-minimal-downtime/) 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
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](https://docs.bytebase.com/change-database/online-schema-migration-for-mysql/) 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](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl.html)
- [PostgreSQL: ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)
- [PostgreSQL: CREATE INDEX and concurrent index builds](https://www.postgresql.org/docs/current/sql-createindex.html)
- [Evolutionary Database Design](https://martinfowler.com/articles/evodb.html)
- [gh-ost: GitHub's online schema migration tool](https://github.com/github/gh-ost)
- [Percona: pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html)
- [AWS: Deploy schema changes in Aurora MySQL with minimal downtime](https://aws.amazon.com/blogs/database/deploy-schema-changes-in-an-amazon-aurora-mysql-database-with-minimal-downtime/)
## Related reading
- [Postgres Schema Migration without Downtime](https://www.bytebase.com/blog/postgres-schema-migration-without-downtime/)
- [MySQL Schema Migration Best Practice](https://www.bytebase.com/blog/mysql-schema-migration-best-practice/)
- [SQL Server Schema Migration and Change Management](https://www.bytebase.com/blog/sql-server-schema-migration-guide/)
- [gh-ost vs pt-online-schema-change](https://www.bytebase.com/blog/gh-ost-vs-pt-online-schema-change/)