A deployed service may have old and new application instances running at the same time. A safe schema change first expands what storage can hold, then migrates data, then changes readers and writers, and only later removes the old shape. Adding a required column and immediately expecting all old writers to populate it can break requests during rollout. Backfill work should run in bounded batches and report progress. Before dropping an old column, verify that no supported application version or job still reads it. A migration is an operational sequence, not only a SQL file.
Make this comfortable
Expand-and-Contract Schema Migrations
Working case
The inspection table gains a normalized reviewer_id column. First add it nullable and deploy code that can read either the new value or the older legacy_reviewer_id field. Backfill existing inspections in batches. Next deploy writers that always set reviewer_id, validate the backfill, and enforce the new non-null constraint. Only after the previous app version is retired should the old read path disappear. If rollback is required during the overlap, the older version still reads records. A one-step rename would make that rollback much harder.
Implementation
ALTER TABLE inspections ADD COLUMN reviewer_id bigint;
-- Backfill in bounded batches and verify remaining NULL rows.
WITH batch AS (
SELECT inspection_id FROM inspections
WHERE reviewer_id IS NULL AND legacy_reviewer_id IS NOT NULL
ORDER BY inspection_id LIMIT 250 FOR UPDATE SKIP LOCKED
)
UPDATE inspections
SET reviewer_id = inspections.legacy_reviewer_id
FROM batch WHERE inspections.inspection_id = batch.inspection_id;
-- Enforce NOT NULL only after the new writer is deployed and data is verified.Cost and boundaries
A full-table backfill touches O(N) rows and may create significant I/O, locks, and replication lag. Batching reduces peak pressure but extends the coexistence period and adds progress tracking. Each compatibility branch adds temporary application complexity; remove it after the contract stage is complete. Test the old and new binaries against the expanded schema. A code rollback cannot reverse an incompatible data transformation by itself.
Common Mistakes
- Do not drop a field while a supported old version still reads it.
- Do not make a large backfill one unbounded transaction.
- Do not treat code rollback as a complete database rollback.
Connected lessons
Data Persistence; Relational Records and Database Constraints; Transactions and Concurrent Writes; Indexes and Query Plans for Case Feeds; Form submission: validate on the server and return field errors; Authorization: check permission for this record on every request; HTTP caching: validate a changed representation with an ETag.
Failure trace
A migration renames note_text while old app instances still write the previous column. Half the requests succeed and half fail during the rolling release. First add a compatible new column, then write and read across both versions while data is backfilled, and remove the old column only after old binaries are gone. Check the failure path in staging with both versions active.
Verification
- Boot old and new application versions against the expanded schema at the same time.
- Backfill a partial batch, interrupt it, then resume without changing already migrated records incorrectly.
- Attempt the contract step while an old instance is still serving and ensure the release gate blocks it.
Decision note
Compatibility windows cost temporary code and storage. They are usually cheaper than downtime or data repair; keep the window short and observable rather than leaving two authoritative columns indefinitely.
Apply and check
Build Project: paginated inspection feed with safe writes; then check the boundary with Web Development: data and API contracts quiz.
Further connections
Resumable Data Backfills and Reconciliation; Online Index Builds and Query Plan Release Gates.
web-tech
web-development