Database Migrations That Survive Mixed App Versions

Zero-downtime database migrations are compatibility projects. The difficult period is usually the overlap: old application instances still run while new instances begin using a changed schema. A migration that works perfectly with the final version can still break that transition.

I prefer to promise a tested migration sequence with bounded operational risk rather than guaranteed zero downtime. That wording forces attention onto lock acquisition, backfill load, reader behavior, and rollback. A fast schema command alone proves very little about the whole release.

Begin with a compatibility matrix

Suppose a customer table currently has display_name, and the product needs separate given_name and family_name fields. Names cannot reliably be split by taking the first and last word. For this hypothetical migration, assume the new fields are initially populated from explicitly collected customer input, with unresolved historical records left identifiable.

I would write this matrix before a migration file:

The schema change is easy to describe. The compatibility policy is where the product decisions live. A background importer that still updates only display_name belongs in the matrix too.

Keep display_name authoritative for displayed names while any legacy writer remains. Otherwise, an old instance can change it after structured fields were populated, and a new reader that prefers those fields will silently show a stale name. Track the row revision associated with verified structured input. Before switching readers, retire legacy writers and reconfirm any structured values whose source revision is stale; unresolved records must retain the documented fallback. Do not attempt to repair this conflict by automatically splitting the display name.

Expand without removing the old contract

An illustrative expansion is:

ALTER TABLE customers
  ADD COLUMN given_name text,
  ADD COLUMN family_name text;

Do not infer that a short command cannot wait on locks. PostgreSQL documents lock levels and operation-specific behavior in ALTER TABLE. Review the deployed version and set an operational limit on how long the migration may wait.

For my proposed rollout, expansion ships separately from the application switch. Verify the columns exist, the old application still works, and the new version handles unresolved records. If the rollout stops here, the existing product should remain usable.

This separation is valuable even if your deployment tool can combine everything into one job. It produces a concrete checkpoint at which reverting the application remains straightforward.

Backfill as a resumable workload

A backfill needs a progress marker, a bounded batch size, and a policy for rows changed concurrently. For this example, the job might consume a verified import keyed by customer ID and only fill new fields that remain unset.

The important condition is in the update, not merely in an earlier read. A worker that checks for an empty field, waits, and then writes can overwrite a user's intervening change. Express both field eligibility and the expected source revision when updating, then inspect the affected-row count. A revision mismatch returns the record for reconfirmation rather than applying stale input.

I would log counts for attempted, updated, skipped, and invalid records. Keep personal values out of routine logs. Run a small initial batch, observe normal application latency and replication behavior, then increase throughput cautiously. Batch size is an operational control, not a magic constant copied from another project.

Restart the backfill during rehearsal. It should continue from recorded progress and handle already-completed rows without corrupting them. If restarting requires deleting a table or guessing which half ran, the workflow is not ready for production.

Treat constraints and indexes as their own steps

PostgreSQL provides ways to add some constraints without immediately validating all existing rows, then validate them later. The exact syntax and lock behavior are operation-dependent; consult the same ALTER TABLE reference before choosing the sequence.

For an index needed by new reads, concurrent creation has different operational rules from a normal build, including restrictions on running inside a transaction block and cleanup considerations after failure. Those caveats are documented in CREATE INDEX.

In my release plan, each step gets its own success check. “The deployment process exited” is weaker than “the intended constraint is validated” or “the intended index is valid and the new query uses an acceptable plan.” Do not bundle unrelated schema cleanup into this migration.

Put the rollback boundary on the calendar

Before removing display_name, verify that all application versions, workers, exports, and operational tools have stopped depending on it. Then wait through the agreed rollback window. Only afterward should a separate release remove the field.

A rollback to the old binary after destructive schema removal is not a rollback plan. It is a request to reconstruct compatibility under incident pressure. If restoring an older application also requires a data conversion, document and rehearse that conversion before approving removal.

For this example, I would retain the old field longer than the minimum technically possible. The small storage cost buys a clearer operational exit while the team validates name rendering and downstream exports.

Use query-plan diagnosis to evaluate the new access path and the connection budget to plan backfill concurrency. Those checks make migration load part of the rollout decision.

The migration is complete when the final schema is deployed, the historical data policy is satisfied, and the team can explain which earlier versions are still recoverable. Until then, it is an active release sequence, not a finished SQL file.