Database Migration Patterns

In this lesson, you will learn how to execute zero-downtime schema changes in MySQL and MariaDB using the ghost-table migration pattern.

The Challenge of Traditional ALTER TABLE

In standard MySQL/MariaDB environments, running an ALTER TABLE on a large table often triggers a table-copying operation. During this time, the database locks the table to prevent data inconsistency, effectively causing an application outage. For tables with millions of rows, this metadata lock can last for minutes or even hours.

The Ghost-Table Pattern

The ghost-table pattern avoids these locks by performing the schema change on a secondary "shadow" table while keeping it synchronized with the original. Here is the typical workflow:

1. Creation: The migration tool creates an empty table (_ghost) with the desired new schema.
2. Synchronization: The tool adds triggers to the original table. Every time an INSERT, UPDATE, or DELETE occurs on the original table, the trigger immediately replicates that change to the _ghost table.
3. Backfill: The tool copies data from the original table to the _ghost table in small, manageable chunks to avoid overwhelming the system.
4. Cutover: Once the _ghost table is synchronized and caught up with the original, the tool performs a rapid, atomic RENAME TABLE operation. This swaps the original table with the ghost table, completing the migration in a fraction of a second.

Tools like gh-ost (GitHub's Online Schema Migration) or pt-online-schema-change (Percona Toolkit) automate this process. Using gh-ost specifically, you can migrate without triggers by reading the binary log (binlog) directly, which reduces overhead on the master server.

-- Conceptual flow of the cutover
RENAME TABLE production_table TO old_table, 
             ghost_table TO production_table;

By decoupling the schema change from the primary transaction flow, you ensure that your application remains responsive and the database remains available throughout the entire maintenance window.

Key Takeaway: The ghost-table pattern achieves zero-downtime migrations by asynchronously copying data to a side table and performing an atomic rename, bypassing the long-running locks associated with standard ALTER TABLE commands.