Zero-Downtime Database Migration During Deployments: Expand-and-Contract Workflow and Rollback Checklist
Database MigrationsZero DowntimeDeploymentsCI/CDSQLRollback

Zero-Downtime Database Migration During Deployments: Expand-and-Contract Workflow and Rollback Checklist

PPlkdt Labs
2026-11-17
7 min read

Use this expand-and-contract checklist to change production database schemas safely, manage backfills, monitor deployments, and plan rollback.

A production schema change does not have to require a maintenance window. This deployment guide explains the expand-and-contract workflow for zero-downtime database migrations, including compatible schema steps, backfills, feature flags, observability, deployment ordering, and the limits of rollback. Use the checklists before a release, framework upgrade, or large data change.

Overview

A zero-downtime database migration is less about running one clever SQL statement and more about controlling compatibility between application versions and the database. During a rolling deployment, old and new application instances may run at the same time. The database must therefore support both versions until the transition is complete.

The safest general pattern is expand and contract:

  1. Expand: Add new tables, columns, indexes, or data structures without removing or changing the old interface.
  2. Migrate: Deploy application code that can use both representations, then backfill existing data in controlled batches.
  3. Contract: After all application instances and readers use the new representation, remove obsolete columns, indexes, or code paths in a separate change.

Do not treat a migration as successful merely because the migration command exits with code zero. A safe database schema migration deployment also considers lock duration, query latency, replication lag, connection pool pressure, backfill progress, and the ability to stop or reverse the application rollout.

Compatibility is the primary design constraint

Adding a nullable column is usually easier to roll out than renaming or dropping one. A direct rename can break old application instances, reporting jobs, scripts, and replicas that still expect the previous name. Instead, add the replacement column, write to both columns temporarily, backfill existing rows, switch reads behind a feature flag, and remove the old column only after dependencies have been audited.

Rollback also needs a precise definition. Application rollback may be straightforward while database rollback is not. A destructive change, such as dropping data or converting values irreversibly, may not be safely reversible. Prefer forward-compatible migrations and restore procedures that have been tested separately.

Checklist by scenario

Adding a nullable column

  • Confirm that the column can be added without a long table rewrite or blocking lock on your database version.
  • Add it as nullable or provide a database-safe default that will not force an expensive rewrite.
  • Deploy code that ignores the column until the schema change is confirmed.
  • Start writing the new value only after the column exists in every production database.
  • Backfill older rows in small, resumable batches if the field is required by the new feature.
  • Enable reads gradually, using a feature flag or a measured rollout where practical.

Renaming or replacing a column

  1. Add the new column without removing the old one.
  2. Deploy a compatibility layer that reads the new value when present and falls back to the old value when necessary.
  3. Write both columns for a defined transition period. Make the write operation idempotent if retries are possible.
  4. Backfill the new column and verify row counts, null rates, and representative values.
  5. Switch all reads to the new column behind a flag or controlled release.
  6. Search application code, dashboards, exports, scheduled jobs, and ad hoc scripts for references to the old column.
  7. Remove the old write path and defer dropping the old column until the dependency review is complete.

Adding an index

  • Review the index definition against the queries it is intended to improve.
  • Use the database's online or concurrent index option where supported and appropriate.
  • Schedule the operation with awareness of write volume, disk usage, and replication capacity.
  • Watch lock waits, query latency, CPU, I/O, and replica health while the index builds.
  • Verify the query plan after creation rather than assuming the optimizer will use the index.

Changing a data type or constraint

  • Measure existing values before changing the type or adding a constraint.
  • Clean invalid or out-of-range records in a separate, observable step.
  • Use a staging column or compatibility representation when old and new formats cannot coexist.
  • Apply the constraint only after validation shows that existing and incoming data comply.
  • Test application serialization, API contracts, imports, and exports, not just database queries.

Example migration sequence

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

-- Phase 2: migrate in batches, outside one large transaction
UPDATE accounts
SET display_name_new = display_name
WHERE id > :last_id
  AND id <= :next_id
  AND display_name_new IS NULL;

-- Phase 3: contract later, after all readers and writers migrate
-- ALTER TABLE accounts DROP COLUMN display_name;

The exact syntax and locking behavior depend on the database engine, version, table size, indexes, and workload. Test the generated migration against a production-like copy before using it in production.

What to double-check

Preflight checklist

  • Dependency inventory: Identify application versions, workers, reporting queries, ETL jobs, admin tools, and third-party integrations that access the affected object.
  • Migration cost: Estimate row count, index size, expected duration, lock behavior, disk growth, and transaction-log or WAL impact.
  • Deployment order: Ensure the database expands before code that depends on the new structure. Remove old structures only after code cleanup.
  • Backfill design: Use bounded batches, a stable key, checkpoints, throttling, retries, and a way to resume without duplicating harmful side effects.
  • Observability: Define success signals before starting: error rate, p95 latency, lock waits, replication lag, queue depth, backfill rate, and data validation results.
  • Capacity: Leave room for temporary disk use and additional read or write load. A backfill that is correct but overwhelms replicas can still cause an outage.
  • Recovery: Confirm backups, point-in-time recovery settings, restore procedures, and the person authorized to stop the rollout.

CI/CD gate example

A deployment pipeline should separate schema preparation, application rollout, backfill, and cleanup. A simplified job sequence might look like this:

jobs:
  expand:
    steps:
      - run: ./migrate up --phase expand
      - run: ./checks/schema-compatible.sh

  deploy:
    needs: expand
    steps:
      - run: ./deploy --version "$APP_VERSION"
      - run: ./checks/health-and-error-rate.sh

  backfill:
    needs: deploy
    steps:
      - run: ./backfill --batch-size 500 --max-runtime 20m
      - run: ./checks/validate-migrated-data.sh

  contract:
    needs: backfill
    if: ${{ inputs.approve_contract == 'true' }}
    steps:
      - run: ./migrate up --phase contract

The commands are placeholders, but the separation is important. Require an explicit approval for destructive cleanup, and do not make the contract phase an automatic consequence of a partially completed backfill.

Rollback checklist

  • Stop the backfill or rollout if error rates, lock waits, or replication lag exceed agreed thresholds.
  • Disable the feature flag and route reads back to the known-good representation where compatibility code supports it.
  • Keep dual writes enabled until the incident is understood if reverting the application would otherwise create divergence.
  • Do not drop newly added columns or tables simply to make the schema resemble the previous release.
  • Compare old and new values before deciding whether a forward fix, reconciliation job, or database restore is appropriate.
  • Record the last successful checkpoint so a paused backfill can resume safely.

Common mistakes

Combining expand and contract in one release: Dropping a column in the same deployment that stops using it creates a narrow but real compatibility failure window. Separate the changes.

Running one massive backfill transaction: Large transactions can hold resources for too long, create replication pressure, and make retries expensive. Prefer small batches with checkpoints.

Assuming a default is free: Adding a default or changing a type can have different operational effects across database engines and versions. Verify behavior in a representative environment.

Ignoring non-application consumers: A dashboard query, data export, worker, or maintenance script may continue using the old schema after the main service is updated.

Making dual writes non-idempotent: Retries are normal during deployments and backfills. Design writes so repeating a batch does not create duplicate records or conflicting values.

Using rollback as a substitute for compatibility: Rolling back binaries cannot restore deleted data or undo an irreversible transformation. Design the migration so the previous application version can continue operating.

Monitoring only application errors: A migration can increase lock waits, replication lag, or database saturation before visible HTTP errors appear. Monitor database signals alongside service health.

When to revisit

Revisit this checklist before every production schema change that affects a busy table, a shared database, a high-volume index, or data used by more than one service. It is especially useful before seasonal planning cycles, framework or ORM upgrades, changes to deployment tooling, and major application releases.

Update the procedure when your database engine, version, migration tool, cloud platform, connection pool, or replication topology changes. Lock behavior and online-operation support can differ between versions, so do not carry forward assumptions from an older environment without testing them.

After each migration, capture the actual duration, peak resource use, batch size, validation results, alerts, and operator decisions. Turn those observations into thresholds for the next run. Before starting the next change, confirm that the runbook names an owner, a stop condition, a validation query, a recovery path, and the exact point at which cleanup is safe.

The practical sequence is simple: expand safely, deploy compatible code, migrate and validate data, observe the system, then contract only after dependencies are gone. Treating each phase as independently observable and interruptible is what makes a zero-downtime database migration a repeatable deployment practice rather than a one-time gamble.

Related Topics

#Database Migrations#Zero Downtime#Deployments#CI/CD#SQL#Rollback
P

Plkdt Labs

Editorial Engineering Team

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.