Mastering VSchema Syntax and Structure

A single misplaced field in a VSchema does not throw a parse error at the application — it silently changes how every query is routed. Declare a hash vindex where the shard key is a UUID string, and point lookups quietly degrade into scatter-gather reads across every shard. Bind a table to the wrong column_vindexes entry, and writes land on the wrong physical MySQL instance. This page resolves that risk by treating the VSchema as what it actually is: the JSON contract the VTGate routing layer compiles into an execution plan for every statement. It walks the full structural hierarchy — keyspaces, tables, vindexes, sequences, and routing rules — shows the exact JSON that produces correct single-shard routing, and gives you the vtctldclient and Python workflows to apply and verify a change without guessing.

The audience is database platform engineers, MySQL SREs, and Python orchestration builders who own the VSchema in production and need to reason about it precisely rather than copy fragments from a tutorial. Everything below is the grammar that the validation, lookup, and dynamic-routing pages in VSchema Configuration & Routing Rule Management assume you already understand.

Prerequisites #

Confirm the following before editing a production VSchema:

• Vitess 16.0 or later. The vtctldclient ApplyVSchema and GetVSchema verbs used throughout are stable from v16; the older vtctlclient interface is deprecated and its JSON validation is looser.
• A committed baseline VSchema per keyspace. Every change is a diff against the last-known-good artifact. Store each keyspace’s VSchema as JSON in version control, not only in the topology server, so ApplyVSchema is always reproducible.
• A defined sharding key per sharded table. You must know which column determines row placement before you can declare a primary vindex. If that decision is still open, resolve it against the keyspace partitioning models first — the VSchema encodes a partitioning choice, it does not make one.
• Read access to the topology server (etcd/Consul) so vtctldclient GetKeyspace and FindAllShardsInKeyspace can confirm the physical shard ranges the VSchema must line up with.
• Python 3.11+ if you plan to apply VSchema programmatically; the examples use subprocess around vtctldclient, the same interface an async validation workflow drives.

The VSchema Object Hierarchy #

A VSchema is a JSON document with a single top-level keyspaces map. Each key is a keyspace name; each value describes how that keyspace routes. The structure is strictly nested — tables reference vindexes by name, and vindexes are defined once and reused — so the grammar is best read from the outside in.

The keyspace object. Every keyspace declares sharded as a boolean. An unsharded keyspace needs almost nothing else: VTGate sends all of its queries to the single shard. A sharded keyspace requires two sibling blocks, vindexes and tables, and every routable table must appear in the tables map.

The vindexes block. This is a map of vindex definitions — the named routing functions. A definition has a type (hash, xxhash, unicode_loose_md5, lookup, lookup_unique, consistent_lookup, and so on) and, for lookup types, a params object and an owner. Defining a vindex here does not attach it to anything; it only makes the name available.

The tables block. This is a map of logical table names to their routing config. A sharded table carries a column_vindexes array. The first entry in that array is special: it is the primary vindex, and it determines the physical shard the row lives on. Every subsequent entry is a secondary vindex used only to route reads that filter on a non-primary column. This ordering rule is the single most consequential piece of VSchema syntax — swapping the first entry moves data.

Here is a minimal but complete sharded keyspace that ties every piece together:

{
  "sharded": true,
  "vindexes": {
    "hash": {
      "type": "hash"
    },
    "customer_email_lookup": {
      "type": "lookup_unique",
      "params": {
        "table": "commerce.customer_email_lookup",
        "from": "email",
        "to": "customer_id"
      },
      "owner": "customer"
    }
  },
  "tables": {
    "customer": {
      "column_vindexes": [
        { "column": "customer_id", "name": "hash" },
        { "column": "email", "name": "customer_email_lookup" }
      ],
      "auto_increment": {
        "column": "customer_id",
        "sequence": "commerce_seq.customer_seq"
      }
    },
    "customer_email_lookup": {
      "column_vindexes": [
        { "column": "email", "name": "unicode_loose_md5" }
      ]
    }
  }
}

Read it as a routing contract: customer rows are placed by hash(customer_id); a query filtering on email uses the customer_email_lookup vindex to find the owning shard instead of scattering; and new customer_id values are drawn from a global sequence in a separate unsharded keyspace so IDs never collide across shards.

Primary vindexes: placement functions #

A primary vindex must be functional and unique — given a column value it deterministically returns exactly one keyspace ID, and that ID must fall inside one of the shard ranges reported by FindAllShardsInKeyspace. hash (an 8-byte hash suitable for integer keys) and xxhash (better distribution for arbitrary bytes, the modern default) are the common choices. unicode_loose_md5 is a string vindex for case-insensitive text keys — it is not a substitute for hash on an integer or UUID column, and using it there wastes CPU on hash computation for no benefit. The type must match the actual column data type or routing correctness and performance both suffer.

Secondary and lookup vindexes: alternate access paths #

A lookup vindex is a materialized reverse index: a real table mapping a non-sharding column (email) back to the sharding key (customer_id). Declaring it as a secondary column_vindexes entry lets VTGate resolve WHERE email = ? to a single shard with one extra hop instead of a broadcast. The owner field names the table whose writes keep the lookup in sync — Vitess maintains the mapping row transactionally when the owner table is written. Getting the lifecycle of these tables right is its own discipline, covered in depth on the cross-shard joins page; the syntax point here is that the definition lives in vindexes and the binding lives in the owner table’s column_vindexes.

Sequences: cross-shard identifier generation #

AUTO_INCREMENT does not work across shards — two shards would hand out the same value. Vitess replaces it with a sequence table living in an unsharded keyspace, referenced from the sharded table’s auto_increment block. VTGate reserves blocks of IDs from the sequence and injects them into inserts that omit the sharding column, guaranteeing global uniqueness without a coordinator on the write path.

How VTGate Compiles and Reloads a VSchema #

Understanding the runtime is what turns syntax knowledge into safe operations. When vtctldclient ApplyVSchema succeeds, the new document is written to the topology server, not pushed to proxies directly. Each VTGate watches the topology server and reloads the serving VSchema when it changes, governed by --srv_topo_cache_ttl and --srv_topo_cache_refresh. Propagation is therefore asynchronous and per-proxy: for a brief window, different VTGate instances in the fleet can plan the same query against different VSchema versions. Backward-compatible changes are safe across that window; a change that renames or drops a vindex a live query still references is not.

On each VTGate, the reloaded VSchema is compiled once, and query plans are cached keyed by normalized SQL plus VSchema version. A reload invalidates the plan cache, so the first occurrence of each query shape after a change pays a re-planning cost. This is why a large VSchema change during peak traffic shows up as a transient CPU and latency bump even when the change is correct — the plans are being rebuilt. Coordinating that window with in-flight schema changes is the subject of How to Deploy VSchema Changes Without Downtime; the mechanism to remember is that the reload is atomic per proxy but not synchronized across the fleet.

Routing rules — a separate top-level construct applied with ApplyRoutingRules — sit in front of table resolution and can redirect a logical table name to another target entirely. They are how traffic shifting and read/write splitting are expressed without touching the keyspace’s tables block, and their runtime behavior is detailed in Dynamic Routing Rules and Query Rewriting.

Step-by-Step: Author and Apply a VSchema #

Each step below is independently runnable, so a failure isolates cleanly.

1. Confirm the physical shard layout #

The VSchema must match the ranges that already exist. Enumerate them first.

vtctldclient GetKeyspace commerce
vtctldclient FindAllShardsInKeyspace commerce --format=json

Note the shard boundaries (for example -40, 40-80, 80-c0, c0-). A primary vindex whose output can fall outside every declared range is structurally impossible to route.

2. Write the VSchema as a version-controlled file #

Author the document as vschema/commerce.json using the structure from the hierarchy section above. Keep it in the repository so the applied artifact and the reviewed artifact are the same bytes.

3. Validate the JSON before it touches the live cluster #

Catch malformed payloads and unknown vindex types with zero infrastructure. jq proves the file is well-formed JSON; a full structural check against the Vitess VSchema schema belongs in CI, as the async VSchema validation workflow describes.

jq -e . vschema/commerce.json > /dev/null && echo "valid JSON"

4. Dry-run against the control plane #

Ask the live vtctld to plan the change without persisting it. This exercises the same code path production will and reports what it would do.

vtctldclient ApplyVSchema \
  --vschema="$(cat vschema/commerce.json)" \
  --dry-run \
  commerce

A non-zero exit or a diff touching tables outside the intended change set fails the step before any real mutation.

5. Apply, then confirm the persisted document #

Only after a clean dry run, apply for real and read the document back from the topology server.

vtctldclient ApplyVSchema \
  --vschema="$(cat vschema/commerce.json)" \
  commerce

vtctldclient GetVSchema commerce > /tmp/live.json
diff <(jq -S . vschema/commerce.json) <(jq -S . /tmp/live.json)

An empty diff confirms the control plane holds exactly the artifact you reviewed.

6. Apply programmatically from an orchestration harness #

Python orchestration builders should wrap the same vtctldclient interface rather than reimplementing the wire protocol, so the automated path is identical to the manual one.

import json
import subprocess

def apply_vschema(keyspace: str, path: str, dry_run: bool = True) -> str:
    with open(path) as f:
        vschema = f.read()
    json.loads(vschema)  # fail fast on malformed JSON before any RPC

    cmd = ["vtctldclient", "ApplyVSchema", f"--vschema={vschema}"]
    if dry_run:
        cmd.append("--dry-run")
    cmd.append(keyspace)

    result = subprocess.run(cmd, capture_output=True, text=True)
    if result.returncode != 0:
        raise RuntimeError(f"ApplyVSchema failed: {result.stderr.strip()}")
    return result.stdout

Call it with dry_run=True in CI to gate a pull request, and with dry_run=False only after every offline check has passed.

Configuration Reference #

These flags govern how a VSchema change propagates and how much routing state each component holds. Set them consistently across the fleet so staging behaves like production.

Leaving --vschema_ddl_authorized_users empty is deliberate: enabling in-band ALTER VSCHEMA lets a client mutate routing outside the reviewed, version-controlled path and bypass every validation gate.

Failure Modes #

Each scenario below is a specific way VSchema syntax breaks routing, with the signal that identifies it.

Wrong primary vindex type. A hash vindex is declared on a UUID-string sharding column. The JSON is valid and ApplyVSchema succeeds, but the hash of the string distributes poorly and point queries that should hit one shard scatter. Symptom: vtgate_queries_processed{plan="ScatterGather"} climbs and p99 latency rises on the keyspace. Mitigation: match the vindex type to the column type — xxhash or unicode_loose_md5 for string keys — and confirm single-shard routing with VEXPLAIN before promoting.

Reordered column_vindexes. An edit moves a secondary vindex to the first array position, silently changing the primary. New rows are placed by the wrong function while existing rows stay put. Symptom: reads for recently written keys miss; the same query returns rows inconsistently depending on when the row was inserted. Mitigation: treat any change to the first column_vindexes entry as a data-movement operation requiring a migration, never a routine edit; diff the first entry explicitly in review.

Orphaned vindex reference. A table’s column_vindexes names a vindex that was removed from the vindexes block in the same change. Symptom: VTGate logs vindex ... not found and affected queries fail to plan. Mitigation: validate that every referenced vindex name resolves to a declared definition before apply — a structural check the offline validation stage is built to catch.

Broken sequence reference. A table’s auto_increment block points at a sequence in an unsharded keyspace that a prior migration renamed or dropped. Valid JSON, valid structure — only the control-plane dry run reaches the sequence resolution path. Symptom: inserts fail with cannot find sequence. Mitigation: keep --dry-run mandatory; never promote on offline checks alone.

Fleet reloads out of step. A non-backward-compatible change (a dropped vindex a live query still uses) propagates to some VTGate instances before others because reload is per-proxy. Symptom: intermittent planning errors that correlate with which proxy served the request. Mitigation: stage changes so the old and new VSchema are both valid for any in-flight query — add the new vindex before removing the old binding — and coordinate the cutover with Online DDL orchestration so schema and routing never contradict each other.

Verification #

Confirm the applied VSchema routes the way the syntax intends.

1. Prove the control plane holds your artifact. After apply, the diff from step 5 must be empty:

   vtctldclient GetVSchema commerce > /tmp/live.json
   diff <(jq -S . vschema/commerce.json) <(jq -S . /tmp/live.json)
   

2. Prove a point query routes to one shard. Use VEXPLAIN to inspect the plan for a query filtering on the primary vindex column; it should target a single shard, not scatter.

   VEXPLAIN PLAN SELECT * FROM customer WHERE customer_id = 42;
   -- expect a single-shard route, not a ScatterGather plan
   

3. Prove the lookup path resolves. A query filtering on the secondary column should route via the lookup vindex, again to a bounded shard set:

   VEXPLAIN PLAN SELECT * FROM customer WHERE email = 'a@example.com';
   -- expect a lookup-vindex route, not a broadcast
   

4. Watch the routing metric that a regression would move. After promotion, the scatter-gather plan rate on the keyspace should stay flat:

   # Prometheus: scatter plan rate must not step up after a VSchema change
   rate(vtgate_queries_processed{plan="ScatterGather",keyspace="commerce"}[5m])
   

   A step change after a clean apply means a query shape you did not check now scatters — inspect it with VEXPLAIN and correct the vindex binding. For the authoritative field-by-field grammar, the official Vitess VSchema Reference remains the source of truth.

A VSchema that passes all four checks gives you the deterministic routing this layer exists to deliver: the artifact under review is what the control plane holds, primary-column queries hit one shard, secondary-column queries resolve through their lookup, and the scatter rate confirms nothing silently broadened.

Related #

• Configuring Lookup Vindexes for Cross-Shard Joins — the lifecycle of the reverse-index tables the secondary vindex syntax binds to.
• Dynamic Routing Rules and Query Rewriting — the routing-rule layer that sits in front of the tables block for traffic shifting.
• Async VSchema Validation Workflows — checking a candidate VSchema out of band before ApplyVSchema reaches production.
• How to Deploy VSchema Changes Without Downtime — coordinating the asynchronous per-proxy reload with live traffic.
• Coordinating Multi-Shard Schema Migrations — keeping VSchema changes aligned with in-flight DDL across the fleet.

← Back to VSchema Configuration & Routing Rule Management