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

Reference

Add a Foreign Key Constraint to a Large Sharded Table Without Blocking Writes on Neki

This guide adds a foreign key from a 2,000,000-row line_items table to customers on a sharded Neki database while a writer keeps inserting. It adds the constraint as NOT VALID, finds and fixes the rows that would fail validation, and then validates it. Both tables share a shard group and shard key, so every child row and its parent live on the same shard and each shard can check the constraint on its own.

What you will build

customers (by customer_id)  <-- line_items.customer_id   both in shard group by_customer
ALTER TABLE ... ADD CONSTRAINT ... NOT VALID     -> new rows checked from now on
LEFT JOIN line_items -> customers                 -> find orphans, fix them
ALTER TABLE ... VALIDATE CONSTRAINT               -> existing rows checked on every shard

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, add a foreign key constraint to a large sharded Neki table while the application keeps writing to it.

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 child tables in the same shard group, sharded by the same column, and include that column in the foreign key. Each shard enforces the constraint against its own rows only, so a parent in a different shard group is not found.
- Run the constraint as native DDL through the router: ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID, then ALTER TABLE ... VALIDATE CONSTRAINT. Pass each statement with its own psql -c.
- Before validating, find rows without a parent with a LEFT JOIN on the shard key, and fix them. A failed VALIDATE leaves the constraint unvalidated on every shard.
- Keep a writer running and log every insert result and its latency.
- A foreign key takes both tables off the online DDL copy path.
- 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, jq, and python3:

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, a customers table with 5,000 rows and a line_items table with 2,000,000 rows, both sharded by customer_id. Customer 2 is missing from customers, so its line items have no parent. Create them with these commands:
pscale database create orderdesk --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create orderdesk 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 orderdesk 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 \
  -c "CREATE TABLE customers (customer_id bigint PRIMARY KEY, name text NOT NULL)" \
  -c "CREATE TABLE line_items (customer_id bigint NOT NULL, item_id bigint NOT NULL, qty integer NOT NULL, price_cents integer NOT NULL, PRIMARY KEY (customer_id, item_id))" \
  2>&1 | grep -v NOTICE

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'")

cat > data-topology.json <<JSON
{
  "shard_indexes": { "xxhash_customer_id": { "type": "xxhash", "columns": ["customer_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" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "customers":  { "shard_group": "by_customer" },
    "line_items": { "shard_group": "by_customer" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update orderdesk main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

python3 -c "
for c in range(1, 5001):
    if c != 2: print(f'{c},customer {c}')" > customers.csv
python3 -c "
import sys
w = sys.stdout.write
for i in range(1, 2000001): w(f'{(i*7919)%5000+1},{i},{i%7+1},{(i*13)%50000}\n')" > items.csv
psql -c "\copy customers FROM 'customers.csv' WITH (FORMAT csv)"
psql -c "\copy line_items FROM 'items.csv' WITH (FORMAT csv)"

The two COPY commands print COPY 4999 and COPY 2000000.

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 orderdesk --force --org "$PLANETSCALE_ORG".

1. Start a writer

The writer inserts one line item at a time for customers 3 to 5,000 and logs each result with its latency in milliseconds:

cat > writer.sh <<'EOF'
#!/bin/bash
n=5000000
while [ ! -f stop.flag ]; do
  n=$((n+1))
  t0=$(python3 -c 'import time; print(int(time.time()*1000))')
  out=$(psql -At -c "INSERT INTO line_items VALUES ($((n % 4998 + 3)), $n, 1, 100)" 2>&1 | head -1)
  t1=$(python3 -c 'import time; print(int(time.time()*1000))')
  echo "$(date -u +%T) $n $((t1 - t0)) $out" >> writer.log
  sleep 0.2
done
EOF
chmod +x writer.sh
rm -f stop.flag writer.log
nohup ./writer.sh >/dev/null 2>&1 &
sleep 5
tail -1 writer.log

Real output (time, id, latency in milliseconds, result):

22:09:16 5000004 661 INSERT 0 1

2. Add the constraint as NOT VALID

time psql -At -c "ALTER TABLE line_items ADD CONSTRAINT line_items_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (customer_id) NOT VALID" 2>&1 | grep -v NOTICE
psql -At -c "INSERT INTO line_items VALUES (999999, 1, 1, 1)" 2>&1 | head -2

Real output:

ALTER TABLE

real	0m0.743s
user	0m0.018s
sys	0m0.015s
ERROR:  insert or update on table "line_items" violates foreign key constraint "line_items_customer_fk"
DETAIL:  Key (customer_id)=(999999) is not present in table "customers".

NOT VALID skips the check of existing rows, so the statement is quick. New rows are checked from now on: an insert for a customer that does not exist is rejected.

3. Find rows that would fail validation

Both tables are sharded by customer_id, so the join runs on each shard without moving rows:

psql -At -c "SELECT li.customer_id, count(*) FROM line_items li LEFT JOIN customers c ON c.customer_id = li.customer_id WHERE c.customer_id IS NULL GROUP BY 1"
psql -At -c "ALTER TABLE line_items VALIDATE CONSTRAINT line_items_customer_fk" 2>&1 | grep -v NOTICE | head -2
psql -At -c "SELECT convalidated FROM pg_constraint WHERE conname = 'line_items_customer_fk'"

Real output:

2|400
ERROR:  insert or update on table "line_items" violates foreign key constraint "line_items_customer_fk"
DETAIL:  Key (customer_id)=(2) is not present in table "customers".
f

Customer 2 has line items but no customers row, so validation fails and the constraint stays unvalidated.

4. Fix the orphans and validate

Here the parent row was missing, so add it. Deleting the orphaned line items works too.

psql -At -c "INSERT INTO customers VALUES (2, 'customer 2')"
time psql -At -c "ALTER TABLE line_items VALIDATE CONSTRAINT line_items_customer_fk" 2>&1 | grep -v NOTICE

Real output:

INSERT 0 1
ALTER TABLE

real	0m0.878s
user	0m0.023s
sys	0m0.021s

5. Stop the writer and check it was never blocked

sleep 3; touch stop.flag; sleep 2
echo "inserts=$(wc -l < writer.log) failed=$(grep -vc 'INSERT 0 1' writer.log) max_ms=$(awk '{print $3}' writer.log | sort -n | tail -1)"

Real output:

inserts=      13 failed=0 max_ms=712

No insert failed. The writer's latency includes the round trip from the client to the region, and every insert in this run took between 661 and 712 milliseconds, so neither DDL statement held the writer up noticeably.

Verify the result

for n in sh1 sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  echo -n "$n: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT conname || ' validated=' || convalidated FROM pg_constraint WHERE conname = 'line_items_customer_fk'" | tail -1
done
psql -At -c "INSERT INTO line_items VALUES (999999, 2, 1, 1)" 2>&1 | head -1

Expected: validated=true on all three shards, then ERROR: insert or update on table "line_items" violates foreign key constraint "line_items_customer_fk".

How it works

The router sends native DDL to every shard, and each shard's Postgres adds and validates the constraint against the rows it holds. That only gives the right answer when every child row's parent is on the same shard, which is what sharing a shard group and shard key guarantees. NOT VALID makes the ADD a catalog change, and VALIDATE CONSTRAINT scans existing rows with a lock that, in Postgres, does not block inserts, updates or deletes. When validation fails on one shard, the router reports the error and the constraint stays unvalidated everywhere. Native DDL is described on the Neki schema changes page.

Common issues

The parent table is in a different shard group

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 data shard checks its own empty copy of the parent table. Put the parent in the child's shard group, or enforce the relationship in the application.

Online DDL stops working on both tables

With the foreign key in place, a copy-path online DDL on line_items failed on every data shard with foreign key in table line_items references non-existent table customers, and online_ddl_create on customers was rejected with online DDL on public.customers is not supported: referenced by foreign key line_items_customer_fk. '{"apply_direct": true}' still worked on the child table. Plan schema changes to these tables before adding the key, or drop and re-add the key around them.

Adding the key in one step

In testing on the same 2,000,000 rows, a single ADD CONSTRAINT without NOT VALID took 1.2 seconds and the slowest concurrent insert took 0.78 seconds, close to the two-step numbers. The two-step form keeps the expensive scan under the weaker lock, which matters more as tables grow, and lets you fix orphans in between.

Next steps

  • Delete the database with pscale database delete orderdesk --force --org "$PLANETSCALE_ORG".
  • Read the Neki data topology guide for placing related tables in the same shard group.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "per-shard pg_constraint.convalidated for line_items_customer_fk, then an insert for a missing customer"
  expected_result: "validated=true on every shard, and the insert is rejected with a foreign key violation."