Chapter

Schema Changes Without Surprises

Part H · Running Data Systems|3,923 words|about 17 min read|Volume H

49.0 What this chapter gives you#

A schema change alters the agreement between stored records and the programs using them. The challenge is not merely to make a new table definition valid; it is to keep old data and overlapping application versions meaningful while the change takes place.
You will plan compatibility windows, separate expansion from removal, design bounded backfills and define evidence for cutover. You will also distinguish undoing a deployment from reconstructing information already lost.
The worked change adds an explicit currency field to a separate copy of the shop’s INR-only teaching data. It does not change the agreed amounts or pretend that a real multi-currency checkout has been implemented.
All executable changes in the companion use newly created, disposable databases. No instruction in this chapter authorises running a migration against an existing user or production database.

49.1 Compatibility windows#

49.1.1 PLAIN — in simple words#

During an update, not every program changes at the same instant. An old web process, new worker, reporting job and delayed mobile request may all use the database during the transition.
A safe plan asks which combinations must work. A new program with the old schema and an old program with the new schema can fail in different ways.
Compatibility includes meaning, not only syntax. A field still called amount can become dangerous if one version reads rupees while another writes paise.
List readers and writers before changing the contract. A forgotten export script is still a consumer even if it has no place on the architecture diagram.

49.1.2 PLAIN — a picture in your head#

Mira changes an order form while some printed pads with the old design remain at the tills. The office has to interpret both forms for a while.
Removing a box from the new form does not remove it from old pads. Changing the meaning of an existing box without a label can be worse than adding a new one.
Where the comparison breaks: software can keep running old code for hours, replay old messages later and cache schema assumptions. The overlap is not limited to what is visibly on the counter today.

49.1.3 PLAIN — a worked example#

The original agreed prices are integer paise and the teaching contract says INR. A proposed schema adds currency_code so future readers no longer need that implicit assumption.
Version V1 knows only amount_paise. Version V2 knows amount_paise and currency_code. During the overlap, a V1 insert may omit the new field; a V2 reader must not falsely claim that every missing value in every dataset means INR.
For this specific copied fixture, documented provenance establishes that all old records are INR. A compatibility adapter may use that provenance while the backfill runs. Imported records without the same provenance remain unresolved.
The compatibility matrix therefore includes V1 read/write before and after expansion, V2 read/write during partial backfill and V2-only work after cutover. It also records when V1 writers are no longer allowed.

49.1.4 PLAIN — what is really happening inside#

Readers depend on field presence, type and meaning. Writers depend on accepted inputs, defaults and constraints. Schema and application releases change those dependencies at different times.
A message or API contract can outlive the program that originally created it. Replaying old payloads must follow a declared version adapter or rejection policy, not accidental interpretation by current code.
Database locks and resource use are a second compatibility dimension. A logically compatible DDL statement can still block ordinary work for longer than the service permits.

49.1.5 TECHNICAL — the engineer’s version#

Model compatibility as a relation among application version, schema version, payload version and data state. “Backward compatible” is incomplete unless the direction and supported combinations are stated.
PostgreSQL’s ALTER TABLE operations have operation-specific lock and rewrite behaviours. A change plan must consult the exact operation and version rather than assume every ADD COLUMN is operationally identical. [S194]
The capstone migration experiment tests a small local subset. It is not an online production migration or evidence of acceptable lock duration under concurrent load.

49.1.6 WORDS — remember these#

Compatibility window: the overlap period for contracts — the time during which specified old and new consumers must coexist. Consumer: anything relying on the data contract — a reader, writer, job, export, API adapter or replay process. Semantic compatibility: preserving meaning across versions — agreement about units and interpretation, not only accepted syntax.

49.2 Expand and contract#

49.2.1 PLAIN — in simple words#

Expansion adds the new representation while preserving the old path. Contraction removes the old path only after the remaining dependencies have been checked.
Separating these steps gives programs time to move. It also creates temporary complexity, which needs an owner and a removal plan.
Two writable copies of the same fact can disagree. Decide which one is authoritative during each phase and how disagreement is detected.
Do not make the destructive final step the first step merely because it produces a cleaner schema diagram.

49.2.2 PLAIN — a picture in your head#

To replace a bridge while traffic continues, first build the new crossing, direct traffic onto it and only then remove the old one.
Building the new bridge is not proof that every road sign now points to it. The switch requires observation and coordination.
Where the comparison breaks: two schema representations can both accept writes. Unlike passive bridges, they can create conflicting versions of the same fact unless the transition protocol controls them.

49.2.3 PLAIN — a worked example#

Phase E1 adds nullable currency_code to the copied exercise table. Existing reads remain possible, and no historical value is guessed during the DDL step itself.
Phase E2 deploys a reviewed writer that supplies INR for this INR-only workflow and a reader that handles the recorded transition state. Old-writer behaviour is explicitly tracked.
Phase E3 backfills eligible historical rows using their known provenance. Phase E4 validates completeness and allowed values, confirms that old writers are gone and strengthens the constraint.
Only a later contraction removes compatibility fallback logic. That removal is a distinct decision with evidence, not an automatic side effect of the backfill completing once.

49.2.4 PLAIN — what is really happening inside#

An additive schema permits both representations for a limited period. Application releases then change which representation is read or written.
Dual writes should normally be kept within one appropriate atomic boundary when they describe one fact. Separate best-effort writes can leave one representation stale after a failure.
Monitoring must distinguish expected transitional gaps from defects. An alert cannot interpret missing currency_code correctly without knowing the current phase and its deadline.

49.2.5 TECHNICAL — the engineer’s version#

The expand/backfill/validate/contract sequence here is an original deployment plan. Its safety depends on the stated writer controls and provenance assumptions; it is not a universal recipe guaranteeing zero downtime.
PostgreSQL 17 supports adding certain CHECK and foreign-key constraints as NOT VALID and validating them later. The feature does not mean “ignore all checks”: new changes remain subject to the documented enforcement rules. [S194]
SQLite’s supported ALTER TABLE operations differ from PostgreSQL’s, and more involved changes may require the documented table-rebuild procedure. Do not copy PostgreSQL syntax into the SQLite lab and infer equivalence. [S196]

49.2.6 WORDS — remember these#

Expand: add a compatible new path — introducing representation or capability before removing the old one. Contract: retire the old path — removing obsolete fields, adapters or access after dependencies are checked. Authoritative representation: the selected source of meaning — the version whose value governs while multiple copies coexist.

49.3 Backfills#

49.3.1 PLAIN — in simple words#

A backfill transforms existing records into a new representation. It should have explicit eligibility, ordering, batch limits and rules for partial progress.
A stopped backfill should be resumable without silently duplicating work or overwriting a more recent legitimate change.
A default is not evidence. Filling every missing currency with INR is justified only where the record’s origin or governing contract establishes INR.
Progress must represent completed work. Advancing a cursor before the data change is committed can skip records after a failure.

49.3.2 PLAIN — a picture in your head#

Dev adds a currency label to old order cards. He knows one labelled box contains only INR orders, but a mixed box from an external source has no such guarantee.
He completes and records one small batch before moving his bookmark. If interrupted, the bookmark tells him which completed batch to resume after.
Where the comparison breaks: records can change while a backfill is running. A database bookmark and a person’s static pile of cards do not have identical concurrency behaviour.

49.3.3 PLAIN — a worked example#

Copy five synthetic rows with ordered identifiers 1 through 5 and a provenance flag. Rows 1–4 have the documented INR-only origin. Row 5 has unknown origin.
Process at most two eligible rows per transaction. The first committed batch fills rows 1 and 2. The next fills 3 and 4. Row 5 remains unresolved rather than receiving an invented label.
Retry the second batch. Its conditional predicate requires currency_code IS NULL and the known origin; already-filled rows are unchanged. Idempotence here means repeating the permitted transformation does not invent additional changes.
Inject an exception before the batch commit. Both data changes and progress metadata owned by that transaction must roll back. A progress record outside the transaction would need its own recovery protocol.

49.3.4 PLAIN — what is really happening inside#

A worker selects a bounded set under a stable ordering and applies a conditional update. Keyset progress can avoid some problems of changing offset-based lists, but its correct use still depends on the key and eligibility rules.
A competing writer can make a row ineligible or change its version. Recheck relevant conditions at update time; do not treat an earlier selection as permanent permission to overwrite.
Resource budgets matter. A backfill uses I/O, logs, locks and cache capacity that ordinary traffic also needs. Throttle or pause based on defined service signals rather than forcing maximum speed throughout.

49.3.5 TECHNICAL — the engineer’s version#

Specify a transformation precondition and postcondition. For the exercise: source provenance is INR_ONLY, destination is NULL, and the only permitted new value is INR. Unknown provenance is not a successful transformation.
Keep progress and transformed data atomic where possible, or design replay-safe reconciliation where they cross a boundary. Preserve counts for selected, changed, already-complete, conflicting and unresolved rows.
The companion backfill is sequential and in memory. Its rollback and replay tests do not establish correctness under every production writer schedule or acceptable WAL growth.

49.3.6 WORDS — remember these#

Backfill: transforming already-stored records — population of a new representation under explicit source assumptions. Keyset progress: resuming from a stable ordered key — a traversal technique whose eligibility and concurrent-change boundaries still need design. Transformation precondition: what must be true before changing a row — the provenance, version or state justifying the operation.

49.4 Validation and cutover#

49.4.1 PLAIN — in simple words#

Validation asks whether the new representation satisfies the intended contract. A backfill process exiting successfully does not answer every validation question.
Compare counts, keys, values, exclusions and meaning. Equal totals can hide two offsetting mistakes.
Cutover changes which path the service trusts or serves. It should occur after the relevant evidence is available and while a clearly defined recovery option still exists.
The validation itself needs a time boundary. A clean check followed by an uncontrolled old writer can make the new state incomplete again.

49.4.2 PLAIN — a picture in your head#

Mira compares old and new order forms before telling every till to use the new version. She checks actual identifiers and line values, not only the number of pages.
She also removes the old blank pads. Otherwise the next order can recreate the ambiguity she just resolved.
Where the comparison breaks: software versions can remain in worker pools, clients or retry queues after a deployment dashboard says the main service is updated.

49.4.3 PLAIN — a worked example#

Validate the copied canonical fixture: four lines, six units, two order identities and 28,650 paise. Compare each line’s scoped key, product, quantity, agreed price and currency.
Confirm O-1042 remains 17,100 paise and O-1043 remains 11,550. A currency addition is not permission to replace their agreed prices with current catalogue prices.
Count unresolved origins separately. If the five-row backfill example still contains row 5 with unknown currency, it cannot support the statement “all rows satisfy the new required currency contract.”
Before cutover, stop or adapt every old writer, rerun the applicable validation and establish the new constraint. The order matters because uncontrolled writes between checking and enforcing can reopen the gap.

49.4.4 PLAIN — what is really happening inside#

Validation queries observe a snapshot or sequence of snapshots according to the database and transaction settings. Their scope should be recorded so discrepancies can be interpreted.
A database constraint can maintain selected properties after successful validation. It does not prove that the chosen currency value reflects the real transaction unless the input provenance is sound.
Index creation and schema locking may require special operational treatment. Even a carefully chosen concurrent index build has failure states that a deployment script must inspect.

49.4.5 TECHNICAL — the engineer’s version#

PostgreSQL documents CREATE INDEX CONCURRENTLY restrictions and the possibility of an invalid index after failure. “The command was started” is not evidence of a usable index or successful schema cutover. [S195]
Bind validation evidence to source revision, schema state, record scope and runtime. A row count from one database and a constraint report from another do not compose into one verified result.
The companion validates a deliberately small copied fixture. Production cutover needs representative traffic, real role/constraint tests and a deployment-specific concurrency and recovery plan.

49.4.6 WORDS — remember these#

Cutover: switching the authoritative path — a controlled transition from old to new representation or service behaviour. Reconciliation: comparing meaningfully corresponding records — checking identities, values and declared exclusions rather than one headline total. Validation boundary: the exact state a check describes — its database, snapshot or time, schema and input scope.

49.5 Rollback versus roll-forward#

49.5.1 PLAIN — in simple words#

Rolling back an application release can restore old code. It does not automatically restore deleted information or reverse every data change made by the new code.
Some changes preserve enough information to undo them. Others lose distinctions: merging fields, rounding values or deleting columns can make exact reconstruction impossible without another source.
A roll-forward repair applies a new corrective change instead of pretending the earlier state can simply be restored. The choice depends on evidence, compatibility and the remaining recovery paths.
A backup is useful only if it can restore the needed state under the allowed time and data-loss constraints. Restoring an old backup can also discard legitimate work performed afterward.

49.5.2 PLAIN — a picture in your head#

Putting the old form back on the counter does not recover notes shredded during the update. The paper design and the recorded information are different things.
Sometimes the correct response is to retain the new form and repair its unclear label, rather than force every completed order back into a structure that no longer fits.
Where the comparison breaks: databases can support transactional rollback for some DDL and data changes, but behaviour depends on the engine, operation and commit boundary. Do not generalise from one product’s example.

49.5.3 PLAIN — a worked example#

Adding nullable currency_code and filling justified INR values preserves the old amount_paise field. Reverting V2 to V1 during the compatible phase may be possible under the declared reader/writer rules.
By contrast, replacing all exact paise with rounded rupee amounts loses information. Both 7,550 and 7,549 paise could round to 75 under one whole-rupee rounding policy. The old exact value cannot be inferred from that rounded value alone.
If new legitimate orders were created after a backup, restoring that backup to undo the migration may remove those orders too. The recovery plan must address the intervening work, not call it irrelevant because rollback is urgent.
A repair record should state which values were reconstructed from retained evidence, which were corrected by new authorised input and which remain unresolved.

49.5.4 PLAIN — what is really happening inside#

A rollback boundary is a point before or after durable commitment. Within a transaction, supported changes can be discarded. After commitment, recovery requires another operation with its own risks.
Reversibility is a property of the transformation and retained evidence. An invertible encoding can be reversed; an aggregation or destructive overwrite may not be.
Deployment teams should rehearse the intended recovery procedure on representative disposable copies. Discovering that the old application cannot read newly accepted values during an incident is too late.

49.5.5 TECHNICAL — the engineer’s version#

Record a point of no return for each destructive step and the evidence required before crossing it. Keep application rollback, schema rollback and data restoration as separate entries.
SQLite’s documented generalised ALTER TABLE procedure includes explicit copying, rebuilding and checks. A home-made variant that changes ordering or skips dependencies is not validated merely because the resulting CREATE TABLE looks right. [S196]
The companion demonstrates rollback inside its owned transaction and explicitly labels transformations that lose information. It does not claim an automated production rollback facility.

49.5.6 WORDS — remember these#

Rollback: returning within a defined reversible boundary — undoing a transaction or deployment, with scope that must be stated. Roll-forward: correcting through a subsequent change — a new reviewed transformation rather than automatic restoration of the past. Irreversible transformation: one that loses needed distinctions — a mapping whose original inputs cannot be recovered from the retained output alone.

49.6 Staged evidence#

49.6.1 PLAIN — in simple words#

A good migration record separates proposed work, reviewed work, executed work and verified outcomes. Those stages are related but are not the same event.
Preparing a script proves that a script exists. Running it proves that execution occurred. A successful exit does not prove every intended business property.
Preserve failure evidence. Replacing a failed report with a later green result without recording the change hides what was learned and which state was actually tested.
The final decision belongs to the authorised owner. A technical check supplies evidence; it does not grant itself permission to change a live system.

49.6.2 PLAIN — a picture in your head#

A bridge drawing, an approved construction plan, a completed bridge and an inspected bridge are four different things.
A stamp saying “plan approved” cannot be moved onto a photograph and treated as a structural inspection of the finished work.
Where the comparison breaks: software artefacts can change with one byte, and the difference may be invisible in a screenshot. Evidence needs a reliable identity for the actual candidate tested.

49.6.3 PLAIN — a worked example#

The proposed currency migration record lists the schema precondition, eligible provenance, maximum batch size, transaction owner, stop conditions, validation queries and recovery plan.
A disposable rehearsal records the exact script digest and input fixture identity, then reports changed rows, unresolved rows and rollback-control results. Its scope is the rehearsal environment.
A later live execution would require separate authority and live preconditions. The rehearsal’s four correct rows do not establish that a larger external dataset has only four eligible rows or no exceptions.
After execution, verify the intended schema, row-level reconciliation and the behaviours of the actual readers and writers. Retain the before/after record and unresolved findings.

49.6.4 PLAIN — what is really happening inside#

Evidence connects an artefact identity to an environment, an action and an observation. Losing any of those links weakens the conclusion.
A migration runner can automate ordering and record applied identifiers. It cannot infer a business requirement omitted from the migration design.
A staged process limits the size of an uncertain step. It also makes a failure easier to diagnose because the observed change is bounded and the previous state is recorded.

49.6.5 TECHNICAL — the engineer’s version#

Use a review template containing preimages, change identity, schema version, engine/driver versions, permissions, transaction boundaries, reconciliation outputs and recovery conditions. The template is an original engineering aid, not certification.
Distinguish idempotent transformation logic from a migration registry that simply refuses a repeated migration ID. They address different retry situations.
The capstone’s final checks establish only the listed disposable-example outcomes. They neither access the user’s database nor grant a release decision for an unrelated application.

49.6.6 WORDS — remember these#

Precondition: what must be true before a change — the checked state and authority on which safe execution depends. Artefact identity: which exact candidate was used — a stable reference, often a digest, linking execution to reviewed bytes. Rehearsal: a bounded execution before live work — an experiment whose representativeness and limitations must be stated.

49.97 Practice and worked answers#

Question: Why test an old writer after adding a required concept? Answer: It may omit the new field or supply values with old semantics during the compatibility window.
Question: Can every NULL currency become INR? Answer: Only where authoritative provenance establishes that meaning; unknown origins remain unresolved.
Question: What must be atomic in the backfill example? Answer: The owned batch changes and its committed progress record, or an alternative replay-safe protocol must handle their separation.
Question: Does 28,650 paise after migration prove every line is unchanged? Answer: No. Compare keys and individual values too; offsetting errors can preserve a total.
Question: Why remove old writers before final validation/enforcement? Answer: Otherwise they can recreate incompatible data after an earlier clean check.
Question: Does restoring old code recover rounded-away paise? Answer: No. Lost information needs another trustworthy source or remains unknown.
Question: What does a successful rehearsal establish? Answer: The recorded candidate behaved as observed on the stated disposable input and environment, not every production condition.
Question: Does a green technical report authorise a live migration? Answer: No. Authority and technical evidence are separate requirements.

49.98 Common wrong ideas#

Wrong: A schema is used only by the newest application. Right: Old workers, exports and replayed payloads can remain active.
Wrong: Adding a default supplies missing historical evidence. Right: Defaults are rules, not recovered facts.
Wrong: Dual writing guarantees agreement. Right: It needs an atomic or reconciled transition protocol.
Wrong: A backfill should always run as fast as possible. Right: Ordinary service capacity and failure budgets still matter.
Wrong: Equal totals prove a correct migration. Right: Keys, values, exclusions and semantics also need comparison.
Wrong: Reverting code undoes every data change. Right: Committed and lossy transformations may need separate recovery.
Wrong: A migration ID proves safe repeated execution. Right: Registry identity and transformation idempotence are different properties.
Wrong: A prepared script is an executed and verified result. Right: Preparation, execution, verification and authority are separate stages.

49.99 Chapter summary in 20 lines#

A schema is a contract with multiple consumers.
List old and new readers and writers.
Preserve units and meaning across the compatibility window.
Inspect operation-specific lock and rewrite behaviour.
Expand before removing the old path.
State which representation is authoritative during transition.
Give temporary compatibility code an owner and retirement condition.
Backfill only where provenance justifies the new value.
Use bounded batches and explicit ordering.
Keep committed progress aligned with committed changes.
Recheck update conditions when competing writers can act.
Preserve unresolved rows instead of guessing.
Validate identities and values, not only totals.
Prevent old writers from reopening a validated gap.
Treat cutover as a distinct controlled event.
Separate application rollback from data restoration.
Identify transformations that lose information.
Rehearse the actual recovery procedure on disposable data.
Bind evidence to exact artefacts and environments.
Technical success does not manufacture live-change authority.

Return to contents