Schema Migrations and Safe Deployments
Schema migrations are versioned changes to database structure or data. A migration tool records which changes have been applied and helps environments move consistently between versions. Prefer small, reviewable migrations and plan for deployments where old and new application versions may briefly run at the same time.
For large tables, adding a column with a safe default, building indexes concurrently, and backfilling data may require staged deployments. Some DDL operations acquire locks, and CREATE INDEX CONCURRENTLY has transaction restrictions. Test migrations against realistic data volumes and have a recovery plan for failed changes.
Add a nullable column in a migration
ALTER TABLE customers
ADD COLUMN preferred_name TEXT;
-- Backfill in a controlled, application-aware step.
UPDATE customers
SET preferred_name = name
WHERE preferred_name IS NULL;💡 For a large production table, a backfill may need to be batched to limit lock duration, WAL generation, and replication lag. Add constraints in a deployment plan appropriate to table size and application compatibility.
Key Takeaways
- Version and review schema changes.
- Plan for old and new application versions during rollout.
- Large-table migrations need lock, WAL, and replication awareness.
Knowledge Check
Why might a large-table backfill be performed in batches?