Expand/Contract Database Migrations

Origin

The expand/contract pattern (also called parallel change) was articulated by Pete Hodgson on martinfowler.com. The core idea: first expand the system so it supports both old and new simultaneously, then contract by removing the old once everything has migrated.

Applied to databases, this means schema changes are split into two releases: an additive expand migration (new columns, tables, indexes — no drops or renames) and a later contract migration that removes deprecated structures after cutover.

Reference: Hodgson, Pete. “Parallel Change”. martinfowler.com, 2014. https://martinfowler.com/bliki/ParallelChange.html

Why Separate Databases?

The blue-green schemas approach (same DB, two schemas) works well for data swaps but doesn’t cover PostgreSQL version upgrades, zone failovers, or cross-provider migrations. Using separate database instances makes all of these routine:

• PG version upgrades (e.g. 15 → 17) — green runs the new version
• Zone/region failover — green is provisioned in a different availability zone
• Cloud provider migration — green can be on a different provider entirely
• Major schema refactors — green gets the target schema from scratch

The same pipeline handles all cases. Routine becomes boring, which is the goal.

Workflow Overview

Release Phases

Replication

PostgreSQL logical replication copies row-level changes from a publication on blue to a subscription on green. The expand migration must be applied to green before replication starts, since logical replication transfers data, not DDL.

Setup (PG 10+)

-- On blue
CREATE PUBLICATION blue_pub FOR ALL TABLES;

-- On green (after expand migration)
CREATE SUBSCRIPTION blue_to_green
  CONNECTION 'host=blue-db dbname=mydb ...'
  PUBLICATION blue_pub;

PG 17+ shortcut

pg_createsubscriber converts a physical standby into a logical replica, creating publications and subscriptions without copying initial table data.

Teardown

-- After cutover
DROP SUBSCRIPTION blue_to_green;  -- on green
DROP PUBLICATION blue_pub;        -- on blue

Go Migration Tooling

Several Go tools can manage the expand and contract migrations. The key criterion is whether the tool can enforce “expand only” (no destructive operations) during phase 2.

Recommendation: Atlas

Atlas is the strongest fit because:

• Declarative diffing: define the desired end state; Atlas generates the migration
• Migration linting: catches drops and renames that would break replication
• Two-directory split: schema/ (expand-safe target) and schema-final/ (clean final state) formalises the pattern
• CI integration: atlas migrate lint can enforce “no destructive ops” in the expand phase

Atlas Schema Structure

schema/
  schema.hcl          # expand-safe target (additive only)
schema-final/
  schema.hcl          # clean final state (old stuff removed)
scripts/
  create_publication.sql
  create_subscription.sql
  wait_for_sync.sh

CI can lint schema/ to ensure it contains no destructive operations.

Key Atlas commands

# Preview expand migration
atlas schema diff \
  --from "$BLUE_DB_URL" \
  --to file://schema \
  --dev-url "docker://postgres"

# Apply expand to green
atlas schema apply \
  --url "$GREEN_DB_URL" \
  --to file://schema \
  --dev-url "docker://postgres"

# Apply contract after cutover
atlas schema apply \
  --url "$GREEN_DB_URL" \
  --to file://schema-final \
  --dev-url "docker://postgres"

Taskfile Integration

# Taskfile.yml (excerpt)
tasks:
  green:migrate-expand:
    desc: Apply additive-only schema to green
    cmds:
      - atlas schema apply
          --url "{{.GREEN_DB_URL}}"
          --to file://schema
          --dev-url "docker://postgres"

  green:replicate:
    desc: Start logical replication blue→green
    cmds:
      - psql "{{.BLUE_DB_URL}}" -f scripts/create_publication.sql
      - psql "{{.GREEN_DB_URL}}" -f scripts/create_subscription.sql

  green:wait-sync:
    desc: Wait for replication lag to reach zero
    cmds:
      - scripts/wait_for_sync.sh "{{.GREEN_DB_URL}}"

  green:cutover:
    desc: Switch feature flag to green
    cmds:
      - echo "flip feature flag / update connection string"

  green:contract:
    desc: Apply contract migrations (drop old columns/tables)
    cmds:
      - psql "{{.GREEN_DB_URL}}" -c "DROP SUBSCRIPTION blue_to_green;"
      - psql "{{.BLUE_DB_URL}}" -c "DROP PUBLICATION blue_pub;"
      - atlas schema apply
          --url "{{.GREEN_DB_URL}}"
          --to file://schema-final
          --dev-url "docker://postgres"

  deploy:
    desc: Full blue→green cutover
    cmds:
      - task: green:migrate-expand
      - task: green:replicate
      - task: green:wait-sync
      - task: green:cutover
      - task: green:contract

Comparison with Blue-Green Schemas

Caveats

References

• Hodgson, Pete. “Parallel Change”. martinfowler.com, 2014. martinfowler.com/bliki/ParallelChange.html
• Sato, Danilo. “Evolutionary Database Design”. martinfowler.com, 2016. martinfowler.com/articles/evodb.html
• Atlas documentation. atlasgo.io
• PostgreSQL Logical Replication. postgresql.org/docs/current/logical-replication.html
• PostgreSQL 17: pg_createsubscriber. postgresql.org/docs/17/app-pgcreatesubscriber.html