Expand and Contract Database Changes for Safe Deployments

A database schema can change in milliseconds while an application fleet takes minutes or hours to converge on a new version. During that interval, old and new application instances may use the same database at the same time.

That overlap turns an ordinary schema edit into a compatibility problem. Renaming a column in one migration, for example, can break old instances immediately even when the new application code is correct.

Expand and contract handles this transition as a sequence. First, expand the schema with a compatible addition. Next, move data and application traffic to the new representation. Finally, contract the schema by removing the obsolete representation after nothing depends on it.

The central rule is: do not require every participant to switch at the same instant.

A direct rename creates a coordination point

Assume an orders table contains this column:

customer_note TEXT

A new domain model calls the field delivery_note. A direct migration seems attractive:

ALTER TABLE orders
RENAME COLUMN customer_note TO delivery_note;

The schema is tidy immediately, but any old application instance still issuing this query now fails:

SELECT id, customer_note
FROM orders
WHERE id = $1;

A rolling deployment makes that failure likely. So can a rollback: after the database migration, reverting application binaries may restore code that expects the old column.

The schema and application release have become one coordinated event. That coupling makes routine deployment mechanics part of correctness.

Expand and contract replaces the coordinated event with compatible intermediate states.

Phase one: expand without removing existing behavior

Start by adding the destination column:

ALTER TABLE orders
ADD COLUMN delivery_note TEXT;

At this point:

  • old code can continue reading and writing customer_note;
  • new code can recognize delivery_note;
  • the database supports both representations.

The addition alone does not keep values synchronized. The next design decision is to establish a safe transition for writes.

A common application-level sequence is:

  1. deploy code that still reads the old column but writes both columns;
  2. backfill the new column for existing rows;
  3. switch reads to the new column while dual writes continue;
  4. stop writing the old column;
  5. remove the old column in a later release.

Each step leaves a state that can operate for longer than expected. That property matters because deployments pause, instances restart, queues drain slowly, and rollback decisions arrive at inconvenient times.

Dual writes need an explicit authority rule

Writing two columns sounds simple:

UPDATE orders
SET customer_note = $1,
    delivery_note = $1
WHERE id = $2;

But migration logic becomes unsafe if different application versions can update different columns independently.

Suppose old code writes only customer_note while new code writes both. A new instance can write "side door", followed by an old instance writing "front desk" only to the old column. The columns now disagree.

The transition therefore needs an authority model. Options include temporarily routing all relevant writes through compatible code, using a database trigger for synchronization, or keeping reads on the old representation until old writers have disappeared.

The correct mechanism depends on the system, but the invariant should be precise:

Before reads switch to the new representation, every accepted write that can affect the value must also update that representation.

Without such an invariant, a backfill can complete successfully and still be followed by fresh divergence.

Backfill as an online data operation

Existing rows need their values copied:

UPDATE orders
SET delivery_note = customer_note
WHERE delivery_note IS NULL;

For a small table, one statement may be adequate. For a large production table, a single transaction can create heavy write load, long lock retention, replication lag, or a large transaction log burst.

Batching gives operators more control:

UPDATE orders
SET delivery_note = customer_note
WHERE id > $1
  AND id <= $2
  AND delivery_note IS NULL;

Process bounded key ranges, record progress, and make each batch safe to retry. The delivery_note IS NULL predicate also avoids rewriting rows already migrated.

Yet null can be a valid business value. If so, null cannot distinguish “not migrated” from “migrated to null.” Use a separate migration marker, derive completeness from another stable condition, or design a backfill statement whose repeated execution is harmless.

Treat the backfill as production workload, not as a one-time script exempt from operational standards. Measure its query latency, lock behavior, replication effects, error rate, and progress.

Switch reads only after proving readiness

Once compatible writes are established and historical rows are migrated, new code can read delivery_note.

Do not base this step only on elapsed time. Establish evidence that the destination is ready. Useful checks include:

SELECT COUNT(*)
FROM orders
WHERE customer_note IS DISTINCT FROM delivery_note;

The exact query depends on database semantics and the chosen authority rule. On a large table, a full comparison may be too expensive; sampled checks, migration counters, partition-level verification, or offline reconciliation may fit better.

The goal is not a particular SQL statement. The goal is a measurable condition that supports the read switch.

After new reads are active, keep the old representation for a compatibility window. This creates room to detect faults before destructive cleanup.

Contraction is a separate release decision

Removing the old column is the irreversible-looking part:

ALTER TABLE orders
DROP COLUMN customer_note;

Schedule it only after confirming that no supported application version, background worker, report, integration, migration job, or operational query still needs the column.

Application traffic is not the entire dependency graph. Schema consumers can include:

  • asynchronous workers deployed on another cadence;
  • analytics and reporting jobs;
  • change-data-capture pipelines;
  • manual operational tooling;
  • database views and stored routines;
  • external services with direct database access.

A repository search helps, but runtime evidence is stronger when hidden consumers are possible.

Once the old structure is removed, rollback boundaries change. Reverting to code that expects customer_note is no longer safe unless the rollback procedure also restores compatible schema state.

Constraints need staged treatment too

Expand and contract applies to more than column names. Consider adding a required field.

This direct change can fail against existing data:

ALTER TABLE accounts
ADD COLUMN region TEXT NOT NULL;

A staged version starts permissively:

ALTER TABLE accounts
ADD COLUMN region TEXT;

Then compatible code writes region, existing rows are backfilled, completeness is verified, and the constraint is added after the data satisfies it:

ALTER TABLE accounts
ALTER COLUMN region SET NOT NULL;

Some database engines provide lower-impact methods for validating constraints or building indexes. Use engine-specific facilities when table size and availability requirements make locking behavior important.

The pattern stays the same: introduce capability, populate and adopt it, verify the invariant, then tighten or remove compatibility structures.

Index replacement follows the same shape

Suppose a query needs a different index definition. Dropping the old index before the replacement is usable can create a performance cliff.

A safer sequence is:

create replacement index
        |
        v
confirm planner usage and production behavior
        |
        v
remove obsolete index

For engines that support online or concurrent index creation, those features can reduce blocking, but they do not remove the need to observe resource consumption and failure states.

Compatibility includes performance characteristics. A schema state that returns correct results but overloads the database is not operationally safe.

Keep transition states understandable

A common cost of staged migration is temporary complexity. Two columns, dual-write code, feature flags, backfill jobs, and compatibility branches can remain long after the transition.

Make the temporary state visible. Record:

  • the old and new representations;
  • the authority rule during migration;
  • the condition for switching reads;
  • the condition for stopping old writes;
  • the evidence required before cleanup;
  • the rollback boundary at each stage.

A migration is not complete when new code works. It is complete when obsolete compatibility machinery has been removed and the remaining model is coherent.

Design every phase for interruption

Production changes rarely follow an ideal timeline. A deployment can halt at 40 percent, a backfill can stop overnight, or a release can be rolled back after the read switch.

Evaluate each phase with three questions:

  1. Can old and new application versions coexist in this state?
  2. Can the current operation be retried without corrupting data?
  3. Is rollback still compatible with the current schema?

If any answer is no, the phase contains a coordination hazard that deserves explicit handling.

This approach also discourages migrations that combine unrelated destructive edits. Smaller transitions produce clearer invariants and narrower recovery decisions.

A practical sequence for a column replacement

For a representative replacement from customer_note to delivery_note, a robust plan can look like this:

Release A
  add delivery_note
  deploy compatible write path

Backfill
  copy historical values in bounded batches
  reconcile mismatches

Release B
  read delivery_note
  continue compatibility writes if rollback needs them

Observation window
  confirm old readers and writers are absent
  confirm data consistency

Release C
  stop compatibility code
  drop customer_note

Cleanup
  remove migration flags, temporary metrics, and backfill machinery

The exact number of releases is not important. The useful property is that each boundary has a clear compatibility contract.

Cases that need extra care

Expand and contract reduces coordination risk, but it is not a universal substitute for database-specific planning.

Large table rewrites, type conversions, primary-key changes, partition changes, and operations that acquire strong locks can have substantial engine-specific effects. A syntactically simple ALTER TABLE may behave very differently across database versions and storage engines.

For those changes, inspect the database documentation for the exact engine and version, test against production-scale data, and establish lock and resource expectations before execution.

Cross-service schema ownership also deserves caution. If many independently deployed services directly share tables, staged compatibility can make a change possible, but it does not repair unclear ownership. The migration may expose a broader architectural boundary problem.

The durable idea

Safe schema evolution is less about clever migration syntax and more about controlling compatibility over time.

Expand first so old behavior keeps working. Move writes, data, and reads with explicit invariants. Verify the destination under real operating conditions. Contract only after obsolete dependencies are gone.

That sequence converts a synchronized cutover into several reversible or observable steps. The database can then evolve while mixed application versions continue serving traffic, and cleanup becomes a deliberate final act rather than a risky part of the initial deployment.

Software Engineering Databases Deployment Reliability