You need to add a column to a table with 100 million rows.

Old approach: Lock the table, add column, wait 30 minutes, production is down.

New approach: Add column, backfill data, no downtime.

The Pattern

Step 1: Add column (new, nullable)

ALTER TABLE users ADD COLUMN new_field VARCHAR ( 255 ) NULL ;

Enter fullscreen mode

Exit fullscreen mode

Takes 2 seconds. Table briefly locked. No big deal.

Step 2: Backfill in batches

UPDATE users SET new_field = computed_value WHERE id >= 0 AND id < 10000 ; UPDATE users SET new_field = computed_value WHERE id >= 10000 AND id < 20000 ; -- ...…