How to Add, Rename, and Backfill Database Columns Safely
When you change a database column in a production service, the service's code and the database rarely switch at the same instant.…
When an operator changes the structure (schema) of a large table in a live MySQL database, the work uses disk, CPU, and I/O. MySQL also needs a brief lock on the table definition to finish some changes, and that lock can make application requests wait. An online schema migration tool copies the table in stages while the original continues to serve traffic, reducing the risk of a long, hard-to-control rebuild.
GitHub built gh-ost, an open-source tool that does this work in a paced, observable way. It copies rows to a second table, reads live changes from MySQL’s binary log, and pauses or slows its own work when conditions call for it. The final table swap still needs careful planning, and the migration still uses database resources. “Boring” means operators can control and inspect the work, not that the change has no impact.
MySQL’s native online data definition language (DDL), which provides commands for changing database structures, handles some changes efficiently. These include operations that run instantly or modify a table in place. Other changes rebuild the table. For those, MySQL documents limits such as the inability to pause or throttle the DDL’s CPU and I/O use, and the need for an exclusive metadata lock in the final phase of some operations. The right first step is to check the exact change against the MySQL online DDL documentation for the server version in use.
When native DDL does not offer enough control, gh-ost uses a shadow table, a separate table with the desired schema. The tool copies existing rows from the original table in chunks, so it can pace its work rather than attempt one large copy. Production reads and writes continue against the original table while that copy proceeds.
New writes still need to reach the shadow table. MySQL records row changes in its binary log, a stream of events used for replication and recovery. gh-ost reads those events and applies the relevant changes to the shadow table. When the copy catches up and the tool is ready to finish, gh-ost attempts to swap the two tables. GitHub describes this migration design as a way to run the work outside application transactions and give operators more control over its pace.
A trigger-based migration tool attaches database triggers to the original table. Each application write then invokes extra work to update the shadow table in the same transaction context. gh-ost’s triggerless design instead reads row changes from the binary log and applies them asynchronously, separately from the application’s writes.
That separation lets an operator pause gh-ost’s work on the shadow table without adding trigger writes to every transaction that changes the original table. gh-ost still reads and writes table data and uses database resources. It also moves data over the network. Its design documentation describes the tradeoff as greater operational control alongside a more complex asynchronous path.
The tool also depends on the database setup. The project’s requirements and limitations call for row-based binary logging, which records changes to individual rows, with a FULL row image. They also require a primary or unique key shared by the original and shadow tables. gh-ost uses this key to identify rows, and the key must have no NULL values. The documentation also says gh-ost does not support foreign-key constraints, which link rows in different tables. Check the requirements for the exact gh-ost release and database service, including privileges and replica configuration, before scheduling a production migration.
Operators can set limits for database load and replication lag, the delay before changes on the primary reach a replica. gh-ost pauses its work when the database or replica reaches those thresholds. They can also pause the migration manually and inspect progress, including copied rows and changes waiting to be applied. Set thresholds from the behavior of your own database; example values from another deployment do not establish safe limits for yours. The project documents runtime controls in its command-line flags.
The table swap, called cut-over, deserves its own plan. Before swapping, gh-ost must ensure that the shadow table has received the outstanding changes. Its documented atomic cut-over uses locks and checks so the swap either succeeds as a unit or returns to the pre-cut-over state. Pending connections can be blocked briefly. A completed row copy therefore does not mean the migration is finished: the operator still needs to monitor the cut-over and confirm that the application can use the new table.
An operator can configure gh-ost to wait for approval before cut-over, then choose a suitable window for the brief locking step. The cut-over documentation also describes a two-step alternative that briefly leaves the table unavailable between renames; the project recommends its atomic approach. Keep an operator available during the swap, and verify the configured behavior before running the migration.
A replica, a copy of the database that receives changes from the primary, provides a useful place to test the table copy and the application of binary log changes without directing the migration’s writes to the primary database. GitHub described testing migrations on dedicated production replicas and comparing table contents with checksums, calculated values used to check whether data matches, in its account of MySQL testing automation. That is evidence of GitHub’s practice, not a guarantee that a replica test will predict every production outcome. Confirm that the replica’s schema and workload resemble the primary’s, and verify that the test actually covers the intended alteration.
GitHub’s earlier account says the company made schema changes multiple times per day at the time and wanted to reduce user impact. The later testing approach shows that repeated testing and validation made frequent changes manageable, while operators still had to check the results.
After a migration, compare the resulting schema and data, then watch application behavior and replication health. Keep the old table only under a deliberate cleanup and recovery plan. The old table does not by itself guarantee a safe rollback, especially after new writes have begun reaching the replacement table. gh-ost release notes describe checkpoint and revert capabilities with specific prerequisites, so verify those conditions for the installed release rather than treating them as universal recovery features.
Use native DDL when the exact operation and MySQL version support an acceptable algorithm and lock level. MySQL’s ALGORITHM and LOCK options help specify what the server should use or permit; test them on a representative table before relying on them in production.
Consider pt-online-schema-change when its trigger-based design better fits the schema or the operators’ experience. Percona documents its chunked copy, trigger-based change capture, and controls for replication lag and server load in the tool documentation. Compare the actual trigger and foreign-key constraints, the database topology, and the write workload. Neither tool is the right choice for every table.
Check trigger support carefully if the source table already has triggers. gh-ost’s requirements page says triggers are unsupported, while its v1.1.8 release notes describe added trigger support under specified conditions. That documentation conflict makes the exact release behavior a point to verify before execution.
Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.
Take a look at vroni.com