This guide shards a multi-tenant table by a uuid tenant id on Neki. The xxhash shard index accepts uuid columns directly, so tenants spread evenly across shards, and every way of writing a given UUID literal routes to the same shard. Generated ids, from gen_random_uuid() or uuidv7(), work as the shard key on insert.
What you will build
tenants (tenant_id uuid DEFAULT gen_random_uuid()) --+
tenant_events (tenant_id uuid, event_id, ...) --+-- shard group by_tenant, xxhash(tenant_id)
WHERE tenant_id = '<uuid>' -> one shard
WHERE tenant_id::text = '<uuid>' -> every shard
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, shard a multi-tenant Neki table by a uuid tenant id.
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.
- Keep the column type uuid and use {"type":"xxhash","columns":["tenant_id"]}. The range and modulo index types need an integer column; a topology that uses them on a uuid column is accepted, but every insert then fails.
- Put every tenant-scoped table in the same shard group with tenant_id in its primary key.
- Compare tenant_id with a uuid value. Casting the column to text loses the routing.
- DEFAULT gen_random_uuid() and uuidv7() can supply the shard key on insert.
- 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.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
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 saasapp --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create saasapp 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 saasapp 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-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete saasapp --force --org "$PLANETSCALE_ORG".
1. Create the tables and shard them by tenant
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE tenants (tenant_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL)" \
-c "CREATE TABLE tenant_events (tenant_id uuid NOT NULL, event_id bigint NOT NULL, kind text NOT NULL, PRIMARY KEY (tenant_id, event_id))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": { "tenant_uuid": { "type": "xxhash", "columns": ["tenant_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_tenant", "default_shard_index": "tenant_uuid",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"tenants": { "shard_group": "by_tenant" },
"tenant_events": { "shard_group": "by_tenant" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update saasapp main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
Real output:
applied
2. Load 200 tenants
python3 -c "
import random, uuid
random.seed(5)
for t in range(200):
u = uuid.UUID(int=random.getrandbits(128), version=4)
for e in range(50): print(f'{u},{e},view')" > events.csv
psql -c "\copy tenant_events FROM 'events.csv' WITH (FORMAT csv)"
for n in sh2 sh3; do
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
echo "$n: $(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(DISTINCT tenant_id) || ' tenants, ' || count(*) || ' events' FROM tenant_events" | tail -1)"
done
Real output:
COPY 10000
sh2: 99 tenants, 4950 events
sh3: 101 tenants, 5050 events
The 200 tenants split almost evenly. The ids are random version 4 UUIDs generated with a fixed seed, so this split is the same on every run.
3. Query one tenant
T=$(head -1 events.csv | cut -d, -f1)
T_UPPER=$(echo "$T" | tr 'a-f' 'A-F')
for q in "tenant_id = '$T'" "tenant_id = '$T_UPPER'" "tenant_id = '{$T}'::uuid" "tenant_id::text = '$T'"; do
echo "$q | $(psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*) FROM tenant_events WHERE $q" | grep -oE 'Route \[[A-Za-z]+\]') | rows: $(psql -At -c "SELECT count(*) FROM tenant_events WHERE $q")"
done
Real output:
tenant_id = '5bc8fbbc-bde5-4099-8164-d8399f767c45' | Route [EqualUnique] | rows: 50
tenant_id = '5BC8FBBC-BDE5-4099-8164-D8399F767C45' | Route [EqualUnique] | rows: 50
tenant_id = '{5bc8fbbc-bde5-4099-8164-d8399f767c45}'::uuid | Route [EqualUnique] | rows: 50
tenant_id::text = '5bc8fbbc-bde5-4099-8164-d8399f767c45' | Route [Scatter] | rows: 50
The lowercase, uppercase and braced spellings parse to the same uuid, so they route to the same shard. Casting the column to text hides the shard key from the router, and the query scatters.
4. Insert tenants with generated ids
psql -At -c "INSERT INTO tenants (name) VALUES ('acme') RETURNING tenant_id" | head -1
psql -At -c "INSERT INTO tenants VALUES (uuidv7(), 'globex') RETURNING tenant_id" | head -1
for id in $(psql -At -c "SELECT tenant_id FROM tenants ORDER BY name"); do
echo "$id: keyed lookup finds $(psql -At -c "SELECT count(*) FROM tenants WHERE tenant_id = '$id'")"
done
Real output (the generated ids differ per run):
1ef7c43d-b7d1-4ee5-82c3-60963459ba60
01a184f0-03af-7d5c-873a-c32e3aaa081d
1ef7c43d-b7d1-4ee5-82c3-60963459ba60: keyed lookup finds 1
01a184f0-03af-7d5c-873a-c32e3aaa081d: keyed lookup finds 1
The router computed each default or function value before routing the row, so the rows went to the shard that a lookup by the same id reaches.
Verify the result
psql -At -c "SELECT count(DISTINCT tenant_id), count(*) FROM tenant_events"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM tenant_events WHERE tenant_id = '$T'" | grep -oE 'Route \[[A-Za-z]+\]'
psql -At -c "SELECT count(*) FROM tenants"
Expected: 200|10000, Route [EqualUnique], and 2.
How it works
xxhash hashes the uuid value rather than the text you wrote, so formatting does not affect placement, and random UUIDs spread evenly across the hash space. Time-ordered UUIDs spread too: in testing, 200 ids from uuidv7() split 99 and 101 across the two shards, because the hash does not preserve order. Keeping every tenant-scoped table in the same shard group puts one tenant's rows on one shard, so per-tenant queries and joins stay on that shard. The index types and accepted column types are listed in the Neki data topology guide.
Common issues
Storing the id as text
In testing with a text column, the lowercase and uppercase spellings of one UUID were stored as two separate rows on different shards. Keep the column uuid, or normalize the text before it reaches the database.
Comparing through a cast
WHERE tenant_id::text = '...' scattered in testing. Compare the column itself with a uuid value; a string literal is cast to uuid automatically.
Range or modulo on a UUID
A data topology that puts a range or modulo index on the uuid column was accepted without an error, and the stored topology kept the index type. Every insert then failed with invalid primary index 0 ("k") for INSERT: Range index requires an integer shard key, got "uuid" (or Modulo index requires ...), and lookups by tenant_id scattered. Use xxhash for a UUID key, and test an insert after any topology change.
Next steps
- Delete the database with
pscale database delete saasapp --force --org "$PLANETSCALE_ORG". - Read the Neki data topology guide for placing related tables in one shard group.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "count of tenants and events; EXPLAIN (NEKI_PLAN) for one tenant; count of generated tenants"
expected_result: "200|10000, Route [EqualUnique], 2"