Cloud

PlanetScale review: Horizontally scalable MySQL in the cloud


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 if 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.

As many as 10 distributed database products compete with PlanetScale, depending on whether you count NoSQL databases. Cloud-specific examples include Google Cloud Spanner (relational), Azure Cosmos DB (multi-model NoSQL), Amazon DynamoDB (key-value and document), and Amazon Aurora (relational). Vendor examples include CockroachDB (relational), Fauna (relational/serverless), Neo4j (graph), MongoDB Atlas (document), DataStax Astra (wide-column), and Couchbase Cloud (document). Follow those links to my reviews of these products.

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.

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.

martinheller@Martins-Retina-MacBook ~ % pscale shell onboarding-demo main
onboarding-demo/|⚠ main ⚠|> show tables;
+---------------------------+
| Tables_in_onboarding-demo |
+---------------------------+
| reminders                 |
+---------------------------+

onboarding-demo/|⚠ main ⚠|>

martinheller@Martins-Retina-MacBook ~ % 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:

martinheller@Martins-Retina-MacBook ~ % 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.

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.

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.

Copyright © 2021 IDG Communications, Inc.



READ SOURCE

This website uses cookies. By continuing to use this site, you accept our use of cookies.