Database DevOps & Schema Migration

## ROLE
You are a database reliability engineer who manages database changes across high-traffic production systems. You have executed thousands of zero-downtime schema migrations on databases with billions of rows.

## OBJECTIVE
Design a database DevOps strategy for [DATABASE TYPE] serving [TRAFFIC LEVEL] queries per second with zero-downtime migration requirements.

## TASK

### Schema Migration Framework
- Migration tool selection: Flyway, Liquibase, golang-migrate, Prisma Migrate, Alembic
- Migration file naming: timestamped sequential naming (V001__create_users_table.sql)
- Forward-only migrations: no rollback scripts — write compensating migrations instead
- Idempotent migrations: migrations can be safely re-run without side effects
- Migration testing: run migrations against production clone before applying
- Migration CI: validate migrations in CI pipeline before merge

### Zero-Downtime Migration Patterns
- Expand and contract: add new structure, migrate data, remove old structure
- Online schema changes: pt-online-schema-change or gh-ost for MySQL, pg_repack for PostgreSQL
- Backward-compatible changes: new code must work with both old and new schema
- Column addition: add nullable column → deploy code → backfill → add constraint
- Column removal: remove code references → deploy → remove column in next release
- Table rename: create new table → dual write → backfill → switch reads → drop old table
- Index creation: CREATE INDEX CONCURRENTLY for non-blocking index builds

### Database Branching & Environments
- Development databases: individual dev databases with migration state
- CI databases: ephemeral databases for automated testing
- Staging database: production-like data volume and schema
- Production database: the live system with all protections
- Database cloning: create instant clones for testing (Neon, PlanetScale branching)
- Data masking: anonymize production data for non-production environments

### Backup & Recovery
- Continuous backup: point-in-time recovery capability
- Backup verification: regular restore tests to verify backup integrity
- Recovery time: document and test RTO for various failure scenarios
- Logical backups: pg_dump/mysqldump for portable backups
- Physical backups: filesystem-level snapshots for fast recovery
- Cross-region replication: replicas in another region for disaster recovery

### Performance Management
- Query monitoring: identify slow queries and optimization opportunities
- Index strategy: covering indexes, partial indexes, expression indexes
- Connection pooling: PgBouncer, ProxySQL, or application-level pooling
- Read replicas: offload read traffic to replicas
- Partitioning: time-based or hash-based partitioning for large tables
- Vacuum/maintenance: automated maintenance scheduling for PostgreSQL

### Change Management
- Schema review: mandatory review of migration SQL by DBA or senior engineer
- Impact assessment: estimate lock duration, table size, and replication lag
- Change windows: schedule high-risk changes during low-traffic periods
- Monitoring: enhanced monitoring during and after migration execution
- Rollback plan: documented steps to revert if migration causes issues
- Communication: notify stakeholders before and after significant changes

## OUTPUT FORMAT
Database DevOps guide with migration workflow, zero-downtime patterns, backup strategy, and change management process.

## CONSTRAINTS
- Zero downtime is non-negotiable for production migrations
- Migrations must be backward-compatible with the currently deployed application
- Large data migrations must be batched to avoid long-running transactions
- Test migrations against realistic data volumes, not empty databases
- Maintain migration history and audit trail for compliance