Database migrations without breaking running code

A migration can pass against an empty database and still break a deployment. Production has existing rows, active connections, background workers, and application instances that do not all restart at the same moment.

The tricky part is the overlap. During a rolling deployment, one instance may use the new schema while another still expects the old one. A useful migration plan makes that period safe and gives the team a way to stop between steps.

Expand the schema before depending on it

Imagine replacing an order's free-text delivery address with a structured delivery_address_json field. The old application still reads and writes delivery_address_text.

I would begin by adding the new nullable field while preserving the old one. This gives the existing release a schema it can still use. The next application release can understand both representations, initially continuing to treat the text field as authoritative.

In that compatibility release, updates write both forms in one transaction where possible. Reads can stay on the old representation until the backfill and verification are complete. The important condition is that every writer participates, including administrative scripts and background jobs.

Decide when the old writers are gone

If an older instance updates only the text field after a row has been backfilled, the two representations can diverge. A backfill alone does not solve that race.

For this example, I would wait until all active writers run the compatibility release before doing the final backfill. If the deployment cannot guarantee that, it needs another synchronization mechanism and a reconciliation pass. That is a design decision to settle before starting the migration.

A deployment record should identify which release introduces dual writes and which release is allowed to depend exclusively on the new field. “The deploy finished” is too vague if a scheduled worker is still running last week's image.

Backfill in work you can resume

Historical addresses will include awkward inputs. Some may be incomplete, contain multiple lines, or use a format the conversion cannot safely interpret. Those rows need an explicit unresolved state rather than a fabricated structured address.

I would process the data in bounded batches, record progress, and make each batch safe to repeat. Track converted rows, unresolved rows, and disagreements between the two representations. The stopping condition should be based on those counts and sample checks, not merely on the worker reaching the end of its loop.

Batch size should be tuned against the live system's tolerance for write load and contention. A migration that saturates the database can disrupt users even if every converted row is correct.

Review the locks, not just the SQL

In PostgreSQL 18, ALTER TABLE normally acquires an ACCESS EXCLUSIVE lock unless the particular operation documents a weaker level. For supported constraints, NOT VALID can defer checking old rows, while VALIDATE CONSTRAINT later checks them using a less restrictive lock. Neither means that every schema operation is free of blocking. PostgreSQL 18 ALTER TABLE documentation.

For example, after compatible writers are deployed and missing data is resolved, a check can be introduced and validated in separate steps:

ALTER TABLE orders
  ADD CONSTRAINT orders_delivery_address_present
  CHECK (delivery_address_json IS NOT NULL) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_delivery_address_present;

This is an illustrative constraint for the proposed schema. Adding it starts enforcing the rule for new or updated rows, so the deployment order matters. Check the exact database version, expected lock duration, and operational timeout before running a migration.

Contract after the rollback window

Once the new representation is complete and verified, a later release can read it as the source of truth. Keep the old field and compatible writes for the agreed rollback period. Removing them belongs in a subsequent, deliberate change.

Rollback also needs a data story. If new features start writing information the old format cannot represent, restarting an old binary may no longer be a valid recovery plan.

The deliverable is a sequence of compatible states: add, synchronize, backfill, verify, switch, and eventually remove. Each state should be understandable to the engineer who has to pause the rollout. That is also why a reviewable change description matters.