⚡ ~/naveed Interview Prep
⚡ Portfolio Home 👤 About Naveed Bio & Credentials 📫 Contact Naveed Get In Touch ✍️ Engineering Blog Deep Dives 🎯 Interview Hub 1,000+ Scenarios ☸️ Kubernetes Mastery Hub 24 Modules 🎮 DevOps Arcade & Quizzes Subnet Blitz ⚡ 🗺️ DevOps Roadmaps PDFs & Guides 🤖 Morpheus Analysis AI Quant ↗ 🛠️ Developer Tools Utilities 🧪 Labs & Experiments 📄 Interactive CV & Certs 🔗 All Links & Socials ⚡ Join The Dispatch (Weekly SRE Newsletter) →
← Back to All Databases & Storage Interview Questions Scenario 42 of 54 in Databases & Storage
Senior DevOps / SRE Database Database GitOps & Migrations Database SRE
🎯 Target Role / Context: Senior Database SRE / Platform Architect Scenario

Q: Deploying relational database schema changes (adding columns, creating indexes, altering tables) in a GitOps workflow is notoriously dangerous: applying a breaking DDL change immediately crashes existing running application pods. Running migrations in application startup init containers causes lock contention when multiple pods scale up. How do you design and execute automated, zero-downtime database migrations with Flyway and GitOps?

Engineering a zero-downtime database schema migration pipeline in GitOps using Flyway / Liquibase, Kubernetes PreSync Jobs, and expand-contract (backward-compatible) schema patterns.

#CI/CD #GitOps #Flyway #Liquibase #Database Migrations #Kubernetes #PostgreSQL
🎙️ Candidate Opening & Architectural Context
"Executing database migrations inside application startup init containers causes concurrent lock collisions when pods autoscale, and rolling out backward-incompatible DDL changes causes immediate downtime. We designed a GitOps database migration framework using Flyway, Kubernetes PreSync Jobs, and the Expand-Contract architectural pattern."
Advertisement
⚡ Recommended Practice Lab

Want to master this scenario in a live sandbox? KodeKloud's PostgreSQL Database Administration & High Availability Course covers this exact problem with hands-on terminal drills.

🛠️ Production Runbook & Step-by-Step Resolution

Direct Answer: Executing database migrations inside application startup init containers causes concurrent lock collisions when pods autoscale, and rolling out backward-incompatible DDL changes causes immediate downtime. We designed a GitOps database migration framework using Flyway, Kubernetes PreSync Jobs, and the Expand-Contract architectural pattern.

1️⃣

Enforce Backward-Compatible Schema Migrations (Expand-Contract Pattern)

Never execute breaking DDL changes in a single deployment:

  • Phase 1 (Expand): Add new column phone_number while keeping old column mobile. Deploy application code that writes to both columns and reads from new column.
  • Phase 2 (Contract): Run background data backfill. In a subsequent release, drop old column mobile.
  • Zero Downtime: Running v1 pods and newly booting v2 pods remain 100% compatible with the database at all times.
Pro Tip: The Expand-Contract pattern guarantees that database schema changes are backward-compatible, preventing crashes during rolling updates.
2️⃣

Encapsulate Migrations in a Single-Run Kubernetes Job (Not Init Containers)

Eliminate database lock contention across autoscaled pods:

  • Dedicated Migration Container: Packaged SQL migration scripts (V1.2__add_index.sql) into a lightweight Flyway container image.
  • Kubernetes Job Spec: Configured kind: Job with backoffLimit: 1 and restartPolicy: Never, ensuring exactly ONE migration instance executes against the database.
Pro Tip: Running migrations as a Kubernetes Job guarantees a single execution thread, avoiding the catastrophic deadlocks caused by multiple pods running init containers simultaneously.
Advertisement
3️⃣

Enforce Non-Blocking DDL (CREATE INDEX CONCURRENTLY)

Prevent table locks from freezing production transactional traffic:

  • Non-Blocking Indexes: Enforced rule: All PostgreSQL index creation must use CREATE INDEX CONCURRENTLY.
  • Lock Timeouts: Configured Flyway connection script: SET lock_timeout = '3s';, ensuring migration aborts immediately if it cannot acquire a table lock, rather than queueing and blocking user queries.
Pro Tip: Standard CREATE INDEX locks tables against writes for minutes or hours; CONCURRENTLY builds indexes without blocking transactional operations.
4️⃣

Sequence Migrations via Argo CD PreSync Hooks & Automated Rollback

Orchestrate execution before application pods deploy:

  • PreSync Hook: Annotated migration Job with argocd.argoproj.io/hook: PreSync.
  • Sync Halting: If Flyway detects a syntax error or lock timeout, the job fails, Argo CD halts synchronization, and application pods are NOT updated, leaving production running safely.
  • Audit Baseline: Flyway maintains the flyway_schema_history table, providing an immutable audit trail of every applied migration.
Pro Tip: Coupling Flyway with Argo CD PreSync hooks guarantees deterministic, automated database migrations with zero downtime.
💡 The Senior SRE Gold Nugget (Key Architectural Takeaway)
"Zero-downtime database GitOps requires the Expand-Contract pattern for backward compatibility, single-instance Kubernetes Jobs (not init containers), non-blocking DDL (CREATE INDEX CONCURRENTLY), and Argo CD PreSync hooks."
⚡ 60-Second Elevator Pitch Talking Points
  • Enforce the Expand-Contract pattern so database schemas remain backward-compatible during rollouts.
  • Run migrations inside dedicated single-instance Kubernetes Jobs to eliminate lock contention.
  • Use non-blocking DDL (CREATE INDEX CONCURRENTLY) with aggressive 3-second lock timeouts.
  • Orchestrate execution using Argo CD PreSync hooks to abort rollouts if migrations fail.
⛔ Critical Candidate Mistakes to Avoid in This Scenario

Senior hiring managers and bar raisers evaluate how you prioritize during outages. Avoid these common anti-patterns:

  1. Applying exclusive-lock DDL table migrations on live production databases during peak traffic windows when configuring Database GitOps Zero DDL migrations that acquire an exclusive table lock block all concurrent queries, queuing connections until the database connection pool exhausts.
  2. Allowing high-frequency queries to trigger full table scans without appropriate composite indexing when configuring Database GitOps Zero Sequential table scans saturate database CPU and disk I/O, evicting frequently queried data from the buffer cache and causing cascading timeouts.
  3. Disabling referential integrity constraints during bulk ETL imports without post-import consistency validation when configuring Database GitOps Zero Skipping relational consistency checks risks introducing orphaned records and referential integrity violations into production tables.
  4. Relying on database snapshot backups that have never been verified through automated restoration drills when configuring Database GitOps Zero Backups that have never been tested with automated restore verification frequently fail during real disaster recovery scenarios.
Advertisement
Want more Databases & Storage scenarios?
Explore our complete collection of scenario-based Databases & Storage interview runbooks.
Browse All Databases & Storage Questions →