Shard Orders and Inventory by Different Keys on Neki with the PlanetScale CLI

This guide shards an ecommerce schema on Neki with two keys: orders and order items by user_id, products and inventory by product_id, in two shard groups that share the same two shards. It then tests an idempotent order insert, a checkout transaction under tx_mode = 'single', and a join that crosses the two groups.

What you will build

Four sharded tables in two shard groups over the same shards, with checks that related rows sit together and that checkout and join queries route the way the design intends.

by_user    (xxhash on user_id)     orders, order_items       -> sh2 (00..7f) | sh3 (80..ff)
by_product (xxhash on product_id)  products, inventory       -> sh2 (00..7f) | sh3 (80..ff)
sh1 stays the authoritative shard and holds no table rows

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, shard orders and order_items by user_id and products and inventory by product_id on a Neki database, then test idempotent writes, checkout transactions and joins.

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. Neki has no single-node configuration.
- Create one admin role and use only that role for every statement. A second role cannot run DDL on tables the first role created.
- Apply one topology with two shard groups that both use the same two shards, each with its own xxhash shard index, and bind each table to a group. A topology update is a complete replacement.
- Apply the topology before loading rows.
- Include the shard key in every unique constraint that an ON CONFLICT clause depends on. Neki accepted a unique index without the shard key, but uniqueness was enforced per shard only.
- Use SET __neki.tx_mode = 'single' before BEGIN for checkout transactions. A transaction that reaches a second shard fails and rolls back.
- A join between tables in different shard groups can scatter across the whole second table. Check the plan with EXPLAIN (NEKI_PLAN) and use a keyed IN lookup when you already know the ids.
- 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 and the four tables. Create everything with these commands, using the one role for all statements:

pscale database create storefront --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create storefront 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 storefront 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

psql -q -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  status text NOT NULL,
  total numeric NOT NULL,
  idempotency_key text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, idempotency_key)
);
CREATE TABLE order_items (
  item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  order_id bigint NOT NULL,
  product_id bigint NOT NULL,
  quantity integer NOT NULL,
  price numeric NOT NULL
);
CREATE INDEX order_items_user_idx ON order_items (user_id, order_id);
CREATE TABLE products (
  product_id bigint PRIMARY KEY,
  name text NOT NULL,
  price numeric NOT NULL
);
CREATE TABLE inventory (
  product_id bigint PRIMARY KEY,
  quantity integer NOT NULL CHECK (quantity >= 0)
);
SQL

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, and shard ids differ per database. 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 storefront --force --org "$PLANETSCALE_ORG".

1. Apply a topology with two shard groups on the same shards

Both groups use SHARD_2 below 80 and SHARD_3 from 80. Each group has its own shard index, and each table is bound to one group.

cat > data-topology.json <<JSON
{
  "shard_indexes": {
    "xxhash_user_id": { "type": "xxhash", "columns": ["user_id"] },
    "xxhash_product_id": { "type": "xxhash", "columns": ["product_id"] }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_user", "default_shard_index": "xxhash_user_id",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
    { "uid": "by_product", "default_shard_index": "xxhash_product_id",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "orders": { "shard_group": "by_user" },
    "order_items": { "shard_group": "by_user" },
    "products": { "shard_group": "by_product" },
    "inventory": { "shard_group": "by_product" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update storefront main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -c '.data_topology | keys'
sleep 10
for t in orders order_items products inventory; do
  key=$([ "$t" = orders -o "$t" = order_items ] && echo user_id || echo product_id)
  echo "== $t"
  psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT * FROM $t WHERE $key = 7;" | grep -E "Route|ShardGroup"
done

Real output:

["authoritative_shard_group","databases","default_shard_group","shard_groups","shard_indexes"]
== orders
Route [EqualUnique]
  ShardGroup: by_user
== order_items
Route [EqualUnique]
  ShardGroup: by_user
== products
Route [EqualUnique]
  ShardGroup: by_product
== inventory
Route [EqualUnique]
  ShardGroup: by_product

The topology was accepted with the same two shards in two groups, and each table routes to its own group with a single-shard route.

2. Load data and check where it landed

psql -At <<'SQL'
INSERT INTO products (product_id, name, price) SELECT g, 'product-' || g, 10 + g % 90 FROM generate_series(1, 2000) g;
INSERT INTO inventory (product_id, quantity) SELECT g, 100 FROM generate_series(1, 2000) g;
INSERT INTO orders (user_id, status, total, idempotency_key) SELECT u, 'paid', 50, 'key-' || u || '-' || n FROM generate_series(1,200) u, generate_series(1,3) n;
INSERT INTO order_items (user_id, order_id, product_id, quantity, price) SELECT o.user_id, o.order_id, (o.user_id * 7 + k) % 2000 + 1, 1, 25 FROM orders o, generate_series(1,2) k;
SQL
echo "== rows per shard (orders, order_items, products, inventory)"
for u in "$SHARD_2" "$SHARD_3" "$SHARD_1"; do
  echo -n "$u: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT (SELECT count(*) FROM orders)||' '||(SELECT count(*) FROM order_items)||' '||(SELECT count(*) FROM products)||' '||(SELECT count(*) FROM inventory);" | tail -1
done

Real output:

INSERT 0 2000
INSERT 0 2000
INSERT 0 600
INSERT 0 1200
== rows per shard (orders, order_items, products, inventory)
sh7ezmt40qc4pf: 258 516 955 955
shqg6iyreb9wtd: 342 684 1045 1045
shv0iz4t22dq33: 0 0 0 0

Rows are split across the two data shards and the authoritative shard holds none. Each shard has twice as many order items as orders, and as many inventory rows as products.

3. Check co-location in both groups

Count order items without an order on the same shard, and inventory rows without a product on the same shard:

for u in "$SHARD_2" "$SHARD_3"; do
  echo -n "$u items without a local order: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM order_items i WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id=i.order_id AND o.user_id=i.user_id);" | tail -1
  echo -n "$u inventory rows without a local product: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM inventory v WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.product_id=v.product_id);" | tail -1
done

Real output:

sh7ezmt40qc4pf items without a local order: 0
sh7ezmt40qc4pf inventory rows without a local product: 0
shqg6iyreb9wtd items without a local order: 0
shqg6iyreb9wtd inventory rows without a local product: 0

4. Make an order insert idempotent

ON CONFLICT (user_id, idempotency_key) matches the unique constraint that includes the shard key. Run the same checkout request twice:

for i in 1 2; do
  psql -At -c "INSERT INTO orders (user_id, status, total, idempotency_key) VALUES (501, 'pending', 99.00, 'checkout-abc') ON CONFLICT (user_id, idempotency_key) DO UPDATE SET status = EXCLUDED.status, total = EXCLUDED.total RETURNING order_id, status;" | grep -v '^INSERT'
done
psql -At -c "SELECT count(*) FROM orders WHERE user_id=501 AND idempotency_key='checkout-abc';"
psql -At -c "INSERT INTO orders (user_id, status, total, idempotency_key) VALUES (502, 'pending', 99.00, 'checkout-abc') ON CONFLICT (user_id, idempotency_key) DO NOTHING RETURNING order_id;" | grep -v '^INSERT'
psql -At -c "SELECT count(*) FROM orders WHERE idempotency_key='checkout-abc';"

Real output (the order ids vary per run):

1001|pending
1001|pending
1
1002
2

Both runs returned the same order_id and left one row. The same key for a different user created a separate order, which is why the key is scoped to the user.

5. See what a unique index without the shard key enforces

A UNIQUE index on idempotency_key alone is accepted. Users 1 and 3 are on one shard and user 2 is on the other (the placement comes from the hash, so it is the same in any database with this topology). Insert the same key for each:

psql -At -c "CREATE UNIQUE INDEX orders_key_only ON orders (idempotency_key);"
for u in 1 2 3; do
  echo -n "insert key dup1 for user $u: "
  psql -At -c "INSERT INTO orders (user_id,status,total,idempotency_key) VALUES ($u,'pending',1,'dup1');" 2>&1 | grep -v NOTICE | head -2 | tr '\n' ' '; echo
done
psql -At -c "SELECT user_id FROM orders WHERE idempotency_key='dup1' ORDER BY 1;" | tr '\n' ' '; echo
psql -At -c "DROP INDEX orders_key_only;"
psql -At -c "DELETE FROM orders WHERE idempotency_key='dup1';"

Real output:

CREATE INDEX
insert key dup1 for user 1: INSERT 0 1 
insert key dup1 for user 2: INSERT 0 1 
insert key dup1 for user 3: ERROR:  duplicate key value violates unique constraint "orders_key_only" DETAIL:  Key (idempotency_key)=(dup1) already exists. 
1 2 
DROP INDEX
DELETE 2

The key was accepted for users 1 and 2 on different shards and rejected for user 3, who shares a shard with user 1. The index enforced uniqueness within each shard only. Keep the shard key in every unique constraint that your application relies on.

6. Run checkout with `tx_mode = 'single'`

A checkout inserts an order for a user and decrements inventory for a product. User 1 is on the same shard as product 3 and on a different shard from product 2:

checkout() {
  psql -At <<SQL 2>&1 | grep -E "ERROR|COMMIT|ROLLBACK" | head -2 | tr '\n' ' '
SET __neki.tx_mode = 'single';
BEGIN;
INSERT INTO orders (user_id,status,total,idempotency_key) VALUES (1,'paid',20,'co-$1-$RANDOM');
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = $1;
COMMIT;
SQL
  echo
}
echo -n "product 3 (same shard as user 1): "; checkout 3
echo -n "product 2 (other shard):          "; checkout 2
psql -At -c "SELECT string_agg(product_id||':'||quantity, ' ' ORDER BY product_id) FROM inventory WHERE product_id IN (2,3);"

Real output:

product 3 (same shard as user 1): COMMIT 
product 2 (other shard):          ERROR:  __neki.tx_mode = 'single' forbids a transaction from touching more than one shard ROLLBACK 
2:100 3:99

The same-shard checkout committed. The cross-shard checkout failed and rolled back, and inventory for product 2 stayed at 100. In the default multi mode the same cross-shard checkout committed:

psql -At <<'SQL' | grep -E "ERROR|COMMIT" | tr '\n' ' '; echo
BEGIN;
INSERT INTO orders (user_id,status,total,idempotency_key) VALUES (1,'paid',20,'co-multi-1');
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 2;
COMMIT;
SQL

COMMIT

Because users and products hash independently, a checkout that spans two shard groups often crosses shards. Under single mode, those checkouts need a different design, such as the two-step pattern in "Keep Ledger Transactions on One Neki Shard".

7. Read across the two groups without scanning a whole table

Orders and order items are in the same group, so a join between them stays on one shard:

psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT o.order_id, i.product_id FROM orders o JOIN order_items i ON i.user_id=o.user_id AND i.order_id=o.order_id WHERE o.user_id=1;" | head -4

Route [EqualUnique]
  Query: SELECT o.order_id, i.product_id FROM public.orders AS o JOIN public.order_items AS i ON i.user_id = o.user_id AND i.order_id = o.order_id WHERE o.user_id = $1
  ShardGroup: by_user
  Values: $1

Joining order items to products crosses the groups. The plan below reads the keyed order items and scatters a read of every product:

psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT i.item_id, p.name FROM order_items i JOIN products p ON p.product_id=i.product_id WHERE i.user_id=1;" | cut -c1-110
psql -At -c "SET __neki.fanout = 'single';" -c "SELECT i.item_id, p.name FROM order_items i JOIN products p ON p.product_id=i.product_id WHERE i.user_id=1;" 2>&1 | grep ERROR

Collapse
  ->  Join [Hash]
        ->  Route [EqualUnique]
              Query: SELECT i.product_id, i.item_id FROM public.order_items AS i WHERE i.user_id = $1
              ShardGroup: by_user
              Values: $1
        ->  Route [Scatter]
              Query: SELECT p.product_id, p.name FROM public.products AS p
              ShardGroup: by_product
ERROR:  statement's fan-out (scatter) exceeds __neki.fanout (single)

When the product ids are known, look them up with a keyed IN query instead:

IDS=$(psql -At -c "SELECT string_agg(DISTINCT product_id::text, ',') FROM order_items WHERE user_id=1;")
echo "ids for user 1: $IDS"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT product_id, name FROM products WHERE product_id IN ($IDS);" | cut -c1-110
psql -At -c "SET __neki.fanout = 'multi';" -c "SELECT product_id, name FROM products WHERE product_id IN ($IDS) ORDER BY 1;" | grep -v '^SET'

Real output:

ids for user 1: 10,9
Collapse
  ->  Route [IN]
        Query: SELECT product_id, name FROM public.products WHERE product_id = ANY($1)
        ShardGroup: by_product
        Values: $1
9|product-9
10|product-10

Route [IN] reaches only the shards that hold those product ids, so it is allowed under fanout = 'multi'.

Verify the result

Confirm that no related rows were split across shards and that a repeated checkout request produces one order:

orphans=0
for u in "$SHARD_2" "$SHARD_3"; do
  a=$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM order_items i WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id=i.order_id AND o.user_id=i.user_id);" | tail -1)
  b=$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM inventory v WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.product_id=v.product_id);" | tail -1)
  orphans=$((orphans + a + b))
done
echo "orphaned rows across both groups: $orphans"
first=$(psql -At -c "INSERT INTO orders (user_id, status, total, idempotency_key) VALUES (700, 'pending', 10, 'verify-key') ON CONFLICT (user_id, idempotency_key) DO UPDATE SET status = EXCLUDED.status RETURNING order_id;" | head -1)
second=$(psql -At -c "INSERT INTO orders (user_id, status, total, idempotency_key) VALUES (700, 'pending', 10, 'verify-key') ON CONFLICT (user_id, idempotency_key) DO UPDATE SET status = EXCLUDED.status RETURNING order_id;" | head -1)
echo "same order id both times: $([ "$first" = "$second" ] && echo yes || echo no); rows: $(psql -At -c "SELECT count(*) FROM orders WHERE user_id=700 AND idempotency_key='verify-key';")"

Expected output:

orphaned rows across both groups: 0
same order id both times: yes; rows: 1

How it works

Each shard group has its own shard index and key ranges, and a table follows the group it is bound to. Two groups can list the same shards, so one physical shard holds rows from both groups. Rows that share a key value in the same group land on one shard, which keeps an order and its items together. Users and products hash independently, so a user and a product often sit on different shards. A unique index is built independently on each shard, so it can only enforce uniqueness across rows that share a shard. The data topology reference and query planning describe shard groups, shard indexes and routes in full.

Common issues

A unique index without the shard key is not globally unique

CREATE UNIQUE INDEX ... ON orders (idempotency_key) succeeded, and the same key was then accepted for users on two different shards. ON CONFLICT (idempotency_key) without a matching unique constraint fails with there is no unique or exclusion constraint matching the ON CONFLICT specification. Include user_id in the unique constraint and in the ON CONFLICT list, as in step 4.

A second role cannot change tables the first role created

A role created later returned ERROR: must be owner of table orders for CREATE UNIQUE INDEX. Tables belong to the role that created them. Use one role for DDL, or connect as the owner. Resetting the owner role's password with pscale role reset gave access to it in this run.

Joins across shard groups can read the whole second table

The join between order_items and products planned a Route [Scatter] over every product, and fanout = 'single' rejected it. Look up the product ids with a keyed IN query, as in step 7.

Statements fail with "no healthy sidecars" right after the shards appear

In one run on another database the router listed all three shards and the first CREATE TABLE failed with ERROR: no healthy sidecars available for shard ... with type SIDECAR_TYPE_PRIMARY. The prerequisites wait for a direct SELECT 1 on every shard before creating tables.

Next steps

Move an existing table onto shards with a live writer: see "Reshard a Live Table onto New Neki Shards with the PlanetScale CLI".
Design a transfer that never spans shards: see "Keep Ledger Transactions on One Neki Shard with the PlanetScale CLI".
To model your own schema, see the Neki data topology reference and best practices.

verification:
  status: verified
  tested_at: "2026-10-12"
  product_version: "pscale 0.344.0; Neki PostgreSQL 18.6 (Platform Preview)"
  command: "count order_items and inventory rows without a same-shard parent on each data shard, and run the same ON CONFLICT (user_id, idempotency_key) insert twice"
  expected_result: "Zero orphaned rows across both groups, and the repeated insert returns the same order id and leaves one row. Order ids and shard ids vary per run."