Schema recommendations
Overview
With schema recommendations inside of PlanetScale Insights, you will automatically receive recommendations to improve database performance, reduce memory and storage, and improve your schema based on production database traffic.
Schema recommendations uses query-level telemetry to generate tailored recommendations in the form of DDL statements that can be applied directly to a database branch and then deployed to production.
Note
If you are a PlanetScale Enterprise customer, please get in touch with your account manager to learn how you can fully benefit from schema recommendations.
How to use schema recommendations
To find the schema recommendations for your database, go to the “Insights” tab in your PlanetScale database and click “View recommendations.”
You will see the current open recommendations that may help improve database performance. Select a recommendation to learn more.
Each recommendation will have the following:
- An explanation of the recommended changes, including some of the benefits of the recommended change (E.g., reduced memory and storage, decreased execution time, prevent ID exhaustion)
- The schema or query that it will affect
- The exact DDL that will apply the recommendation
- The option to apply the recommended change to a branch for testing and a safe migration
Note
Schema recommendations that depend on your database traffic run once per day. Recommendations that depend only on database schema are run whenever the the schema of your default branch is modified. Schema recommendations are generated only for the database's default branch.
Applying a recommendation
Once you better understand the recommendation, you can apply the recommendation by either:
- Applying it directly through a database branch
- Making the schema change directly in your application or ORM code
We recommend making the schema change directly in your application or ORM code if it is where you manage your schema outside of PlanetScale. We look for different migration framework’s table names in your schema to alert you when you might want to change the schema directly in your application or ORM code. If you manage the schema directly in the database, you can use the following method.
Applying a recommendation directly through a database branch
When directly applying through a database branch, you can apply the recommendation by creating and applying it to a new or existing branch.
After applying the recommendation, click on the branch name to see the recommended schema changes. At this point, you can connect to this branch to do your own testing of the change. You can create a connection string for this branch using the “Connect” button.
Once ready, you can use the “Create deploy request” button on the branch page to open a deploy request to merge the changes with your base branch.
Once it is reviewed and ready to be deployed, you can deploy the changes to production. If you realize you have made a mistake, you can still revert the schema change in the deploy request for up to 30 minutes.
Applying a recommendation directly in your application or ORM code
When directly applying through your application or ORM code, you can take the recommended change from PlanetScale and make the equivalent change in your code. The exact DDL provided in each recommendation will help you know what change to make. The exact change will depend on how you manage your schema in your application code.
Then, once you have made the change, you will run the same migration process you would run for any other schema changes with PlanetScale. We recommend doing this inside a development branch with safe migrations. This will allow you to do your own testing of the change inside the isolated branch. You can create a connection string for this branch using the “Connect” button.
Once ready, you can use the “Create deploy request