Schema migrations need compatibility budgets

A schema can be valid at rest and still break the product during the hour when two application versions disagree about it.

Rolling deployments create mixed readers and writers. A worker may retain old code longer than the web fleet. An analytics extract may read yesterday's column. A rollback may restore a writer that cannot populate the new representation. Compatibility is therefore a timed operating promise, not a property of one migration file.

PostgreSQL documents that ALTER TABLE lock levels depend on the subcommand, that some changes can rewrite the table, and that a rewrite can temporarily require substantial extra disk. The same page documents adding some constraints as NOT VALID and validating them later under a less disruptive lock profile.

For indexes, CREATE INDEX CONCURRENTLY can avoid blocking ordinary writes, but it performs more work, carries important caveats, and cannot simply be dropped into every transaction. The safe choice depends on the object, workload, data, and recovery plan.

I previously framed why migrations deserve product briefs: teams need the user consequence, rollout owner, and recovery story before changing production data. This article is deliberately narrower and more technical. A compatibility budget specifies which old and new readers and writers may coexist, for how long, under which invariants, and what evidence authorizes removing the old representation.

Inventory every reader and writer

List builds, workers, jobs, reports, replicas, pipelines, scripts, and integrations that depend on the affected object or meaning.

The dependency list must reach beyond deployable services. A weekly finance export, a dormant support query, a change-data-capture consumer, and a disaster-recovery script can all preserve an old schema contract after the web fleet moves. I would attribute database access by application name, build, role, or query fingerprint and then compare observation with the ownership inventory. Unknown traffic becomes a named investigation. Otherwise contraction is based on the absence of remembered consumers rather than evidence that the old representation is unused.

A repository search is only the start of dependency discovery. I would reconcile static references with database query attribution, deployment inventories, scheduled-job ownership, replica consumers, dashboards, support scripts, and restore procedures. Each caller needs a deployment cadence and a way to observe whether it reads or writes the affected meaning. A weekly importer and a rarely used disaster-recovery command can extend the compatibility window more than the web fleet. The inventory is complete enough to govern rollout when every known path has an owner and an attribution signal, and the review can answer:

• Who reads the old form?
• Who still writes it?
• Which process deploys separately?
• How is each caller attributed?

Inventory proof is observed attribution for each listed consumer across its real cadence, plus an explicit decision for any caller that cannot emit reliable telemetry.

Publish supported version pairs

State whether old readers accept new writes, new readers accept old writes, writers overlap, or deployment order prevents a pair.

A version-pair matrix exposes the actual rolling-deploy problem. If version 18 writes only status_text and version 19 reads status_code first, the new reader needs a defined fallback until every old writer has left. If version 19 begins writing a code that version 18 interprets incorrectly, deployment order alone may be the safe answer. Rollback adds another pair: restoring an old binary after new-only data exists. I would test these combinations against domain meaning, not merely whether SQL queries return rows.

The reader-writer matrix should contain examples of domain meaning, not green checkmarks based only on successful parsing. An old reader must interpret new writes correctly; a new reader must tolerate old writers that have not populated the expanded form; rollback binaries must not erase meaning introduced during overlap. If deployment order prevents a pair, the gate enforcing that order belongs beside the matrix. I would run contract fixtures against every pair the rollout can create and record the first phase where each pair becomes unsupported. The compatibility review should decide:

• Which builds can coexist?
• What meaning must both forms preserve?
• Can rollback restore an old writer?
• When does support end?

The matrix passes when mixed-version fixtures preserve domain meaning for every deployable pair and an intentional gate blocks combinations the budget does not support.

Compatibility example — a nightly importer outlives the web rollout. The web fleet may reach the new schema while a scheduled writer emits the old representation tomorrow. Inventory its cadence and quiet horizon. I would deploy it first, translate at its boundary, or prevent an incompatible run. Observing only application pods leaves the least frequent writer able to revive invalid state.

Set a compatibility budget

Define the mixed-version horizon, capacity allowance, observation interval, and extension decision so temporary paths do not become permanent.

The budget makes temporary compatibility cost visible. A seventy-two-hour overlap might allow a nullable column, fallback reads, comparison logging, and extra index storage; a mobile or customer-installed client may require months and a different design. The document names the expected window, maximum extension, storage and latency allowance, and owner who can approve more time. If the window expands, the team revisits whether translation belongs in a durable API instead of leaving fragile dual behavior hidden in persistence code.

The budget gives temporary duplication a cost and an end. I would state the maximum mixed-version duration, write amplification, comparison traffic, extra storage, fallback rate, and on-call burden, then name who can extend each limit. An extension should cite a straggling consumer or failed invariant rather than move the date silently. The horizon must cover the slowest supported deploy and scheduled workload, including rollback observation. This turns compatibility from a permanent precaution into an operated phase whose expense can be compared with rollout risk. Before approving the window, the team should resolve:

• How long may old code remain?
• What overhead is allowed?
• Which signal extends the window?
• Who approves contraction?

Budget closure requires measured overlap cost, a named contraction date, and either on-time retirement or an extension tied to specific evidence and ownership.

Expand with production lock evidence

Choose DDL from measured size, load, database version, locks, rewrites, replication, and disk impact rather than simple syntax.

I would rehearse DDL against production-shaped row counts, data distribution, indexes, replicas, and concurrent queries. The measurement includes time waiting to acquire the lock, time holding it, statements queued behind it, replica lag, temporary disk, and abort cleanup. A command that runs quickly after acquiring ACCESS EXCLUSIVE can still cause an outage while waiting in a transaction. A lock timeout, short statement scope, and observable retry window are part of the change, not operational trivia added after review.

DDL review must use the exact database version, object size, workload, and subcommand. I would rehearse lock acquisition behind a long transaction, observe which requests queue behind the command, measure rewrite and WAL behavior, and practice cancellation cleanup. A short command can still cause an outage while waiting for its lock, and a concurrent operation can leave an invalid artifact after failure. The runbook should include timeouts, blocker queries, disk headroom, replica thresholds, and a safe retry. Production evidence must support the selected path, so reviewers need answers to:

• Which lock and duration?
• Can it rewrite data?
• What waits behind it?
• How is an abort cleaned up?

DDL evidence includes lock acquisition and hold time, queued workload, rewrite or WAL volume, replica behavior, disk headroom, abort cleanup, and a practiced retry.

DDL example — a fast command can still wait dangerously. A metadata-only alteration may execute quickly after it acquires its lock, yet wait behind a long transaction while newer requests queue behind it. The rehearsal therefore records acquisition timeout, blocker query, cancellation behavior, and cleanup in addition to execution duration. I would set a short lock timeout and abort rather than let a maintenance command form an invisible convoy.

Make translation semantics explicit

Define how reads choose a value, writes populate both forms, conflicts surface, and authority works during overlap.

Coexisting fields need one semantic authority rule. During expansion, a new reader may prefer status_code and fall back to a translated status_text. A new writer may populate both in one transaction while an old writer can only populate the old field. That asymmetry requires conflict detection: a later old write must not overwrite a new state it cannot represent. Comparison telemetry should distinguish missing backfill from semantic disagreement. Without that model, dual writes multiply values without establishing which one means the product truth.

During overlap, one representation must be authoritative for each phase. I would specify whether writers derive both fields from a normalized input, whether readers prefer the new form with a verified fallback, and how disagreement is surfaced. A transaction or explicit reconciliation rule should prevent a partial dual write from creating two plausible truths. Old code must be tested against values that only the new model can express, while new code must handle rows old writers leave incomplete. The translation contract should make conflicts observable instead of choosing silently, and its review should establish:

• Does the new reader fall back?
• Must one transaction write both?
• How is disagreement surfaced?
• Can old code erase new meaning?

Translation is proven when retries and partial-failure tests leave one authoritative meaning, comparison telemetry detects disagreement, and neither application version erases the other's valid data.

Run the backfill as separate work

Historical conversion needs stable selection, bounded chunks, preconditions, checkpoints, capacity limits, pause controls, and reconciliation.

The backfill is deliberately outside the DDL transaction because it has a different failure and capacity profile. It selects rows by a stable key, writes only when the observed old state still holds, records conflicts with live traffic, and advances checkpoints after commit. Throttling follows foreground latency and replica lag rather than a fixed sleep copied from staging. The application remains tolerant of missing new values throughout. That means a paused backfill delays contraction but does not turn the running product into a partially migrated invalid state.

The historical conversion should not share a migration transaction with DDL or application deployment. I would give it a stable population, indexed cursor, old-value precondition, chunk budget, pause control, checkpoint, and independent receipt. A live writer that changes a row after selection should win according to the translation contract; the backfill records a conflict or reevaluates rather than overwriting it. Restart should resume committed work without rescanning by offset. The migration plan remains reversible only if conversion can stop cleanly, so the backfill review needs to answer:

• Which rows qualify?
• Can live writes race conversion?
• What protects foreground traffic?
• How does restart resume?

Backfill readiness means chunks pause, resume, and race live writes without violating translation rules, foreground latency, or the migration's current recovery boundary.

Translation example — dual writes disagree. If old status text and new status code are written by separate statements or derived under different defaults, retry and partial failure can leave both populated but semantically inconsistent. I would define one authoritative input during each phase and write both forms transactionally from the same normalized value. Comparison telemetry names the writer build and conflict. A fallback reader may preserve availability, but it should not silently choose whichever representation appears convenient because that hides the evidence needed to end the overlap.

Validate before enforcing

Constraints, indexes, types, and derived values need validation queries and exception ownership before becoming universal assumptions.

Validation should be independently computable. For a new status code, I would compare translated old values to new values by cohort, count nulls and unknown mappings, inspect conflicting live writes, and verify the future constraint predicate. Adding a constraint as NOT VALID can separate acceptance of new violations from historical validation when the database supports that plan. The important distinction is that process completion is not data proof: a worker can process every checkpoint and still apply a systematically wrong mapping.

Finishing the converter does not prove the new invariant. Validation should count eligible, converted, conflicted, excluded, null, and invalid rows through queries that do not simply reuse worker results. Exceptions need owners and product meaning before a constraint becomes mandatory. Where the database supports staged validation, I would measure its lock and scan impact separately from adding the definition. A clean sample is insufficient when one cohort can fail systematically. Enforcement is justified when independent evidence balances the population and the exception ledger is empty or explicitly accepted. Reviewers should examine:

• What invariant holds now?
• Can validation avoid blocking?
• Where do exceptions go?
• Which errors stop enforcement?

Validation proof is an independently balanced population, clean invariant queries by cohort, and an exception ledger whose remaining entries have approved disposition before enforcement.

Observe and evict stragglers

Attribute old reads, writes, fallback, mismatch, errors, and process versions so zero traffic is observed rather than assumed.

Legacy access telemetry needs enough dimensions to find the owner. A counter called old_column_reads cannot tell whether the remaining traffic comes from one scheduled importer or thousands of old application instances. I would tag builds and service roles, retain query fingerprints for non-application access, and set an observation horizon that includes weekly and month-end work. Zero for five minutes is not a contraction gate. Zero across the defined cadence, plus an explicit accounting of offline clients, is evidence the compatibility promise can end.

Contraction needs evidence from every cadence, not only a current deployment dashboard. I would attribute old reads, old writes, fallback use, comparison mismatches, and errors to build or caller; deliberately run scheduled and recovery paths; and observe zero through the agreed horizon. A straggler should be upgraded, isolated behind an adapter, or blocked at a deliberate gate. Unknown traffic is not equivalent to absent traffic. The dashboard should make the last legacy access and owning component visible so the contraction decision can withstand a late job, and the gate should answer:

• Can legacy access be identified?
• How long must zero persist?
• Do jobs exceed that horizon?
• What removes a straggler?

Straggler eviction is complete only after attributed legacy activity remains zero through scheduled and recovery workloads, not merely through one quiet web deployment.

Backfill example — a live edit wins deliberately. The conversion reads a legacy value, a customer changes status through the new application, and the worker later attempts its update. A version or old-value precondition should reject the stale conversion and classify it as conflicted or already satisfied after reevaluation. This keeps historical conversion from overwriting fresh intent. The migration receipt balances that row separately from a failure, and invariant validation confirms the new writer now maintains the representation that contraction will eventually require.

Name rollback and roll-forward boundaries

Each phase should define whether recovery restores code, disables writes, retains both forms, repairs data, or rolls forward.

Rollback is strongest before new-only writes begin. After data is converted or the old column is removed, restoring the binary may produce corruption or immediate query failure. The phase table should therefore move from binary rollback, to disable-new-write and repair, to forward-only recovery. I would rehearse the boundary most likely to be used under pressure, including how feature flags, deployment rollback, DDL rollback, and data repair interact. A nominal down migration is not sufficient if it cannot reconstruct discarded meaning.

Recovery changes shape as the migration advances. Before new-only writes, binary rollback may be safe. After new values appear, rollback may require disabling writes, retaining expanded columns, or deploying a forward adapter. After destructive contraction, restoring an old binary can corrupt or fail immediately. I would publish these boundaries per phase and rehearse the most likely incident response with feature controls, application deploys, DDL state, and repair queries together. A down migration is not a plan if it cannot reconstruct meaning. The incident owner must be able to decide:

• When is binary rollback safe?
• Can old code read converted data?
• What supports repair?
• Who decides roll-forward?

Recovery confidence comes from phase-specific drills that preserve data and service while demonstrating exactly when binary rollback gives way to controlled roll-forward repair.

Contract only with a closure receipt

Removing old fields and compatibility machinery requires proof that consumers migrated, invariants validate, recovery fits, and owners accept the boundary.

Contraction deserves the same review as expansion because it removes the safety net. The gate includes zero observed old traffic, clean invariant validation, a current backup or repair path, accepted rollback limits, and removal of temporary code and observability. I prefer a single closure change that deletes the old field and its translation paths close together, followed by a query proving no stale dependency remains. Leaving compatibility code indefinitely invites later engineers to mistake it for supported behavior and write new consumers against it.

Removing the old representation is a production release with an irreversible edge. The closure packet should include the dependency inventory, supported-pair results, zero-straggler horizon, validation totals, measured DDL behavior, accepted recovery boundary, and owners for temporary machinery removal. I would run one post-change query that proves the remaining schema and application agree, then delete fallbacks, comparison metrics, triggers, and adapters on a planned schedule. If any legacy path still lacks attribution, closure waits. The final approval should leave retained evidence for future maintainers and settle:

• Are legacy callers absent?
• Was the constraint observed under load?
• Can temporary machinery leave?
• What evidence is retained?

The migration closes when schema, applications, scheduled consumers, observability, and the incident runbook all use one supported representation and temporary compatibility machinery has owners for removal.

Contraction example — zero needs a horizon. Seven quiet minutes do not prove a weekly report, mobile client, or disaster-recovery script is gone. The gate should derive its observation period from the slowest supported cadence plus telemetry confidence, with an owner for anything that cannot emit attribution. I would intentionally run scheduled and recovery paths before approval. If an unverifiable consumer remains, the team either extends the budget or creates a deliberate compatibility adapter; it does not translate absence of evidence into permission to drop the old contract.

A compatibility budget teams can operate

My budget would be a one-page control record linked to the migration code. It lists affected objects, data size, old and new reader-writer pairs, maximum overlap, lock and disk estimates, deployment sequence, backfill owner, comparison threshold, and contraction gate. Each phase has a green condition and a recovery action. The purpose is not ceremony; it lets an incident lead decide whether to wait, abort, disable a writer, or roll forward without reconstructing the rollout from chat. The illustrative values in this article, including row counts, version numbers, percentages, and dates, demonstrate the record shape rather than report production measurements.

# scope
customer.status_text → status_code / 1.2B rows
Web, worker, billing export, support query, and replica readers inventoried; writers app-v18, app-v19, and nightly importer.
# budget
mixed versions ≤ 72h / old writes observed by build
Expansion lock sampled on production-like data; backfill chunked separately; comparison alert at 0.01 percent mismatch.
# gate
old writes zero 7d / constraint validated / contract approved
Rollback boundary recorded before column removal; restore drill uses forward adapter; owner and incident path named.

Reading lock evidence without superstition

I would show the exact database version and subcommand documentation beside the rehearsal result because ALTER TABLE is not one lock profile. An additive column, type conversion, constraint validation, and index build can behave very differently. The rehearsal also records what was running concurrently; an idle copy of production data proves little about lock queues. If a concurrent index build fails, the runbook explains how an invalid index is detected and removed before retry. The tradeoff is clear: less write blocking can mean more scans, longer execution, and additional failure states. The budget chooses deliberately instead of treating CONCURRENTLY as a universal safety keyword.

The mixed-version test suite

The most valuable fixtures boot an old writer with a new reader, a new writer with an old reader, and the rollback pair against the expanded schema. They create missing new values, conflicting representations, stale application caches, retried writes, and a backfill racing a customer edit. Assertions compare domain meaning and error behavior, not only database rows. I would run those fixtures before deployment and retain a small production canary comparison during overlap. Once contraction begins, the same suite changes purpose: old pairs must fail at an intentional deployment or compatibility gate rather than produce a surprising query error deep in a request.

Contraction is a product release

Removing the old representation changes rollback, support queries, data exports, and the behavior of any forgotten client. I would schedule contraction with an owner, a quiet-enough operational window, explicit go or no-go evidence, and a post-change validation query. Temporary indexes, triggers, comparison logs, and fallback branches leave in the same planned sequence so they do not become maintenance debris. If one legacy consumer returns after the gate, the recovery path is a forward adapter or controlled repair, not silently recreating the old column and reviving an undefined dual-authority state.

How this differs from a migration brief

The earlier migration-brief article is about framing a database change as product work: consequence, stakeholders, rollout, communication, and recovery. This compatibility-budget article zooms into the overlap contract after that brief approves the direction. It asks which binaries coexist, how representations translate, what database operations consume, and when the old contract may end. I would show both artifacts in a case study because they answer different review questions. Combining them into one giant document usually hides the distinction between deciding that the product should change and proving that a rolling system can survive the transition.

The compatibility horizon I would defend

I would invite an interviewer to challenge the compatibility horizon. Why not deploy all writers at once? Why not dual-write forever? Why is a backfill precondition sufficient under this isolation level? What happens when rollback occurs after a new enum value has been stored? A strong answer names the deployment topology and data semantics before choosing a pattern. It also admits where the plan depends on PostgreSQL behavior and where it belongs to application logic. The signal is not memorizing expand and contract; it is budgeting the messy period between them and proving when it is over.

What a closed migration demonstrates

A closed migration demonstrates that I can connect product cadence to persistent contracts. The evidence includes a dependency inventory, mixed-version fixtures, measured lock behavior, guarded backfill receipts, invariant queries, zero-straggler observation, and a contraction decision. That collection shows restraint: I know when an additive path is safer, when temporary duplication is justified, and when compatibility cost has become risk. It also shows follow-through. The work is not finished when the new application deploys; it is finished when the system, recovery model, and operating team agree on one supported representation.