Database Operations: Scenario-Based Questions

Making changes to a live production database can introduce risk — from application crashes to corrupt data. Zero-downtime migrations ensure users never see errors while schemas evolve safely behind the scenes.

🛠️ Zero-Downtime Migration Principles

• Backward Compatibility: Old and new code should work with both schema versions.
• Three-Step Process: Add (non-breaking), Migrate, Remove (post-deploy cleanup).
• Avoid Locks: Never block writes — avoid long-lived DDL on large tables.

🔄 Common Patterns

• Additive Changes: Add new columns or tables with defaults — don’t remove yet.
• Dual Writes: Temporarily write to both old and new schemas.
• Read Switch: Update app to read from new schema only once populated.
• Cleanup Phase: Remove old columns, triggers, fallback logic.

⚙️ Tools & Automation

• Online Migration Tools: pt-online-schema-change, gh-ost (for MySQL).
• Frameworks: Liquibase, Flyway, Alembic, Rails ActiveRecord migrations.
• Release Coordination: Use feature flags or staggered deploys to roll out read/write changes.

✅ Best Practices

• Test migrations on staging with production-like data sizes.
• Monitor latency and locking metrics during rollout.
• Communicate with downstream teams and SREs before impactful changes.

🚫 Common Pitfalls

• Dropping or renaming columns in use by older code.
• Running large ALTER TABLE on peak traffic without throttling.
• Not validating new data flows before removing the old one.

📌 Final Insight

Schema evolution is part of software growth. Treat migrations like product releases — staged, tested, and monitored — to achieve agility without user disruption.