Non-Blocking Schema Changes
By Lucy Burns |
With the launch of PlanetScale, we are excited to share more about the non-blocking schema change workflow we’ve built for our platform.
The challenge
We love developers. We obsess over how to make them more productive with their database. In every conversation with our customers, we’ve heard that schema changes are one of the biggest pain points when it comes to using a relational database. We’ve heard about schema change processes that require opening tickets to get a DBA review for each change. This can take weeks.
At some companies, engineering teams won’t change or update certain columns in their production databases because the migration will take too long and will cause performance issues. Others have told us about how they turned columns in their relational databases into JSON stores, just to avoid schema migrations.
While other technologies have grown more and more developer friendly, databases remain difficult to use, in part because of the challenges schema changes present.
Several options are available to alleviate some of the pain. Liquibase and Flyway are two more manual tools used to handle the challenges of schema versioning and also provide some deployment management capabilities. Both pt-online-schema-change and gh-ost offer online or non-blocking schema changes (schema change migrations that don’t lock tables while being deployed). They do so by creating a new table that is a copy of the given table. The schema changes are applied to the new table and the data in the original table is copied over. Once that is complete, the original table is replaced by the new table. However, these tools are often run manually and require the support of additional infrastructure. (Read about how GitHub implements gh-ost here.)
PlanetScale is a platform designed with an out-of-the box workflow that doesn’t require additional management overhead and protects our users from making changes that block databases, lock individual tables, or slow down production during schema changes.
We want developers to push schema changes as easily, and as often, as they push code changes.
Non-Blocking Schema Changes
Using Vitess and gh-ost under the hood, we provide our users with a safe, easy, reliable way to push schema changes to production.
Our non-blocking schema change workflow:
- Allows users to test out schema changes on a branch that is isolated
- Analyzes schema changes in advance to ensure there are no conflicts
- Deploys schema changes in the background without impact to production
How does it work?
Branching
We provide a database branching feature that allows users to create sandbox environments for testing database changes. When a user needs to make a schema change, they create a database branch from their production database, which is automatically deployed with a copy of the production schema. The user can test out schema changes on their branch without worrying about impacting the production database.
Deploy requests
Once a user is satisfied with their schema changes, they can create a deploy request. Users have the option to request a review of their changes from a teammate, or they can add their deploy request directly to the deploy queue.
Deploy queue
The deploy queue represents all of the deploy requests, or schema changes, for a given database that are awaiting deployment. PlanetScale deploys schema changes in the order in which they are received. (In our experience, deploying schema changes one at a time is generally more efficient than running them concurrently, with a few exceptions.)
When the deploy request reaches the front of the queue, the deployment to production begins. This process happens in the background and is sensitive to production traffic. If there’s a spike in traffic, the schema change migration will scale down to avoid using resources needed to handle the increased traffic.
What about conflicts?
To avoid migrating a schema change that will conflict with the production schema, PlanetScale analyzes the schema changes in advance of deployment.
When a user creates a deploy request, PlanetScale automatically checks for conflicts, analyzing the schema on the branch against the main branch schema at the time of the branching. PlanetScale also analyzes the schema changes against current schema on main, which may have changed in the time since the branch was created, ensuring that no conflicts exist.
Additionally, when a user adds a deploy request to the deploy queue, PlanetScale checks the schema changes in the deploy requests ahead of that user’s deploy request for any potential conflicts. If a conflict exists, the deploy request is rejected from the queue, and the user is notified of the conflict. This prevents users from having to wait until it is their turn to deploy, only to discover unanticipated conflicts with their schema changes, and with longer running migrations, this can mean a time savings of up to a few days.
Can I try it out?
We’ve created a demo database on PlanetScale that walks you through the non-blocking schema change workflow with real data. Give it a try.