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

Reference

Shard a Table by an Expression Such as a Lowercased Email on Neki

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.

  • 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 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-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 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"