Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Choose Between Hash, Range, and Modulo Shard Keys for an Integer Id on Neki

Neki offers three shard index types for an integer column: xxhash, range and modulo. This guide shards the same 10,000-row table three ways and compares where rows land, where new sequential ids go, and how the router plans equality, IN and range queries. In this build all three route an equality lookup to one shard and scatter range predicates; they differ in placement.

What you will build

ev_hash  -> xxhash(id)          sh2: hash < 80      sh3: hash >= 80
ev_range -> range(id)           sh2: id < 0x1388    sh3: id >= 0x1388   (0x1388 = 5000)
ev_mod   -> modulo(id, 4)       sh2: bucket 0, 1    sh3: bucket 2, 3

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, compare xxhash, range and modulo shard indexes for an integer id on a Neki database.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Define the shard indexes as {"type":"xxhash","columns":["id"]}, {"type":"range","columns":["id"]} and {"type":"modulo","columns":["id"],"index_params":{"modulus":"4"}}. The modulus is a JSON string.
- Key range bounds are hexadecimal: for range they are id values, for modulo they are bucket numbers, for xxhash they are positions in the hash space.
- Apply the data topology before creating rows, then load.
- Compare per-shard placement with SET __neki.shard, and routing with EXPLAIN (NEKI_PLAN, COSTS OFF).
- Run the verification step below before finishing.

Prerequisites

  • A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, and jq:

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
  • A Neki database with three shards. Create it with these commands:
pscale database create idkeys --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create idkeys main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
       PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done

pscale branch shard create idkeys main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
  until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done

SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")

Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster. PS-10-AWS-ARM-NEKI lists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete idkeys --force --org "$PLANETSCALE_ORG".

1. Create three tables and a topology with three indexes

for t in ev_hash ev_range ev_mod; do
  psql -q -c "CREATE TABLE $t (id bigint PRIMARY KEY, payload text NOT NULL)" 2>&1 | grep -v NOTICE
done

cat > data-topology.json <<JSON
{
  "shard_indexes": {
    "by_hash":   { "type": "xxhash", "columns": ["id"] },
    "by_range":  { "type": "range",  "columns": ["id"] },
    "by_modulo": { "type": "modulo", "columns": ["id"], "index_params": { "modulus": "4" } }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "g_hash",  "default_shard_index": "by_hash",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" },   { "shard_uid": "$SHARD_3", "start": "80" } ] },
    { "uid": "g_range", "default_shard_index": "by_range",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "1388" }, { "shard_uid": "$SHARD_3", "start": "1388" } ] },
    { "uid": "g_mod",   "default_shard_index": "by_modulo",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "02" },   { "shard_uid": "$SHARD_3", "start": "02" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "ev_hash":  { "shard_group": "g_hash" },
    "ev_range": { "shard_group": "g_range" },
    "ev_mod":   { "shard_group": "g_mod" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update idkeys main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

Real output:

applied

The range split 1388 is hexadecimal for 5,000. The modulo split 02 is a bucket number: with a modulus of 4, id % 4 gives buckets 0 to 3, and buckets 0 and 1 go to sh2.

2. Load the same ids and compare placement

cat > placement.sh <<'EOF'
#!/bin/bash
# usage: placement.sh <where clause>
for t in ev_hash ev_range ev_mod; do
  echo -n "$t: "
  for n in sh2 sh3; do
    u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
    echo -n "$n=$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) || ' (ids ' || coalesce(min(id), 0) || '-' || coalesce(max(id), 0) || ')' FROM $t WHERE $1" | tail -1)  "
  done
  echo
done
EOF
chmod +x placement.sh
for t in ev_hash ev_range ev_mod; do psql -At -c "INSERT INTO $t SELECT g, 'x' FROM generate_series(1, 10000) g" > /dev/null; done
./placement.sh "true"

Real output:

ev_hash: sh2=4997 (ids 2-9999)  sh3=5003 (ids 1-10000)  
ev_range: sh2=4999 (ids 1-4999)  sh3=5001 (ids 5000-10000)  
ev_mod: sh2=5000 (ids 1-10000)  sh3=5000 (ids 2-9999)

All three split the table in half. Range keeps contiguous ids together, modulo interleaves them, and hash scatters them.

3. Add sequential new ids

for t in ev_hash ev_range ev_mod; do psql -At -c "INSERT INTO $t SELECT g, 'new' FROM generate_series(10001, 12000) g" > /dev/null; done
./placement.sh "id > 10000"

Real output:

ev_hash: sh2=1045 (ids 10003-12000)  sh3=955 (ids 10001-11995)  
ev_range: sh2=0 (ids 0-0)  sh3=2000 (ids 10001-12000)  
ev_mod: sh2=1000 (ids 10001-12000)  sh3=1000 (ids 10002-11999)

With an increasing id, every new row lands on the last range shard, so one shard takes all inserts. Hash and modulo keep spreading them.

4. Compare routing

for t in ev_hash ev_range ev_mod; do
  for q in "id = 4242" "id IN (1, 2, 3)" "id BETWEEN 100 AND 200" "id > 9000"; do
    echo "$t | $q | $(psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*) FROM $t WHERE $q" | grep -oE 'Route \[[A-Za-z]+\]')"
  done
done
PGOPTIONS='-c __neki.fanout=single' psql -At -c "SELECT count(*) FROM ev_range WHERE id BETWEEN 100 AND 200" 2>&1 | head -1

Real output:

ev_hash | id = 4242 | Route [EqualUnique]
ev_hash | id IN (1, 2, 3) | Route [IN]
ev_hash | id BETWEEN 100 AND 200 | Route [Scatter]
ev_hash | id > 9000 | Route [Scatter]
ev_range | id = 4242 | Route [EqualUnique]
ev_range | id IN (1, 2, 3) | Route [IN]
ev_range | id BETWEEN 100 AND 200 | Route [Scatter]
ev_range | id > 9000 | Route [Scatter]
ev_mod | id = 4242 | Route [EqualUnique]
ev_mod | id IN (1, 2, 3) | Route [IN]
ev_mod | id BETWEEN 100 AND 200 | Route [Scatter]
ev_mod | id > 9000 | Route [Scatter]
ERROR:  statement's fan-out (scatter) exceeds __neki.fanout (single)

Every index routes an equality lookup to one shard and plans an IN list as Route [IN], which __neki.fanout = 'single' treats as multi-shard even when every value is on one shard. In this build none of them prunes a range predicate, not even the range index when the whole range is on one shard; with __neki.fanout = 'single' that query is rejected as a scatter.

Verify the result

for t in ev_hash ev_range ev_mod; do echo "$t: $(psql -At -c "SELECT count(*) FROM $t") $(psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM $t WHERE id = 11999" | grep -oE 'Route \[[A-Za-z]+\]')"; done
./placement.sh "id > 10000" | grep ev_range

Expected: 12000 Route [EqualUnique] for each table, and ev_range with sh2=0 for the new ids.

How it works

A shard index turns the key into a position, and the shard group's key ranges map positions to shards. xxhash hashes the value, so neighbouring ids end up anywhere and load spreads evenly whatever the id pattern. range uses the value itself, so you decide which ids live on which shard, which helps when you want to place known ranges deliberately, and it concentrates new rows wherever the highest ids live. modulo uses id % modulus, so placement follows a fixed number of buckets, and a table can never use more shards than it has buckets. The index types are described in the Neki data topology guide.

Common issues

The modulus is a number

"index_params": { "modulus": 4 } fails with invalid data topology JSON: proto: (line 1:160): invalid value for string field value: 4. Write it as a string, "4".

Range bounds read as decimal

"end": "1388" splits at 5,000, not 1,388, because the bounds are hexadecimal. Convert with printf '%x' 5000.

Rows inserted before the topology

The first attempt in testing applied a topology that failed, and the inserts went to the authoritative shard. Check the topology update's status before loading data, and reload a table that was filled before its topology was in place.

Expecting a range query to stay on one shard

id BETWEEN 100 AND 200 scattered on all three tables. If a workload needs single-shard range scans, make the range lie within one shard key value, for example by sharding on a tenant id and scanning ids within that tenant.

Next steps

  • Delete the database with pscale database delete idkeys --force --org "$PLANETSCALE_ORG".
  • Read the Neki data topology guide for column types each index accepts.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "row count and EXPLAIN (NEKI_PLAN) for id = 11999 on each table; placement of ids above 10000 for ev_range"
  expected_result: "12000 rows and Route [EqualUnique] on each table; every new id on sh3 for ev_range."