When a parent table and its child tables share a Neki shard group and the same shard key, every child row lives on the same shard as its parent. This guide places customers, orders and order_items together, carries customer_id into each child's primary key, adds foreign keys that each shard enforces locally, and compares the join plans with a child table placed by a different key.
What you will build
shard group by_customer, xxhash(customer_id):
customers (customer_id)
orders (customer_id, order_id) FK -> customers
order_items (customer_id, order_id, line_no) FK -> orders
shard group by_order, xxhash(order_id):
order_items_by_order (order_id, line_no) same data, placed by order_id
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, keep a parent table and its foreign-key children on the same Neki shard so joins and foreign keys stay on one shard.
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.
- Put the parent and every child table in one shard group with one shard index on the same column, here customer_id.
- Carry the shard key into every child table and make it the first column of the child's primary key and of each foreign key.
- Add foreign keys as native DDL through the router. Each shard enforces them against its own rows, which is correct only because parent and child rows share a shard.
- Compare join plans with EXPLAIN (NEKI_PLAN, ANALYZE): RemoteCalls and NetworkRows show how much work leaves the shards.
- 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 ordersco --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create ordersco 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 ordersco 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 ordersco --force --org "$PLANETSCALE_ORG".
1. Create the tables and place them
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE customers (customer_id bigint PRIMARY KEY, name text NOT NULL)" \
-c "CREATE TABLE orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, PRIMARY KEY (customer_id, order_id))" \
-c "CREATE TABLE order_items (customer_id bigint NOT NULL, order_id bigint NOT NULL, line_no int NOT NULL, sku text NOT NULL, PRIMARY KEY (customer_id, order_id, line_no))" \
-c "CREATE TABLE order_items_by_order (order_id bigint NOT NULL, line_no int NOT NULL, sku text NOT NULL, PRIMARY KEY (order_id, line_no))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": {
"xxhash_customer_id": { "type": "xxhash", "columns": ["customer_id"] },
"xxhash_order_id": { "type": "xxhash", "columns": ["order_id"] }
},
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_customer", "default_shard_index": "xxhash_customer_id",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
{ "uid": "by_order", "default_shard_index": "xxhash_order_id",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"customers": { "shard_group": "by_customer" },
"orders": { "shard_group": "by_customer" },
"order_items": { "shard_group": "by_customer" },
"order_items_by_order": { "shard_group": "by_order" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update ordersco main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -At \
-c "INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 500) g" \
-c "INSERT INTO orders SELECT (g % 500) + 1, g FROM generate_series(1, 5000) g" \
-c "INSERT INTO order_items SELECT (g % 500) + 1, g, l, 'sku' || l FROM generate_series(1, 5000) g, generate_series(1, 3) l" \
-c "INSERT INTO order_items_by_order SELECT g, l, 'sku' || l FROM generate_series(1, 5000) g, generate_series(1, 3) l"
Real output:
applied
INSERT 0 500
INSERT 0 5000
INSERT 0 15000
INSERT 0 15000
2. Add the foreign keys
psql -At \
-c "ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (customer_id)" \
-c "ALTER TABLE order_items ADD CONSTRAINT items_order_fk FOREIGN KEY (customer_id, order_id) REFERENCES orders (customer_id, order_id)" \
2>&1 | grep -v NOTICE
psql -At -c "INSERT INTO order_items VALUES (42, 999999, 1, 'orphan')" 2>&1 | head -2
psql -At -c "DELETE FROM orders WHERE customer_id = 42 AND order_id = 41" 2>&1 | head -2
Real output:
ALTER TABLE
ALTER TABLE
ERROR: insert or update on table "order_items" violates foreign key constraint "items_order_fk"
DETAIL: Key (customer_id, order_id)=(42, 999999) is not present in table "orders".
ERROR: update or delete on table "orders" violates foreign key constraint "items_order_fk" on table "order_items"
DETAIL: Key (customer_id, order_id)=(42, 41) is still referenced from table "order_items".
Both keys were accepted, and each shard rejects a child without a parent and a parent delete that would leave children behind.
3. Compare join plans
plan() { psql -At -c "EXPLAIN (NEKI_PLAN, ANALYZE) $1" | grep -E "^ *(-> )?(Route|Join|Collapse|Aggregate)|RemoteCalls:|NetworkRows" | sed 's/ (cost=[^)]*)//; s/ (actual[^)]*)//' | head -12; }
echo "== one customer, co-located"; plan "SELECT count(*) FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.customer_id = o.customer_id AND i.order_id = o.order_id WHERE c.customer_id = 42"
echo "== all customers, co-located"; plan "SELECT count(*) FROM orders o JOIN order_items i ON i.customer_id = o.customer_id AND i.order_id = o.order_id"
echo "== one customer, child placed by order_id"; plan "SELECT count(*) FROM orders o JOIN order_items_by_order i ON i.order_id = o.order_id WHERE o.customer_id = 42"
Real output:
== one customer, co-located
Route [EqualUnique]
RemoteCalls: 1
CostAtActualRows: Total=1001 RemoteCalls=1 NetworkRows=1
== all customers, co-located
Aggregate [Ordered]
CostAtActualRows: Total=2002 RemoteCalls=2 RowsProcessed=2 NetworkRows=2
-> Collapse
CostAtActualRows: Total=2002 RemoteCalls=2 NetworkRows=2
-> Route [Scatter]
RemoteCalls: 2
CostAtActualRows: Total=2002 RemoteCalls=2 NetworkRows=2
== one customer, child placed by order_id
Aggregate [Ordered]
CostAtActualRows: Total=8065 RemoteCalls=3 RowsProcessed=5030 NetworkRows=5010 MemoryRows=10
-> Collapse
CostAtActualRows: Total=8065 RemoteCalls=3 RowsProcessed=5020 NetworkRows=5010 MemoryRows=10
-> Join [Hash]
CostAtActualRows: Total=8065 RemoteCalls=3 RowsProcessed=5020 NetworkRows=5010 MemoryRows=10
-> Route [EqualUnique]
RemoteCalls: 1
CostAtActualRows: Total=1010 RemoteCalls=1 NetworkRows=10
-> Route [Scatter]
RemoteCalls: 2
CostAtActualRows: Total=7000 RemoteCalls=2 NetworkRows=5000
The co-located join for one customer is a single request to one shard. Without a customer filter, it is still one join per shard, and only two partial counts cross the network. With the child placed by order_id, the router fetched the customer's 10 orders from one shard, then read order_items_by_order from every shard, 5,000 rows, and joined them itself: 5,010 rows crossed the network to count 30 items.
Verify the result
psql -At -c "SELECT count(*) FROM pg_constraint WHERE conname IN ('orders_customer_fk', 'items_order_fk') AND convalidated"
psql -At -c "SELECT count(*) FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.customer_id = o.customer_id AND i.order_id = o.order_id WHERE c.customer_id = 42"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*) FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.customer_id = o.customer_id AND i.order_id = o.order_id WHERE c.customer_id = 42" | head -1
Expected: 2, 30 (10 orders with 3 items each), and Route [EqualUnique].
How it works
The shard index maps a customer_id to one position in the hash space, and every table in the group uses the same index and key ranges, so all rows with that customer_id land on the same shard. Joins on the shard key can then run entirely inside each shard's Postgres, and foreign keys that start with the shard key can be checked there too. A child placed by its own id breaks that: its rows are spread independently of their parents, so the router has to move rows between shards to join them, and a foreign key between the two could not be checked on any single shard. Shard groups are described in the Neki data topology guide.
Common issues
A foreign key to a table in another shard group
In testing, a foreign key from a sharded table to a table on the authoritative shard was accepted, and then a valid update failed with violates foreign key constraint, because each shard checked its own empty copy of the parent. Keep parent and child in the same group.
Online DDL on tables with foreign keys
With these keys in place, a copy-path online DDL workflow on the child failed with foreign key in table ... references non-existent table, and one on the parent was rejected because the table is referenced by foreign key. Plan large schema changes to these tables accordingly.
Deleting or archiving parents
DELETE FROM orders for rows that still have items fails with violates foreign key constraint "items_order_fk" on table "order_items". Delete or move the children first, in the same transaction or batch.
Next steps
- Delete the database with
pscale database delete ordersco --force --org "$PLANETSCALE_ORG". - Read the Neki data topology guide for schema-level default shard groups.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "count of validated FKs; keyed three-table join count; its EXPLAIN (NEKI_PLAN) top node"
expected_result: "2, 30, Route [EqualUnique]"