FDEBench Join us live

Q
139 / 159

Serving & inference

Add a column to a 300 million row table that is serving traffic 24/7, with zero downtime. Walk me through the exact sequence.

question bank FDEBench bank

01
Ask

What a strong candidate asks first

Before designing anything. Skipping this is the most common way the round is lost.

  • Nullable, or NOT NULL with a computed default? Completely different operations.
  • Which database and version? Postgres 11+ changed what a default costs.
  • Are there long-running analytics queries against this table right now?
  • Does the application need to read the new column immediately, or can the backfill lag?

02
Rubric

What earns the points

7 criteria, 100 points total. Scored independently, partial credit allowed.

Asks if the change rewrites rows 20 pts

Distinguishes the metadata-only case (nullable, or a constant default in modern Postgres) from the rewriting case (NOT NULL with a computed default, type change), and asks which one this is.

If you miss it You treated all ALTER TABLE the same. Adding a nullable column is a catalogue change measured in milliseconds; adding NOT NULL with a computed default rewrites 300 million rows. Establish which you're doing before designing around it.
Sees the lock queue is the risk 25 pts

Identifies that the outage comes from the ACCESS EXCLUSIVE lock queuing behind a long-running query, with all subsequent traffic queuing behind the ALTER. Full credit requires the queue, not just "it takes a lock".

If you miss it You blamed row rewriting. The DDL itself is instant — the outage is the lock queue: your ALTER waits behind one long query, and every read and write arriving after it waits behind your ALTER. That cascade is the incident.
Sets a short lock timeout 15 pts

Sets a short lock_timeout (one to two seconds) on the migration session so a failed acquisition aborts harmlessly, with the tool retrying until a quiet window opens.

If you miss it Guard the DDL with a short lock_timeout. Then a failed acquisition costs you a retry instead of an outage — this single setting is the difference between a blip and a page.
Ships it in reversible steps 15 pts

Gives the sequence in order: add nullable column, deploy dual-write, batched backfill, verify, switch reads, then drop the old path in a later release. Full credit requires each intermediate state to be independently deployable and reversible.

If you miss it You did it in one step. Expand, migrate, contract — and each stage has to be shippable and rollback-able on its own. It is combining them into one migration that causes the downtime.
Backfills in small batches 12 pts

Backfills in small batches with pauses, and explains why one large UPDATE is wrong: a single enormous transaction bloats WAL, blocks vacuum, and cannot be interrupted safely.

If you miss it A single UPDATE over 300 million rows is one giant transaction — WAL bloat, vacuum blocked, and no safe way to stop it halfway. Batch it with pauses and make it resumable.
Adds indexes without locking 8 pts

Builds indexes CONCURRENTLY and adds constraints as NOT VALID then VALIDATE, to avoid long exclusive locks.

If you miss it If this column needs an index or a constraint, the naive form locks the table again. CREATE INDEX CONCURRENTLY, and add constraints NOT VALID then VALIDATE separately.
Checks the copy before switching 5 pts

Verifies old against new — row counts, checksums, or a sampled comparison — before switching reads.

If you miss it You switched reads without checking the backfill was complete and correct. Compare counts or checksums between old and new before the cutover.

03
Flags

Red flags

Reported separately, not subtracted — this is the "what would have ended the interview" note.

  • Proposes a maintenance window as the primary plan.
  • Runs one UPDATE over all 300M rows.
  • Says "just add the column, it's instant" without mentioning the lock queue.
  • Assumes staging would have caught it.

—