Adding a Column to a 300-Million-Row Table Without Downtime

"How would you add a new column to a table with 300 million rows that is being read and written 24/7, without any downtime?" This is a classic database interview question, and it is really a test of one thing: do you understand that a schema change is not a single action but a migration — a sequence of small, safe steps. The wrong answer is a single confident sentence. The right answer is a plan. This guide walks through that plan, explains why each step is shaped the way it is, and then answers the question everyone forgets to ask: does this actually work the same way on every database?

1. The Trap: One Giant Transaction

The instinctive answer is to do it all at once:

-- ❌ The dangerous way
BEGIN;
ALTER TABLE orders ADD COLUMN region TEXT NOT NULL DEFAULT 'unknown';
UPDATE orders SET region = derive_region(...);  -- 300 million rows
COMMIT;

Why this is a disaster on a live table:

• It locks a huge number of rows (or the whole table). A statement that touches 300 million rows holds locks for as long as it runs — minutes to hours.
• Live traffic stalls behind the lock. Every user INSERT/UPDATE/SELECT ... FOR UPDATE that needs those rows waits for the lock to release. From the user's side, the app is "down."
• It is all-or-nothing. If it fails at row 250 million, the whole transaction rolls back and you have burned hours of I/O for nothing, plus a bloated write-ahead log.

The core mistake is treating a heavy migration as one atomic event and hoping it works. The professional move is to break it into stages where the database stays online the entire time.

2. The Staged Migration

The whole strategy fits in three stages, following a pattern often called expand → backfill → contract.

Stage 1 — Add the column as nullable, with no default

-- ✅ Cheap and safe
ALTER TABLE orders ADD COLUMN region TEXT;  -- nullable, no default

Why nullable and no default? On most modern databases this is a metadata-only change. The database updates its internal catalog ("this table now has a region column") and does not rewrite the 300 million existing rows. Existing rows simply report NULL for the new column. This finishes in milliseconds, regardless of table size.

The column now exists but is empty. The instinct is to immediately start filling it — resist that instinct.

Stage 2 — Fix the new writes before touching old data

Users are writing 24/7. If you start backfilling old rows while new rows keep arriving without the column populated, you will never catch up — every batch you fix is outpaced by fresh unfilled rows.

So fix the source of new data first:

1. Update your application logic so that every write now collects and stores the new value.
2. Deploy that code.

-- After deploy, every new row is complete:
INSERT INTO orders (..., region) VALUES (..., 'apac');

From the moment the new code is live, every new row is complete. The set of unfilled rows is now bounded — it can only shrink. That is the precondition that makes the backfill finishable.

💡 Key ordering rule: fix the leak before you mop the floor. Writes first, backfill second.

Stage 3 — Backfill the old rows in small batches

Now the old rows are a fixed, finite set. Fill them with a background job that works in small batches — a few thousand rows at a time — never one big UPDATE.

-- One batch (repeat until no rows remain)
UPDATE orders
SET region = derive_region(...)
WHERE region IS NULL
  AND id BETWEEN :start AND :start + 5000;

The batching discipline:

• Small batches → each transaction is short → locks are held briefly → live traffic barely notices.
• Pause between batches. After each batch, check database load (replication lag, CPU, active locks, connection queue). If load is high, wait and let it recover before the next batch. This is the throttle that keeps the migration invisible to users.
• Idempotent + resumable. The WHERE region IS NULL (or a tracked cursor) means you can stop and restart the job any time without double-work.

Stage 4 (optional) — Contract the schema

Only after every row is filled do you tighten constraints, so live traffic never blocks on a big rewrite:

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;   -- see per-DB notes below
CREATE INDEX CONCURRENTLY idx_orders_region ON orders(region);  -- Postgres: non-blocking

⚠️ Watch the DDL lock, even for the "cheap" steps. In PostgreSQL, ALTER TABLE grabs a brief ACCESS EXCLUSIVE lock. It is fast, but if a long-running query is already holding the table, your ALTER queues — and every new query then queues behind your ALTER, freezing the table. Always run migration DDL with a short lock_timeout (e.g. SET lock_timeout = '2s') and retry, so a blocked ALTER fails fast instead of taking the table down.

3. Does This Apply to All Databases?

The staging strategy is universal. The "milliseconds" promise is not. The three-stage pattern (add → fix writes → batched backfill) is sound on every system. But whether Stage 1 is actually free depends entirely on your engine, its version, and the exact column options. Here is what "add a nullable column with no default is metadata-only" really means in practice.

What actually generalizes

1. Never do the whole thing in one transaction. True everywhere.
2. Expand → fix writes → backfill in throttled batches. True everywhere, including NoSQL.
3. Backfill only after new writes are complete. True everywhere.
4. Verify the cost of your specific DDL — do not assume. This is the part that is not portable. Before running ALTER in production, confirm the operation is online/instant for your engine and version. In MySQL that means specifying/checking ALGORITHM=INSTANT or INPLACE, LOCK=NONE; in Postgres it means knowing your default is constant, not volatile; and it means reaching for gh-ost/pt-osc when the native operation would rewrite.

💡 The portable principle: the choreography (stages, batching, throttling) is universal; the cost of one DDL statement is engine-specific. A good engineer knows the pattern and checks the manual for their database before touching production.

4. The Interview Cheat Sheet

If you have 30 seconds to answer, say this:

1. Don't do it in one big transaction — it locks too many rows and stalls live traffic.
2. Add the column as nullable with no default — a metadata-only change that finishes in milliseconds and rewrites nothing.
3. Fix the writes first — deploy app code so every new row is complete, bounding the problem so the backfill can finish.
4. Backfill old rows in small batches, pausing to watch DB load; throttle or stop if load spikes.
5. Then, optionally, contract — add NOT NULL/indexes once every row is populated, using online/concurrent variants.
6. Caveat: the strategy is universal, but confirm that "add column" is truly online for your database and version — MySQL, Postgres, SQL Server, and Oracle all differ, and tools like gh-ost exist for exactly this.

The one line that wins the interview: you don't perform one dangerous migration and hope — you turn it into small, safe, reversible steps so the database stays online the whole time.