iTranslated by AI
Zero-Downtime Schema Updates for Massive Tables with PlanetScale
PlanetScale is a MySQL-based serverless database.
I was struggling with how to handle schema updates for huge tables, but I was so impressed that I could do it quickly without downtime or load using PlanetScale's standard features that I decided to write this article.
Background
I run a service called AI Butler.
It's a game where you freely enter character abilities as text and have OpenAI's AI judge the battle.
Being a unique type of game, it has gained quite a bit of traction, and currently, 30,000 to 40,000 battles are conducted every day.
Problem
And there lies the problem. While the battle results themselves just display what the AI outputs, they are saved in the DB so they can be reviewed later. This means that amount of battle results is added every single day...
When I first released it, it wasn't a problem, but the storage on PlanetScale reached 20GB, incurring additional costs, and it gradually became more than I could manage as an individual.
Load is increasing
It's not just about displaying results; there are also lists of challenged battles, where you can see the history of battles fought or received, organized by user and character. This history has started becoming quite heavy; while it's fine for some, some users have started appearing who take about 40 seconds to display their history page.
PlanetScale has an "Insights" page where you can see query trends, and it revealed that the query for user history lists was by far the heaviest.
Unable to add indexes
You might think, "Why not just add an index?" I agree, but the data volume has grown so much that it's become a bit risky. It would be a disaster if things like "Wait, my results are missing..." happened or if the game became unplayable.
I had noticed this a long time ago, but I was too busy and kept putting it off, eventually leaving it undone. I spent my days in regret, wishing I had handled it sooner...
One might say, "Just put it into maintenance," but announcing it is difficult and it's a bit of a hassle...
PlanetScale allows schema changes for huge tables without downtime or load
Separately from that, as the storage capacity increased, so did the costs. In PlanetScale, the base fee for the Scaler Pro plan is higher than the Scaler plan, but the additional DB fees are cheaper. Since it seemed that switching to Pro wouldn't make much difference in cost, I decided to upgrade the plan.
At that timing, I was looking into what convenient features were available, and as I re-read various descriptions, I came across PlanetScale's "Deploy Request" feature. Similar to a GitHub pull request, you create a branch, make changes, and then merge them to apply them to production.
I knew about the feature itself, but upon proper research, I found that it allows schema changes even for huge tables without any downtime or load. Seriously...!? I was surprised to find a solution in such an unexpected place. I'll omit the details here, but the following article describes how it works.
"If I can do it with this, it'll solve everything!" So, I actually gave it a try.
What I actually did
Moving to the Production branch
PlanetScale has Development branches and Production branches. Production branches support deploy requests and offer high availability. Embarrassingly, because I had been using it quite casually, I had been using a Development branch the whole time.
Therefore, as preparation this time, I switched it to a Production branch. I contacted support in advance just in case, and it seems the connection information remains the same.
The switch went smoothly. However, the DB became unreachable for 1 to 2 minutes. Please be aware of this.
Setting up to accept deploy requests
There is a setting called "Safe migrations," so I enabled it. By enabling this, schema changes can only be performed via deploy requests. You will no longer be able to make changes directly by executing SQL.
API
PlanetScale provides an API.
I decided to use this to integrate and automate the process into my CI.
Create a deploy request
As a preliminary step, I use Prisma, but I make sure to run migrations automatically beforehand. At this time, migrations are performed against a separately created derived branch.
There is an API for creating deploy requests, so I'll use that.
Deploy the deploy request
Actually accept this request and reflect it on the Production branch. Since the API documentation was a bit lacking and unclear in this area, I experimented with several things.
First, Approve it. I haven't tested whether this step is necessary or not, so it might be okay to skip it. I use the following API to set it to "Approved".
To specify which request to handle, use the number. This is included in the response when the deploy request is created, so I use that.
Next, actually deploy. The API for deployment was this one.
However, whether it's right after Approving or right after creating, executing them in rapid succession results in an error saying it's still in process and cannot be performed. Therefore, I wait for about 10 seconds before executing it.
Basically, that's it. It should be set to automatic reflection by default, so if you just leave it, it will be reflected in Production.
Considering how to integrate into CI
Originally, I was performing migrations in the middle of CloudBuild. However, as mentioned above, the deployment command is a "Queue". In other words, it is not executed immediately. This means it's possible that the system deployment is complete, but the DB hasn't been reflected yet. Therefore, I decided against incorporating it into the existing CI.
Setting it up to trigger when pushed to a specific branch
There was an article on the official blog about using the PlanetScale CLI with GitHub Actions.
However, looking at the article, it seemed a bit tedious, and since I had already created a JS file to handle deploy requests, I decided to just use the idea mentioned above.
Specifically, the deploy request is executed only when a push is made to a branch like db/**. This seemed like a good approach as it allows me to control the timing myself. It's a bit of a trade-off with convenience since pushing to main won't handle everything automatically anymore, though.
It looks something like this. The JS file mostly just executes the sample code generated by the API documentation sequentially. (Maybe npx prisma generate isn't necessary?)
name: Migration
on:
push:
branches:
- db/**
jobs:
migration:
runs-on: ubuntu-latest
env:
PSCALE_API_ENDPOINT: ${{ vars.PSCALE_API_ENDPOINT }}
DATABASE_URL: ${{ secrets.CI_DATABASE_URL }}
PSCALE_API_ID_AND_TOKEN: ${{ secrets.PSCALE_API_ID_AND_TOKEN }}
steps:
- name: checkout
uses: actions/checkout@v3
- name: setup-node
uses: actions/setup-node@v3
with:
node-version: 18.14
cache: yarn
- run: yarn
- run: npx prisma generate
- run: npx prisma db push
- run: node migrate.js
Along with that, I removed the migration execution part from CloudBuild.
By the way, after operating this for a while, I'm starting to feel that just creating the deploy request might be sufficient. This is because I end up staring at the PlanetScale console until the deploy request is generated and merged... I just get too nervous and can't help but keep an eye on it.
What happened when I actually did it
Now that preparations and testing were finished, I actually went ahead with it. At this point, the table in question contained 3.5 million records. It had already reached a level where even running direct queries in the PlanetScale console for things like a month's worth of aggregation would time out.
What I did was set default values as preparation for a column I wanted to delete, and replaced four indexes with a composite index.
Here is what it looked like when I actually did it:
It completed in about 30 minutes. I tested it during that time, and battles could be conducted as usual, and displaying history was also fine.
Thanks to this, the page that used to take 40 seconds to load now displays instantly. (Although it seems further adjustments will be needed since there are various search patterns.)
Summary
In any case, it was a huge help to be able to quickly and easily solve something that had been a long-standing bottleneck and was only expected to get worse. The sense of security is incredible, knowing that I can now make as many changes as I want.
PlanetScale is truly amazing; I don't feel like I can ever go back.
Discussion