Imagine you need to renovate a busy restaurant kitchen — but you can't close the restaurant. Every table is full, orders keep coming in, and you need to tear out the old countertops and install new ones. That's exactly the problem database engineers face when they need to change the structure of a table that's serving millions of live requests. Stopping the database to make changes is like closing the restaurant: technically simple, but completely unacceptable in production.
The Scenario
You have a users table with 100 million rows (500 GB). You need to add a new column: preferred_language. You run a plain ALTER TABLE users ADD COLUMN preferred_language VARCHAR(50);.
What Standard MySQL Does
In standard MySQL (and many other relational database engines), this command acts like a wrecking ball:
- Global Lock: The database acquires an exclusive lock on the entire table to guarantee consistency during the operation.
- Rebuild: It creates a new, empty table with the updated schema (including the new column).
- Copy: It copies all 100 million rows from the old table to the new one, row by row.
- Swap: It renames the new table to the original name and drops the old one.
- Unlock: It releases the lock.
For a 500 GB table, step 3 might take 4 hours. During those 4 hours, no one can sign up, log in, or update their profile. Your site is effectively down.
Why This Matters Beyond Downtime
The problem isn't just the outage window. It's the unpredictability. A migration that takes 4 hours on staging might take 6 hours in production because of higher I/O contention. You can't easily abort halfway through — the table is locked. And if something goes wrong mid-copy, you're left in an inconsistent state that requires manual recovery.
The Solution
Online Schema Migration tools allow you to modify massive tables without locking them for writes. They perform the migration in the background while the application continues to run normally. The core insight is: instead of modifying the table in place, build a new version of the table alongside the old one, keep them in sync, and then swap them atomically.
Interviewers often ask: "How would you add a column to a 500 GB production table with zero downtime?" The wrong answer is "schedule a maintenance window." The right answer walks through the shadow table pattern: create a ghost table with the new schema, backfill data in chunks, sync live writes via triggers or binlog streaming, then perform an atomic rename. Mention gh-ost or pt-online-schema-change by name and explain the trade-off between them.