Why Direct Alters Hurt
On large tables, a direct ALTER can hold a lock and rewrite the entire table, blocking writes for minutes or hours. On a busy system that is an outage. Online schema change tools perform the same alteration with minimal locking by working on a copy.
The Shadow Table Approach
- Create a new empty table with the desired schema.
- Copy existing rows from the original in small batches.
- Capture changes that happen during the copy so the shadow stays current.
- Swap the shadow table in for the original with a quick atomic rename.
How Changes Are Captured
- Trigger based tools like pt online schema change install triggers on the original to mirror writes into the shadow.
- Log based tools like ghost read the replication stream instead, avoiding trigger overhead on the primary.
Tradeoffs
- The copy uses extra disk for the duration.
- It adds load, so it should run during quieter periods and be throttled.
- The brief rename is the only locking moment.
Key idea
Online schema change tools build a shadow table, copy and sync data, then atomically swap it in, altering large tables without a long blocking lock.