Changing a table without downtime

Interviewers ask: "The orders table has 40 million rows and the site must stay up. How do you change a column?" They check that you know a change can block users.

A weak answer says "run it at night". A strong answer uses small steps. Each is safe, and all but the last can be undone.

Think about a shop that must stay open while you rebuild it. You build a new door beside the old one, move the customers across, then close the old door.

A migration is a change to the structure of a table, such as a new column. It is online when users keep working. Two dangers make it risky:

1. A lock and a rewrite. Some changes take a strong lock: other queries on the table must wait. Some also rewrite the table: the database copies every row. In Postgres, a strong lock blocks even reads, and a change of column type normally rewrites the table. The time grows with the rows.
2. Old and new code together. You update the servers one at a time. This is a release. For a short time old and new code both run, and both must work with the table. A column rename breaks the old code at once.

The fix is expand, then contract:

1. Expand. Add the new column beside the old one. Nothing that the old code uses changes.
2. Move. New code writes to both columns. A backfill copies the old rows into the new column in small groups of rows, called batches, with a pause between them. Then new code reads the new column.
3. Contract. When no code uses the old column, remove it.

Each step is safe alone. Only the last cannot be undone, so it comes last. Cheap and costly changes in Postgres:

Also set a lock wait limit (in Postgres, lock_timeout), so the change stops instead of waiting forever.

The orders table of lesson 20 has 40,000,000 rows. At the peak, 120 new orders arrive per second. The column total is an integer. We want bigger totals, so we move to bigint.

Assumptions (made up to teach with): a rewrite copies 50,000 rows per second. A batch of 10,000 rows takes 200 ms, and we pause 200 ms after it. A bigint takes 8 bytes.

Step 1: change in place. 40,000,000 / 50,000 = 800 seconds, which is 13 minutes 20 seconds, with the table locked. In that time 120 x 800 = 96,000 orders wait or fail. This answers the predict box.

Step 2: expand, then contract.

-- Expand (milliseconds; gives up if it cannot get the lock in 2 s)
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN total_big bigint;

-- Release A: code writes total and total_big, and still reads total.

-- Backfill: run again and again, one id range at a time
UPDATE orders SET total_big = total
 WHERE id BETWEEN 1 AND 10000 AND total_big IS NULL;

-- Check: rows where the two columns differ; expect 0 (a read-only scan)
SELECT count(*) FROM orders WHERE total_big IS DISTINCT FROM total;

-- Release B: code reads total_big. Release C: code stops writing total.
ALTER TABLE orders DROP COLUMN total;

Step 3: the numbers.

• Batches: 40,000,000 / 10,000 = 4,000.
• Time: 4,000 x (200 + 200) ms = 1,600 seconds, about 27 minutes. That is twice Step 1. But each batch locks only 10,000 rows for 0.2 seconds.
• Extra space during the move: 40,000,000 x 8 bytes = 320 MB. Each update leaves a dead row (lesson 20), which vacuum cleans in the pauses. Dropping a column does not return its space at once.

If Release B shows a fault, go back to Release A. The old column is still correct.

• Expand, then contract solves a blocked table and a change that cannot be undone. It costs three releases, more time, code that writes twice, and 320 MB of extra space. I would use a plain change for a small table, or when a short stop is fine.

"A rename is instant, so it is safe." The database part is instant. Old servers still ask for the old name.

"One big UPDATE is the quick way to backfill." It locks every row it changes until it ends. It also leaves 40,000,000 dead rows for vacuum (lesson 20). Use batches.

"Drop the old column as soon as the switch works." A rollback needs it. Drop it when no code uses it.

1. Name the three phases of expand then contract. Why is a one-step rename risky?
2. A table has 10,000,000 rows. Batches have 5,000 rows, 100 ms of work and 100 ms of pause. A rewrite copies 50,000 rows per second. (a) How many batches? (b) How long is the backfill? (c) How long does a rewrite block the table?
3. A team switches reads to the new column while the backfill is half done. What goes wrong, and what check prevents it?