Zero-Downtime Database Migrations in CI/CD: Expand-and-Contract Patterns

Overview

A database migration becomes risky when an application release and a schema change assume the same moment in time. In a rolling deployment, blue-green release, canary rollout, or manually coordinated deployment, old and new application instances may run simultaneously. A schema change that is safe for only one version can therefore cause errors, data loss, or an emergency rollback.

The expand-and-contract migration pattern separates a breaking change into compatible steps:

1. Expand: Add the new table, column, index, or other structure without removing the old one.
2. Transition: Deploy application code that can work with both structures, then gradually move reads and writes to the new representation.
3. Contract: After old code, old jobs, and old clients no longer depend on the previous structure, remove it in a later change.

This approach does not guarantee that a migration will be invisible. Large table rewrites, locks, replication lag, disk pressure, and inefficient backfills can still affect users. Its purpose is to reduce coupling between application availability and schema evolution, giving the team more control over timing and rollback.

Before changing production, define the compatibility window: the period during which the old and new application versions may coexist. Every migration step should be safe throughout that window, not merely safe when the deployment is complete.

Checklist by scenario

Adding a nullable column or optional field

• Confirm that adding the column does not require a long table rewrite or blocking lock on the target database.
• Deploy the schema addition before deploying code that reads or writes the field.
• Keep the field optional in the first application version. Do not require a value until existing rows and all writers are accounted for.
• Decide whether the application, a background worker, or a controlled backfill will populate existing records.
• Measure backfill progress, error rate, database load, and replication or synchronization lag.

Renaming a column or changing a data representation

• Add the replacement column rather than renaming the original in the first step.
• Deploy code that writes both columns, using one clearly defined source of truth.
• Backfill the replacement column in batches that can pause and resume.
• Update reads to prefer the new column, with a temporary fallback only if the fallback behavior is understood and monitored.
• Stop old writers, verify that the old column is no longer changing, and remove the dual-write path in a separate release.
• Drop the old column only after the compatibility window has closed and a restore or recovery path has been reviewed.

Splitting one table or field into several structures

• Create the new tables and constraints without requiring the existing application to use them immediately.
• Define how identifiers, ownership, uniqueness, and missing values map from the old structure to the new one.
• Backfill parent and child records in an order that preserves referential integrity.
• Deploy a compatibility layer or application code that can read the old and new forms during the transition.
• Compare record counts, checksums, representative queries, and business-level totals before switching reads.

Adding an index or constraint

• Check whether the database supports an online or concurrent form of the operation and confirm the operational trade-offs for your engine.
• Test the operation against production-sized data, not only a small development database.
• Check for duplicate or invalid data before adding a uniqueness or foreign-key constraint.
• Set an explicit maintenance window, timeout, or cancellation procedure for operations that may wait on locks.
• Verify query plans and write performance after the index or constraint is active.

Removing a column, table, or compatibility path

• Search application code, scheduled jobs, reporting queries, data exports, and external consumers for references.
• Confirm that all deployed application versions are past the point of using the old structure.
• Use schema or query telemetry where available to detect unexpected reads and writes.
• Take the removal as a separate deployment from the code change that stops using the structure.
• Document recovery options; a rollback of application code may not be possible after irreversible schema removal.

Pipeline gates for every migration

1. Review gate: Require a migration plan, compatibility statement, expected lock behavior, and rollback decision.
2. Test gate: Run migrations from the current schema and from representative prior versions against realistic data volumes.
3. Static gate: Check migration ordering, destructive statements, transaction assumptions, and unsafe defaults according to your database and migration tool.
4. Staging gate: Exercise the complete expand, deploy, backfill, and contract sequence in an environment that resembles production.
5. Release gate: Require backups or recovery verification, health checks, migration observability, and an owner available during rollout.
6. Post-release gate: Keep the migration and application metrics under observation before starting cleanup.

The exact implementation differs between GitHub Actions, GitLab CI, Jenkins, and other systems, but the gates should remain recognizable. A useful pipeline treats schema changes as release artifacts with their own tests and approval points, rather than hiding them inside an application startup command.

What to double-check

Locking and transaction behavior

Determine which locks each statement takes, how long they may be held, and what happens when they conflict with active traffic. A migration that is quick on an empty database can behave differently on a busy production system. Configure sensible timeouts where supported, and make sure a cancelled operation leaves the database in a known state.

Backfill design

A backfill should be resumable, observable, and bounded. Prefer batches based on stable keys or another predictable boundary. Avoid one uninterruptible transaction for a large dataset unless the database team has specifically validated that approach. Record progress, handle retries safely, and define what happens when a row changes during the backfill.

Read and write compatibility

For each release, write down which application versions can read and write which schema versions. Pay special attention to defaults, null handling, enum values, serialized data, timestamp precision, and case sensitivity. A new writer may introduce values that an older reader cannot understand even when the columns themselves still exist.

Rollback boundaries

Application rollback and database rollback are not always symmetrical. An additive migration may support an application rollback, while a destructive migration may not. Use this decision table during release planning:

Observability and ownership

Track migration duration, lock waits, database errors, query latency, write failures, backfill progress, and application health. Logs should identify the migration version and deployment revision. Pair these signals with an explicit owner who can pause the pipeline or disable a feature flag. Teams building broader release observability can also review the OpenTelemetry setup guide for instrumentation planning.

Common mistakes

• Combining expand and contract in one release: Adding and immediately removing the old structure defeats compatibility with rolling deployments and delayed workers.
• Assuming a migration is safe because it is backward-compatible at the code level: Database locks, disk usage, replication, and query plans still require testing.
• Using application startup for production migrations: Multiple instances can race, restart loops can repeat failures, and traffic may become coupled to migration completion.
• Running an unbounded backfill: A process that consumes all available database capacity can turn a routine change into an availability incident.
• Forgetting non-application clients: Analytics jobs, scripts, integrations, and ad hoc reports may depend on the old schema.
• Relying on a backup without testing recovery: A backup is part of a recovery plan only when its restoration process and expected recovery time are understood.
• Leaving compatibility code permanently: Temporary dual reads and writes increase complexity. Assign an owner and a removal condition when the transition begins.

When to revisit

Revisit this checklist before every production schema change, especially when a release includes a rename, type conversion, constraint, large backfill, or removal. Repeat the review before seasonal planning cycles or periods of expected traffic change, when database size and operational risk may differ from the last migration.

Update the implementation details when your database engine, migration framework, CI/CD platform, deployment strategy, or recovery process changes. Commands and online-operation capabilities are tool- and version-dependent, so verify them against the supported environment instead of copying an older example.

For the next migration, create a short release record containing the compatibility matrix, migration order, test evidence, owner, dashboards, pause conditions, and rollback boundary. Run the expand step first, deploy compatibility-aware code, observe the transition, and schedule contract cleanup only after usage evidence supports it. Treat that record as a reusable review artifact for future releases and incident retrospectives.