Review of PlanetScale: Horizontally scalable MySQL in the cloud

Spread the love

PlanetScale powered by Vitess can contain as much data as you need, but it is currently confined to a single cluster.

PlanetScale

PlanetScale is a MySQL-compatible, serverless database platform powered by Vitess, which is a database clustering system for horizontal scaling of MySQL (as well as Percona and MariaDB). Vitess also powers Slack, Square, GitHub, and YouTube, among others. The Slack deployment of Vitess has about 6,000 servers; the current largest Vitess deployment has about 70,000 servers.

MySQL databases are easy to use when you are just starting out, but when your database grows (for example, after a year or so of operations) you can easily run into its limits. The most commonly used MySQL database engine, InnoDB, has a maximum page size of 64 KB, which limits the row size. InnoDB also has a maximum tablespace size of 256 TB, which limits the size of a table.

Vitess helps you to scale a MySQL database by allowing you to shard it. A shard is a horizontal (row-wise) partition of the data. Once the database is sharded, Vitess takes care of routing queries to the appropriate MySQL instance based on the shard key field value, which lets you keep application changes to a minimum. Vitess also creates much lighter-weight connections than MySQL, allowing it to support thousands of connections, and it has configurable rules to rewrite queries that would hurt database performance.

PlanetScale is basically Vitess on MySQL in the cloud, with a cloud user interface, plus a command line interface (CLI) that you can run on your own machine. The CLI can control almost everything in your PlanetScale instance, from creating databases down to manipulating data. Data manipulation depends on the Oracle MySQL client, which also needs to be installed on your machine.

Depending on whether NoSQL databases are counted, up to ten distributed database products compete with PlanetScale. Google Cloud Spanner (relational), Azure Cosmos DB (multi-model NoSQL), Amazon DynamoDB (key-value and document), and Amazon Aurora are some cloud-specific examples (relational). CockroachDB (relational), Fauna (relational/serverless), Neo4j (graph), MongoDB Atlas (document), DataStax Astra (wide-column), and Couchbase Cloud are some vendor examples (document). Follow those links to read my product reviews.

PlanetScale setup

Getting started with PlanetScale is about as easy as it could be. The default is the free developer plan, which doesn’t require a credit card — just a name, email address, password, and eventually an organization name. If and when you exceed the limits of the developer plan, you can add a credit card and switch to the scaler plan.

You also need to install the PlanetScale CLI on your development machine, as well as the MySQL client shell. On a Mac, you install both with Homebrew. On Linux, you download the appropriate binary and use dpkg (Debian) or rpm (Red Hat). On Windows, download and run the installers.

planetscale 01 IDG
To get started with PlanetScale, create a log-in and an organization name.

planetscale 02 IDG
Once you have an organization name, you can create one or more databases. Here I created a tutorial database with the default name.

On my MacBook Pro, the brew installation of pscale was quick and straightforward. The MySQL client installation took over an hour, however, as the Homebrew formula pulled in all sorts of developer packages, but it eventually succeeded.

READ ALSO:  What comes after Kubernetes?

PlanetScale GUI and CLI

You can see examples of the PlanetScale GUI throughout this article. Before you can use the CLI, you need to authorize it to connect with your PlanetScale account, pscale auth. The pscale shell show tables command below shows the state of the tables in the onboarding demo database after deploying the sample branch into main.

[email protected] ~ % pscale shell onboarding-demo main
onboarding-demo/|⚠ main ⚠|> show tables;
+---------------------------+
| Tables_in_onboarding-demo |
+---------------------------+
| reminders                 |
+---------------------------+

onboarding-demo/|⚠ main ⚠|>

[email protected] ~ % pscale –help
pscale is a CLI library for communicating with PlanetScale’s API.

Usage:
pscale [command]
Available Commands:
audit-log      List audit logs
auth           Login and logout via the PlanetScale API
backup         Create, list, show, and delete branch backups
branch         Create, delete, diff, and manage branches
completion     Generate completion script for your shell
connect        Create a secure connection to a database and branch for a local client
database       Create, read, delete, and dump/restore databases
deploy-request Create, review, diff, and manage deploy requests
help           Help about any command
org            List, show, and switch organizations
region         List regions
service-token  Create, list, and manage access for service tokens
shell          Open a MySQL shell instance to a database and branch
signup         Signup for a new PlanetScale account

Flags:
–api-token string            The API token to use for authenticating against the PlanetScale API.
–api-url string              The base URL for the PlanetScale API. (default “https://api.planetscale.com/”)
–config string               Config file (default is $HOME/.config/planetscale/pscale.yml)
–debug                       Enable debug mode
-f, –format string               Show output in a specific format. Possible values: [human, json, csv] (default “human”)
-h, –help                        help for pscale
–no-color                    Disable color output
–service-token string        Service Token for authenticating.
–service-token-name string   The Service Token name for authenticating.
–version                     Show pscale version

Use “pscale [command] –help” for more information about a command.

PlanetScale branches

As a rule, schema changes can be a pain in the neck to deploy in production relational databases, especially while they are actively processing transactions. PlanetScale improves this situation by adding a branching feature; the terminology may be familiar if you use Git.

When you add a branch, you are making a copy of the database schema (without data) to a new empty database. You can then modify the schema in the branch, and finally add the branch to the deploy queue to modify the main production database. Any schema conflicts are caught when you attempt to deploy. Valid deployments may be throttled to avoid any impact on production queries.

Schema changes in PlanetScale are non-blocking. Note that PlanetScale doesn’t support foreign key constraints, because they can cause database failures during online schema changes.

planetscale 03 IDG
In PlanetScale, you can only change the database schema in a branch. When you’re sure it’s OK, you can deploy the branch to the main production database.

PlanetScale regions

PlanetScale currently runs in three regions, with more planned for the future:

[email protected] ~ % pscale region list
  NAME      SLUG      ENABLED 
 --------- --------- ---------
  US East   us-east   Yes     
  US West   us-west   Yes     
  EU West   eu-west   Yes

You can only choose a single region when you create a database. You should choose the region closest to your applications. You can create branches in other regions in order to place them near the developers making schema changes and testing them.

READ ALSO:  JDK 16 begins to take shape

PlanetScale authentication and MFA

There are three ways to authenticate with PlanetScale: email address and password, single sign-on, and OAuth via GitHub. If you use email address and password, you can optionally enable multi-factor authentication (MFA). MFA on PlanetScale uses the time-based one-time password (TOTP) method, as implemented in Authy and other MFA apps. PlanetScale also generates recovery codes, to be used if you lose your mobile device.

PlanetScale security, logs, and backups

PlanetScale is SOC 2 Type 2 compliant, and the SOC audit report is available on request. PlanetScale encrypts all data at rest, and provides authentication (see above), audit logging, and user access management. Audit logs show all database-level and branch-level events for the organization, including who performed the action, their IP address, and the date and time of the action. There are security logs as well, to record authentications and user-level configuration changes.

By default, PlanetScale automatically backs up all your database branches daily, with a two-day retention period. Automatic backups are free. You can also schedule manual backups, for example to take monthly backups for long-term retention.

planetscale 04 IDG
PlanetScale can generate passwords, and format connection strings 12 ways.

Connecting PlanetScale to apps

PlanetScale can generate passwords and use them to format connection strings for general use, the MySQL console, Elixir, Go, Java, Laravel, .NET, PHP, Prisma, Python, Rails, and Rust. There are tutorials for connecting normal apps, as well as Rails and Express.js apps, which can generate schemas. There are also tutorials for deploying to Vercel and Heroku, and doing automatic migrations with Rails and Prisma. PlanetScale can automatically copy the migration data table from the working branch to main when you deploy schema changes.

Relational, not distributed

PlanetScale will serve you well as a MySQL-compatible cloud database that can scale to hold essentially as much data as you need. As of now, it’s not a globally distributed database, despite its name. While there are currently three regions around the world where you can deploy PlanetScale, you need to choose just one.

PlanetScale’s scalability is a big plus. So is its ability to deploy schema changes online without blocking production workloads. PlanetScale’s lack of support for foreign key constraints may be annoying, but that’s not likely to be a show-stopper for most applications. PlanetScale is most likely to attract developers working on a new, rapidly-changing application.

PlanetScale doesn’t yet support automatic sharding; that’s planned for 2022. For now, PlanetScale’s tech support will work with you on sharding using the underlying Vitess capabilities when you get to the point where you’ve outgrown a single MySQL instance.

If your application requires a distributed relational database, there are several alternatives, although none of them includes the branching and non-blocking schema changes features of PlanetScale: Google Cloud Spanner, CockroachDB, and Amazon Aurora are probably the top candidates. If you expect your database to eventually grow to more than 30TB of storage, though, you should check the current size limits of your candidate.

READ ALSO:  What to expect from AWS re:Invent 2020

For example, Aurora recently inceased its maximum storage size from 64 TB to 128 TB; Cloud Spanner is currently limited to 2 TB per node, maximum 15 nodes; and CockroachDB recommends a maximum of 2.5 TB per node, with no hard limit on the number of nodes, and customer examples (such as Baidu) with hundreds of nodes.

Cost: Developer edition: Free (three free databases, 10 GB storage per database, 100 million rows read per month per database, 10 million rows written per month per database). Scaler edition: $1.25 per month per 1GB storage, $15 per month per 100 million rows read, $15 per month per 10 million rows written.

Platform: PlanetScale cloud.

  •  

    PlanetScale

     

    PlanetScale will serve you well as a MySQL-compatible cloud database that can scale to hold essentially as much data as you need. As of now, it’s not a globally distributed database, despite its name. While there are currently three regions around the world where you can deploy PlanetScale, you need to choose just one.

    Pros

    • MySQL compatible
    • Scales without hard limits, using Vitess sharding
    • Able to perform online schema changes using a branching mechanism
    • Easy and free to get started

    Cons

    • Not a distributed database
    • Doesn’t yet shard automatically
    • Doesn’t support foreign key constraints

Martin Heller is a contributing editor and reviewer for InfoWorld. Formerly a web and Windows programming consultant, he developed databases, software, and websites from 1986 to 2010. More recently, he has served as VP of technology and education at Alpha Software and chairman and CEO at Tubifi.

 244 

Leave a Reply