Zero-downtime Migrations

Apply schema changes — index builds, column splits, safe renames — without taking the database offline. Real locks, real progress views, real workload running throughout.

Every production database eventually needs a schema change while traffic keeps flowing. This course teaches the tools and patterns that make that possible: CREATE INDEX CONCURRENTLY to build indexes without blocking writes, DROP INDEX CONCURRENTLY to remove them safely, and the expand/contract pattern to split or rename a column across a rolling deploy. We work on a real two-million-row shop database with a live workload script running throughout, so every claim about locks and blocked writers is observable, not theoretical. The course closes with a bad monolithic migration that we pull apart into three safe steps, each guarded by lock_timeout and statement_timeout, each carrying a rollback note.

What you'll build

• Build an index without blocking writes using CREATE INDEX CONCURRENTLY
• Spot and clean up an INVALID index left by an interrupted concurrent build
• Drop an index safely with DROP INDEX CONCURRENTLY
• Split a column with the expand/contract pattern: add, backfill, validate, drop
• Explain why RENAME COLUMN is unsafe during a rolling deploy
• Guard every migration step with lock_timeout and statement_timeout
• Break a bad monolithic migration into safe independent deploy steps

Contents

1. A database that can't afford downtime
2. Start it
3. Connect with psql
4. The users table — with a deliberate flaw
5. The orders table — two million rows
6. Create the tables
7. Seed the users, repeatably
8. Seed two million orders
9. Load the data
10. Fresh statistics and a version check
11. Simulate a live workload
12. A uniform way to apply migrations
13. The blocking index build
14. Where to see the queue building up
15. Apply the blocking index
16. The non-blocking alternative
17. Watch the concurrent build in real time
18. Apply the concurrent index
19. The INVALID index trap
20. Create and cancel a concurrent build to get an INVALID index
21. Drop the invalid index safely
22. Clean up the invalid index
23. Why CONCURRENTLY cannot run inside a transaction block
24. Phase one: expand — add the new columns
25. Add first_name and last_name
26. Monitor the backfill progress
27. Confirm all rows are unfilled before the backfill starts
28. Phase two: backfill in small batches
29. Run the backfill
30. Phase three: validate — lock in the constraint
31. Add NOT NULL to the new columns
32. Phase four: contract — drop the old column
33. Remove full_name
34. Why RENAME COLUMN is not the answer
35. The bad monolithic migration
36. Safe step one: add the column
37. Safe step two: create the index concurrently
38. Safe step three: expand for the rename
39. Apply all three safe steps under workload
40. The final state of orders
41. Clean up the stand