Change a stored field while old code still runs

The shop stores prices as whole credits and wants to introduce a price_minor field measured in hundredths of a credit. Existing application instances still write the old price field while newer instances are being deployed.

Adding a column is easy compared with ensuring that old readers, new readers, old writers, and new writers agree during the transition. A migration changes stored representation or schema while preserving the application’s required behavior.

Expand before relying on the new representation

For this example, the conversion is exact: old price 20 becomes price_minor 2000. Assume a controlled writer path can keep both representations consistent during the transition.

Expand
Add the new representation and compatible code. Keep the old field usable.
Migrate existing rows
Populate price_minor in bounded batches while coordinated writes maintain both fields.
Validate and switch
Verify the conversion and writer coverage before new readers rely exclusively on price_minor.
Retire the old form
Remove the old field only after no supported reader or writer depends on it.

The transition is a period of coexistence, not one instantaneous schema statement.

A backfill populates existing records with the new representation. It must not overwrite a newer concurrent change using a stale value read earlier. One approach derives the new value from the row inside a protected update; another uses a version check and retries if the row changed. The chosen protocol must match the engine’s transaction behavior.

Validation must stay true afterward

Suppose a backfill reaches the end and every row has a new value. An old writer then inserts a row containing only price. The validation result was true at one moment but did not establish that future writes preserve it.

Before making price_minor mandatory, account for every writer: application instances, jobs, imports, and repair tools. A compatibility layer or database-managed rule may bridge the transition, but it must be deliberately designed and tested.

Large changes also consume resources. Backfills can compete with ordinary requests and create substantial log or index work. Bounded batches, progress records, and a safe restart procedure help make the work controllable.

Attempt a rollback

New readers are live, but the old field has already been dropped. Can we simply restart the previous application version as a rollback?

Check the previous version's requirements

Not if it requires that field. Application rollback and data rollback are different operations. The migration needs a supported compatibility window or a tested restoration/conversion path. Keeping the old representation until the rollback window closes is one possible choice.

The same reasoning applies to an engine’s own page formats and log formats: identify which software versions can read and write each stored representation before changing it.

PostgreSQL’s ALTER TABLE documentation specifies operation-dependent locking and validation behavior. The staged protocol here is an application-level design, not a claim that every schema change is automatically online.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter