Transparent Sharding in Vitess: How VSchema and VIndexes Route Queries

When does application logic need to know about database shards?

Ideally, it never does. The goal of a system like Vitess is to provide a single, logical database interface while handling the physical distribution (sharding) across many smaller database instances (shards). The core problem is ensuring that a query targeting a specific piece of data always lands on the correct shard without the application needing to calculate that location.

The Declarative Routing Mechanism

Vitess uses VSchema to declaratively define the entire topology: keyspaces (logical database groups), shards (physical groups of tablets), and tables. The magic happens with VIndexes. A VIndex maps a column from your application table to a deterministic keyspace_id. This ID dictates which shard holds the data.

Routing via VTGate

VTGate is the query router. When a query arrives, it inspects the WHERE clause or bind variables, extracts the relevant column value, uses the VIndex definition to determine the keyspace_id, and routes the request directly to the corresponding vttablet shard. This makes sharding transparent to the SQL query itself.

Concrete Sharding Example

Suppose you shard a users table based on user ID. You define a hash VIndex over the user_id column. If user_id = 100 hashes to keyspace_id = 3, VTGate ensures that any query for user 100 goes only to shard 3. This avoids inefficient scatter-gather operations.

Verifying the Design

To confirm your setup, run vtctl GetVSchema on the Vitess cluster. Inspect the definition for your keyspace to verify that the VIndex correctly maps the target column to the intended keyspace_id range across your defined shards.

The Cross-Shard Limitation

The primary constraint is that Vitess does not natively support cross-shard joins or multi-shard transactions. If your application logic requires joining data from two different shards, VTGate will typically reject the query or require application-level scatter-gather logic, which negates the transparency benefit.

Trade-off: Immutability of Placement

Once a row is placed on a shard based on its VIndex, its keyspace_id is effectively immutable. Changing a VIndex type (e.g., from hash to numeric) requires a full data backfill and migration using VReplication, not just a schema update. This prevents accidental data migration during configuration changes.

Practical Check: Write a simple SELECT query targeting a known user_id. Use VTGate query tracing (if available for your Vitess version) or check the query execution plan to confirm the target shard ID matches the expected keyspace_id derived from the VIndex.