Expand/Contract Pattern

The expand/contract pattern (also called parallel change) ships additive schema changes while old and new application versions run, then removes obsolete schema only after traffic fully cuts over.

Recipe

Phase 1 EXPAND  - migration adds new column/table (nullable or unused)
Phase 2 DEPLOY  - app v2 writes to both old and new
Phase 3 BACKFILL - job fills new column for historical rows
Phase 4 DEPLOY  - app v3 reads new only
Phase 5 CONTRACT - migration drops old column

When to reach for this: Renaming columns, changing types, splitting tables, or any breaking DDL on tables under active traffic.

Working Example

Rename full_name to display_name without downtime:

-- Migration V10 EXPAND
ALTER TABLE app.users ADD COLUMN display_name text;

-- App v2 dual-write (application code)
INSERT INTO app.users (full_name, display_name, ...)
VALUES ($1, $1, ...);
UPDATE app.users SET display_name = full_name WHERE display_name IS NULL;

-- Migration V11 BACKFILL (batch in application or SQL job)
UPDATE app.users
SET display_name = full_name
WHERE display_name IS NULL AND id BETWEEN $1 AND $2;

-- Migration V12 CONTRACT (after app v3 ships)
ALTER TABLE app.users DROP COLUMN full_name;
ALTER TABLE app.users ALTER COLUMN display_name SET NOT NULL;

What this demonstrates:

• Old column lives through at least two deploy cycles
• Backfill can be chunked by primary key ranges
• Contract phase is irreversible without restore - gate on metrics

Deep Dive

Expand Techniques

Contract Safety Checklist

• No production code references old object (grep + deploy graph)
• No BI dashboards query old column
• Replication/subscribers include new shape
• Backup taken before DROP
• Contract migration in low-traffic window (DROP still locks briefly)

Feature Flags

-- Optional: track migration phase in config table
INSERT INTO app.feature_flags (key, enabled) VALUES ('use_display_name', true);

• Coordinate schema phase with app feature flags.
• Roll back app flag without contract migration if expand-only so far.

Gotchas

• Contract before backfill complete - NOT NULL fails or app reads NULLs. Fix: SELECT count(*) WHERE new_col IS NULL gate in deploy pipeline.
• Dual-write forgotten in one code path - Admin tool still writes old column only. Fix: Code search all writers; integration tests assert both columns.
• DROP in same release as expand - Downtime or deploy ordering failure. Fix: Minimum one release between expand and contract.
• View depends on old column - Contract migration fails or breaks reports. Fix: Update views in expand migration to use COALESCE bridge.
• ORM schema cache - Old pods crash after contract. Fix: Rolling deploy ensures zero old pods before contract job runs.

636f64656775696465732e696f7c6367696f313037317c323032363037

Alternatives

FAQs

Related

• Zero-Downtime DDL - concurrent indexes and NOT VALID
• Large Table Migrations - chunked backfill
• Migrations Basics - versioned SQL discipline

External Related Links

• Current Version Documentation - Full manual for the latest stable release.
• PostgreSQL Wiki - Community FAQ, howtos, and technical notes.
• Stack Overflow – PostgreSQL Tag - Q&A for practical PostgreSQL problems.
• Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
• PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.