An online schema change is needed on a billion-row MySQL table with zero downtime. Compare trigger-based, binlog-based and native approaches, and describe how the change is gated and reverted.

Why the naive approach fails

A direct ALTER TABLE on a very large table may lock it, may take hours, and cannot be cancelled cleanly once underway. Even where the engine supports online DDL for the specific change, the operation generates substantial I/O and can degrade the serving workload badly enough to constitute an outage.

The three approaches

Trigger-based (pt-online-schema-change): create a new table with the desired schema, install triggers on the original that mirror every write into the new table, copy existing rows in batches, then swap the tables atomically.

Advantages: mature, well-understood, no replication configuration required. Costs: the triggers execute inside the application's write transactions, so write latency increases for the whole duration — which for a busy table is a sustained performance impact. Triggers also add lock contention, and on a heavily-written table this can itself cause an incident.

Binlog-based (gh-ost): create the new table, copy rows in batches, and apply ongoing changes by reading the binary log rather than by triggers.

Advantages: no triggers, so no added latency or contention on the write path — the migration's load is decoupled from application transactions. It can be paused, throttled and resumed, and it can be run against a replica to reduce primary load. Costs: requires row-based binary logging and appropriate access; slightly more moving parts.

For a billion-row table on a busy system, binlog-based is generally the right choice, and the decoupling of migration load from application transactions is the reason.

Native online DDL is genuinely the best option where the specific change supports it fully — many ADD COLUMN operations are instant in recent versions. Check first, because the tooling is unnecessary when the engine can do it instantly.

Gating, monitoring and reverting

• Throttle on replication lag and on primary load, pausing automatically when either exceeds a threshold. This is the single most important control, because the migration must yield to serving traffic rather than compete with it.
• Run during a low-traffic window where one exists, though a well-throttled migration should be safe at any time.
• Monitor write latency, replication lag, lock waits and error rate throughout — and be prepared to pause rather than abort, since a paused migration costs nothing while an aborted one wastes hours of copying.
• Postponed cutover: the tooling can complete the copy and wait, so the brief table swap happens at a moment a human chooses. Separating the long risky part from the short risky part is the main operational benefit.
• Revert before cutover by deleting the new table — free, and the reason to postpone the swap.
• Revert after cutover requires the original table to have been retained rather than dropped. Keep it for a defined period, which is the only meaningful rollback available.

The application-side discipline that makes it safe

Schema changes must be backwards compatible with the currently-deployed code, always. The expand-contract sequence:

1. Expand: add the new column, nullable, with the old code still running and ignoring it.
2. Deploy code that writes both old and new.
3. Backfill the new column for existing rows.
4. Deploy code that reads the new column.
5. Contract: remove the old column, in a later release.

Each step is independently deployable and independently revertible, which is what makes the whole sequence safe. The failure mode is combining steps — shipping a schema change and the code that requires it together, after which rolling back the code leaves a schema the old version cannot use, and rolling back the schema destroys data the new version wrote.