How do you run database migrations without downtime?

Summary

Use expand/contract: first make backward-compatible additive changes (add nullable columns or new tables), deploy code that writes to both/reads from new, backfill data in batches, switch reads, and only later remove old columns. Avoid long table locks (create indexes concurrently, split large updates).

Longer Answer

Never rename or drop a column in the same deploy that stops using it; old instances still running would break.

Code Example

-- Step 1 (expand): additive and safe
ALTER TABLE users ADD COLUMN display_name text;
CREATE INDEX CONCURRENTLY idx_users_display_name ON users (display_name);
-- Step 2: deploy code writing both columns; backfill in batches
-- Step 3 (contract, a later release): drop the old column
ALTER TABLE users DROP COLUMN nickname;

Key Points

• Expand → migrate → contract
• Additive changes first
• Batched backfills
• Concurrent index creation
• Drop later