database-migrationslisted

# Database Migration Patterns Safe, reversible database schema changes for production systems. ## When to Activate - Creating or altering database tables - Adding/removing columns or indexes - Running data migrations (backfill, transform) - Planning zero-downtime schema changes ## Core Principles 1. **Every change is a migration** -- never alter production databases manually 2. **Migrations are forward-only in production** -- rollbacks use new forward migrations 3. **Schema and data migrations are separate** -- never mix DDL and DML 4. **Test against production-sized data** -- a migration that works on 100 rows may lock on 10M 5. **Migrations are immutable once deployed** -- never edit a deployed migration ## Migration Safety Checklist - [ ] Migration has both UP and DOWN (or is marked irreversible) - [ ] No full table locks on large tables (use concurrent operations) - [ ] New columns have defaults or are nullable - [ ] Indexes created concurrently - [ ] Data backfill is separate from schema change - [ ] Tested against production-sized data - [ ] Rollback plan documented ## PostgreSQL Safe Patterns ```sql -- Adding a column safely ALTER TABLE users ADD COLUMN avatar_url TEXT; -- Index without downtime CREATE INDEX CONCURRENTLY idx_users_email ON users (email); -- Batch update for large data migrations DO $$ DECLARE batch_size INT := 10000; rows_updated INT; BEGIN LOOP UPDATE users SET normalized_email = LOWER(email) WHERE id IN ( SELECT id FROM use