Zero-Downtime Database Migration Guide: Expand-and-Contract Patterns, Checks, and Rollbacks
databasesschema-migrationspostgresqlmysqldeployment-safetybackfills

Zero-Downtime Database Migration Guide: Expand-and-Contract Patterns, Checks, and Rollbacks

QQueries.cloud Editorial Team
2026-11-20
7 min read

Use this expand-and-contract checklist to plan safe schema changes, controlled backfills, verification, and realistic database rollback decisions.

A zero-downtime database migration is less about running one clever ALTER statement and more about coordinating schema changes with application compatibility, traffic, backfills, monitoring, and rollback decisions. This guide provides a reusable expand-and-contract checklist for changing production schemas without breaking old or new application versions.

Overview

The safest default for a production schema change is to separate it into compatible phases. First, expand the schema so it can support both the current and the new application versions. Next, deploy code that can use the expanded structure while remaining compatible with the old one. Then backfill or migrate data in controlled batches. Finally, remove obsolete structures only after older application versions, jobs, and integrations are no longer using them.

This approach is commonly called an expand-and-contract migration. It reduces the number of changes that must succeed at once and gives the team observable checkpoints between phases. It does not guarantee zero locking, zero replication lag, or zero risk. Database engine behavior, table size, indexes, constraints, transaction volume, replicas, and managed-service limits still matter.

The basic sequence

  1. Expand: Add nullable columns, new tables, compatible indexes, or other structures without requiring the application to use them immediately.
  2. Deploy compatible code: Make the application write to or read from both representations when necessary.
  3. Backfill: Copy or derive existing data in small, restartable batches.
  4. Verify: Compare counts, null rates, values, constraints, query behavior, and operational metrics.
  5. Contract: Stop using the old structure, remove compatibility code, and only then consider dropping obsolete columns, indexes, or tables.

For API-facing systems, treat the database and application release as one compatibility contract. An API contract-testing workflow can help identify whether a new response or write path is safe for consumers; see the OpenAPI validation and contract testing guide for a related checklist.

Checklist by scenario

Use the scenario below as a starting point, then confirm the exact behavior for your database version, ORM, hosting provider, and deployment method.

ChangeSafer patternMain risks to check
Add a columnAdd it as nullable or with a database-safe default; deploy code separately.Table rewrite, locks, default-expression cost, replica lag, ORM assumptions.
Make a column requiredBackfill first, validate completeness, then add the constraint separately.Existing nulls, validation locks, failed writes during mixed-version rollout.
Rename a columnAdd a new column, dual-write, backfill, switch reads, then remove the old column.Old application versions, background jobs, reports, direct SQL clients.
Change a data typeAdd a new typed column or table, transform in batches, compare values, then switch.Truncation, rounding, invalid values, conversion cost, query-plan changes.
Add an indexUse an online or concurrent option when supported and schedule observation time.Lock duration, write overhead, disk usage, build time, replica effects.
Remove a column or tableRemove all reads and writes first; wait through the maximum deployment and job overlap.Rollback requirements, stale workers, analytics queries, undocumented clients.
Split or merge tablesIntroduce the new model, dual-write or replicate data, validate, then cut over.Ordering, transaction boundaries, duplicate records, consistency gaps.

Reusable pre-migration checklist

  • Define the old schema, target schema, affected services, and compatibility window.
  • Identify every reader and writer, including workers, scheduled jobs, admin tools, reports, and data pipelines.
  • Estimate the volume of rows to backfill and the expected write load during the operation.
  • Choose a batch key, such as a monotonically increasing identifier, and make the backfill restartable.
  • Write verification queries before changing production.
  • Confirm the migration can pause without leaving an unrecoverable partial state.
  • Set thresholds for lock time, error rate, replication lag, query latency, and resource consumption.
  • Document who can stop the rollout and which signal triggers that decision.

Example migration template

-- Phase 1: expand
ALTER TABLE accounts ADD COLUMN normalized_email TEXT NULL;

-- Phase 2: deploy code that can read the new value
-- and continues to support the old column.

-- Phase 3: backfill in application-controlled batches
UPDATE accounts
SET normalized_email = lower(trim(email))
WHERE id > :last_id
  AND id <= :next_id
  AND normalized_email IS NULL;

-- Phase 4: verify, switch reads, and stop old writes
-- Phase 5: add constraints or remove the old structure later

The syntax and locking behavior vary by engine, so treat this as a sequencing example rather than a copy-and-paste production migration.

What to double-check

Locking and online DDL

An operation described as “online” may still take brief metadata locks, consume substantial I/O, or wait behind a long-running transaction. Check whether the operation blocks reads, writes, or only certain metadata changes. Review active transactions, lock queues, statement timeouts, and the database provider’s guidance before starting.

Defaults, constraints, and validation

Adding a default, setting a column to not-null, or validating a constraint can have different costs depending on the engine and version. A safer sequence is often to add the structure without an immediate table-wide validation, backfill or validate separately, and enforce the final rule after the data is known to comply. Do not assume that a migration tool’s “safe” label reflects your table size or workload.

Backfill design

A backfill should be bounded, observable, and restartable. Prefer key ranges or stable cursors over repeatedly scanning the entire table. Keep transactions small enough to limit lock duration and rollback work, but large enough to avoid excessive overhead. Throttle based on measurable signals such as write latency, CPU, storage throughput, or replica lag. Avoid running a large backfill during an unrelated deployment or capacity event.

Replication and failover

Check how the change propagates to read replicas, logical subscribers, change-data-capture consumers, backups, and failover targets. A migration may succeed on the primary while a replica falls behind or a downstream consumer cannot interpret the new shape. Include replica health and recovery procedures in the go/no-go review.

ORM and deployment behavior

Review generated SQL rather than relying only on the migration filename or ORM command. Confirm whether the ORM wraps the operation in one transaction, automatically creates indexes, assumes a new column is non-null, or starts using the new field as soon as the application boots. During a rolling deployment, old and new application versions must both be able to operate against the intermediate schema.

Verification queries

Prepare checks that answer concrete questions:

  • How many rows have been backfilled, and how many remain?
  • Are any new values null, malformed, duplicated, truncated, or outside expected ranges?
  • Do old and new representations agree for a sample and for the full population where practical?
  • Did query latency, lock waits, error rates, or replication lag change?
  • Are all application instances, workers, and scheduled tasks using the intended representation?

For SQL-heavy validation and regression work, the SQL query optimization playbook offers complementary practices for checking plans and performance.

Common mistakes

  • Combining expand, backfill, and contract in one release: This makes failures harder to isolate and usually removes the safest rollback point.
  • Assuming a nullable column is harmless: The DDL may be low risk, but its index, default, trigger, or ORM behavior may not be.
  • Writing a backfill that cannot resume: A single giant transaction can create long locks, heavy WAL or binlog growth, and expensive recovery.
  • Forgetting non-application clients: Reporting queries, scripts, exports, and older workers often outlive the main service rollout.
  • Dropping the old field too early: A successful deployment does not prove that every old process has stopped.
  • Testing only correctness: A migration can produce correct values while degrading latency, storage, replication, or backup behavior.
  • Treating rollback as “run the reverse migration”: Once data has been transformed or new writes have occurred, reversing the DDL may not restore the previous state. Define whether rollback means restoring code compatibility, stopping writes, replaying data, or restoring from backup.

Keep database rollback separate from application rollback. If the new code has written data that the old code cannot understand, immediately reverting the application may increase damage. A compatibility-first design gives the team more recovery options.

When to revisit

Revisit this checklist before every production schema release, especially when table size, traffic, database version, ORM behavior, hosting provider, replication topology, or deployment strategy has changed. It is also worth reviewing before seasonal traffic increases, large data imports, retention jobs, infrastructure migrations, and changes to backup or disaster-recovery procedures.

Before execution, hold a short go/no-go review and confirm:

  1. The expand step is compatible with every currently running application version.
  2. The backfill has a tested pause and resume path.
  3. Dashboards and alerts cover locks, latency, errors, resource pressure, and replication health.
  4. The owner, stop conditions, and communication channel are clear.
  5. The contract step is scheduled separately from the initial rollout.
  6. The team knows whether the recovery plan is application rollback, migration pause, forward fix, or data restoration.

Afterward, record actual lock duration, throughput, lag, errors, and operator decisions. Those observations turn the next database migration into a more predictable developer and platform workflow.

Related Topics

#databases#schema-migrations#postgresql#mysql#deployment-safety#backfills
Q

Queries.cloud Editorial Team

Developer Productivity Editors

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.