Approach 1 — Trigger-Based Sync (pt-online-schema-change)

Triggers are a built-in database feature: you can tell the database "whenever a row is inserted into this table, automatically run this additional SQL statement." Trigger-based migration tools exploit this to keep the ghost table in sync without any external process needing to watch the database.

The Tool

Percona Toolkit's pt-online-schema-change (commonly abbreviated pt-osc) was the industry standard for online MySQL migrations for many years. It ships as part of the Percona Toolkit, a collection of command-line utilities for MySQL administration.

How Trigger-Based Sync Works

1. pt-osc creates three triggers on the original table: AFTER INSERT, AFTER UPDATE, and AFTER DELETE.
2. When your application writes to the original table, the database engine automatically fires the corresponding trigger.
3. The trigger executes a matching write on the ghost table within the same transaction as the original write.

This means the ghost table is always consistent with the original — if a write to the original table succeeds, the ghost table is updated atomically via a REPLACE INTO inside the same trigger, so there is no lag.

The Write Amplification Problem

The critical downside is write amplification. Every write to the original table now causes two writes: one to the original, one to the ghost. On a database already under heavy write load, this can be catastrophic.

Consider a database at 85% CPU with 5,000 writes per second. Adding triggers effectively doubles the write workload to 10,000 writes per second — pushing the database past its limits. The triggers also acquire row-level locks, which increases lock contention and can cause cascading latency spikes across the application.

Pros:
- Guaranteed consistency — the database engine enforces synchronization.
- Simpler architecture — no external process needs to stream logs.
- Works even without binary logging enabled.

Cons:
- High overhead on write-heavy databases.
- Triggers cannot be paused — if the database is struggling, the triggers keep firing.
- Trigger overhead is proportional to write throughput, making it unpredictable under load spikes.