Add a column to a 250 million row table with nobody noticing

The question lands forty minutes into the interview, after the easy part.

"You have a table with 250 million rows. Busiest table in the product, reads and writes, twenty four hours a day. Product wants a new field on it. You cannot take the site down, not for a minute, not at 3am. How do you add the column?"

Most people answer in four seconds:

ALTER TABLE events ADD COLUMN source text;

That answer is correct. It is also how you take the site down.

What a lock actually is

Before anything else, the thing nobody explains properly.

A database has to stop two people changing the same thing at the same moment. So before touching a table, a query takes a lock: a claim that says "I am using this, wait your turn". Different jobs take different strengths of claim. Reading takes a weak one, and two readers can hold it at once. Changing the shape of a table takes the strongest one, and it excludes everybody.

Here is the part that matters. Picture a single-file doorway into a room. Readers slip through side by side because they are thin.

Then somebody arrives carrying a wardrobe. They need the whole doorway, so they wait at the front until the room empties. And while they stand there, nobody else gets in either, however thin they are.

That queue is the problem. Not the wardrobe. The line behind it.

Adding the column really is free

Postgres does not rewrite the table to add a column. It writes a note in the catalogue saying "rows now have a source field, and old rows read as NULL". Nothing on disk changes. It has worked this way since version 11, which also made non-volatile defaults free.

So the statement finishes in about a millisecond, on 250 million rows or on ten.

But to write that catalogue note it needs ACCESS EXCLUSIVE, the strongest lock there is. The wardrobe.

The queue is what kills you

Follow the timeline.

An analytics query started nine seconds ago and holds a read lock. Your millisecond migration asks for ACCESS EXCLUSIVE and joins the queue behind it. Fine so far.

Now an ordinary SELECT arrives from a checkout page. It is perfectly compatible with the analytics query, but Postgres will not let it jump ahead of your pending exclusive lock. So it waits. So does the next one, and every other query on your busiest table.

Nine seconds later the analytics query ends, your migration runs in a millisecond, and the dam breaks. By then you have thousands of stalled connections, an exhausted pool, and pages timing out that never touched this table at all.

The migration took one millisecond. The outage lasted nine seconds and looked like everything was broken.

The fix is two lines

Do not wait in that queue. Refuse to wait.

SET lock_timeout = '2s';
ALTER TABLE events ADD COLUMN source text;

If the lock is not free within two seconds, the migration gives up and errors instead of holding the door shut. You retry a few seconds later, when the long query has finished. Most attempts succeed instantly, and a failed attempt costs nothing and blocks nobody.

Make your migration tool retry five or six times with a pause between. That is the whole difference between a safe schema change and an incident.

Filling in 250 million rows

The column exists and every row reads NULL. Now product wants a value in it, and this is the part that actually takes hours.

The obvious statement is the dangerous one:

-- Do not run this.
UPDATE events SET source = 'legacy';

One transaction, 250 million rows. Postgres does not update rows in place, it writes a new version of each one. So this creates 250 million dead rows, doubles the table on disk, holds a transaction open for hours, and blocks vacuum from cleaning up behind it, which the Postgres docs go into properly. Fail at 90 percent and all of it rolls back.

Batch it instead, walking the primary key:

UPDATE events
SET source = 'legacy'
WHERE id > $1 AND id <= $1 + 5000
  AND source IS NULL;

Each batch is its own transaction. Record $1 after every batch and the job becomes resumable: if it dies at 2am you restart from the last checkpoint, not from zero.

Two details people get wrong:

Never paginate with OFFSET. OFFSET 10000000 makes the database count through ten million rows before returning anything, so every batch is slower than the last and the job quietly grinds to a halt. Ranging on an indexed key stays flat.

Sleep between batches, and make the pause adaptive. A fixed 100ms is a guess. Read your replication lag after each batch and slow down when it climbs. Otherwise the backfill finishes on the primary while your replicas fall minutes behind, which users experience as data vanishing and reappearing.

Ship the code before the column is required

The order of deploys matters as much as the SQL.

The column stays NULL until the backfill catches up. So the application has to tolerate NULL before the backfill starts, not after. Deploy in three passes: code that reads the column defensively, then the backfill, then the constraint that makes it required.

Adding NOT NULL directly scans the whole table under a lock. Add a CHECK marked NOT VALID first, then validate it separately under a weaker lock:

ALTER TABLE events
  ADD CONSTRAINT events_source_not_null
  CHECK (source IS NOT NULL) NOT VALID;
 
ALTER TABLE events VALIDATE CONSTRAINT events_source_not_null;

Same rule for indexing the new column: use CREATE INDEX CONCURRENTLY, which does not block writes, then check it did not end up invalid.

The ninety second version

The danger is never the size of the change.

• Adding a column is free. Taking the lock to do it is not.
• The outage comes from the queue behind your lock request, not from the work.
• SET lock_timeout and retry. Fail fast rather than blocking the table.
• Never one big UPDATE. Batch by primary key, one transaction each, resumable.
• Never OFFSET. It gets slower every batch.
• Sleep between batches, based on real replication lag, not a fixed guess.
• Deploy tolerant code first, backfill second, constraint third.

The answer that actually impresses an interviewer comes before all of these: ask whether the column belongs on the busiest table in the product at all.

If you are wrestling with a migration like this, tell me about it.