PostgreSQL migrations across application versions
Expand the schema so old and new application versions can coexist, migrate data under a concurrency-safe rule, then remove the old representation only after rollback and consumer requirements are resolved. Budget database locks explicitly; “zero downtime” is a tested operating objective, not a property of every ALTER TABLE command.
Inside this article 6 sections +
A rolling application deployment creates a period when old and new code use the same database. A migration that works only after every process upgrades can fail during that overlap or make rollback impossible. Expand-contract design treats coexistence as a normal release state with its own tests.
Consider replacing a free-form customer region field with a normalized region code. The change affects writes, historical rows, search filters, and possibly external reporting. Renaming the column and deploying new code together is not enough when workers, scheduled jobs, and old application instances remain active.
List every reader and writer
Identify web services, workers, maintenance scripts, reporting jobs, and integrations that read or write the old field. A deployment inventory is more useful than assuming the main API is the only consumer. Record which version of each component understands the new representation.
Add the new column in a form old code can tolerate, then deploy code that can operate during the transition. Define one authoritative write rule. New code may write both representations temporarily, but old writers still need a strategy so they cannot leave the new field stale indefinitely.
| Phase | Application behavior | Database promise |
|---|---|---|
| Expand | Old application still works | New nullable representation exists |
| Compatible release | Readers tolerate both; writers follow the transition rule | No assumption that historical rows are complete |
| Backfill and verify | Concurrent writes remain consistent | Eligible historical rows gain normalized values |
| Read switch | New representation becomes authoritative | Validation supports the new invariant |
| Contract | No supported consumer needs the old field | Old representation can be removed separately |
Make dual writing a controlled temporary state
If the application writes both fields, keep the update atomic in the same database transaction. Define how the old string maps to the new code and how ambiguous values are handled. A guessed mapping can make a technically complete backfill commercially incorrect.
Old writers complicate the transition. Options include upgrading every writer before backfill, temporarily maintaining consistency through a carefully tested database mechanism, or recording changes for reconciliation. Choose one explicitly. “The new app writes both” does not explain what happens when an old worker updates only the legacy field.
Track unmatched values and conflicting writes as a visible worklist. Do not mark the migration complete while silently assigning a default code to unknown data unless that default has a defined business meaning. The migration needs an exception policy as well as a happy-path transformation.
Backfill without overwriting newer work
Process bounded batches with a stable cursor and a persisted progress record. Avoid one enormous transaction that holds resources for the entire table. Make the operation resumable and safe to repeat, then throttle according to database load rather than running at maximum speed by default.
Illustrative guarded backfill protocol:
read a bounded batch with legacy value and row revision
compute the normalized region code
update only when the expected revision still matches
and the new field is still eligible for backfill
record conflicts for reread or manual resolution
commit the batch and its progress evidence
The revision guard prevents a stale transformation from overwriting a concurrent customer edit. A simple “new field is null” condition may be enough for some designs, but only if the writer protocol makes that condition meaningful. Test the exact race rather than assuming a generic backfill loop is safe.
Keep progress accounting separate from correctness checks. Reaching the final primary key does not prove every eligible row migrated; conflicts and newly inserted rows may remain. Run explicit completeness and consistency queries using the transition rule, and record how they handle rows that legitimately lack a region.
Budget locks and validation work
Review each PostgreSQL statement for lock mode, table scan, rewrite, and version-specific behavior. Set an appropriate lock wait budget so a migration does not queue behind a long transaction indefinitely and then surprise the application. Observe blocking sessions and retry under a controlled policy.
Where supported, add a constraint without validating all existing rows immediately, then validate it in a separate step after the backfill. The new-write behavior and validation lock characteristics depend on the constraint type and statement. Test the actual SQL against the deployed PostgreSQL version.
For an index needed by the new query path, consider concurrent construction when appropriate. It has operational caveats: it cannot run inside an ordinary transaction block, may require more work, and can leave an invalid index after failure. The pipeline must inspect completion and clean up deliberately rather than assuming a failed command left no database object.
Prove the new read path before removing the old one
Compare old and new query results on representative accounts and known edge cases. A normalized value can change sorting, filtering, and null behavior even when every row has been populated. Verify the product behavior, not just equality of row counts.
Deploy the new read path with a clear reversal mechanism while the old representation remains valid. Record which writers and consumers still require the legacy field. Wait through relevant scheduled-job cycles and operational rollback requirements before considering removal.
Rollback is asymmetric after new data begins using a representation old code cannot express. If a new region code has no legacy equivalent, restoring the old binary may be unsafe even while the old column remains. Define that boundary in the release record and test it explicitly.
Remove compatibility in a separate change
Retire old writes, compatibility code, and the legacy column only after usage evidence and rollback policy permit it. Make the contract-removal release reviewable on its own. A successful backfill is not authorization to remove every old field immediately.
The shared database guide establishes ownership, and migration rollback guidance explains reversible boundaries. Database-backed application implementation should make every intermediate state safe enough to operate, not merely make the final schema look clean.