The Database Migration Interview Answer: Prove Old and New Code Can Coexist

The answer to “How would you migrate this table without downtime?” is not “run a migration carefully.” It is: make every deployed version of the application tolerate both schemas, observe the transition, then remove the old path last. That sequence is testable in a small program, which makes it much easier to explain under interview pressure.

This is a practical backend and TPM interview problem because it exposes whether you can separate application compatibility from database change. A column rename that works in a quiet development database can still fail in production when old workers, queued jobs, or a slow canary release are reading the previous column.

Here is the four-stage model I use.

What makes a migration safe during a rolling deploy?

Suppose a users.full_name column needs to become users.display_name. The dangerous plan is:

1. Rename or drop full_name.
2. Deploy code that reads display_name.

Between those two steps, old application instances can still be serving traffic. The inverse is dangerous too: deploy code that requires display_name before the column exists everywhere it needs to.

A safer order is deliberately boring:

Stage	Database state	Application behavior	Exit signal
Expand	Add nullable display_name	Keep reading full_name	Migration succeeded
Compatible deploy	Both columns exist	Read new value, fall back to old	All versions can handle both
Backfill	Copy old values in bounded batches	Optionally write both values	Remaining old rows reaches zero
Contract	New column is authoritative	Stop fallback, later drop old column	Old readers and jobs are gone

The interesting part is the compatibility deploy. It must happen before the backfill is complete, and it must not assume every row has already moved.

Can you prove the compatibility contract?

You do not need a database server to rehearse the key invariant. This dependency-free Node.js example models the temporary state and verifies two things:

• an old row remains readable;
• no row needs the old field once the backfill completes.

import assert from "node:assert/strict";

const rows = [
  { id: 1, full_name: "Ada Lovelace", display_name: null },
  { id: 2, full_name: "Grace Hopper", display_name: "Grace" },
];

const visibleName = (row) => row.display_name ?? row.full_name;
const nextBatch = (afterId, size) =>
  rows.filter((row) => row.id > afterId && row.display_name === null).slice(0, size);

assert.equal(visibleName(rows[0]), "Ada Lovelace");
assert.equal(visibleName(rows[1]), "Grace");

let cursor = 0;
for (;;) {
  const batch = nextBatch(cursor, 1);
  if (!batch.length) break;

  for (const row of batch) row.display_name = row.full_name;
  cursor = batch.at(-1).id;
}

assert.deepEqual(rows.map(visibleName), ["Ada Lovelace", "Grace"]);
assert.equal(rows.filter((row) => row.display_name === null).length, 0);

console.log("compatible reads and batched backfill: ok");

Run it with node migration-contract.js. The point is not that an in-memory array is a backfill system. The point is that the interview answer has a concrete contract: during transition, reads have a fallback; after transition, the fallback has no remaining work.

In production, use a monotonic cursor or a stable key for batches. Avoid a single giant update that holds locks or produces an unbounded write spike. The PostgreSQL ALTER TABLE documentation is worth reading before you state what a specific DDL change will do: lock behavior and rewrite costs depend on the operation and database version.

What should the backfill measure?

A migration is not complete because the script exited with code zero. I would name at least four observable signals:

• rows_remaining: rows where display_name IS NULL;
• batch duration and rows per batch;
• application errors split by application version;
• reads that took the fallback path.

That last metric is especially useful. Once it stays at zero for a defined window, it tells you something stronger than “the backfill job finished”: the live request path is no longer relying on the old representation.

For a high-write table, I would also say how the job yields. For example: commit each small batch, cap concurrency at one or a few workers, pause when replication lag or database latency crosses a threshold, and make each batch idempotent. The exact numbers come from the workload, not from a memorized rule.

What are the failure modes interviewers expect you to name?

A good answer gets more specific after the happy path.

Old workers are still alive. A long-running worker can wake up after the web fleet has rolled. Keep the old column until every consumer version, including scheduled jobs and rollback targets, has aged out.

New writes arrive while backfill runs. Either dual-write temporarily or make the new write path authoritative and have the backfill only fill nulls. Say which source wins if the two values disagree. “We will merge later” is not a conflict policy.

The backfill restarts halfway through. Make the operation safe to repeat. A row already copied should be skipped or overwritten with the same deterministic value, not duplicated or corrupted.

The constraint is added too soon. A NOT NULL constraint belongs after the data check, not before it. Validate first, enforce second.

Rollback is needed. Expand-and-contract makes rollback less dramatic: code can return to the fallback read while both fields still exist. Once the old column is removed, rollback requires a different plan, so that step should be intentionally delayed.

How would I say this in an interview?

Here is a concise version that still shows judgment:

“I would treat the rename as a compatibility problem, not a one-shot DDL change. First I add the new nullable column. Then I deploy code that can read the new value or fall back to the old one, so mixed application versions are safe. I backfill in small, observable, idempotent batches and track remaining rows plus fallback reads. When those are zero and old workers are drained, I enforce the new contract. Only in a later release do I remove the old column. That gives me a meaningful rollback window.”

Then stop and let the interviewer probe. If they ask about a multi-service program, add ownership: one team owns schema and backfill telemetry, consuming teams report their last compatible release, and the contract stage has an explicit go/no-go review. That is the same sort of dependency sequencing that shows up in TPM loops; aceround.app — AI interview assistant is useful for rehearsing the follow-up questions aloud, not for replacing the technical reasoning.

A 20-minute practice routine

1. Pick one real change: rename a field, split a column, or add a required attribute.
2. Write the four stages in a note before drawing an architecture.
3. State the compatibility invariant in one sentence.
4. List the metric that says it is safe to contract.
5. Have someone interrupt with “what if the job dies at 40%?” and answer without changing the invariant.

This works better than memorizing a migration checklist because the trade-off stays visible: keeping two representations temporarily costs complexity, but deleting one representation too early costs availability.

Sources and disclosure

• PostgreSQL, ALTER TABLE reference, for operation-specific DDL behavior.
• Amazon Jobs, Technical Program Manager interview preparation, for the role context around sequencing dependencies and communicating risk.

AI was used to help draft and edit this article. The migration model, code, technical claims, and sources were reviewed by the author before publication.