zero gravitySOLUTIONS
← All insightsBACKEND & RELIABILITY / SCHEMA TRANSITION WALKTHROUGH

PostgreSQL migrations across application versions

QUICK ANSWER

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.

Illustrative region-field migration compatibility
PhaseApplication behaviorDatabase promise
ExpandOld application still worksNew nullable representation exists
Compatible releaseReaders tolerate both; writers follow the transition ruleNo assumption that historical rows are complete
Backfill and verifyConcurrent writes remain consistentEligible historical rows gain normalized values
Read switchNew representation becomes authoritativeValidation supports the new invariant
ContractNo supported consumer needs the old fieldOld representation can be removed separately
The database supports more than one application version during the transition. Removing the old field is a later release decision.

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.

FROM INSIGHT TO ACTIONCustom software development

Make database evolution reversible where it matters.

We can implement staged PostgreSQL changes, lock-aware execution, and evidence for retiring the old representation.

Explore software developmentDiscuss your build

Start with your goals.
We’ll work through the next step together.