Learn Vitess - Schema Management & Vindexes

#Introduction

In episode 4 you met the hash vindex as a mapping of values to shards. Episode 8 goes deeper: vindexes come in many types with different roles — there are global vindexes that share data across shards and local vindexes that belong to a single shard. Your vindex choice determines how fast your queries run, how easy resharding is, and how consistent routing is.

Episode 8 roadmap: a deeper look at the vindex concept, comparing global and local vindexes, designing sharded vs unsharded schemas, and configuring vindexes in practice. This is a design episode — its effects are felt for the entire lifetime of the system.

#The Vindex Concept in Depth

A vindex is not an ordinary database index. It's a function that answers one question: "which shard does value X live on?" Three main roles a vindex can play:

• Unique vindex — maps a unique value to one shard, like a primary key. The hash vindex is unique: one user_id only exists in one shard.
• Non-unique vindex — maps one value to many shards, suited to columns like tenant_id where a single tenant spreads out. The unicode_loose_md5 vindex is often used for this.
• Lookup vindex — stores the value-to-shard mapping as data itself, rather than a pure function. Suited when routing needs the reverse of the sharding key column.

{
  "sharded": true,
  "vindexes": {
    "hash": { "type": "hash" },
    "tenant_lookup": { "type": "consistent_lookup_unique" }
  },
  "tables": {
    "users": {
      "column_vindexes": [
        { "column": "user_id", "name": "hash" }
      ],
      "columns": [
        { "name": "tenant_id", "vindex": "tenant_lookup" }
      ]
    }
  }
}

Notice that the tenant_id column also gets a vindex — this is an example of a secondary vindex used to speed up queries based on tenant_id without causing a scatter.

#Global vs Local Vindexes

#Local Vindex

A local vindex lives inside a single shard: its data isn't shared between shards. The hash vindex is an example — a pure function computed from the value, storing no data. Local vindexes are simple, with no extra storage cost and no consistency issues. However, they can't answer queries based on a column that isn't the sharding key column.

#Global Vindex

A global vindex is a mapping that can route a query from a non-sharding-key value to the right shard. How it works: the mapping data is stored as a lookup table — either in its own shard (an unsharded lookup keyspace) or inside a table with another vindex. When the application queries WHERE tenant_id = 'X', VTGate consults the lookup vindex to find the shard for that tenant_id value, without a scatter.

{
  "vindexes": {
    "tenant_lookup": {
      "type": "consistent_lookup_unique",
      "params": {
        "table": "tenant_lookup",
        "from": "tenant_id",
        "to": "user_id"
      }
    }
  }
}

The consistent_lookup_unique vindex uses the tenant_lookup table to map tenant_id to user_id, which is then routed through the primary hash vindex. This bridges two worlds: a query based on tenant_id can find its shard without a scatter.

#Trade-offs

• Local vindex: fast, cheap, no consistency cost, but only works for the sharding key column.
• Global vindex: enables routing from other columns, but requires extra storage and carries a transactional consistency cost when the lookup table is updated.

#Designing Schemas for Sharded and Unsharded Tables

#Sharded Tables

Sharded tables store rows in different shards based on a vindex. This has an important consequence: foreign keys between tables in different shards can't be consistently guaranteed, because related rows may live in different MySQL instances. Vitess handles this by disabling FK enforcement across shards and pushing integrity maintenance to application design.

Common design patterns:

• Tables that are always accessed together are placed under the same sharding key, so they're co-located.
• Large tables (e.g., orders) are sharded by user_id, just like users.
• A combined users JOIN orders ON user_id query can be resolved in one shard.

#Unsharded Tables

Not every table needs sharding. Small reference tables that rarely change — country codes, small product lists, configuration — are better left unsharded: stored in one shard (or in a separate unsharded keyspace), so queries always hit exactly one location with no scatter.

CREATE TABLE countries (
  code char(2) primary key,
  name varchar(100)
)

The CREATE TABLE countries command in an unsharded keyspace stores the table in one location. Vitess also has reference tables, a mechanism that copies an unsharded table to every shard of a sharded keyspace — so joins with sharded tables can run without leaving the shard.

#Configuring Vindexes in Practice

The usual workflow:

1. Create an initial VSchema with a primary vindex for the sharding key column.
2. Apply the VSchema and test routing with queries.
3. Add secondary or lookup vindexes when new query needs arise.
4. Always keep the VSchema as a file in git and apply it through a pipeline, not manually in a shell.

vtctlclient ApplyVschema -vschema_file=vschema.json users
vtctlclient GetVschema users

vtctlclient GetVschema displays the active VSchema — a quick way to verify the configuration is correct and consistent with your intent.

#Closing

In this episode 8 you understood vindexes in depth: the unique, non-unique, and lookup roles, the difference between global and local vindexes along with their trade-offs, designing schemas for sharded and unsharded tables, and the vindex configuration workflow with VSchema.

Key takeaways:

• A vindex answers one question: which shard does this value live on.
• Local vindexes are pure functions, cheap, but limited to the sharding key column.
• Global lookup vindexes enable routing from other columns at storage and consistency cost.
• Large tables are sharded with the same sharding key so they're co-located for joins.
• Small reference tables are better unsharded, or reference tables copied to every shard.
• VSchema is a file managed in git and applied through a pipeline, not manually.

In the next episode, episode 9, we handle guarantees: transactions and consistency models — ACID in Vitess, single-shard vs cross-shard transactions, XA support, locking, and consistency best practices. See you there!