The Shadow Table Strategy (The Ghost)

The shadow table strategy is the foundation that nearly every online migration tool is built on. The name comes from the idea that you're building a "shadow" or "ghost" copy of your table in the background — invisible to the application — and then swapping it in when it's ready.

The General Algorithm

Almost all online migration tools (like pt-online-schema-change and gh-ost) follow the same high-level pattern:

1. Create Ghost: Create an empty copy of the table (the "shadow" or "ghost" table) with the new schema — for example, with the extra preferred_language column already present.
2. Backfill: Slowly copy existing data from the original table to the ghost table in small chunks (e.g., 1,000 rows at a time). Chunking prevents the migration from saturating disk I/O and starving the application of database resources.
3. Sync: While copying, capture any new writes (INSERTs, UPDATEs, DELETEs) happening on the original table and apply them to the ghost table to keep it current. This is the hardest part — and the part where the two main tools differ fundamentally.
4. Cutover: Once the ghost table contains all the data and is fully in sync, perform an atomic RENAME to swap the tables:
5. users → users_old_backup
6. _users_gho → users
7. Cleanup: Drop the users_old_backup table.

The magic is in Step 3 (Sync). How do you capture new writes without locking the table? There are two main approaches, and choosing between them has significant operational consequences.