A database migration runs while old processes, new processes and existing data coexist. Expand-contract is useful because it treats that overlap as the normal state of deployment. My focus is the operational contract: which combinations can run, what work the database must perform and what evidence permits the next irreversible step.
Define continuity in terms of user operations
Zero downtime is too vague to serve as an acceptance criterion. A deployment can keep every process alive while requests wait behind a schema lock until they time out. It can preserve reads while silently rejecting writes from an old worker. I would define the objective using successful operations and latency: which endpoints must continue, what error budget applies and which background jobs may pause. That turns the migration plan into a testable service contract rather than a promise that no machine will intentionally restart.
PostgreSQL's ALTER TABLE documentation shows why physical operation details matter: subcommands take different locks and some changes require substantial data work. An apparently small schema edit can have a large effect on a busy table. I would check the exact database version, subcommand and existing constraints before predicting its impact. The migration description should name the expected lock, whether a rewrite or scan occurs, and what the process does if it cannot acquire the lock quickly. Those details determine whether an otherwise compatible change can coexist with production traffic.
References: [1] PostgreSQL ALTER TABLE
Expand the set of valid application versions
Suppose an illustrative application replaces a legacy display_name column with preferred_name. Renaming the column immediately makes old code invalid. Expanding first adds the new representation while preserving the old one, allowing a compatibility release to understand both. The transition should specify which field is authoritative at each stage and how writes keep the representations aligned. If both columns live in the same database, updating them in one transaction is materially simpler than introducing a cross-system dual write. Compatibility is a property of concrete read and write paths, not just the presence of two columns.
The table assumes the new representation can express every old value. During the initial compatibility rollout, reads still use the old column: an old writer can change it without updating an already populated new column, so a null-only fallback is insufficient. Retire those old-only writers before convergence and read cutover, or install a separately validated synchronisation mechanism. Rollback also requires compatible values. Inventory asynchronous workers, scripts and scheduled jobs alongside web processes; a deployment is not fully migrated merely because the main HTTP fleet is new. The oldest active writer determines the transition's safe boundary.
| Phase | Readers | Writers | Removal allowed |
|---|---|---|---|
| Expand | Read old column | Old writers remain valid | No |
| Compatibility | Keep old column authoritative | New code maintains both; retire old-only writers | No |
| Migrate and verify | Compare representations | All active writers maintain both columns | No |
| Cut over | Read new representation | Preserve rollback compatibility | No |
| Contract | Only supported new readers | Only supported new writers | After old dependencies and rollback window are retired |
References: [2] GitLab batched background migrations
Separate schema installation from data convergence
Adding a nullable column can be a short operation while populating hundreds of millions of rows is a long one. Combining them into one deployment transaction can turn a manageable transition into a large lock, logging and rollback event. GitLab's batched background migration guidance treats substantial data changes as controlled background work with explicit lifecycle management. My interpretation is to make convergence independently observable: total eligible rows, completed ranges, mismatches and remaining work should be measurable without relying solely on a worker's claim that it finished.
The backfill must coexist with live writers. An old snapshot should not overwrite a newer preferred_name chosen by a user during the migration. Use a source version, a conditional update or another clearly defined conflict rule, and make restarting a batch safe. This essay's focus is the deployment boundary rather than the full backfill controller, but the connection is important: a release cannot assume the new column is complete just because the migration job was started. The read path needs a valid fallback until evidence establishes that the relevant data has converged.
References: [2] GitLab batched background migrations
Install constraints in stages where the engine supports it
PostgreSQL supports adding certain constraints as NOT VALID and validating existing data later. For a CHECK constraint, new or changed rows are still checked after the constraint is installed; validation handles the existing population separately. This can make the expensive verification phase easier to schedule, but it is not a general promise that every constraint change becomes lock-free. The exact command and lock mode remain important. I would use the documented mechanism deliberately, with a short acquisition timeout and a plan for violations discovered during validation.
The illustrative SQL below assumes the column already exists and the compatibility writers now populate it correctly. Each transaction has a local lock timeout so it does not wait indefinitely to acquire a contested lock. The validation can still consume resources and must be monitored. Turning the result into a NOT NULL declaration or removing the old column is a later decision, after checking the relevant engine behaviour and application dependencies. Treating successful validation as permission to contract every related interface would skip the separate question of which deployed processes still use the old schema.
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE profiles
ADD CONSTRAINT profiles_preferred_name_present
CHECK (preferred_name IS NOT NULL) NOT VALID;
COMMIT;
-- A later, monitored migration step:
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE profiles
VALIDATE CONSTRAINT profiles_preferred_name_present;
COMMIT;References: [1] PostgreSQL ALTER TABLE
A waiting migration can become part of the outage
A schema operation waiting for a strong lock can interact badly with a busy queue of transactions. Even before it begins its intended work, its position in the lock queue can affect later requests. I would inspect long-running transactions and define a short lock-acquisition budget instead of allowing a deployment command to wait without a deadline. If acquisition fails, the safe response may be to retry during a quieter interval after investigating the blocker. Repeatedly retrying at high frequency can itself create operational noise and contention.
The deployment system also needs a clear interpretation of partial completion. Some operations can leave an object present but not usable in the intended way, and some schema tools execute several statements with different transactional properties. Record durable phase completion and inspect actual catalog state when resuming. A migration identifier in a tool's history table is helpful, but it should not replace validation of the expected database state after an interrupted operation. The recovery procedure should say whether to continue, clean up a partial object or roll back the application while leaving the expanded schema in place.
References: [1] PostgreSQL ALTER TABLE
Contract only after evidence removes the old dependency
The contract phase removes compatibility, so it needs stronger evidence than a successful canary. Check that old versions are gone, scheduled jobs have run on supported code and migration lag is zero within the chosen definition. Before contraction, the rollback target remains the compatible dual-writing release; restoring an old-only writer requires reversing the read cutover first. Query telemetry can identify remaining readers, but its observation window must cover infrequent jobs. I would also search source and operational scripts because absence of observed access during a quiet hour is not proof of absence.
The counterargument is that long compatibility windows create complexity and duplicated state. That is true, and expand-contract should not become expand forever. Give each transition an owner, exit criteria and a planned removal window. Small low-risk tables may justify a simpler coordinated maintenance period when the product permits it. The method is valuable when continuity matters and versions overlap, not as a ritual for every local development change. The objective is to retire old assumptions deliberately, with enough evidence that removal does not convert an otherwise recoverable deployment into a user-visible failure.
References: [2] GitLab batched background migrations
Rehearse the overlaps, not only the final schema
I would test old code against the expanded schema, compatibility code against partially migrated data and new code while old workers still write. Then interrupt the backfill, restart it and attempt an application rollback before contraction. These combinations are the migration's actual operating states. A test suite that only creates the final schema from scratch can miss every one of them. Include representative table size and transaction duration when assessing locks, because functional correctness on an empty database says little about production continuity.
The acceptance record should show both semantic and operational evidence: values remained interpretable, writes stayed consistent, constraints validated, latency remained within budget and old dependencies were retired. Expand-contract works by turning one risky transition into several smaller transitions whose preconditions can be checked. It does not make expensive database work disappear or guarantee that any arbitrary change can happen without interruption. Its value is that the application remains in a defined compatible state while the team performs, observes and, when necessary, stops each piece of the work.
References: [1] PostgreSQL ALTER TABLE[2] GitLab batched background migrations
Sources and further reading
- PostgreSQL ALTER TABLE
Primary documentation defines lock and constraint-validation semantics. The staged migration and timeout choices are illustrative and require version-specific planning.
- GitLab batched background migrations
Primary engineering documentation describes controlled background data migration. The profile-column rollout and acceptance criteria are this essay's independent example.