Vitess Hash Primary Vindex: Route Queries to One Shard

Why a Hash Vindex Matters

When Vitess shards a table, it needs a deterministic way to map a row’s key to the correct shard. The hash primary vindex does exactly that by hashing the chosen column into an 8‑byte keyspace id. VTGate uses this id to route an WHERE user_id = ? clause to a single shard, keeping most reads and writes local.

VSchema – The Blueprint for Sharding

The VSchema is a JSON document that describes how a keyspace is sharded, which vindexes exist, and which columns they cover. A minimal VSchema that shards a users table on user_id looks like this:

{
  "sharded": true,
  "vindexes": {
    "hash": {"type": "hash"}
  },
  "tables": {
    "users": {
      "column_vindexes": [
        {"column": "user_id", "name": "hash"}
      ]
    }
  }
}

To apply it, run:

vtctlclient ApplyVSchema --keyspace users --vschema-file users_vs.json

Replace vtctlclient with vtctldclient if you are on a newer release. The command must be executed with a user that has the root role on the Vitess cluster.

How VTGate Routes Queries

• Equality on the primary vindex – SELECT * FROM users WHERE user_id = 1234 → VTGate computes hash(1234), looks up the shard mapping, and sends the query to that one shard.
• Non‑equality or missing vindex column – SELECT * FROM users WHERE user_id > 1000 or SELECT * FROM users → VTGate scatters the query to all shards and merges the results.
• INSERT – Must supply the primary vindex column; VTGate hashes the value and writes to the single target shard.
• UPDATE/DELETE – If the primary vindex column is present in the WHERE clause, the operation stays single‑shard; otherwise it scatters.

Common Pitfalls and Limits

• Low‑cardinality or skewed keys – Using a column that has few distinct values (e.g., status) will concentrate traffic on a single shard, creating a hotspot.
• Omitting the vindex column in hot paths – A frequent mistake is writing SELECT * FROM users WHERE email = ? when email is not part of a vindex. The query scatters, degrading performance.
• Range predicates – WHERE user_id BETWEEN 1000 AND 2000 forces a scatter because the hash vindex only supports equality.
• Auto‑increment in a sharded keyspace – MySQL’s auto_increment values collide across shards. Vitess solves this by mapping the column to a sequence table defined in an unsharded keyspace. The VSchema must include an auto_increment field pointing to that sequence.
• Cross‑shard transactions – By default, VTGate uses transaction_mode=xa which relies on two‑phase commit. If you need atomicity across shards, enable 2PC and test the transaction_mode setting.

What to Verify After Applying the VSchema

1. Run EXPLAIN SELECT * FROM users WHERE user_id = 42 through VTGate and confirm the plan shows a single shard.
2. Insert several rows with different user_id values, then query the underlying MySQL shards directly to see the distribution matches the hash output.
3. Check VTGate’s debug endpoint /debug/query_plan for a given query to ensure no unexpected scattering.
4. Review the Vitess documentation for your release to confirm that the hash vindex type and auto_increment syntax are still current.

When to Choose a Hash Vindex

Use a hash primary vindex when:

• The column is an integer or string that can be evenly distributed.
• Equality lookups dominate the workload.
• You’re willing to accept scatter for range queries or non‑equality predicates.

For workloads that require frequent range scans, consider adding a secondary lookup vindex (e.g., lookup or bigtable) or re‑architect the data model.

Conclusion

Vitess’s hash primary vindex is the simplest and most common way to shard a table. It guarantees that any query with an equality predicate on the sharding key stays single‑shard, which keeps latency low and simplifies application logic. However, be mindful of the limits—range queries scatter, low‑cardinality keys cause hotspots, and auto‑increment requires special handling. By validating the routing plan and monitoring shard load, you can ensure your Vitess deployment scales as expected.