Executing an ALTER TABLE statement with a column rename or default value modification on a table containing hundreds of millions of rows can trigger heavy ACCESS EXCLUSIVE locks. Under active concurrent query loads, these locks queue incoming transactions, resulting in connection pool exhaustion and cascading system timeouts.
The Expand Phase
Instead of modifying existing columns directly, the schema transition begins by introducing new columns alongside the old fields as nullable attributes. Application code is deployed to write to both the old and new columns simultaneously while continuing to read from the old column. This guarantees zero breaking changes across distributed microservice deployments.
Backfilling Historical Data
Once dual-writing is verified, a background worker script iterates over primary key ranges to populate the new column for historical records. By processing batches in small increments (e.g., 2,000 records per transaction block) with explicit sleep intervals between chunks, I/O saturation and replication lag are kept strictly within safe operating bounds.
The Contract Phase
When the background backfill completes and data validation confirms zero drift between column pairs, a second application release shifts read traffic entirely to the new column. Finally, in an off-peak maintenance phase, the deprecated old column is safely removed, completing the cycle without downtime.