Map every reader and writer
Work on an isolated schema with representative sanitized data. You need current and next application versions, knowledge of your migration framework, a reviewed backup strategy and visibility into long transactions. Include workers, scheduled jobs, reports and older application instances. An API may update quickly while a long-running worker still writes the old column.
Our illustrative reporting service stores an export label in label and wants display_name. This is a design example, not universal runnable SQL. Confirm your exact PostgreSQL version and framework before choosing migration syntax or lock settings.
Expand without retiring the old representation
Introduce the new nullable field first. Deploy a compatibility release that writes both values in one transaction and reads with the required fallback while data is incomplete. Define what happens when a customer edits a record during the backfill. A retry must not create a second logical export.
PostgreSQL ALTER TABLE operations acquire locks, with the level depending on the subcommand. A small change can wait behind a long transaction and then obstruct other work. Review the specific operation and observe waits during rehearsal; additive does not mean lock-free.
Technical reference: PostgreSQL ALTER TABLE · PostgreSQL explicit locking.
Make the compatibility boundary visible
| Schema and writers | Reader behavior | Recovery decision |
|---|---|---|
| Only label exists | Old code supported | Add new field first |
| Both fields; old-only writers remain | Read label as authoritative | Do not switch readers yet |
| All writers dual-write; verified backfill | New field can become authoritative | Rollback only to compatible dual-writing code |
| Old field removed | No code may reference label | Old artifact is incompatible |
A fallback for missing values cannot detect a non-null but stale new value. Before switching readers, retire old-only writers and verify consistency. Rolling back to old-only code after reader cutover could recreate divergence. Keep the compatibility release as the reviewed recovery artifact instead.
Backfill in bounded, restartable work
Find rows that need copying without overwriting a newer customer edit. Use a stable ordering, a concurrency-safe update condition and batches chosen for the workload. Persist progress so a failure can resume at a known boundary. Monitor write volume, lock waits, request latency and replication where present.
No batch size is safe for every application. In rehearsal compare a small batch with ordinary traffic, then choose a pause or stop rule. If a batch fails, inspect what committed before retrying. An unlimited retry loop can turn a recoverable mismatch into sustained database pressure.
Check semantics, not just populated rows
Verify missing values, representative labels, new records and updates to existing exports. Counting non-null rows can look correct while values were copied from the wrong source. Test the compatibility artifact against partially populated data and exercise a worker started before deployment.
Record schema version, migration revision, completion criteria and incompatible combinations. Inspect fallback use and consistency before removing either. If checks fail, stop the backfill or promotion, preserve evidence and choose a compatible artifact or reviewed forward repair. Do not claim an application rollback reconstructs committed data.
Retire the old field in another reviewed release
Remove old reads and writes after the backfill and observation period meet your criteria. Check infrequent jobs as well as interactive routes. Dropping the old field belongs in a later change with its own recovery decision, so a release problem does not force you to combine code repair with data reconstruction.
If bad data appears after retirement, stop further damage and use the recovery plan or an approved repair. Do not apply a destructive down migration just because tooling exposes a rollback button. Attach the matrix to the release record and rehearse data recovery before changing production. The useful result is an explicit compatibility boundary, not a zero-downtime guarantee.
Official references
Documentation was reviewed for this article. Examples are planning exercises, not commands tested on a PrivacyNodes server. Check the documentation for your installed version.