Database migration safety
Leave room for the migration's temporary data
A table change can consume storage, I/O and replication capacity while both old and new representations exist. Estimate the peak, not only the final size.
In this article
Count overlapping representations
A migration that adds a new column or table may retain the old data throughout a rollback window. Indexes, temporary work and logs can add further demand depending on the database operation.
Estimate peak storage with the actual command and platform behaviour in mind. Do not assume the final schema size describes the space required to reach it.
Include replicas and backup processes in the capacity review. A change that fits on the primary may still increase transfer or retention pressure elsewhere.
Measure a representative slice
Run the migration against a realistic dataset and observe storage growth, write volume and elapsed time. A small sample is useful for understanding work per row, but scaling may not remain linear when indexes, caches or I/O limits change.
Use measurements to form a range rather than a falsely precise completion time. Record the assumptions that drive it, including batch size and concurrent application load.
For an illustrative backfill of 12 million rows at a sustained 1,000 rows per second, processing alone takes about 3 hours and 20 minutes. Pauses, retries and final verification extend that window. The rate must come from rehearsal, not a generic benchmark.
Choose a batch size for service health
Large batches can reduce per-batch overhead but hold resources longer and make retries more expensive. Small batches improve control but may spend more time on coordination.
Adjust using application latency, lock waits and replication lag. A backfill that maximises its own throughput can degrade the service it is meant to improve.
Provide a pause mechanism and persist progress. Operators should be able to reduce pressure without discarding completed work or guessing where to resume.
Budget for the overlap period
Keep enough headroom until old structures can be removed safely. Pressure to reclaim space immediately can force a destructive step before rollback and consumer verification are complete.
Compare the cost of temporary capacity with the risk and downtime of a compressed migration. The cheapest monthly infrastructure figure may not produce the lowest total delivery cost.
Finish the plan with cleanup ownership and a verification date. Temporary copies that have no removal condition often become permanent storage, while premature cleanup can erase the only practical recovery path.
Primary sources
PostgreSQL: ALTER TABLEPostgreSQL: progress reportingReferences checked 11 September 2026.