What Are Online Schema Change Tools and How Do They Work?
Learn how online schema change tools like gh-ost alter large production tables without long locks, using shadow tables.
Expected Interview Answer
Online schema change tools such as gh-ost and pt-online-schema-change let you alter a large production table — add a column, change an index — without holding a long exclusive lock, by building a new shadow table with the desired structure, copying rows across in small batches, capturing ongoing writes, and then atomically swapping the shadow table in for the original.
A naive ALTER TABLE on a huge table can lock it for minutes or hours, blocking every read and write and effectively causing an outage. Online schema change tools avoid this by creating an empty shadow table with the new structure, copying existing rows over in small chunks throttled to avoid overloading the server, and meanwhile capturing every INSERT, UPDATE, and DELETE that happens on the original table during the copy — either via triggers (pt-online-schema-change) or by tailing the binary replication log (gh-ost) — and replaying those changes onto the shadow table. Once the shadow table is fully caught up, a brief atomic rename swaps it in for the original, with only a few seconds of blocking rather than the full duration of the copy.
- Avoids long table locks on large production tables
- Keeps the table readable and writable throughout the change
- Throttles copy speed to protect production load
- Cutover to the new table structure takes only seconds
AI Mentor Explanation
Resurfacing an entire cricket outfield without cancelling the match season means groundskeepers build a new outfield in an adjacent unused area, replicate the exact turf pattern, and keep tracking any drainage or marking changes made to the live outfield during construction, applying the same changes to the new one, before finally swapping which field is officially in play during a short off-day window. An online schema change tool does this same build-shadow-then-swap trick for a database table, avoiding a season-long closure.
Step-by-Step Explanation
Step 1
Create the shadow table
Build an empty table with the target schema (new column, index, or type) alongside the original.
Step 2
Copy data in throttled batches
Copy existing rows from the original table to the shadow table in small chunks, pausing under load to protect production.
Step 3
Capture ongoing writes
Track every INSERT, UPDATE, and DELETE on the original table during the copy, via triggers or by tailing the binlog, and apply them to the shadow table.
Step 4
Atomically swap the tables
Once the shadow table is fully caught up, perform a brief atomic rename to make it the live table, minimizing lock time to seconds.
What Interviewer Expects
- Naming a concrete tool such as gh-ost or pt-online-schema-change
- Explanation of the shadow table copy-and-catch-up mechanism
- Understanding of why a naive ALTER TABLE locks large tables
- Awareness that the final cutover still involves a brief atomic swap
Common Mistakes
- Assuming online schema change tools involve zero locking at all, including cutover
- Running a naive ALTER TABLE directly on a huge production table
- Not throttling the copy, causing replication lag or overload
- Forgetting that triggers or binlog tailing must capture writes during the entire copy window
Best Answer (HR Friendly)
“For altering a huge production table, I would not run a direct ALTER TABLE since that can lock it for a long time. Instead I would use a tool like gh-ost, which builds a copy of the table with the new structure, copies the data across gradually while tracking any live changes, and then swaps the new table in with only a couple of seconds of impact instead of a full outage.”
Code Example
-- 1) Shadow table with the desired new structure
CREATE TABLE _orders_new LIKE orders;
ALTER TABLE _orders_new ADD COLUMN discount_code VARCHAR(50);
-- 2) Batched copy of existing rows (tool paces this automatically)
INSERT INTO _orders_new
SELECT *, NULL FROM orders
WHERE id BETWEEN 1 AND 10000;
-- repeats in batches across the whole table
-- 3) Ongoing writes to "orders" are captured (via triggers or binlog)
-- and replayed onto "_orders_new" throughout the copy.
-- 4) Final atomic swap once fully caught up:
RENAME TABLE orders TO _orders_old, _orders_new TO orders;Follow-up Questions
- What is the difference between how gh-ost and pt-online-schema-change capture live writes?
- Why does even an online schema change tool need a brief lock during the final swap?
- What happens if replication lag spikes during a gh-ost migration?
- How would you decide whether a schema change needs an online tool versus a direct ALTER TABLE?
MCQ Practice
1. Why do online schema change tools use a shadow table instead of altering the table directly?
A direct ALTER TABLE on a huge table can lock it for a long time; a shadow table avoids that by rebuilding off to the side.
2. How do these tools stay consistent with writes that happen during the migration?
Triggers (pt-online-schema-change) or binlog tailing (gh-ost) capture live changes so the shadow table stays current.
3. What happens at the very end of an online schema change?
After the shadow table is fully caught up, a fast atomic rename makes it the live table with only a few seconds of impact.
Flash Cards
Name two online schema change tools. — gh-ost and pt-online-schema-change.
What problem do they solve? — Avoiding long exclusive locks from a direct ALTER TABLE on a large production table.
How do they capture live writes during the copy? — Via triggers on the original table, or by tailing the replication binlog (gh-ost).
How does the final cutover work? — A brief atomic rename swaps the fully-caught-up shadow table in for the original, taking only seconds.