ALTER VSCHEMA syntax reference

The VSchema describes how your data is organized across keyspaces and shards: which tables exist in each keyspace, how sharded tables map rows to shards via Vindexes, and how IDs are generated with sequence tables. ALTER VSCHEMA statements let you modify the VSchema using SQL over a regular MySQL connection to your database branch, as an alternative to editing the VSchema JSON directly in the Clusters page or with the pscale CLI.

ALTER VSCHEMA statements do not work on a branch with safe migrations enabled, which is the recommended setup for production branches. Instead, run them on a development branch and promote the changes to production with a deploy request. See Modifying VSchema for the full workflow.

ALTER VSCHEMA only changes Vitess routing metadata. It never creates, alters, or drops the underlying MySQL tables — you manage those with regular DDL.

Statement overview

Statement	Purpose
`ALTER VSCHEMA ADD TABLE`	Add a table to an unsharded keyspace’s VSchema
`ALTER VSCHEMA DROP TABLE`	Remove a table from a keyspace’s VSchema
`ALTER VSCHEMA ADD SEQUENCE`	Register a table as a sequence table
`ALTER VSCHEMA DROP SEQUENCE`	Remove a sequence table from the VSchema
`ALTER VSCHEMA ... ADD AUTO_INCREMENT`	Back a column with a sequence table
`ALTER VSCHEMA ... DROP AUTO_INCREMENT`	Remove the sequence backing from a table
`ALTER VSCHEMA CREATE VINDEX`	Define a Vindex in a keyspace
`ALTER VSCHEMA DROP VINDEX`	Remove a Vindex definition from a keyspace
`ALTER VSCHEMA ... ADD VINDEX`	Attach a Vindex to one or more columns of a table
`ALTER VSCHEMA ... DROP VINDEX`	Detach a Vindex from a table

How keyspaces are resolved

Every ALTER VSCHEMA statement operates on exactly one keyspace. Vitess determines which one as follows:

If the table name is qualified (keyspace.table), the qualifier wins.
Otherwise, the keyspace you are currently connected to (or selected with USE) is used.
If neither is available, the statement fails with an error.

-- Explicit keyspace qualifier
ALTER VSCHEMA ADD TABLE product.product;

-- Uses the current keyspace
USE product;
ALTER VSCHEMA ADD TABLE product;

Keyspace, table, and column names that contain special characters (such as hyphens) or collide with reserved words (such as binary) must be quoted with backticks, just like in regular MySQL statements:

ALTER VSCHEMA ON `my-sharded-keyspace`.users ADD VINDEX shard_index(id) USING xxhash;

Managing tables

Add a table

ALTER VSCHEMA ADD TABLE [<keyspace>.]<table_name>;

Adds a table to the VSchema of an unsharded keyspace. In an unsharded keyspace, tables carry no Vindex information, so the VSchema entry is simply the table name.

-- Example
ALTER VSCHEMA ADD TABLE product.product;

This statement fails if:

The keyspace is sharded. In a sharded keyspace, every table needs a primary Vindex, so use ALTER VSCHEMA ... ADD VINDEX instead — it creates the table entry for you.
The table is already present in the VSchema.

Drop a table

ALTER VSCHEMA DROP TABLE [<keyspace>.]<table_name>;

Removes a table from the keyspace’s VSchema. Unlike ADD TABLE, this works on both sharded and unsharded keyspaces. The underlying MySQL table is not touched.

-- Example
ALTER VSCHEMA DROP TABLE product.product;

This statement fails if the table is not present in the VSchema.

Managing sequences

Sequence tables provide auto-increment-style ID generation for sharded tables. The sequence table itself must live in an unsharded keyspace.

Add a sequence

ALTER VSCHEMA ADD SEQUENCE [<keyspace>.]<sequence_table_name>;

Registers a table in an unsharded keyspace as a sequence table (the VSchema entry gets "type": "sequence").

-- Example
ALTER VSCHEMA ADD SEQUENCE product.customer_seq;

The backing MySQL table must be created separately, with the required schema and the vitess_sequence comment, and seeded with a single row:

CREATE TABLE customer_seq (
  id BIGINT,
  next_id BIGINT,
  cache BIGINT,
  PRIMARY KEY (id)
) COMMENT 'vitess_sequence';

INSERT INTO customer_seq (id, next_id, cache) VALUES (0, 1, 1000);

This statement fails if:

The keyspace is sharded.
A table with that name is already present in the VSchema.

Drop a sequence

ALTER VSCHEMA DROP SEQUENCE [<keyspace>.]<sequence_table_name>;

Removes a sequence table from the keyspace’s VSchema. The backing MySQL table is not touched.

-- Example
ALTER VSCHEMA DROP SEQUENCE product.customer_seq;

This statement fails if:

The keyspace is sharded.
The sequence is not present in the VSchema.

Managing auto-increment columns

Add an auto-increment column

ALTER VSCHEMA ON [<keyspace>.]<table_name>
  ADD AUTO_INCREMENT <column_name> USING [<keyspace>.]<sequence_table_name>;

Configures a column to be populated from a sequence table whenever an INSERT does not provide a value for it — typically the primary key of a sharded table. The sequence table is usually in a different (unsharded) keyspace, in which case it must be qualified.

-- Example
ALTER VSCHEMA ON customer.customer ADD AUTO_INCREMENT customer_id USING product.customer_seq;

This statement fails if:

The table is not present in the keyspace’s VSchema. Add its primary Vindex first with ALTER VSCHEMA ... ADD VINDEX.
The table already has an auto-increment column configured. A table can only have one; drop the existing one first.

Drop an auto-increment column

ALTER VSCHEMA ON [<keyspace>.]<table_name> DROP AUTO_INCREMENT;

Removes the sequence backing from a table. Inserts will no longer pull values from the sequence table.

-- Example
ALTER VSCHEMA ON customer.customer DROP AUTO_INCREMENT;

This statement fails if:

The table is not present in the keyspace’s VSchema.
The table has no auto-increment column configured.

Managing Vindexes

A Vindex definition lives at the keyspace level and consists of a name, a type, and optional parameters. Tables then reference a Vindex by name, binding it to one or more of their columns. The first Vindex on a table is its primary Vindex — the one that decides which shard each row lives on. You will usually use ALTER VSCHEMA ... ADD VINDEX, which defines the Vindex and attaches it to a table in one statement. CREATE VINDEX exists for defining a Vindex ahead of time, without attaching it to any table.

Create a Vindex

ALTER VSCHEMA CREATE VINDEX [<keyspace>.]<vindex_name> USING <vindex_type>
  [WITH <param1>=<value1>, <param2>=<value2>, ...];

Defines a Vindex in the keyspace without attaching it to a table. <vindex_type> is one of the predefined Vindex types, such as xxhash, hash, unicode_loose_xxhash, binary, numeric, consistent_lookup, or consistent_lookup_unique.

-- Example
ALTER VSCHEMA CREATE VINDEX customer.shard_index USING xxhash;

This statement fails if a Vindex with that name already exists in the keyspace.

Defining a Vindex is what makes a keyspace sharded: if this is the first Vindex in the keyspace, Vitess flips the keyspace to sharded in its VSchema. There is no such thing as a Vindex on an unsharded keyspace, so only run Vindex statements against keyspaces that are meant to be sharded.

Drop a Vindex

ALTER VSCHEMA DROP VINDEX [<keyspace>.]<vindex_name>;

Removes a Vindex definition from the keyspace.

-- Example
ALTER VSCHEMA DROP VINDEX customer.shard_index;

This statement fails if:

The Vindex does not exist in the keyspace.
Any table still uses the Vindex. Detach it from every table first with ALTER VSCHEMA ... DROP VINDEX.

Add a Vindex to a table

ALTER VSCHEMA ON [<keyspace>.]<table_name>
  ADD VINDEX <vindex_name> (<column_name1> [, <column_name2> ...])
  [USING <vindex_type> [WITH <param1>=<value1>, <param2>=<value2>, ...]];

Attaches a Vindex to one or more columns of a table. This is the statement that makes a table sharded: the first Vindex added to a table becomes its primary Vindex. If the table is not yet in the VSchema, the entry is created automatically. The USING clause is optional and controls whether the Vindex definition is created along the way:

With USING: if no Vindex named vindex_name exists in the keyspace, it is created with the given type and parameters. If it already exists, the type, owner, and parameters must match the existing definition exactly.
Without USING: the Vindex named vindex_name must already exist in the keyspace (created earlier via CREATE VINDEX or a previous ADD VINDEX ... USING).

-- Add a primary Vindex (creates both the Vindex and the table entry)
ALTER VSCHEMA ON customer.customer ADD VINDEX shard_index(customer_id) USING xxhash;

-- Reuse an existing Vindex on another table (no USING clause)
ALTER VSCHEMA ON customer.corder ADD VINDEX shard_index(customer_id);

-- A multi-column lookup Vindex, defined inline (note the backtick-quoted
-- comma-separated column list in the from parameter)
ALTER VSCHEMA ON customer.corder ADD VINDEX corder_region_idx(region, corder_id)
  USING consistent_lookup
  WITH owner=corder, table=`product.corder_region_idx`, from=`region,corder_id`, to=`keyspace_id`;

This statement fails if:

A Vindex with the same name is already attached to the table.
USING is given but the existing Vindex definition has a different type, owner, or parameters.
USING is omitted and no Vindex with that name exists in the keyspace.

The order in which Vindexes are attached matters: the first one is the primary Vindex. There is no statement to reorder Vindexes on a table, so add the primary Vindex first. If you get the order wrong, detach the table’s Vindexes with ALTER VSCHEMA ... DROP VINDEX and re-add them, primary first.

Like CREATE VINDEX, adding the first Vindex to any table in a keyspace marks that keyspace as sharded in its VSchema. Most keyspaces become sharded through this statement rather than through a standalone CREATE VINDEX.

Drop a Vindex from a table

ALTER VSCHEMA ON [<keyspace>.]<table_name> DROP VINDEX <vindex_name>;

Detaches a Vindex from a table. The keyspace-level Vindex definition is kept; use ALTER VSCHEMA DROP VINDEX to remove it entirely. If this was the last Vindex on the table, the table entry is removed from the VSchema as well.

-- Example
ALTER VSCHEMA ON customer.corder DROP VINDEX corder_region_idx;

This statement fails if:

The table is not present in the keyspace’s VSchema.
No Vindex with that name is attached to the table.

Vindex parameters

The WITH clause passes key-value parameters to the Vindex. Which parameters are valid depends on the Vindex type — most functional Vindexes such as xxhash take none, while lookup Vindexes require several:

Parameter	Meaning
`owner`	The table that owns the lookup Vindex. Vitess keeps the lookup table up to date as rows are inserted, updated, and deleted in the owner table.
`table`	The lookup table that stores the mapping, as `keyspace.table`.
`from`	Comma-separated list of column(s) in the lookup table that hold the input value(s).
`to`	The column in the lookup table that holds the keyspace ID.

Only parameter values that contain special characters need backticks — such as the dot in a qualified table reference or the comma in a multi-column from list. Plain values like owner=corder are left unquoted:

ALTER VSCHEMA ON customer.corder ADD VINDEX corder_keyspace_idx(corder_id)
  USING consistent_lookup_unique
  WITH owner=corder, table=`product.corder_keyspace_idx`, from=corder_id, to=keyspace_id;

See the Vitess Vindex documentation for the full list of Vindex types and their parameters.

A complete example

This example shards a customer keyspace while keeping reference data and sequence tables in an unsharded product keyspace.

-- Register a table in the unsharded keyspace
ALTER VSCHEMA ADD TABLE product.product;

-- Shard the customer table on customer_id
ALTER VSCHEMA ON customer.customer ADD VINDEX shard_index(customer_id) USING xxhash;

-- Give it sequence-backed IDs (the customer_seq MySQL table must already exist
-- and be seeded — see "Add a sequence" above for the CREATE TABLE and INSERT)
ALTER VSCHEMA ADD SEQUENCE product.customer_seq;
ALTER VSCHEMA ON customer.customer ADD AUTO_INCREMENT customer_id USING product.customer_seq;

-- Shard the corder table on customer_id too, so a customer's orders
-- live on the same shard as the customer
ALTER VSCHEMA ON customer.corder ADD VINDEX shard_index(customer_id);
ALTER VSCHEMA ADD SEQUENCE product.corder_seq;
ALTER VSCHEMA ON customer.corder ADD AUTO_INCREMENT corder_id USING product.corder_seq;

-- Add a unique lookup Vindex so corder rows can also be found by corder_id
ALTER VSCHEMA ADD TABLE product.corder_keyspace_idx;
ALTER VSCHEMA ON customer.corder ADD VINDEX corder_keyspace_idx(corder_id)
  USING consistent_lookup_unique
  WITH owner=corder, table=`product.corder_keyspace_idx`, from=corder_id, to=keyspace_id;

Verifying your changes

ALTER VSCHEMA changes take effect immediately. You can inspect the resulting VSchema over the same MySQL connection:

-- List all keyspaces and whether they are sharded
SHOW VSCHEMA KEYSPACES;

-- List all tables in the current keyspace's VSchema
SHOW VSCHEMA TABLES;

-- List all Vindexes in the current keyspace
SHOW VSCHEMA VINDEXES;

-- List the Vindexes attached to a specific table
SHOW VSCHEMA VINDEXES ON customer.corder;

SHOW VSCHEMA TABLES and SHOW VSCHEMA VINDEXES (without ON) report on the keyspace your connection is currently using — there is no keyspace-qualifier form. Switch keyspaces with USE <keyspace> to inspect a different one.

You can also view the full VSchema JSON in the Clusters page or fetch it with the pscale CLI.

Need help?

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

​Statement overview

​How keyspaces are resolved

​Managing tables

​Add a table

​Drop a table

​Managing sequences

​Add a sequence

​Drop a sequence

​Managing auto-increment columns

​Add an auto-increment column

​Drop an auto-increment column

​Managing Vindexes

​Create a Vindex

​Drop a Vindex

​Add a Vindex to a table

​Drop a Vindex from a table

​Vindex parameters

​A complete example

​Verifying your changes

​Need help?

Statement overview

How keyspaces are resolved

Managing tables

Add a table

Drop a table

Managing sequences

Add a sequence

Drop a sequence

Managing auto-increment columns

Add an auto-increment column

Drop an auto-increment column

Managing Vindexes

Create a Vindex

Drop a Vindex

Add a Vindex to a table

Drop a Vindex from a table

Vindex parameters

A complete example

Verifying your changes

Need help?