How do online schema changes work?

👻 What do online schema changes have to do with Halloween? Have you ever heard of ghost tables? Let's answer that question by going over how online schema changes tools work.

Modifying a table's schema is one of the most challenging problems when it comes to relational databases, especially MySQL. Running a direct ALTER TABLE is a blocking operation, which means that when you run it, the table becomes completely inaccessible, even for reads.

To avoid locking tables so that your database remains operational with no interruption to your users, most people use online schema change tools to perform schema changes. These are external, small tools or scripts, that hook onto the database and run a migration through emulation.

Let's look at how online schema changes tools work and what role ghost tables play.

How do online schema change tools work

There are a few different tools you can use to perform online schema changes. They all work similarly for the most part, with a few small differences. One thing they all have in common is that they perform migrations by emulating a schema change on a ghost table.

The general flow looks like this:

1. Create a new, empty table with the same schema as the original table. This is called the ghost table.
2. Instead of altering the original production table directly, ALTER the ghost table with the schema changes that need to be made. Since the table is empty, there is no overhead to this operation and it happens very quickly.
3. Validate that the structural change is compatible with tooling requirements.
4. Analyze the diff between the original table and the ghost table.
5. Begin a long running process of copying existing rows from the original tables to the ghost table. Rows are copied in small batches.
6. Capture or react to ongoing changes to the original table, and continuously apply them onto the ghost table.
7. Monitor general database and replication metric, and throttle so as to prioritize production traffic as needed.
8. When the existing data copy is complete, the migration is generally considered as ready to cutover.
9. Then comes the final step — the cutover. During cutover, the original table is renamed away, and the ghost table, which now has the new schema and all of the data copied over, takes its place. Up to some locking or small table outage time, the users and apps are largely unaware that the table has been swapped under their feet.

With this process, the schema migration flow never changes or updates the original table. We only ever operate on the ghost table. We created a ghost table with an exact copy of the original table's schema, then we ran the ALTER TABLE command on the ghost table.

Let's take it even further

Most online schema change tools wipe their hands and call it a job well done after this. But not PlanetScale.

Believe it or not, that original table that we renamed away in step 9 actually can come in handy. After cutover when the ghost table takes over, we keep that original table around for a bit. Why? It has already all of the data and the previous schema right before the last migration.

If you ship a schema change, go through this whole online schema change process, then realize that you made a mistake and need to go back to the original schema, wouldn’t it be great to be able to just click a button and the tables swap back? That’s exactly what PlanetScale’s revert button does!

Not only do we keep that original table around in case you need to revert, but we also continue syncing data from the ghost table back to the original table (as in steps 5 and 6) so that if you do revert, you don’t lose any data that came in while the new schema change was live. It’s really that simple!

In a lot of cases, there’s nothing scarier than running schema changes on your database. But it doesn’t have to be that way. PlanetScale aims to take the fear out of manipulating your database with our online schema changes. Turns out ghost (tables) aren’t so scary after all.

Happy Halloween! 👻