Zero-Downtime Database Migrations with Expand and Contract

A database migration can keep PostgreSQL fully online and yet trigger application downtime: renaming display_name to full_name instantly breaks running instances that continue querying the column that no longer exists.

The flaw is rarely the SQL syntax itself, but the assumption that application code, background workers, and database schemas change instantaneously. During a rolling deployment, multiple application versions run simultaneously and must share a mutually compatible schema contract.

The Expand Phase: Coexistence

In the expand phase, add full_name without removing display_name and deploy an application release capable of tolerating both structures, maintaining backward-compatible reads while orchestrating data synchronization between fields.

The critical complexity lies in write operations: if older application instances write only to display_name, the new column falls out of sync. Synchronization must bridge this window, and backfilling historical rows should execute in batches with continuous drift verification.

Read Cutover and Dependency Tracking

Once data consistency is validated, gradually transition application reads to full_name. Carefully monitor background workers, delayed jobs, and legacy processes. Completing a deployment does not guarantee that every legacy consumer has vanished.

PostgreSQL demands careful operational hygiene during DDL execution. DDL commands compete for locks with production traffic, index creation often requires CREATE INDEX CONCURRENTLY, and resilient execution plans must configure explicit lock_timeout values, active monitoring, and controlled cancellation thresholds.

The Contract Phase: Retiring Legacy Columns

In the contract phase, drop display_name and retire compatibility shims only after confirming zero legacy readers or writers exist. Acknowledge that completing this contraction closes the window for rolling back directly to previous application releases.

True zero downtime means far more than keeping the database engine reachable. It requires preserving valid contracts while divergent software versions share the same storage engine, treating the removal of compatibility as the final milestone of a migration, never the first.