Migration Discipline and Schema Evolution

1. First screen — a migration is a compatibility change, not a DDL command

Assume customer_contact has long exposed phone_legacy. An older application version still reads and writes that column while a newer version must consume normalized phone_e164. A successful ALTER TABLE ADD COLUMN proves only that the new column exists. It does not prove that existing data is ready, old and new readers/writers can overlap, enforcement is correctly timed, lock/rewrite risk is understood, or recovery is defensible.

The required artifact is Schema Evolution Migration Review Record v1. Every phase records the previous contract, schema change, data transition, old/new reader-writer compatibility, lock-or-rewrite risk, verification, fallback, removal gate, and residual risk. Completion requires positive, negative, and recovery evidence; a migration file or new schema is not acceptance evidence by itself.

Six boundaries are locked from the start: migration is a compatibility change; ALTER TABLE lock levels must not be generalized across subforms; schema existence is not data readiness; expand/contract does not guarantee zero downtime; rollback, forward repair, and restore are distinct; and artifact presence does not replace observed behavior.

2. Route orientation — scaffold changes, evidence does not

Foundation learners use explicit phase checkpoints. Repair learners rebuild only the diagnosed misconception, such as treating column creation as migration completion. Experienced learners may compress syntax but still deliver all six critical dimensions and independent transfer.

Before each phase, state four things: the active contract, what must not yet be removed or enforced, the observation that permits the next phase, and the real fallback if that observation fails. “The migration ran successfully” is not a complete answer.

3. Mental model — current → expand → transition → overlap → enforce → contract

Use this staged model:

current contract → expand schema → backfill/verify → old+new overlap → enforce invariant → contract/remove old path

The current phase identifies authority. Expand adds structure without breaking the old contract. Data transition moves and measures existing data. Overlap tests old/new readers and writers separately. Enforce strengthens the new invariant only after readiness and writer compatibility are proven. Contract removes the legacy path only when the removal gate shows no required consumer remains.

asset://DAT-005/DAT-005-A1 renders this sequence with evidence gates and abort paths. Every arrow requires an observation, not merely elapsed deployment time.

4. Foundation layer — six technical contracts

Migration contract

Define before/after contracts plus intermediate states. DDL without transition states cannot demonstrate compatibility.

ALTER lock scope

Frozen source PG18-ALTERTABLE states that ALTER TABLE subforms can acquire different lock levels and that ACCESS EXCLUSIVE is acquired unless a subform explicitly says otherwise. Do not extrapolate one observed operation to every subform. Check the exact operation and version.

Data transition

Creating phone_e164 does not make old rows ready. Backfill must expose row counts and invalid/null residue. SET NOT NULL before readiness can fail or break an old writer.

Compatibility window

Expand/backfill/switch/contract can reduce overlap risk but is not a zero-downtime guarantee. Model old reader, old writer, new reader, and new writer independently.

Recovery decision

Schema rollback, forward data repair, and backup restore are separate responses. When transformation is lossy, a down migration may restore shape without restoring prior information.

Migration evidence

Completion means schema apply plus invariant, compatibility, failure, and recovery evidence. Migration-file existence or one happy-path query is insufficient.

5. Deep explanation — gate ordering is the design

The fixture begins with phone_legacy. Safe expand first adds a nullable target:

ALTER TABLE customer_contact ADD COLUMN phone_e164 text;

This does not claim data readiness. A bounded backfill follows, then verification measures residue. If any row expected to migrate still has phone_e164 IS NULL, the enforcement gate remains closed. In addition, a target NOT NULL invariant is gated by all remaining phone_e164 IS NULL rows, not only rows whose legacy value was non-null. The lab therefore reports both unresolved_legacy_rows and target_null_rows; a synthetic row with no legacy phone requires an explicit fixture-only resolution before enforcement.

The dangerous shortcut is early enforcement:

ALTER TABLE customer_contact
  ALTER COLUMN phone_e164 SET NOT NULL;

Before backfill it can fail on existing rows. Even after current rows are filled, an old writer may still insert only phone_legacy; enforcement would then break the compatibility window unless writer transition is complete.

Contract has a separate risk. DROP COLUMN phone_legacy may make the schema look clean while an old reader still depends on it. Removal requires consumer evidence, not a calendar assumption.

Two technical overclaims are also prohibited. A constant-default optimization in one documented case does not imply arbitrary column changes avoid rewrites. Likewise, the general ACCESS EXCLUSIVE rule must not erase documented subform exceptions. The exact subform and version remain source-bounded.

6. Worked example — contact migration in seven phases

lab://DAT-005/DAT005-LAB1 uses a random disposable schema.

1. Baseline: record old reader/writer behavior and row counts.
2. Expand: add nullable phone_e164; the old contract must still work.
3. Negative enforcement probe: deliberately attempt SET NOT NULL before backfill and preserve the failure path.
4. Backfill + verification: normalize convertible values and record null/invalid residue.
5. Overlap: test old reader, old writer, new reader, and dual/new writer separately.
6. Enforce: strengthen the invariant only after data and writer gates are green.
7. Contract: remove legacy only after the removal gate. Normalization can discard old formatting, so do not assume a down migration reconstructs prior data.

A lock-risk probe holds a compatible read-side lock in one session and attempts the target ALTER TABLE with a bounded lock_timeout in another. Authoring never fabricates PostgreSQL output; learners preserve their own stdout/stderr and SQLSTATE.

7. Failure modes — plausible shortcuts to reject

“The column exists, so the migration is complete”

Schema existence is one phase. Data readiness and application compatibility remain unproven.

“Set NOT NULL first, then backfill”

Existing nulls or old writers can break immediately. Readiness precedes enforcement.

“The old reader will be upgraded later, so drop legacy now”

Without removal evidence this is a contract break.

“All ALTER TABLE operations take the same lock”

The frozen source explicitly requires subform-specific reasoning.

“A down migration is a safe rollback”

Lossy transformations and new writes can make reversal incomplete.

“One successful apply proves recovery readiness”

Recovery needs a failure scenario and a bounded rollback/forward-repair/restore decision.

8. Experienced bridge — go straight to the decision matrix

Use:

Phase | Schema Change | Data Transition | Old R/W | New R/W | Lock/Rewrite Risk | Verification | Fallback | Removal Gate | Residual Risk

Experienced evidence still includes a deliberate failure, compatibility rejection, source-bounded lock/rewrite decision, recovery decision, and independent transfer. Syntax may be compressed; evidence may not.

9. Alternatives and trade-offs

A big-bang change has fewer steps but couples application and schema transition and removes the overlap buffer. It is defensible only when bounded evidence shows overlap and rollback assumptions are acceptable.

Expand/contract creates a compatibility window but extends dual-representation and dual-write risk. It is a design pattern, not a universal zero-downtime guarantee.

A single large backfill transaction is easy to describe but may increase lock, WAL, and time risk. Batch backfill reduces blast radius but requires progress and idempotency control. This lesson does not claim production performance numbers.

Rollback-first, forward repair, and restore are chosen from reversibility and evidence. Do not rename restore as rollback or treat schema reversal as data restoration.

10. Guided and independent practice

Guided

Run baseline old-reader/writer probes. Add the nullable target and deliberately attempt early SET NOT NULL. Preserve the failure. Backfill, measure residue, and enter overlap only if the gate is green.

Compare the old writer with a dual writer. If enforcement breaks the old writer, repair the transition plan. Before dropping legacy, run the old reader as a negative compatibility probe.

Independent

For the contact case, build a recovery table for every phase: if the new deployment fails, can schema be reversed, is data transformation reversible, is forward repair safer, and what evidence is missing before a final decision?

11. Verification and evidence — keep statement classes separate

Every row of Schema Evolution Migration Review Record v1 separates:

• Prediction: expected outcome before execution.
• Observation: actual PostgreSQL output, count, error, or query result.
• Inference: what the observation supports inside the bounded fixture.
• Decision: phase pass/stop, recovery choice, and residual risk.

All six dimensions—migration_contract, alter_lock_scope, data_transition, compatibility_window, recovery_decision, and migration_evidence—must remain traceable. At least one negative observation and one repair/recovery result are mandatory.

12. Assessment and transfer — tax region without copying the contact sequence

A billing system must replace nullable free-text tax_region_text with validated tax_region_code while two application versions overlap. Identify old/new readers and writers, define the data transition and validation gate, source-bound lock/rewrite assumptions, and define the removal gate.

If some region mappings are ambiguous, do not claim backfill completion; the unresolved set is evidence. If normalization loses information, do not present a down migration as guaranteed recovery. The final migration/recovery design remains learner-owned.

13. References and freshness boundary

This candidate uses only the frozen sources:

• PG18-DDL-ALTER — PostgreSQL 18 table modification behavior for columns, constraints, defaults, and type changes; retrieved 2026-09-02, review due 2026-12-31.
• PG18-ALTERTABLE — PostgreSQL 18 ALTER TABLE forms and documented lock-level behavior; retrieved 2026-09-02, review due 2026-12-31.

As of 2026-09-15, the review date has not arrived, so source scope is not expanded. The disposable fixture does not prove organization-wide zero downtime, disaster readiness, or production performance.