Zero-Downtime Database Migration Interview Questions: Expand, Backfill, and Contract

Zero-downtime database migration is a compatibility problem distributed over time. The schema, old application instances, new application instances, background workers, and replicas do not switch versions in one atomic moment. A strong interview answer therefore proves which combinations can coexist during each deployment instead of reciting “expand, migrate, contract.”

This guide uses a concrete users.full_name split into first_name and last_name. For an adjacent state-transition drill, practice Implement an Idempotent Versioned Database Update.

Evidence boundary: PostgreSQL and GitLab documentation support the database and rollout behavior cited below. Public candidate reports were not consistent enough to support employer-specific claims.

State the invariant before the steps

Assume the old application reads and writes full_name. The new application wants first_name and last_name. During a rolling deploy, both versions may serve traffic. The core invariant is:

Every accepted write must remain readable with equivalent meaning by every application version that can still receive traffic.

That sentence exposes why a direct rename or drop is dangerous. If deployment starts with new code that expects new columns, old schema breaks it. If schema drops full_name while old pods remain, old code breaks. If both columns exist but writes update only one representation, readers disagree.

Build a compatibility matrix before touching data:

Phase	Old app reads/writes	New app reads/writes	Schema	Safe condition
Baseline	full_name	not deployed	old column	existing behavior
Expand	old column	old column, optionally shadow new	old + nullable new	no required new data
Dual compatible	old column	both with defined precedence	both	every write keeps representations coherent
New read	still possible	new columns	both	backfill complete and lag monitored
Contract	removed	new columns	new only	no old binaries, jobs, or rollback path remain

“Zero downtime” does not mean zero risk or zero performance impact. It means the migration does not require an intentional outage and preserves the stated availability/correctness objectives under the tested rollout.

Phase 1: expand without requiring new behavior

Add nullable columns first:

ALTER TABLE users ADD COLUMN first_name text;
ALTER TABLE users ADD COLUMN last_name text;

The exact lock and table-rewrite behavior depends on the database version and operation, so inspect the actual platform documentation and production-sized rehearsal. Do not bundle many unrelated changes into one transaction merely because the syntax is short.

Deploy code that can operate with the expanded schema. A safe transitional writer might write both old and new representations. A transitional reader should have an explicit precedence rule, such as “read new fields when a migration marker says they are authoritative; otherwise derive from full_name.” COALESCE alone can hide partially migrated or semantically inconsistent rows.

If dual writes happen in application code, think about atomicity. Updating the old field and then the new fields in separate transactions creates visible disagreement. A database transaction, trigger, or single authoritative write path can keep them together, but each option has operational and ownership trade-offs.

Phase 2: backfill without racing live writes

A naive job scans every row and overwrites the new columns. A live request can update a user after the job reads the old value but before it writes the derived value, causing the backfill to erase fresher data.

Use a guarded update:

UPDATE users
SET first_name = :derived_first,
    last_name = :derived_last
WHERE id = :id
  AND full_name = :source_value
  AND first_name IS NULL
  AND last_name IS NULL;

The predicate turns a stale read into a harmless zero-row update. Exact guards depend on the source contract; version columns or compare-and-set tokens are often stronger than comparing a free-form name.

Batch by a stable key, keep a durable checkpoint, bound transaction size, and make every batch replay-safe. Measure rows scanned, rows changed, guard misses, errors, lock wait, replica lag, and application latency. Rate limits should respond to production pressure rather than a fixed sleep chosen on a laptop.

GitLab's migration guidance recommends background migrations for large tables so work is spread over time. It also separates schema rollout from data migration, which lets teams observe each phase before relying on it.

Prove completion instead of trusting the job status

“The backfill finished” means the scheduler has no more planned batches. It does not prove the data invariant. Reconcile independently:

SELECT count(*)
FROM users
WHERE first_name IS NULL
   OR last_name IS NULL;

That query is only a starting point. A one-word name may legitimately have no last_name; a parser may be unable to split a name correctly; null may be valid. Define a migration marker or domain-specific validity rule rather than treating every null as failure.

Compare source and target meaning on a sample and on deterministic aggregates. Track dual-write disagreements. Verify late writes and paths that bypass the primary service, including imports, admin tools, scheduled jobs, CDC consumers, and old mobile clients if they write directly through a versioned API.

Only switch reads after reconciliation meets a published threshold and remains stable for an observation window. Keep dashboards split by application version so a small old fleet does not disappear inside totals.

Contract is a separate release decision

Before dropping full_name, prove:

• no running binary, worker, migration, report, or rollback artifact reads it;
• all supported writes update the new representation;
• the new reader has been live long enough to expose correctness and performance problems;
• rollback no longer requires the old column, or a deliberate roll-forward recovery exists;
• replicas and downstream schemas have caught up;
• backups and restore procedures understand the new shape.

GitLab documents multi-release procedures for destructive changes because running code and schema caches can retain references to a removed column. The principle generalizes: remove code dependencies, observe, then remove data structure.

A contract migration can be irreversible in practice even if the DDL has a syntactic DOWN migration. Dropping a populated column destroys information. A reliable answer says how to restore from backup or reconstruct data and whether that recovery meets the incident deadline.

Handle indexes and constraints safely

Adding a uniqueness or not-null constraint can scan data, block writes, or fail on dirty rows. In PostgreSQL, a check or foreign-key constraint can be added NOT VALID so new changes are enforced while existing rows are validated later with VALIDATE CONSTRAINT. The precise lock modes still need review.

For an index on a busy PostgreSQL table, CREATE INDEX CONCURRENTLY avoids locking out ordinary writes, but it performs extra work, cannot run inside a transaction block, and may leave an invalid index if it fails. Your runbook must detect and clean up that state. For a unique constraint, a common staged approach is build a unique index concurrently, validate data assumptions, then attach it as a constraint where supported.

Do not say “online DDL” as if it were cost-free. CPU, I/O, WAL, replication lag, and long-lived transactions can turn a nonblocking operation into an availability incident.

Answer failure and rollback questions

If the backfill fails halfway, rerun from a durable checkpoint with idempotent guards. If new code fails before read cutover, roll back code while leaving additive columns in place. If it fails after some new-only writes, old code may no longer interpret state; that is why the compatibility design must preserve old reads until the rollback window closes.

After contract, rolling back the binary can be unsafe. Prefer a forward fix or re-expand the removed field with a deliberate reconstruction plan. State this asymmetry explicitly.

For a multi-region system, include region skew. One region may run new writers while another runs old readers. Replication order can expose schema before code or code before schema. Gate rollout on regional compatibility and monitor replication lag, not just deployment completion.

Interview prompts and strong answer outlines

Cover CDC, caches, and analytical readers

A migration is not complete when only the primary application works. Change-data-capture connectors may encode column positions or schemas; caches may contain the old representation; ETL jobs and BI queries may select *; search indexes may be populated from either field. Inventory consumers before expand and add contract tests for the ones that can break silently.

Schema registries or versioned events help downstream systems migrate independently. If the database change also changes an event payload, retain old event fields until consumers acknowledge the new version. Do not assume a database transaction makes a message and row update atomic unless an outbox or equivalent protocol provides that guarantee.

During backfill, cache invalidation deserves its own plan. A row can be correct while a cache still serves the old shape. Version cache keys, invalidate on authoritative writes, or tolerate both representations during the observation window. Measure stale reads rather than assuming expiration is fast enough.

“How do you rename a heavily used column?” Add the new column, deploy compatible reads/writes, backfill with race guards, reconcile, switch reads, remove old writes and readers, observe, then drop in a later release.

“Why not use one transaction?” A giant transaction can hold locks, generate heavy WAL, delay vacuum, and create a difficult rollback. More importantly, it cannot atomically update all concurrently running application versions.

“How do you prevent backfill races?” Use a stable source version or compare-and-set guard, make batches idempotent, and let live writes establish authoritative new state.

“What is your rollback point?” Name it per phase. Additive schema can stay during code rollback. Read cutover can revert only while old representation remains coherent. Destructive contract usually requires roll-forward or data restoration.

“When is it safe to drop?” After code and traffic evidence show no dependency, data reconciliation passes, rollback policy changes deliberately, and a separate contract release survives canary and observation.

A verification plan interviewers can challenge

Test old app with old schema, old app with expanded schema, new app with expanded schema before and after backfill, mixed old/new writes, job restart, stale backfill write, replica lag, and rollback at each phase. Add a contract test that scans deployment artifacts or query telemetry for old-column access.

Use a canary tenant or partition, but do not infer global safety from a tiny quiet cohort. Exercise high-write keys because races appear where live mutation overlaps the backfill. Rehearse on production-scale statistics so lock duration and replication effects are meaningful.

A concise answer is: “Expand creates an additive schema both versions tolerate. I deploy compatible reads and atomic dual writes, backfill in replay-safe batches with stale-write guards, and reconcile meaning independently. I switch reads only after observation. Contract is a later destructive release after dependency and rollback evidence prove the old shape is no longer needed.”

Sources and Further Reading

• GitLab: Avoiding downtime in migrations
• GitLab: Migration Style Guide
• PostgreSQL: ALTER TABLE
• PostgreSQL: Building Indexes Concurrently