Expand-contract database migrations

Begin by listing what old and new binaries read and write. An additive nullable column can let old code continue using the original field while new code learns the expanded schema. A new NOT NULL requirement, changed default, renamed column, or altered value meaning can still break old behavior or require expensive work. PostgreSQL DDL has documented locking and rewrite rules, so assess the exact statement rather than categorizing every ALTER TABLE as either free or fully blocking. Set bounded operational expectations and observe the deployed database version.

Compatibility is directional. New code may read old and new rows while old code understands only the old representation. During overlap, a writer may need to maintain both forms within one transaction if they belong to the same database, or use a deliberately reconciled protocol if effects span systems. Two independent writes can fail halfway and disagree. A fallback reader must distinguish genuinely absent migrated data from a valid NULL or empty value, and define which representation wins when both exist. Otherwise the migration silently changes business meaning.

Choose bounded batches, an observable progress marker, and a retry-safe rule for each row. A cursor over stable keys can record where work has reached, but rows may change while the backfill runs. Avoid replacing a newer application write with an older snapshot value. Conditional updates, row versions, appropriate locking, or an authoritative transformation inside the database can protect the intended ordering. Which mechanism is correct depends on the read/write protocol. A checkpoint merely saying batch 40 finished does not establish that concurrent updates preserved both representations.

Measure write amplification, lock waits, replication lag, storage growth, and user-visible latency while backfilling. Throttle or pause work according to a declared operational budget. A count of processed rows is not the same as a count of correctly migrated current rows; reconciliation should compare the intended invariant, including rows created or modified after the scan began. Validate representative edge cases and use aggregate or sampled checks only with their stated coverage. An irreversible transformation can require retaining original information through the rollback horizon.

Switch readers and writers in an order supported by the compatibility matrix, observe that the new representation is authoritative and complete, and wait until old binaries, jobs, and rollback candidates no longer require the old field. Only then consider destructive cleanup. Deploying a new frontend does not prove that a forgotten batch job stopped writing the old representation. Record the cutover revision and reconciliation evidence. Keep backup and recovery procedures aligned with the schema transition rather than treating a column drop as routine garbage collection with no state consequences.

The queue lab can illustrate a simplified migration-work backlog. Reset, set both λ=1 and μ=2, predict its initial four tasks drain at t=4, then Play or Step. Separately Reset, set both λ=0 and μ=2, predict t=2, then Play or Step. New repair tasks arrive only in the first model; real dual-write and backfill rules determine whether such tasks exist. The fluid instrument does not execute SQL, model locks, or validate row versions. Its M/M/1 readout has separate assumptions and supplies no migration-completeness proof.