Expand-and-Contract Migrations

Learning outcomes

Expand-and-contract turns a risky one-step schema change into a sequence of compatible states. First expand the schema so old and new forms coexist. Then migrate applications and data. Finally contract by removing the obsolete structure.

The three phases

EXPAND  add new structureMIGRATE  new code + backfill + validationCONTRACT  remove old structure

Example: rename customer_id to account_id

Unsafe:

ALTER TABLE work_orderRENAME COLUMN customer_id TO account_id;

This assumes every consumer changes at the same instant.

Expand

ALTER TABLE work_orderADD COLUMN account_id bigint NULL;

Add index/FK as appropriate, initially in a way that does not break old writes.

Migrate writers

New code writes both:

customer_id = 17account_id  = 17

or writes the new field while a temporary bridge synchronizes the old one.

Backfill

UPDATE work_orderSET account_id = customer_idWHERE account_id IS NULL;

For large tables, do this in controlled batches rather than one giant transaction.

Migrate readers

New code reads account_id, optionally falling back to customer_id during the transition.

Validate

SELECT COUNT(*)FROM work_orderWHERE account_id IS DISTINCT FROM customer_id;

Expected result: zero before contraction.

Contract

After old applications are gone and data is validated:

ALTER TABLE work_orderDROP COLUMN customer_id;

Exit criteria are essential

• no old application version remains;
• no legacy query/report uses old field;
• backfill complete;
• consistency check passes;
• new constraint validated;
• rollback strategy reviewed.

Splitting a table

Suppose Asset currently stores owner fields directly but new design needs history:

Asset.current_customer_id

target:

AssetOwnership(asset_id, customer_id, valid_from, valid_to)

Expand for table split

1. create AssetOwnership;
2. keep current_customer_id;
3. deploy writes that create/update ownership history;
4. backfill current ownership rows;
5. migrate readers;
6. decide whether current_customer_id remains as derived shortcut or is removed.

Changing one-to-one into one-to-many

Old:

WorkOrder.primary_technician_id

New:

WorkOrderAssignment(  work_order_id,  technician_id,  role_code)

Expand by adding association rows while keeping old field until all readers/writers move.

Changing data type

Changing text to bigint in-place may lock or fail on bad data. Expand:

old_code_textnew_code_bigint

then transform, validate, migrate reads/writes, and contract.

Changing units

Rename alone is not enough if semantics change:

pressure_psi -> pressure_kpa

The migration needs a deterministic conversion and validation, and old/new code must not interpret the same number using different units.

Changing enum/reference semantics

If one status splits into two:

closed -> completed / cancelled

backfill requires a business rule for classifying historical rows, not merely DDL.

Dual-write risks

Dual writes can fail partially. Protect them by:

• same database transaction;
• outbox/idempotent projection when cross-system;
• reconciliation queries;
• short-lived transition periods.

Migration flags

Feature flags can control:

read_oldread_newwrite_oldwrite_new

This supports gradual rollout, but too many combinations can become hard to reason about. Keep migration states explicit.

WorkshopHub migration plan

Practice: table split

Summary and next lesson

Expand-and-contract replaces synchronized schema cutovers with compatible intermediate states. The next lesson focuses on the operationally hardest part of many migrations: backfilling and validating large production tables without downtime.

References

• Pramod Sadalage and Scott Ambler, Refactoring Databases.
• Martin Fowler, parallel change and evolutionary architecture writings.
• Database vendor documentation on online schema changes.