How Do You Safely Run Database Migrations in a CI/CD Pipeline?
Learn the expand-contract pattern for zero-downtime database migrations in CI/CD pipelines, with a real example and interview-ready explanation.
Expected Interview Answer
Safe database migrations in CI/CD rely on the expand-contract pattern: first deploy a backward-compatible schema change that both old and new application code can read and write, then deploy the new application code, and only later remove the old schema elements once every instance is confirmed running the new code.
Expand-contract splits a risky single-step migration into three safe steps. The expand step adds new columns, tables, or indexes without removing or renaming anything the running application still depends on, so it can be applied ahead of the code deploy with zero downtime. The migrate step ships new application code that writes to both old and new schema shapes, or reads from whichever is populated, keeping every running instance compatible during a rolling deploy where old and new pods coexist. The contract step, run only after the rollout is fully complete and stable, drops the old columns or tables that are no longer referenced. Large tables need extra care — adding a NOT NULL column or an index without CONCURRENTLY can lock the table and cause an outage, so migrations against big tables should be broken into smaller, non-locking steps and tested against a production-sized dataset first.
- Enables zero-downtime deploys even with schema changes
- Keeps rollback safe because old code still works against the expanded schema
- Avoids long table locks that cause production outages
- Decouples schema changes from application deploys, reducing blast radius
AI Mentor Explanation
Expand-contract is like widening a stadium’s pitch access tunnel before changing the walk-out routine, rather than doing both on the same match day. First you add the extra tunnel (expand) so both old and new walk-out routines still work through it. Only once every team has used the new routine successfully do you seal off the old, narrower tunnel (contract). Doing all three steps at once risks a team getting stuck mid-walkout on match day.
Step-by-Step Explanation
Step 1
Expand the schema
Add new columns, tables, or indexes without removing anything old code still uses.
Step 2
Deploy compatible application code
Ship code that works against both old and new schema shapes during the rolling deploy.
Step 3
Backfill and verify
Populate new columns for existing rows and confirm all instances run the new code.
Step 4
Contract the schema
Only after full rollout, drop the old columns or tables in a separate, later migration.
What Interviewer Expects
- Knowledge of the expand-contract pattern and why it enables zero-downtime schema changes
- Awareness that rolling deploys mean old and new code run simultaneously against one database
- Understanding of locking risks on large tables and how to avoid them
- Ability to explain why schema and application deploys should be decoupled
Common Mistakes
- Dropping or renaming a column in the same deploy that also removes code using it
- Running a blocking ALTER TABLE on a large table without a non-locking approach
- Assuming all application instances update atomically during a rolling deploy
- Skipping a backfill step and leaving new columns null for existing rows
Best Answer (HR Friendly)
“I always split risky schema changes into smaller safe steps. First I add anything new without touching what is already there, so both the old and new app code keep working. Only once the new code is fully rolled out and stable do I go back and clean up the old schema. It takes an extra step, but it means we never have downtime or a broken rollback path.”
Code Example
jobs:
expand-migration:
steps:
- run: psql -f migrations/001_add_new_column.sql
deploy-app:
needs: expand-migration
steps:
- run: kubectl set image deployment/myapp app=myapp:new-version
- run: kubectl rollout status deployment/myapp
contract-migration:
needs: deploy-app
if: manual_approval_confirmed
steps:
- run: psql -f migrations/002_drop_old_column.sqlFollow-up Questions
- How would you add a NOT NULL column to a large production table without downtime?
- What happens to in-flight requests during a rolling deploy that spans a schema change?
- How do you decide when it is safe to run the contract step?
- What tooling would you use to manage and version database migrations?
MCQ Practice
1. In the expand-contract pattern, when should the old schema element finally be dropped?
The contract step must wait until every running instance uses the new schema shape, otherwise old code still referencing the dropped element would break.
2. Why is it risky to add a NOT NULL column directly to a large production table?
Adding a NOT NULL constraint on a large table can require rewriting or locking the whole table, which can block reads and writes during the migration.
3. Why must migration code be backward-compatible during a rolling deploy?
During a rolling deploy, some pods run the old code and some run the new code at the same time, so the schema must satisfy both simultaneously.
Flash Cards
What is expand-contract? — A migration pattern that adds new schema first, deploys compatible code, then removes old schema only after full rollout.
Why decouple schema changes from deploys? — So schema and code changes can each be validated and rolled back independently.
Risk of ALTER TABLE on large tables? — Can require a long lock that blocks reads/writes and causes an outage.
When is the contract step safe? — Only after every running instance is confirmed on the new code that no longer needs the old schema.