Database Reliability: Scenario-Based Questions

Schema changes in live systems can cause downtime or break application functionality. A zero-downtime approach carefully coordinates code and schema updates in a backward-compatible way.

🪜 General Strategy

• Make changes in a non-breaking, additive manner.
• Use expand-migrate-contract pattern over time.
• Deploy code and schema changes in separate, safe stages.

🛠 Migration Patterns

• Add new columns: Do not remove or rename existing ones during active usage.
• Deploy compatible app code: Read from old + new schema (dual-read or feature-flagged access).
• Backfill data: Use a background job to populate new columns or rows.
• Switch application logic: Read/write to new structure once complete.
• Remove old schema: After verifying traffic and data integrity.

🧪 Testing & Validation

• Use shadow writes or canary databases to validate changes safely.
• Deploy to staging with production data snapshots and realistic load.
• Monitor for query plan regressions using EXPLAIN ANALYZE.

✅ Best Practices

• Use a migration tool (Flyway, Liquibase, Alembic) for versioning and rollback.
• Avoid locks on large tables — break into batched updates with pause logic.
• Pause deployment if schema changes exceed expected duration.
• Keep schema changes observable with logs and alerts.

🚫 Common Pitfalls

• Dropping or renaming columns without application coordination.
• Making schema changes inside app startup routines.
• Ignoring long-running transactions that block migrations.

📌 Real-World Insight

At scale, database changes are treated like code deployments — planned, phased, and rollback-ready. Mature teams version their migrations, test under load, and automate rollouts for consistent, repeatable schema evolution.