The Locking Problem
A plain ALTER on a table with hundreds of millions of rows can hold a lock for hours, freezing the application. Online schema change tools change huge tables without that long lock by building a new copy in the background.
The Shadow Table Technique
Tools like gh ost and pt online schema change follow the same shape.
- Create a shadow table with the new schema.
- Copy existing rows into it in small batches, throttled by load.
- Capture live changes to the original during the copy and apply them to the shadow.
- When the shadow has caught up, swap it with the original in a quick atomic rename.
Only the final rename needs a brief lock, so the application stays available throughout the long copy.
Capturing Live Writes
Two approaches capture concurrent writes. Trigger based tools add triggers that mirror each change onto the shadow. Log based tools read the binlog to replay changes, avoiding trigger overhead. Either way the shadow ends up identical to the original plus the new schema before the swap.
Key idea
Large table migration builds a shadow table with the new schema, copies rows in throttled batches while mirroring live writes, then swaps it in with a brief atomic rename so the app stays online.