A Neki shard index can hash an expression instead of a plain column. This guide shards a users table by lower(email), so every spelling of an address lands on the same shard. That makes email lookups single-shard whatever the case, and lets a unique index on lower(email) reject case-variant duplicates across the whole table.
What you will build
shard index: xxhash(lower(email))
WHERE lower(email) = 'user7@example.com' -> one shard
WHERE email = 'User7@Example.com' -> one shard (the router lowercases the value)
UNIQUE (lower(email)) -> 'USER7@EXAMPLE.COM' rejected as a duplicate
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, shard a Neki table by the expression lower(email) and keep email addresses unique regardless of case.
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 index as {"type":"xxhash","columns":["lower(email)"]}. columns holds exactly one entry, which may be a deterministic expression over one column.
- Create a unique index on (lower(email)). Because every case variant hashes to the same shard, each shard's unique index covers all spellings of an address.
- Every INSERT must include email. UPDATE of email is not supported; change an address with DELETE and INSERT in one transaction.
- Confirm 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.
pscale0.344.0 with a service token that can create databases,psql18.6, andjq:
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 accountsdb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create accountsdb 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 accountsdb 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 accountsdb --force --org "$PLANETSCALE_ORG".
1. Create the table and shard it by lower(email)
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE users (user_id bigint NOT NULL, email text NOT NULL, name text NOT NULL)" \
-c "CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": { "email_ci": { "type": "xxhash", "columns": ["lower(email)"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "users_by_email", "default_shard_index": "email_ci",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"users": { "shard_group": "users_by_email" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update accountsdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -At -c "INSERT INTO users SELECT g, 'User' || g || '@Example.com', 'user ' || g FROM generate_series(1, 1000) g"
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(*) FROM users" | tail -1)"
done
Real output:
applied
INSERT 0 1000
sh2: 490
sh3: 510
2. Look up by email in any case
for q in "lower(email) = 'user7@example.com'" "email = 'User7@Example.com'" "lower(email) = lower('USER7@EXAMPLE.COM')" "user_id = 7"; do
echo "$q | $(psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM users WHERE $q" | grep -oE 'Route \[[A-Za-z]+\]') | rows: $(psql -At -c "SELECT count(*) FROM users WHERE $q")"
done
Real output:
lower(email) = 'user7@example.com' | Route [EqualUnique] | rows: 1
email = 'User7@Example.com' | Route [EqualUnique] | rows: 1
lower(email) = lower('USER7@EXAMPLE.COM') | Route [EqualUnique] | rows: 1
user_id = 7 | Route [Scatter] | rows: 1
Lookups by email route to one shard. Lookups by user_id, which is not the shard key, scatter.
3. Reject an address that differs only by case
psql -At -c "INSERT INTO users VALUES (5001, 'USER7@EXAMPLE.COM', 'duplicate')" 2>&1 | head -2
Real output:
ERROR: duplicate key value violates unique constraint "users_email_lower_key"
DETAIL: Key (lower(email))=(user7@example.com) already exists.
4. Change an address
The shard key column cannot be updated in place, because a new address may belong on another shard:
psql -At -c "UPDATE users SET email = 'renamed7@example.com' WHERE lower(email) = 'user7@example.com'" 2>&1 | head -1
psql -At -v ON_ERROR_STOP=1 <<'SQL' 2>&1 | grep -v NOTICE
BEGIN;
DELETE FROM users WHERE lower(email) = 'user7@example.com' RETURNING user_id, name \gset
INSERT INTO users VALUES (:user_id, 'Renamed7@Example.com', :'name');
COMMIT;
SQL
psql -At -c "SELECT user_id, email FROM users WHERE lower(email) IN ('user7@example.com', 'renamed7@example.com')"
Real output:
ERROR: not implemented: [145] updating index column "email" is not supported
BEGIN
DELETE 1
INSERT 0 1
COMMIT
7|Renamed7@Example.com
Verify the result
psql -At -c "SELECT count(*) FROM users"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM users WHERE email = 'RENAMED7@example.com'" | grep -oE 'Route \[[A-Za-z]+\]'
psql -At -c "INSERT INTO users VALUES (5002, 'renamed7@EXAMPLE.com', 'duplicate')" 2>&1 | head -1
Expected: 1000, Route [EqualUnique], and ERROR: duplicate key value violates unique constraint "users_email_lower_key".
How it works
The router evaluates the index expression on the value in the query or row, lower('User7@Example.com') here, hashes the result, and sends the statement to the shard that owns that hash. Every spelling of an address therefore lands on one shard, and that shard's Postgres unique index on lower(email) sees every spelling, so a per-shard index gives table-wide uniqueness for this key. Columns outside the shard key, such as user_id, have no routing information and scatter. Expression indexes are described in the Neki data topology guide.
Common issues
An insert without the email
INSERT INTO users (user_id, name) VALUES (9999, 'n') fails with shard-key column "email" of primary index 0 ("email_ci") is required but missing from INSERT.
Uniqueness on another column
A unique index on user_id here would be checked on each shard separately, so two shards could hold the same user_id. Only indexes on the shard key expression are table-wide.
Matching the expression exactly
The shard index and the unique index must use the same expression. A query filtering on upper(email) has no routing information and scatters.
Next steps
- Delete the database with
pscale database delete accountsdb --force --org "$PLANETSCALE_ORG". - Read the Neki data topology guide for the restrictions on expressions that name several columns.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "count(*); EXPLAIN (NEKI_PLAN) for email = 'RENAMED7@example.com'; insert of a case-variant duplicate"
expected_result: "1000, Route [EqualUnique], and a unique violation on users_email_lower_key"