Case Study July 2026

Zero Downtime Database Migration

Changing table structure (Schema Migration) in a production database with millions of rows can cause table locks and hours of downtime. How do tech giants perform migrations without disrupting users at all? The answer: Expand and Contract Pattern.

!What's the Problem?

Imagine we have a users table with 10 million rows. We used to separate first_name and last_name columns. Now, the business wants us to combine them into a single full_name column.

If we directly run the standard SQL command:

ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);
ALTER TABLE users DROP COLUMN first_name, DROP COLUMN last_name;

The massive UPDATE command above will lock the entire users table for minutes or hours. During that process, NO user can login, register, or update their profile (Total downtime). This is a nightmare for systems that must be online 24/7.

✓Core Solution: Expand and Contract Pattern

Instead of changing everything at once, we break this migration into 5 small phases that are safe and can be rolled back anytime if an error occurs. Let's discuss it step by step:

Phase 1: Expand (Add New Column)

In this phase, we only add the full_name column into the database, BUT we leave it empty (NULL) and don't delete the old columns. Adding a nullable/empty column in modern databases is very fast and doesn't trigger a table lock.

Database SQL

ALTER TABLE users 
ADD COLUMN full_name VARCHAR(255) NULL;

App Code

No changes to the app code yet. Backend still reads and writes to first_name & last_name.

🛡️Real World Challenges (Edge Cases)

On paper, those 5 phases look easy. But in large-scale Enterprise systems, there are derivative problems that must be handled (Interviewers really love to ask this):

1. What if Dual-Write partially fails?

In Phase 2 (Dual Write), the system tries to save to the old and new columns. What if saving to the old column succeeds, but to the new column fails due to a timeout or validation error? The data will be in an Inconsistent State.

Solution: Use Database Transactions

Must wrap Dual Write logic inside BEGIN TRANSACTION and COMMIT blocks. If one fails to be inserted, everything will be automatically Rolled Back, keeping data integrity intact.

try {
  await db.query("BEGIN");
  // Update kolom lama & kolom baru
  await db.query("COMMIT");
} catch (e) {
  await db.query("ROLLBACK"); // Batalkan semuanya!
}

2. Deployment of Migration Script vs Code

If you use a *framework* (Laravel/Rails/Go-Migrate), separating table structure scripts and logic code is absolute. We must not deploy a DROP COLUMN script alongside code that still uses that column.

Solution: Separate Commit/Deployment

Deployment A (Phase 1): Only run php artisan migrate to add columns.
Deployment B (Phases 2-4): Only change the app code in Git (Controller/Service), without any DB *migration* file at all.
Deployment C (Phase 5): Deploy the new app code, FOLLOWED by running php artisan migrate which contains the DROP COLUMN script a few days later when it's 100% safe.

3. Foreign Keys and Cascading Relations

If the changed table has dozens of strict FOREIGN KEY relations, manual migration via Dual Write can be very troublesome and prone to constraint errors. This is where the Infrastructure team usually steps in using Advanced DBA Tools.

🚀Advanced Solutions (Advanced DBA Tools)

For very massive table changes (changing Primary Key, splitting tables, etc.), Developers usually delegate the task to a DBA (Database Administrator) who uses specialized Online Schema Migration tools. These tools work in the *background* creating duplicate tables (Shadow Tables), copying data slowly, and resynchronizing *delta changes* before executing a *cut-over*:

gh-ost (GitHub)

Developed by the GitHub team for their MySQL database. Very safe and lightweight because it reads the replication stream (binlog) and uses no SQL triggers at all.

pt-online-schema-change

A legendary tool from Percona Toolkit. Creates shadow tables, then sets up Triggers on the original table so that every insert/update is automatically replicated to the shadow table.

PostgreSQL Concurrent

If using Postgres, we have a privilege. We can create/change new Indexes without locking tables at all using the CREATE INDEX CONCURRENTLY syntax.

✓Database Migration Checklist

Migration is divided into small, rollback-able phases
Separation of SQL script deployment from logic code deployment
Dual Write operations are wrapped in Database Transactions
Old data migration/backfill scripts are processed incrementally (batching)
Using specialized tools (like gh-ost) for massive scale DDL (Data Definition Language) changes

Brief Conclusion

Zero downtime migration is not just about executing a single magical SQL command, but about Orchestration and Patience. By breaking down one massive change into 5 stages of app Deployments, we trade a little complexity in the codebase for ensuring UX (User Experience) keeps running 100% smoothly without a second of disruption when releasing a feature to Production.