database-migration-patterns

Zero-downtime database migrations in PostgreSQL use the Expand and Contract pattern to add new schema elements, backfill data in batches, then swap traffic and remove the old structure. This avoids long table locks and service disruption.

The Expand and Contract pattern expands the schema by adding new columns, contracts it by backfilling data and switching application traffic, then removes the obsolete columns. It enables safe schema evolution without breaking compatibility.

Safe NOT NULL transitions require a phased rollout: add a default value, backfill existing rows, validate constraints, and finally enforce NOT NULL. This prevents lock contention and write failures during production deployment.

A safe column rename uses Expand and Contract by adding the new column, dual-writing to both columns, backfilling historical data, switching reads to the new column, and finally dropping the old column. This ensures continuous availability.

Safe index creation avoids long locks by using concurrent indexing techniques. By building indexes in the background, the database allows continued reads and writes, preventing deployment delays and downtime on large tables.

Yes, the patterns support batch processing and data migration workflows for large tables. Using version-controlled migrations with auditable templates, you can execute batch backfills and rollback operations safely within your migration framework.