Safe Schema Changes: Columns and Constraints

Learn to add columns, backfill data and enforce constraints in production PostgreSQL without taking the site down — from lock mechanics to NOT VALID patterns.

Every ALTER TABLE grabs a lock that blocks even SELECT. On a table with a million rows and real traffic, a migration that waits too long can turn into an outage — and a waiting DDL queues up everything behind it. This course shows why that happens and what to do about it. We work on a real stand: four tables, 200 k customers, 1 M orders, 800 k payments. We watch a DDL block in pg_locks, arm the migration with lock_timeout so it fails fast instead of hanging, and walk every column-change pattern the planner cares about — nullable add, constant default, volatile default, and batched backfill. Then we close with the two safest sequences every production engineer needs: adding NOT NULL through NOT VALID + VALIDATE, and wiring up a foreign key the same way. Every output in this course was captured on a live container.

What you'll build

• Explain why ALTER TABLE takes ACCESS EXCLUSIVE and what queues behind it
• Reproduce a blocked DDL and read it in pg_locks and pg_stat_activity
• Set lock_timeout and statement_timeout as a migration header pattern
• Add a nullable column and a column with a constant default safely
• Recognise a volatile default and explain the table-rewrite cost
• Backfill 200 k rows in batches without blocking concurrent traffic
• Add NOT NULL through CHECK NOT VALID + VALIDATE CONSTRAINT + SET NOT NULL
• Add a foreign key through NOT VALID + VALIDATE and understand the lock profile

Contents

1. A database big enough to feel the locks
2. Start it
3. Connect with psql
4. The customers table — without loyalty_tier
5. The orders table — one million rows coming
6. The payments table — foreign key added separately
7. The subscriptions table
8. Create the tables
9. Seed the customers, repeatably
10. Seed a million orders
11. Seed the payments
12. Seed the subscriptions
13. Load the data
14. Refresh statistics and confirm the stand
15. pg_locks — the X-ray for concurrent access
16. Simulated production traffic
17. Hold a lock to block the DDL
18. The DDL blocks and the queue forms
19. Read the lock wait in pg_locks
20. The lock releases and the DDL completes
21. Clean up the test column
22. pg_stat_activity — find the blocked statement
23. Spot the blocked migration from pg_stat_activity
24. The migration header pattern
25. Add the nullable column
26. A script to apply a numbered migration
27. lock_timeout in action — fail fast
28. Apply the real migration — no contention, instant
29. Three ways to add a column — three different risks
30. Constant default — catalog-only on PG 11+
31. Volatile default — every row is rewritten
32. Drop the test columns
33. All loyalty_tier values are NULL
34. A progress query to run while backfill runs
35. Backfill skeleton — find the PK bounds
36. The batch loop — short transactions, small pauses
37. Run the backfill
38. Progress snapshots during backfill
39. Why a single UPDATE is an anti-pattern
40. Four steps to add NOT NULL safely
41. Step 1 — add the constraint without validating
42. Apply step 1
43. Inspect the constraint state
44. Step 2 — validate existing rows with a milder lock
45. Apply step 2 — traffic keeps flowing
46. The lock profile of VALIDATE CONSTRAINT
47. Steps 3 and 4 — promote and clean up
48. Apply the final NOT NULL steps
49. Safe FK: the same NOT VALID pattern
50. Migration 0004 — add the FK without validating
51. Apply the NOT VALID foreign key
52. Confirm the constraint is not yet validated
53. Step 2 — validate the foreign key
54. Apply the validation
55. Confirm the FK is fully validated
56. The complete picture in the catalog