Range vs Hash Vindex Selection

The primary vindex decides where each row physically lands, and the choice between uniform hashing and ordered range distribution is the choice between hotspot-resistance and range-scan locality — you rarely get both.

Context #

This comparison sits under vindex selection and tuning, which frames the full vindex landscape; here the scope narrows to just the primary vindex family that places rows. The mechanics of how a vindex byte string maps onto shard ranges come from understanding Vitess keyspace partitioning models, and because a bad choice here can only be undone by moving data, the resharding consequences tie directly into resharding workflows and shard splits. If you are choosing a vindex for a secondary column rather than the placement key, this page is the wrong one — see the parent guide instead.

Both approaches are functional vindexes: they compute a keyspace ID from the column value with no backing table. What differs is the shape of the mapping. Hashing deliberately destroys order so that adjacent input values scatter across shards; range distribution deliberately preserves it so that adjacent values stay adjacent.

The Comparison #

The table collapses to one trade-off: hashing buys you write distribution at the cost of range locality; range distribution buys you range locality at the cost of write distribution. Everything below is about which cost your workload can absorb.

How Each Distributes Rows #

hash and xxhash run the column through a fixed function and produce an 8-byte keyspace ID that is, for practical purposes, uniformly distributed. Two customer_id values that differ by one — 41 and 42 — hash to unrelated keyspace IDs and typically different shards. That is exactly why a WHERE customer_id BETWEEN 41 AND 100 query must scatter: the range in value space corresponds to no contiguous region in keyspace-ID space. The upside is that a flood of new sequential IDs spreads evenly, so no single shard absorbs the write front.

Range-based distribution using the numeric vindex takes the opposite stance: the column value is the keyspace ID (after fixed-width encoding), so shard ranges are ranges of the actual key. Rows 1000–1999 live on one shard, 2000–2999 on the next. A BETWEEN query touches only the shards whose ranges intersect the predicate, and ORDER BY id LIMIT 20 can often be served from a single shard without a cross-shard merge. The cost is structural: if the key is monotonically increasing (an auto-incrementing surrogate, a timestamp-derived ID), every new row lands on whichever shard owns the top of the range, turning that shard into a permanent write hotspot while the rest hold cold history.

The asymmetry matters most on the two query shapes that dominate real workloads. Point lookups behave identically — both distributions resolve = 42 to exactly one shard, because a single value maps to a single keyspace ID either way. The divergence is entirely on range predicates and ordered pagination. Under hashing, WHERE created_id > 90000 ORDER BY created_id has no contiguous keyspace-ID region and no shard that holds “the largest IDs,” so VTGate must scatter, gather from every shard, and re-sort; the result is correct but its cost scales with shard count. Under range distribution the same query walks a bounded set of shards in key order, and a LIMIT can short-circuit after the first shard returns enough rows. That single difference is why the choice is a workload decision and not a default.

VSchema for Each #

A hash primary vindex — the default for OLTP tables sharded by a high-entropy key:

{
  "sharded": true,
  "vindexes": {
    "xxhash": { "type": "xxhash" }
  },
  "tables": {
    "orders": {
      "column_vindexes": [
        { "column": "customer_id", "name": "xxhash" }
      ]
    }
  }
}

A range-based primary vindex using numeric, where the integer key is placed directly and shard boundaries are set as value ranges rather than hash ranges:

{
  "sharded": true,
  "vindexes": {
    "numeric": { "type": "numeric" }
  },
  "tables": {
    "events": {
      "column_vindexes": [
        { "column": "event_id", "name": "numeric" }
      ]
    }
  }
}

With the numeric vindex, the shard ranges you assign at Reshard/CreateKeyspace time are ranges over the key itself, so event_id between two bounds resolves to specific shards. With xxhash, the same shard-range syntax partitions the hash output, which is why identical range syntax produces uniform scatter for one and locality for the other.

What Resharding Looks Like for Each #

The distribution choice is not only a steady-state decision — it reappears the day you split a shard, and it reappears with opposite ergonomics. Under xxhash, keyspace IDs are uniform, so splitting -80 into -40 and 40-80 sends roughly half the rows to each new shard and roughly halves the write load on both. The split point is a clean binary bisection and the resulting shards are balanced by construction; you rarely need to think about where to cut, only when.

Under range distribution the split is a targeted operation, not a bisection. Because rows are concentrated near the active end of the key space, a naive midpoint cut leaves the cold half nearly empty and the hot half nearly as hot as before. To actually relieve a range hotspot you must cut close to the high end of the active range — carving off, say, the newest 10% of the key space onto a fresh shard — and you will likely repeat that as the key keeps advancing. Range distribution therefore trades cheap range reads for a resharding cadence that never really stops; each split buys relief for a bounded window before the write front catches up again. Plan those targeted splits deliberately with planning a shard split with VReplication, and verify placement afterward, because an uneven range split is easy to get subtly wrong.

Recommend by Workload #

• High-write OLTP keyed by a tenant or customer — use xxhash. You want a customer’s rows co-located (all rows sharing a customer_id hash together) and you want new customers to spread across shards. Range scans across customers are rare and can afford to scatter.
• Append-heavy event or ledger tables read by time window — this is the tension case. Range distribution gives you cheap “last 24 hours” reads but a write hotspot on the newest shard; hashing gives you even writes but forces every time-window read to scatter. If reads dominate and are always time-bounded, range wins; if writes dominate, hash the key and accept scatter on the window reads (or add a secondary index strategy).
• Reference or archival data queried by contiguous ID ranges — use numeric range distribution. Writes are infrequent, so the hotspot never materializes, and range locality makes bulk range reads cheap.
• Anything with a monotonic surrogate key and meaningful write volume — use xxhash. Range distribution on a monotonic key is the classic self-inflicted hotspot.

Edge cases and gotchas #

• Monotonic keys under range distribution. An auto-increment or snowflake-style id used directly with numeric sends 100% of inserts to the last shard. If you need ordered locality and write spread, shard by a coarse bucket (e.g. hash(tenant)) and keep time ordering within the shard via the clustering key, rather than making the monotonic column the vindex.
• Time-series with retention. Range distribution makes dropping old data easy (an old range maps to specific shards you can archive), but it guarantees the write hotspot moves but never disappears. Weigh cheap retention against permanent skew on the active shard.
• Resharding a hashed keyspace is even; resharding a range keyspace is not. Splitting an xxhash shard -80 into -40 and 40-80 moves roughly half the rows each way because the hash is uniform. Splitting a hot range shard moves almost nothing off the cold side and everything off the hot side, so the split must target the hot range specifically rather than bisecting at the midpoint.
• Switching hash ↔ range is a data move, not a config edit. The primary vindex determines physical placement, so changing it re-places every row. Treat it as a full reshard through MoveTables/Reshard, never as an in-place VSchema swap.
• hash vs xxhash. For new keyspaces prefer xxhash — it is faster and has no legacy 3DES dependency. Keep legacy hash only where existing keyspace IDs were computed with it; mixing them on the same column re-places rows.
• Range distribution needs manual boundary management. With xxhash the shard boundaries are hash ranges that never need adjusting as data grows. With numeric range distribution the boundaries are value ranges, and a boundary set when the key was at ten million will strand every new row above it on the top shard as the key climbs. Range distribution is only viable if you are prepared to own boundary maintenance as an ongoing operational task, not a one-time setup.
• Do not reach for range distribution to make ORDER BY cheap unless the ordering column is the sharding key. A range distribution over event_id gives ordered locality on event_id only; an ORDER BY created_at still scatters if created_at is not the vindex column. The locality benefit is specific to the one column the keyspace is ranged on.

Verification #

Prove the distribution behaviour with vtexplain before committing, then confirm real placement on a tablet. First, a range predicate should scatter under hash but stay narrow under range:

# Under xxhash this BETWEEN scatters to all shards
vtexplain --vschema-file vschema.json --schema-file schema.sql \
  --shards 4 --sql "SELECT * FROM orders WHERE customer_id BETWEEN 41 AND 100"

A hashed keyspace prints SelectScatter for that query; a range-distributed keyspace on the same predicate prints a Route touching only the intersecting shards. Then check that writes are actually spreading rather than piling on one shard:

# Row counts per shard should be roughly even under hash, skewed under range
vtctldclient GetTablets --keyspace commerce --tablet-type primary
# then per shard:
vtctldclient ExecuteFetchAsDBA <shard-primary-alias> "SELECT COUNT(*) FROM orders"

A steep, growing count on the highest-range shard is the range-distribution hotspot showing itself; if that shard is also your write-latency outlier, the vindex choice is the root cause.

Related #

• Vindex Selection and Tuning — the full functional-versus-lookup vindex decision this comparison lives inside.
• Understanding Vitess Keyspace Partitioning Models — how keyspace IDs map onto shard ranges for hash and range distributions.
• Planning a Shard Split with VReplication — splitting a hot range shard versus an even hash split.

← Back to Vindex Selection and Tuning