Databases & System Design•Part 1: Candid Database Experience & Intro Scenarios (1 - 5)
How do you handle schema migrations without downtime?
In Simple Words: Use the Expand/Contract Pattern:
- Expand: Add new columns as nullable, and create new indexes concurrently (
CREATE INDEX CONCURRENTLYin Postgres to avoid table locks). - Dual-Writing: Update backend code to write to both old and new schema fields.
- Backfill: Run background scripts to backfill legacy rows.
- Contract: Switch reads to new columns, remove writes to old columns, and safely drop deprecated columns.
sql
-- Step 1 (Zero-downtime column addition & concurrent index):
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20) DEFAULT NULL;
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone_number);
-- Step 2: Code writes to both 'phone' and 'phone_number'
-- Step 3: Backfill legacy rows in small batches:
UPDATE users SET phone_number = phone WHERE phone_number IS NULL AND id IN (SELECT id FROM users LIMIT 1000);
-- Step 4: Drop old column safely:
ALTER TABLE users DROP COLUMN phone;