Zero-Downtime Database Migrations

A database schema is a contract shared by every deployed application instance, background worker, reporting job, and administrative tool that touches it. During a rolling deployment, old and new binaries use that contract at the same time. A migration that is valid for the final version can therefore fail in the transition: a column disappears while old code still reads it, a new constraint rejects rows written by old code, or a backfill competes with production traffic.

Zero-downtime migration is not a special SQL command. It is a deployment protocol that keeps adjacent versions compatible while data and code move through several observable states. The reliable pattern is expand, migrate, contract: add a backward-compatible shape, move reads and writes while both shapes coexist, then remove the old shape only after evidence shows it is unused.

The word “zero” describes user-visible availability, not absence of risk or work. Every DDL statement has engine- and version-specific locking behavior, every backfill consumes resources, and every rollback has a point beyond which it becomes data restoration rather than code redeployment. A safe plan makes those facts explicit before execution.

Treat Compatibility as a Time-Dependent Invariant #

Application and schema versions do not change atomically. Suppose a service has binary versions $A_0$ and $A_1$, and schema states $S_0$, $S_1$, and $S_2$. A rolling deployment can produce combinations such as old code on the expanded schema and new code alongside old code. Compatibility must hold for every combination that can actually occur, not just $(A_0,S_0)$ and $(A_1,S_2)$.

For a conventional rollout, the required window is often:

This table is a more useful design artifact than a single “migration up” script. For each row, list which columns may be null, which writer populates which representation, how reads choose a source, and what rollback means. Include workers and rarely deployed tools; the oldest API pod is not necessarily the oldest schema consumer.

Additive changes are usually compatible, but not automatically harmless. A nullable column can still trigger a table rewrite on some engines when paired with a volatile default. A new index can block writers if built by the wrong mechanism. A trigger can alter latency and failure modes for old code. “Additive” describes logical shape, while operational safety depends on the database implementation and current table size.

The key invariant is dual compatibility, not simultaneous release. At every step, old code must tolerate the expanded schema and new code must tolerate data that old code can still produce. Only after old production writers are gone can the system strengthen assumptions.

Design the Expand, Migrate, Contract Sequence #

The three phases separate changes that are tempting to combine:

Expand introduces the new representation without removing or tightening the old one. Typical actions are adding nullable columns, creating a new table, adding a non-enforced constraint where supported, or building an index with an online/concurrent facility. The old application remains fully functional.

Migrate moves behavior and data. New code may dual-write, backfill existing rows, shadow-read both forms, switch authoritative reads behind a flag, and verify equivalence. This phase can last minutes or months. It is complete only when every live row and writer satisfies the new invariant.

Contract removes compatibility machinery: old columns, triggers, duplicate writes, obsolete indexes, and fallback reads. It is a separate deployment, scheduled after the rollback window and after telemetry proves no old consumer remains.

Some changes need more than one expand or migrate step. Adding a required field safely can be:

1. Add it as nullable without a costly default.
2. Deploy writers that populate it while retaining old behavior.
3. Backfill historical rows in bounded batches.
4. Validate that no nulls remain.
5. Add or validate a not-null constraint using the least disruptive engine-specific path.
6. Deploy readers that assume the invariant.

Likewise, changing a type may require a sibling column rather than an in-place cast. Renaming is usually add-copy-switch-remove, because a direct RENAME COLUMN immediately breaks code compiled against the old name. The temporary duplication is intentional: it purchases compatibility and reversibility.

Do not encode the whole protocol in one transaction. Long transactions retain locks and old row versions, enlarge recovery work, and make progress all-or-nothing. Schema expansion may be transactional where supported, but data migration should usually be resumable and independently observable.

Work an Email Normalization Migration #

Consider a customers table whose original email column stores user input with inconsistent casing and surrounding whitespace. The service needs a canonical address for case-insensitive uniqueness while preserving the entered display form. The target model has email_display and email_canonical; the old email column will eventually disappear.

The migration invariant is:

email_display = the accepted user-facing address
email_canonical = canonicalize(email_display)
all new writes populate both
reads return email_display
email_canonical is unique after conflicts are resolved

The expand phase adds nullable columns. Exact syntax must match the engine and version; the following PostgreSQL-flavored statements illustrate shape, not a universal zero-lock promise:

ALTER TABLE customers ADD COLUMN email_display text;
ALTER TABLE customers ADD COLUMN email_canonical text;

Deploy application version $A_1$ with dual writes. On create or update, it computes both new values and continues writing email so $A_0$ instances and old workers remain correct. Reads still use email. The write should be one database transaction so the representations cannot diverge after a partial application failure.

type EmailFields = Readonly<{
  email: string;
  emailDisplay: string;
  emailCanonical: string;
}>;

function canonicalizeEmail(input: string): string {
  return input.trim().toLowerCase();
}

function fieldsForMixedDeployment(input: string): EmailFields {
  const emailDisplay = input.trim();
  return {
    email: emailDisplay,
    emailDisplay,
    emailCanonical: canonicalizeEmail(emailDisplay),
  };
}

Old writers still populate only email, so dual writing in new code does not close the compatibility window. The backfill copies rows, and a repair loop must revisit rows changed by old code after their batch was processed. One approach is to keep reads on the old field until all old writers are retired, then run a final catch-up pass under an invariant check. Another is a temporary database trigger, but triggers add hidden behavior, operational overhead, and their own removal problem. Choose deliberately.

Backfill by stable primary-key ranges rather than one enormous update:

UPDATE customers
SET email_display = trim(email),
    email_canonical = lower(trim(email))
WHERE id > :after_id
  AND id <= :through_id
  AND (email_display IS NULL OR email_canonical IS NULL);

The application canonicalizer and SQL expression must agree. Better yet, share a precise normalization specification and test vectors; email semantics are more nuanced than this illustrative lowercase rule. Before adding uniqueness, identify collisions such as Ada@example.test and ada@example.test. A migration cannot invent which customer record should own the canonical value. Route conflicts through a product-approved resolution process.

After backfill and conflict resolution, build the unique index using the engine’s online mechanism where available:

CREATE UNIQUE INDEX CONCURRENTLY customers_email_canonical_uq
ON customers (email_canonical);

Then deploy $A_2$ to read email_display, optionally comparing it with email in shadow telemetry first. Retain fallback reads only for a bounded period; permanent fallback makes incomplete migration invisible. Once all old binaries and workers are gone, stop writing email, validate required constraints, wait through the rollback window, and contract by dropping the old column in a later maintenance-aware change.

Run Backfills as Controlled Production Work #

A backfill is a production workload. Even when each batch is small, it competes for CPU, buffer cache, log bandwidth, replication, locks, and autovacuum or equivalent maintenance. “Runs in the background” does not mean “has no foreground effect.”

Make the job resumable. Persist a high-water mark or claim bounded ranges, and make each update idempotent so rerunning a batch is harmless. A primary-key cursor avoids the drift and repeated scanning associated with large OFFSET values. If rows can be inserted behind the cursor or keys are not monotonic, define a second sweep or a predicate-based repair pass.

Throttle on observed database health, not a fixed sleep alone. Useful signals include query latency, lock wait, transaction-log generation, replica lag, dead tuples or versions, CPU, and foreground error rate. Pause automatically when guardrails are crossed. Limit transaction duration and commit each batch so cancellation loses little progress.

The batch size is a control knob, not a constant of nature. Start conservatively, observe representative load, and adjust. Updating 1,000 narrow rows and 1,000 rows with large indexed values can have very different costs. Avoid selecting a batch and then updating it in separate transactions without a claim mechanism; concurrent workers may duplicate work.

Record counts for scanned, changed, already-compliant, conflicted, and failed rows. Progress should be based on eligible records rather than a guessed completion time. Store the migration version and error reason without logging sensitive column contents.

Replication changes the completion definition. A primary can finish while replicas remain far behind, causing stale reads or a dangerous failover. Gate read switching and contraction on replica health and on every serving region having the required schema. If logical replication or change-data capture does not carry a DDL operation automatically, coordinate its consumers explicitly.

Understand DDL Locks Before Execution #

DDL syntax is compact enough to hide its operational consequences. Before running a statement, inspect documentation for the exact database engine and version, then rehearse it against a production-shaped schema and row count. Determine lock mode, lock acquisition behavior, whether existing rows are scanned or rewritten, transaction-log volume, replication effect, and cancellation semantics.

A statement that is fast once it acquires a lock can still be dangerous because it waits behind a long transaction while blocking later requests queued behind it. Set a short lock timeout so the migration fails safely instead of becoming the head of a blocking chain. A statement timeout limits execution after acquisition; it does not replace a lock timeout.

Index construction is a common example. An online or concurrent build reduces write blocking but often takes longer, consumes additional I/O, and may leave an invalid artifact after failure that must be inspected and cleaned up. Constraint validation can sometimes be split: add a constraint without scanning all rows, repair data, then validate it later. Names and capabilities differ, so migration tooling must not pretend all databases behave identically.

Large column drops can be logically quick in one engine and rewrite data in another. Even metadata-only operations may invalidate prepared statements, change query plans, or break replication consumers. Run EXPLAIN or plan inspection for affected queries after indexes and constraints change. A new index is not automatically selected, and removing an old one may expose an unanticipated access path.

Migration runners need single-owner coordination, but a global lock is not a substitute for safe statements. Ensure only one process advances a schema version, record checksums for immutable migration files, and fail when applied history has been edited. Application startup should check compatibility ranges rather than racing to migrate from every replica.

Sequence Deployments and Rollbacks Explicitly #

Write the runbook as a state machine with gates. For the email example:

Each arrow needs entry criteria, observable completion, an owner, and a reversal. Before the read switch, rollback is generally straightforward: redeploy old-reading code while dual writes continue. After new-only writes begin, rolling back to old code can lose visibility of updates unless the old representation is still maintained. After dropping the old column, code rollback may be impossible without a reverse migration and restored data.

This produces a rollback frontier. Mark it in the change plan and require explicit approval before crossing it. A feature flag can make read authority reversible, but only if both representations remain synchronized. Flags do not repair lost data.

Coordinate schema deployment independently from application deployment. Expansion should complete everywhere before new code assumes the shape. Contraction should happen after deployment telemetry confirms zero old versions, queues contain no jobs serialized with old assumptions, scheduled tasks have run on the new version, and external consumers have migrated.

Roll forward is often safer than rollback after data transformation. Prepare a repair release that can disable new behavior while preserving newly written data. Back up before destructive phases, but do not call an untested backup a rollback plan. Measure restoration time and rehearse restoring the relevant tables and dependent objects.

Test the Transitional States, Not Just the Destination #

Migration tests often create the latest schema from scratch and run the latest application. That verifies the least interesting combination. Test the actual path from a representative old database, including dirty data, large cardinalities, interrupted backfills, old workers, and mixed application versions.

A compatibility suite should exercise:

• Old code reading and writing after expansion.
• New code reading rows written only by old code.
• Old code reading rows written by dual-writing code.
• Backfill restart after failure in the middle of a batch.
• Concurrent user updates while the same rows are backfilled.
• Duplicate and malformed legacy values that violate the target invariant.
• Schema changes under a deliberately held transaction to observe lock timeout behavior.
• Replica or change-data-capture consumers receiving the intermediate shape.
• Rollback on both sides of every marked frontier.

Use production-shaped data distributions with synthetic or sanitized values. Row count alone is insufficient: null frequency, duplicate keys, wide values, skew, and index selectivity determine behavior. Time measurements from rehearsal are estimates under stated conditions, not guarantees; the purpose is to expose rewrites, lock classes, and order-of-magnitude risk.

Contract checks can run continuously during migration. Count rows where one representation is null, canonical values disagree, or duplicate groups remain. Compare sampled reads from both paths and alert on divergence. Before adding a strict constraint, run the exact predicate that the constraint will enforce and require zero violations over an agreed observation period.

Finally, prove that contraction is safe. Search deployed code and job definitions for the old field, but supplement static search with database query telemetry, column access auditing where practical, and version inventory. Dynamic SQL and external tools can evade repository search. Remove fallback instrumentation only after the old path has truly gone quiet.

Operate Failure Modes Deliberately #

The most common failure is premature contraction: an old process, delayed job, or rollback still needs the removed field. The remedy is organizational as much as technical. Assign one migration owner, publish the compatibility window, inventory consumers, and make destructive cleanup a separately reviewed change.

Dual writes can diverge when one write succeeds and another fails. Keep both representations in one transaction when they share a database. When they cross systems, the task is no longer a simple schema migration; it needs durable change capture, reconciliation, and explicit consistency semantics. Do not hide distributed dual writes behind a helper that implies atomicity.

Backfills can overwrite fresh user changes. Use predicates that update only unmigrated rows, derive new values from the row version being changed, and consider optimistic version checks. If canonicalization logic changes during the run, pin the migration version or restart deliberately; otherwise identical rows can receive different transformations.

Constraints can reveal legitimate legacy exceptions. Decide whether to repair, quarantine, or model those exceptions rather than weakening the constraint silently. Index builds can fail on duplicates or disk exhaustion. Monitor temporary space and retain a cleanup procedure for partial artifacts.

The tradeoff of expand/migrate/contract is temporary complexity: extra columns, code paths, storage, telemetry, and deployments. That cost is real, but it is bounded and removable. Direct destructive migration is simpler only on the diagram; during a rolling production change, it transfers complexity into outages and emergency restoration.

Zero-downtime evolution succeeds when every intermediate state is a supported state. Expand without breaking old code, migrate data with resumable controlled work, switch authority only after measured equivalence, and contract after the rollback window closes. The schema then evolves as a protocol with evidence, rather than a sequence of hopeful SQL files.