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

Reference

Erase One User's Data Across Several Shard Groups on Neki

This guide erases one user's personal data for a privacy request on Neki, where the user's rows are in several shard groups. The user's profile and sessions are sharded by user_id, the user's purchases are sharded by order_id, and a consent log is unsharded. The erasure script deletes or anonymizes the rows in each place, records a receipt, and can be run again safely. The guide then checks every shard directly to show that no row for the user is left. It covers the rows in the database. It does not cover backups, exports or logs.

What you will build

users, sessions                 -> by_user,  xxhash(user_id)     delete by user_id        (one shard)
purchases, purchase_items       -> by_order, xxhash(order_id)    anonymize / delete by user_id, then by order_id (every shard)
consent_log, erasure_receipts   -> unsharded (authoritative shard)

erase.sh <user>: purchase_items -> purchases (keep the amounts, remove the user) -> sessions -> consent_log -> users -> receipt

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, erase one user's data across several shard groups on Neki.

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.
- Shard users and sessions by user_id, and purchases and purchase_items by order_id. Keep consent_log and erasure_receipts unsharded. Purchases carry user_id as an ordinary column, so deleting or updating by user_id runs on every shard.
- Write erase.sh <user_id>. Delete the user's purchase_items with WHERE order_id IN (SELECT order_id FROM purchases WHERE user_id = ...), anonymize the purchases (set user_id to 0 and clear ship_to, keeping the amounts), then delete the sessions, the consent log rows and the user, and insert a receipt row with the counts. Each statement is idempotent, so running the script again changes nothing.
- Verify that no row for the user is left by counting on every shard with a session pinned by SET __neki.shard, not only through the router.
- 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. Create it with these commands:
pscale database create erasedb --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create erasedb 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 erasedb 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 erasedb --force --org "$PLANETSCALE_ORG".

1. Create the tables, shard them and load users

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE users (user_id bigint PRIMARY KEY, email text NOT NULL, full_name text NOT NULL)" \
  -c "CREATE TABLE sessions (user_id bigint NOT NULL, session_id bigint NOT NULL, ip text, PRIMARY KEY (user_id, session_id))" \
  -c "CREATE TABLE purchases (order_id bigint PRIMARY KEY, user_id bigint NOT NULL, amount_cents int NOT NULL, ship_to text)" \
  -c "CREATE TABLE purchase_items (order_id bigint NOT NULL, line_no int NOT NULL, sku text NOT NULL, PRIMARY KEY (order_id, line_no))" \
  -c "CREATE TABLE consent_log (event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL, event text NOT NULL)" \
  -c "CREATE TABLE erasure_receipts (receipt_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL, erased_at timestamptz NOT NULL DEFAULT now(), purchase_items int NOT NULL, purchases int NOT NULL, sessions int NOT NULL, consent_events int NOT NULL, users int NOT NULL)" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": {
    "user":  { "type": "xxhash", "columns": ["user_id"] },
    "order": { "type": "xxhash", "columns": ["order_id"] }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_user", "default_shard_index": "user",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
    { "uid": "by_order", "default_shard_index": "order",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "users":          { "shard_group": "by_user" },
    "sessions":       { "shard_group": "by_user" },
    "purchases":      { "shard_group": "by_order" },
    "purchase_items": { "shard_group": "by_order" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update erasedb main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

psql -q -c "INSERT INTO users SELECT u, 'user' || u || '@example.com', 'User ' || u FROM generate_series(1, 100) u" \
        -c "INSERT INTO sessions SELECT u, s, '10.0.0.' || s FROM generate_series(1, 100) u, generate_series(1, 3) s" \
        -c "INSERT INTO purchases SELECT o, 1 + o % 100, 500 + o, 'Street ' || o FROM generate_series(1, 1000) o" \
        -c "INSERT INTO purchase_items SELECT o, l, 'sku' || l FROM generate_series(1, 1000) o, generate_series(1, 2) l" \
        -c "INSERT INTO consent_log (user_id, event) SELECT u, 'accepted' FROM generate_series(1, 100) u"
psql -At -c "SELECT count(*) || ' users, ' || (SELECT count(*) FROM purchases) || ' purchases, ' || (SELECT count(*) FROM purchase_items) || ' items' FROM users"

Real output:

applied
100 users, 1000 purchases, 2000 items

The data is synthetic. User 7 has a profile, 3 sessions, 10 purchases with 20 items, and a consent record.

2. See where one user's rows are

rows_for() {  # rows_for <user_id>: count the user's rows on each shard, with a session pinned to that shard
  for n in sh1 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 'users=' || (SELECT count(*) FROM users WHERE user_id = $1) || ' sessions=' || (SELECT count(*) FROM sessions WHERE user_id = $1) || ' purchases=' || (SELECT count(*) FROM purchases WHERE user_id = $1) || ' consent=' || (SELECT count(*) FROM consent_log WHERE user_id = $1)" | tail -1)"
  done
}
rows_for 7
psql -At -c "SELECT order_id FROM purchases WHERE user_id = 7 ORDER BY order_id" > orders_of_7.txt
echo "orders of user 7: $(paste -sd, - < orders_of_7.txt)"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT order_id FROM purchases WHERE user_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'

Real output:

sh1: users=0 sessions=0 purchases=0 consent=1
sh2: users=0 sessions=0 purchases=6 consent=0
sh3: users=1 sessions=3 purchases=4 consent=0
orders of user 7: 6,106,206,306,406,506,606,706,806,906
Route [Scatter]

The profile and the sessions of user 7 are on one data shard, the purchases are on both data shards, and the consent record is on the first shard, which holds the unsharded tables. A lookup of purchases by user_id is a scatter, because the table is sharded by order_id.

3. Write the erasure script

cat > erase.sh <<'EOF'
#!/bin/bash
# erase.sh <user_id>: erase one user's personal data. Safe to run again.
u=$1
n() { awk '{print $2}'; }   # "DELETE 3" -> 3

items=$(psql -At -c "DELETE FROM purchase_items WHERE order_id IN (SELECT order_id FROM purchases WHERE user_id = $u)" | n)
purch=$(psql -At -c "UPDATE purchases SET user_id = 0, ship_to = NULL WHERE user_id = $u" | n)
sess=$(psql -At -c "DELETE FROM sessions WHERE user_id = $u" | n)
cons=$(psql -At -c "DELETE FROM consent_log WHERE user_id = $u" | n)
usr=$(psql -At -c "DELETE FROM users WHERE user_id = $u" | n)

psql -q -c "INSERT INTO erasure_receipts (user_id, purchase_items, purchases, sessions, consent_events, users) VALUES ($u, $items, $purch, $sess, $cons, $usr)"
echo "erased user $u: items=$items purchases_anonymized=$purch sessions=$sess consent=$cons users=$usr"
EOF
chmod +x erase.sh
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) UPDATE purchases SET user_id = 0, ship_to = NULL WHERE user_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) DELETE FROM sessions WHERE user_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'

Real output:

Route [Scatter]
Route [EqualUnique]

The sessions delete is EqualUnique and the purchases update is a Scatter. The script deletes the items first, while the purchases still say which orders belong to the user, then anonymizes the purchases, so the amounts stay for accounting and nothing points to the user.

4. Erase user 7 and check every shard

./erase.sh 7
rows_for 7
echo "items of the old orders: $(psql -At -c "SELECT count(*) FROM purchase_items WHERE order_id IN ($(paste -sd, - < orders_of_7.txt))")"
echo "anonymized purchases: $(psql -At -c "SELECT count(*) FROM purchases WHERE order_id IN ($(paste -sd, - < orders_of_7.txt)) AND user_id = 0 AND ship_to IS NULL")"
psql -At -c "SELECT count(*) || ' users, ' || (SELECT count(*) FROM purchases) || ' purchases, ' || (SELECT count(*) FROM purchase_items) || ' items' FROM users"

Real output:

erased user 7: items=20 purchases_anonymized=10 sessions=3 consent=1 users=1
sh1: users=0 sessions=0 purchases=0 consent=0
sh2: users=0 sessions=0 purchases=0 consent=0
sh3: users=0 sessions=0 purchases=0 consent=0
items of the old orders: 0
anonymized purchases: 10
99 users, 1000 purchases, 1980 items

Every shard reports zero rows for user 7 in every table, the 20 items are gone, and the 10 purchases remain with no user and no address. The other 99 users are untouched.

5. Run the erasure again

./erase.sh 7
psql -At -c "SELECT user_id || ': ' || count(*) || ' receipts' FROM erasure_receipts GROUP BY user_id"

Real output:

erased user 7: items=0 purchases_anonymized=0 sessions=0 consent=0 users=0
7: 2 receipts

The second run changed nothing, and wrote a second receipt with zero counts. If a run stops halfway, run it again.

Verify the result

rows_for 7
psql -At -c "SELECT count(*) FROM users WHERE user_id = 7"

Expected: every shard shows users=0 sessions=0 purchases=0 consent=0, and the router shows 0 for the user.

How it works

The user's rows are in three places that the router treats differently: users and sessions are sharded by user_id, so one shard holds them and a delete by user_id goes to that shard. purchases and purchase_items are sharded by order_id, so a statement that filters by user_id or by a subquery on it runs on every shard. The unsharded tables are on the authoritative shard. Each statement is one operation that the router routes, and each is safe to repeat, so the script needs no cross-shard transaction and a rerun finishes what an earlier run left. Checking the result with sessions pinned by __neki.shard reads each shard directly, so a row on a shard that the router would not have asked is still found. The Neki data topology guide describes how a shard key is hashed and placed.

Common issues

The user id is not the shard key everywhere

A table sharded by order_id has no user_id routing, so deleting by user_id goes to every shard. For a table you erase from often, store the user's rows under the user's shard key, or keep an index table from user to order ids.

Keep what you must, remove what identifies

The script keeps the purchase amounts and removes user_id and the address. Decide per table what your retention rules require, because the script is only as complete as its list of tables. Add every new table that holds the user's data to the script and to rows_for.

The receipt holds the user id

The receipt row has the user id and the counts. If the id itself is personal data under your rules, store a hash of it.

Backups, replicas and exports

This guide checked the primary shards. Backups, data already exported, and application logs are separate places that your retention policy has to cover.

Next steps

  • Delete the database with pscale database delete erasedb --force --org "$PLANETSCALE_ORG".
  • Read the guide on keeping a multi-tenant audit log sharded by tenant with a retention job for deleting old rows in batches.
  • Read the Neki data topology guide for placing tables in a shard group.

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "rows for the erased user on each shard, and through the router"
  expected_result: "users=0 sessions=0 purchases=0 consent=0 on sh1, sh2 and sh3; 0 through the router"