Choosing the Right Vitess Vindex: Hash, Lookup, Range, or Numeric for Your Sharding Key

The Decision You Face

You're designing a Vitess keyspace and must pick a primary Vindex—the virtual index that determines how rows map to shards. This choice locks in your sharding key, query routing behavior, and resharding flexibility for the lifetime of the dataset. Changing it later requires a full MoveTables operation with planned downtime or a VReplication cutover. The right Vindex depends on three constraints: column cardinality and distribution, query access patterns, and future resharding plans.

Supported Vindex Types at a Glance

Vindex Type	Function Name	Best For	Overhead	Resharding Complexity
Hash	hash	High-cardinality columns (user_id, device_id) with point lookups	Zero (computed at write time)	Simple: split by keyspace ID ranges
Lookup	lookup / consistent_lookup	Low-cardinality or skewed columns (tenant_id, country_code)	Lookup table storage + write latency	Moderate: copy lookup table during MoveTables
Range	range	Time-series or ordered data with frequent range scans on sharding key	Zero (range mapping in VSchema)	Complex: range-aware splitting, manual boundary management
Numeric	numeric	Column already a 64-bit integer matching keyspace ID space	Zero	Simple but no distribution control

Trade-offs by Constraint

Cardinality and Distribution

Hash shines when the column has millions of distinct values and uniform distribution. The MD5 hash spreads rows evenly across shards without any coordination. Lookup is necessary when cardinality is low (e.g., 50 tenants) or distribution is skewed—hashing tenant_id would concentrate all rows for a large tenant in one shard, creating a hotspot. The lookup table maps each distinct value to a keyspace ID, letting you place tenants manually.

Range suits columns where values are naturally ordered and queries scan contiguous ranges—think event_timestamp or log_sequence. But if data grows unevenly (more events in recent months), shards become unbalanced. Numeric only works when the column is the keyspace ID (e.g., a pre-sharded shard_key column generated by the application). It offers no distribution guarantees.

Query Patterns

The primary Vindex determines single-shard routing. Queries filtering on the primary Vindex column route to exactly one shard. For other columns, you define secondary Vindexes. A secondary hash or lookup Vindex on a high-selectivity column (e.g., email) also enables single-shard routing. Without a secondary Vindex, VTGate scatters the query to all shards.

Watch out: secondary Vindexes on non-unique columns may return multiple keyspace IDs, forcing a scatter query anyway. Pair them with covering indexes in MySQL to avoid table scans on each shard.

Resharding Flexibility

Hash-based sharding makes MoveTables straightforward: you split the keyspace ID range (e.g., -80 to 80- becomes -40, 40-80, 80-c0, c0-). The Vindex function stays the same; only the shard map changes. Lookup Vindexes require copying the lookup table to the target keyspace during the move. Range Vindexes demand manual range boundary decisions—you must define new ranges that balance shard sizes.

If you anticipate frequent resharding (every 6–12 months), hash is the safest default. Reserve lookup for columns that cannot be hashed, and range for append-only time-series workloads where you control the ingestion rate.

Concrete Implementation: Hash Primary + Lookup Secondary

Consider a multi-tenant SaaS application. The users table has user_id (high cardinality) and tenant_id (low cardinality, ~200 values). Queries filter by user_id for profile lookups and by tenant_id for admin dashboards.

Step 1: Define the VSchema

{
  "sharded": true,
  "vindexes": {
    "hash_user_id": {
      "type": "hash"
    },
    "lookup_tenant_id": {
      "type": "consistent_lookup",
      "params": {
        "table": "tenant_lookup",
        "from": "tenant_id",
        "to": "keyspace_id"
      },
      "owner": "users"
    }
  },
  "tables": {
    "users": {
      "columnVindexes": [
        {"column": "user_id", "name": "hash_user_id"},
        {"column": "tenant_id", "name": "lookup_tenant_id"}
      ]
    },
    "tenant_lookup": {
      "columnVindexes": [
        {"column": "tenant_id", "name": "hash_user_id"}
      ]
    }
  }
}

Notes: consistent_lookup (Vitess 15+) uses a consistent-hashing scheme for the lookup table itself, reducing contention compared to the legacy lookup Vindex. The lookup table is owned by users, so Vitess maintains it automatically on inserts/updates/deletes.

Step 2: Create the Lookup Table

CREATE TABLE `tenant_lookup` (
  `tenant_id` BIGINT NOT NULL,
  `keyspace_id` VARBINARY(8) NOT NULL,
  PRIMARY KEY (`tenant_id`)
) ENGINE=InnoDB;

Populate it with your tenant-to-shard mapping. For 200 tenants across 4 shards, assign ~50 tenants per shard by setting keyspace_id to the shard's range start (e.g., 0x0000000000000000, 0x4000000000000000, etc.).

Step 3: Validate Routing

Apply the VSchema and verify routing with vtctl:

# Apply VSchema (run on any vtctl client with cluster access)
vtctl ApplyVSchema -vschema_file=vschema.json keyspace_name

# Validate before applying (dry-run)
vtctl ValidateVSchema -vschema_file=vschema.json keyspace_name

# Check routing for a user_id query
vtctl GetShardRouting keyspace_name users "SELECT * FROM users WHERE user_id = 12345"
# Expected: single shard targeted

# Check routing for a tenant_id query
vtctl GetShardRouting keyspace_name users "SELECT * FROM users WHERE tenant_id = 42"
# Expected: single shard targeted via lookup

In VTGate logs, confirm targeted (not scatter) for both queries. Run EXPLAIN on the actual queries through VTGate to see the execution plan.

Step 4: Monitor Lookup Table Health

The lookup table grows with distinct tenant_id values. Monitor it like any MySQL table:

SHOW TABLE STATUS LIKE 'tenant_lookup';
-- Watch Data_length, Index_length, and Row count

-- Check for lock contention under write load
SHOW ENGINE INNODB STATUS \G
-- Look for "ROW LOCK" waits on tenant_lookup

If write throughput on users causes lookup table contention, batch tenant assignments or increase the lookup table's shard count (shard the lookup table itself on tenant_id using a hash Vindex).

Validation Checklist Before Production

1. Distribution test: Insert 1M rows with synthetic user_id values. Run vtctl GetShardRouting on a sample and verify even shard distribution via SELECT COUNT(*) FROM users on each shard.
2. Routing test: Execute EXPLAIN for your top 10 query patterns. Confirm primary Vindex queries hit one shard; secondary Vindex queries hit one shard; unindexed queries scatter (expected).
3. Resharding dry-run: Create a 2-shard test keyspace, apply the VSchema, load data, then run vtctl MoveTables to a 4-shard target. Validate with vtctl VerifyShard and check replication lag stays under 30 seconds.
4. Write benchmark: Use sysbench against the hash Vindex alone, then with the lookup Vindex enabled. Measure p99 latency and InnoDB row lock waits on tenant_lookup. If lookup adds >5ms p99 or significant lock waits, evaluate consistent_lookup or sharding the lookup table.

Limitations and When to Revisit

• Primary Vindex changes require full resharding. There is no ALTER VINDEX. Plan the sharding key before loading production data.
• Cross-shard transactions (2PC) work but add latency. If your workload frequently updates rows across user_id and tenant_id in the same transaction, consider denormalizing or using VReplication for a materialized view instead of a secondary Vindex.
• VSchema validation is strict. Referencing a non-existent table or column causes VTGate to reject all queries for that keyspace. Always run ValidateVSchema before ApplyVSchema.
• Version differences matter. Multi-column Vindexes are native in Vitess 15+. Earlier versions require a computed column or application-layer concatenation. Check vitess.io/docs/<your-version>/reference/vschema/ for your release.

Quick Decision Rule

Start with hash on a high-cardinality column that appears in your most frequent point-lookup queries. Add consistent_lookup secondary Vindexes for low-cardinality filter columns. Use range only for append-only time-series where you control ingestion and can manage range boundaries. Avoid numeric unless the column is already a pre-computed keyspace ID.