Coexistence Is the Constraint: Migrating Schemas While Two Versions Serve Traffic
Rolling deploys turn a database schema into a shared contract between versions. During any rollout, version N and version N+1 run at the same time. One process may still read the old column while another writes the new one. From first principles, a migration is not a file that runs once; it is a sequence of states that every running version must survive.
That is why the riskiest schema changes are rarely syntactically wrong. They are temporally wrong. The SQL may be valid, the local test may pass, and the staging deploy may look clean. Then production rolls one pod at a time, and for several minutes the database must satisfy two incompatible expectations. The expand-contract pattern exists to make that overlap explicit and survivable.
The Deployment Window Is the Real Schema
A database schema is what the code assumes, not only what the catalog reports. If version N expects customers.email to be present and version N+1 expects customers.email_normalized, then the true schema during rollout is the union of both assumptions. Any change that removes an assumption before the last process holding it is gone will fail somewhere.
The practical rule is simple: add before you use, use before you require, and remove only after nothing depends. Each step buys a compatibility window. The engineering work is deciding how wide that window must be and what evidence closes it.
A compatibility matrix helps. List every running version, every table it touches, and whether it reads or writes the affected shape. If version N reads email and version N+1 writes both email and email_normalized, the matrix shows a safe overlap. If version N+1 stops writing email while version N still reads it, the matrix shows a production incident waiting for a traffic split.
Phase 1: Expand With Additive Changes Only
The expand phase introduces the new shape without breaking the old one. Add columns as nullable or with safe defaults. Create new tables rather than altering existing ones in place. Add indexes concurrently where the database supports it, and avoid long locks on large tables.
For PostgreSQL, an additive step might look like this:
ALTER TABLE customers ADD COLUMN email_normalized text;
CREATE UNIQUE INDEX CONCURRENTLY customers_email_normalized_key
ON customers (email_normalized)
WHERE email_normalized IS NOT NULL;
The old application version ignores the new column. The new version can begin writing it once the expand deploy is complete. Nothing has been removed, so rollback remains a code-only operation.
This phase also sets the rollback checkpoint. If the new column causes trouble, the team can deploy the previous application version and leave the column in place. An unused nullable column is harmless; a dropped column is not.
Phase 2: Dual-Write and Backfill in Batches
Dual-writing means the new application version writes both the old and new shapes for a period. The old column remains authoritative for old readers. The new column becomes populated for the future.
function saveCustomer(customer) {
const normalized = normalizeEmail(customer.email);
return db.update('customers', customer.id, {
email: customer.email,
email_normalized: normalized
});
}
The backfill handles rows that existed before dual-write began. It must be idempotent, throttled, and restartable. Batch by primary key rather than offset, so concurrent inserts and updates do not cause skipped rows.
UPDATE customers
SET email_normalized = lower(trim(email))
WHERE id IN (
SELECT id
FROM customers
WHERE email_normalized IS NULL
ORDER BY id
LIMIT 500
);
Run the batch until it affects zero rows. In production, pause between batches and watch replication lag, lock waits, and query latency. A backfill is not a migration step that happens once; it is a workload that competes with user traffic.
The rollback checkpoint here is still favorable. Old code continues to read email. New code writes both. If the backfill must stop, the system remains consistent enough for the old version to operate.
Phase 3: Move Reads and Verify
Writes alone do not prove the new shape is correct. Reads must move deliberately. A common approach is shadow reading: the new code reads the new column, compares it with the old value, and logs mismatches without changing behavior. Once mismatch rates are understood, the new column becomes the primary read source.
This is the phase where teams discover normalization edge cases, legacy nulls, and rows that predate validation rules. The comparison does not need to block requests. It needs to produce evidence.
After reads move, add constraints in a way that does not scan and lock the table abruptly. In PostgreSQL, add a check constraint as NOT VALID, then validate it separately:
ALTER TABLE customers
ADD CONSTRAINT customers_email_normalized_present
CHECK (email_normalized IS NOT NULL) NOT VALID;
ALTER TABLE customers
VALIDATE CONSTRAINT customers_email_normalized_present;
Validation still takes a lock, but a weaker one. Schedule it with the same care as the backfill.
Phase 4: Contract Only With Evidence
Contracting is the removal of the old shape. It is the only phase that is genuinely hard to reverse. Before dropping email, the team needs evidence that no running version reads or writes it.
That evidence is usually a combination of deployment history, query logs, and code search. Confirm that all instances run a version that no longer references the old column. Confirm that no background job, reporting query, or external integration still depends on it. Then remove the old column in a separate deploy.
ALTER TABLE customers DROP COLUMN email;
The contract deploy should be boring. If it feels risky, the earlier phases did not produce enough evidence. A useful checkpoint is a short observation window after the last old version is gone. During that window, the new shape must handle all reads, writes, and scheduled jobs without the old column.
A Practical Checklist for Zero-Downtime Teams
- Map every running version and its read/write dependencies before writing SQL.
- Expand with nullable columns, new tables, and concurrent indexes.
- Deploy dual-write code before starting the backfill.
- Backfill in idempotent, keyset-paginated batches with throttling.
- Move reads behind a comparison or shadow phase.
- Add constraints as not-valid first, then validate separately.
- Treat contract as a separate deploy after deployment history proves the old shape is unused.
- Keep rollback checkpoints at expand, dual-write, and read-move phases.
The pattern is not a ritual. It is a way to make time visible in schema design. A column can be added in seconds, but its removal must wait for every process that still believes in it. Zero-downtime migration is the discipline of letting the old shape earn its exit.