Change the number of shards with MoveTables

Adding or removing shards from a sharded keyspace requires some rebalancing, as you may be moving data from one MySQL instance to another. In PlanetScale, this is done with a Vitess MoveTables workflow from one sharded keyspace to another. This involves creating a new sharded (target) keyspace with the new desired number of shards and transferring all of the data from your current (source) keyspace to the new target. This operation is done with no downtime.

The steps in this documentation are similar to those in the Sharding quickstart, however, there is one important addition that you cannot skip. Be sure to review Step 7: Remove "require_explicit_routing": true, as it is a crucial step that differs from sharding an unsharded keyspace.

If you are sharding an existing table in an unsharded keyspace, follow the instructions in the Sharding quickstart documentation.
If you are creating a new table that you want in your existing sharded keyspace, follow the instructions in the Sharding new tables documentation.
If you simply need to adjust the size of each shard, and not the number of shards, you can do so from the Clusters page in the dashboard.

These are advanced configuration settings that expose some of the underlying Vitess configuration of your cluster. Misconfiguration can cause availability issues. We recommend thoroughly reading through the documentation in the Sharding section of the docs prior to making any changes. If you have any questions, please reach out to our support team.

Throughout this guide, we will refer to the source keyspace and target keyspace, which are defined as follows:

Source keyspace: The original sharded keyspace from which you are moving the tables you wish to shard.
Target keyspace: The new sharded keyspace that you are moving the selected tables to.

You run the move with pscale branch vtctl move-tables, so make sure you have the pscale CLI installed and authenticated. You must be an Organization Administrator or a Database Administrator of the database. This guide also assumes that you either are already using @primary in your application code to target your keyspaces or you do not directly set a database name in your application code.

Pre-sharding checklist

There is a small amount of upfront work that needs to happen prior to sharding your table(s) again.

1. Prepare to move all table(s) from source keyspace

Some common signals that a table may benefit from further sharding include:

The table has become very large and query performance has degraded due to this
Schema changes to the table take hours
You expect the table to grow quickly and want to shard it further before it becomes a problem

We strongly recommend moving all table(s) from the source keyspace to the target keyspace, so that by the end of this process your database has at most two keyspaces: one unsharded keyspace, and one sharded keyspace.

2. Create another sharded keyspace

To set up another sharded keyspace:

Go to the “Clusters” tab in the left nav in the PlanetScale dashboard.

Click “New keyspace”.

Enter the keyspace name (for example, metal-sharded-2).

Select the shard count and choose the cluster size for this keyspace. Keep in mind, creating a sharded keyspace will use the selected size for each shard. For example, if you are creating 4 shards and choose the PS-80 cluster size, we will create 4 PS-80s, each with 1 primary and 2 replicas.

Select the number of additional replicas, if any, that you’d like to add to each cluster. Each cluster comes with 2 replicas by default, so any number you choose will be in addition to those 2.

Review the new monthly cost for this keyspace below. This is in addition to your existing unsharded keyspace, as well as any other keyspaces you add.

Once satisfied, click “Create keyspace”.

3. Add `"require_explicit_routing": true`

If you are using Vitess global routing (for example, if you are using @primary), you will get ambiguous table errors once you add Vindexes to your new keyspace. To prevent this error, you must temporarily add require_explicit_routing to your new keyspace’s VSchema:

{
  "sharded": true,
  "require_explicit_routing": true,
}

This will instruct Vitess’s global routing to exclude your new keyspace from routing until explicitly targeted. This is just temporary. You will remove this later in this tutorial before completing the workflow. You can choose to do this step, and the following step, by one of the following methods:

Safe migrations off: modify the target keyspace VSchema directly on the Clusters page
Safe migrations on: modify the target keyspace VSchema using deploy requests

4. Copy Vindexes and auto-increment VSchema settings

Assuming your sharding scheme will remain the same, once you’ve completed the previous step of adding "require_explicit_routing": true, you can copy the relevant parts of your source keyspace VSchema into your target keyspace VSchema. Since we recommend moving all tables from the source to the target keyspace, your target VSchema will look exactly the same as your source VSchema, with the addition of "require_explicit_routing": true. For example, if you are moving the tables users and exercise_logs, and your source keyspace VSchema looks like this:

{
  "sharded": true,
  "vindexes": {
    "xxhash": {
      "type": "xxhash"
    }
  },
  "tables": {
    "exercise_logs": {
      "column_vindexes": [
        {
          "name": "xxhash",
          "columns": ["user_id"]
        }
      ],
      "auto_increment": {
        "column": "id",
        "sequence": "`unsharded`.`exercise_logs_seq`"
      }
    },
    "users": {
      "column_vindexes": [
        {
          "name": "xxhash",
          "columns": ["id"]
        }
      ],
      "auto_increment": {
        "column": "id",
        "sequence": "`unsharded`.`users_seq`"
      }
    }
  }
}

Your target keyspace VSchema will look like this:

{
  "sharded": true,
  "require_explicit_routing": true,
  "vindexes": {
    "xxhash": {
      "type": "xxhash"
    }
  },
  "tables": {
    "exercise_logs": {
      "column_vindexes": [
        {
          "name": "xxhash",
          "columns": ["user_id"]
        }
      ],
      "auto_increment": {
        "column": "id",
        "sequence": "`unsharded`.`exercise_logs_seq`"
      }
    },
    "users": {
      "column_vindexes": [
        {
          "name": "xxhash",
          "columns": ["id"]
        }
      ],
      "auto_increment": {
        "column": "id",
        "sequence": "`unsharded`.`users_seq`"
      }
    }
  }
}

Move the tables with MoveTables

The examples below use a database named mydb, the main production branch, the current metal-sharded keyspace as the source, and the new metal-sharded-2 keyspace as the target.

Step 1: Create the workflow

pscale branch vtctl move-tables create mydb main \
  --workflow reshard_metal \
  --source-keyspace metal-sharded \
  --target-keyspace metal-sharded-2 \
  --all-tables \
  --defer-secondary-keys \
  --format json

This creates a MoveTables workflow named reshard_metal that moves every table from the source keyspace, and starts it right away. To move only some tables, pass --tables with a comma-separated list instead of --all-tables. --defer-secondary-keys creates secondary indexes after the copy finishes, which makes the copy much faster.

Step 2: Watch the copy

As soon as the workflow starts, Vitess copies rows of the tables from your source keyspace to your target keyspace. It uses a combination of SELECT * FROM table and binlog-based replication, and redistributes the rows across the target keyspace’s shards using the Vindexes in its VSchema. Check progress with status:

pscale branch vtctl move-tables status mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --format json

Once every table is copied, the streams move to Running: Vitess keeps replicating every new write on the source keyspace to the target keyspace, while the source keyspace still serves all primary and replica traffic. You can also follow the workflow from the dashboard. Click “Workflows” in the left nav, select your branch, and open the workflow to see its streams, per-table copy progress, replication lag, and traffic routing.

Step 3: Verify data consistency

Once the streams are Running, verify that the source and target keyspaces hold the same data with a VDiff:

pscale branch vtctl vdiff create mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --format json

Pass the uuid from the output to vdiff show, and repeat until the VDiff completes:

pscale branch vtctl vdiff show mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --uuid <VDIFF_UUID> \
  --format json

If the VDiff reports mismatches, do not switch traffic until you have resolved them.

Step 4: Switch replica traffic

Switch replica traffic first to test reads from the new keyspace:

pscale branch vtctl move-tables switch-traffic mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --tablet-types REPLICA,RDONLY \
  --format json

You can skip this step and switch primary traffic directly. Pass --dry-run to see what a switch would do without applying it.

Step 5: Switch primary traffic

When reads from replicas look good, switch primary traffic:

pscale branch vtctl move-tables switch-traffic mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --tablet-types PRIMARY \
  --format json

After primary traffic switches, Vitess replicates writes from the target keyspace back to the source keyspace, so both keep the same data until you complete the workflow.

Step 6: Check traffic in your application

You should now go check out your production application that uses this database to make sure everything is running as expected. You can also check the Insights tab for errors or slow queries. If you notice an issue, switch traffic back to the source keyspace:

pscale branch vtctl move-tables reverse-traffic mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --format json

Step 7: Remove `"require_explicit_routing": true`

You should have added require_explicit_routing to your target keyspace’s VSchema in step 3 of the “Pre-sharding checklist”:

{
  "require_explicit_routing": true,
  ...
}

You’ll need to remove it before completing the workflow.

If you don’t remove this, you may start to see “table not found” errors once the workflow is completed.

Step 8: Complete the workflow

Up until now, you can cancel the workflow or reverse traffic. Completing the workflow is not reversible: it stops replication between the keyspaces and, with the flags below, drops the moved tables from the source keyspace and removes the routing rules. Preview what complete will do with --dry-run first:

pscale branch vtctl move-tables complete mydb main \
  --workflow reshard_metal \
  --target-keyspace metal-sharded-2 \
  --keep-data=false \
  --keep-routing-rules=false \
  --dry-run \
  --format json

If you have removed "require_explicit_routing": true from your target keyspace VSchema, and you are sure you want to proceed, run the same command without --dry-run.

--keep-data=false drops the moved tables from the source keyspace. Add --rename-tables to rename them instead of dropping them. --keep-data=true leaves them in place.
--keep-routing-rules=false removes the routing rules. Pass --keep-routing-rules=true to keep them if some queries still name the source keyspace.

Write both flags with an =: a space-separated value such as --keep-data false is read as --keep-data=true.

Step 9: Check that your production application is working as expected

Finally, check your production application to make sure everything is working as expected. You can check your Insights tab to see if queries are being properly routed to your new keyspace. Insights will also show you any errors, query performance issues, and more. If you moved every table and the source keyspace has no tables left, you can delete it with pscale keyspace delete or from the Clusters page. That’s it! The tables you selected at the beginning are now being served by the new sharded keyspace.

Need help?

Get help from the PlanetScale Support team, or join our Discord community to see how others are using PlanetScale.

Pricing

Platform

Integrations

Guides

Change the number of shards with MoveTables

Pre-sharding checklist

1. Prepare to move all table(s) from source keyspace

2. Create another sharded keyspace

3. Add `"require_explicit_routing": true`

4. Copy Vindexes and auto-increment VSchema settings

Move the tables with MoveTables

Step 1: Create the workflow

Step 2: Watch the copy

Step 3: Verify data consistency

Step 4: Switch replica traffic

Step 5: Switch primary traffic

Step 6: Check traffic in your application

Step 7: Remove `"require_explicit_routing": true`

Step 8: Complete the workflow

Step 9: Check that your production application is working as expected

Need help?

​Pre-sharding checklist

​1. Prepare to move all table(s) from source keyspace

​2. Create another sharded keyspace

​3. Add "require_explicit_routing": true

​4. Copy Vindexes and auto-increment VSchema settings

​Move the tables with MoveTables

​Step 1: Create the workflow

​Step 2: Watch the copy

​Step 3: Verify data consistency

​Step 4: Switch replica traffic

​Step 5: Switch primary traffic

​Step 6: Check traffic in your application

​Step 7: Remove "require_explicit_routing": true

​Step 8: Complete the workflow

​Step 9: Check that your production application is working as expected

​Need help?

Pre-sharding checklist

1. Prepare to move all table(s) from source keyspace

2. Create another sharded keyspace

3. Add `"require_explicit_routing": true`

4. Copy Vindexes and auto-increment VSchema settings

Move the tables with MoveTables

Step 1: Create the workflow

Step 2: Watch the copy

Step 3: Verify data consistency

Step 4: Switch replica traffic

Step 5: Switch primary traffic

Step 6: Check traffic in your application

Step 7: Remove `"require_explicit_routing": true`

Step 8: Complete the workflow

Step 9: Check that your production application is working as expected

Need help?