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

Reference

Delete a Shard Safely After Moving Its Data on Neki with the PlanetScale CLI

This guide consolidates a Neki shard group from two shards onto one new shard with a Reshard workflow, then deletes the two old shards with pscale branch shard delete. The CLI refuses to delete a shard that the data topology still references, but it does not check whether the shard still holds rows, so the guide checks every table on each shard before deleting it.

What you will build

by_customer on sh2, sh3  --reshard-->  by_customer_v2 on sh4
workflow_complete(drop_source_data)
remove by_customer from the data topology
check every table on sh2 and sh3 holds 0 rows
pscale branch shard delete sh2, sh3  ->  sh1 + sh4 remain

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, move a Neki shard group's data off two shards and delete those shards safely.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, shard the tables, load them with COPY, then add one more shard as the target. Wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Move the data with __neki.reshard_create(workflow, 'postgres', source_group, target_group_json, target_group_uid, '{}'), wait for phase streaming, then __neki.workflow_switch_traffic and __neki.workflow_complete(workflow, '{"drop_source_data": true}').
- After completion the old shard group stays in the data topology. Remove it with pscale branch data-topology update; pscale branch shard delete refuses while the shard is referenced.
- pscale branch shard delete does not check for data. Before deleting, count every table on the shard with SET __neki.shard and require 0 rows.
- pscale branch shard delete takes the CLI shard id. pscale branch shard list --format json shows it as id; the router's shard uid is its name.
- Wait until __neki.list_shards() no longer lists the deleted 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.

  • 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, and customers (5,000 rows) and orders (200,000 rows) tables sharded by customer_id across sh2 and sh3. Create them with these commands:
pscale database create fleetops --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create fleetops 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 fleetops 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 orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, total_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_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" },
    "orders":    { "shard_group": "by_customer" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update fleetops main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

python3 -c "
for c in range(1, 5001): print(f'{c},customer {c}')" > customers.csv
python3 -c "
for i in range(1, 200001): print(f'{(i*7919)%5000+1},{i},{(i*31)%100000}')" > orders.csv
psql -c "\copy customers FROM 'customers.csv' WITH (FORMAT csv)"
psql -c "\copy orders (customer_id, order_id, total_cents) FROM 'orders.csv' WITH (FORMAT csv)"

The COPY commands print COPY 5000 and COPY 200000.

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, so removing two shards saves $60 per month at list price. Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete fleetops --force --org "$PLANETSCALE_ORG".

1. Write a shard check

shard-rows.sh prints, for one shard uid, the row count of every table in the public schema of the postgres database:

cat > shard-rows.sh <<'EOF'
#!/bin/bash
for t in $(psql -At -c "SET __neki.shard='$1';" -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY 1" | tail -n +2); do
  echo -n "$t=$(psql -At -c "SET __neki.shard='$1';" -c "SELECT count(*) FROM public.$t" | tail -1) "
done
echo
EOF
chmod +x shard-rows.sh
for n in sh2 sh3; do echo -n "$n: "; ./shard-rows.sh $(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'"); done

Real output:

sh2: customers=2455 orders=98200 
sh3: customers=2545 orders=101800 

2. Add the target shard and move the data

pscale branch shard create fleetops main --config-profile default --count 1 --org "$PLANETSCALE_ORG" > /dev/null
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "4" ]; 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
until [ "$(pscale branch config-profile list fleetops main --org "$PLANETSCALE_ORG" --format json | jq -r '.[0].state')" = "ready" ]; do sleep 15; done
SHARD_4=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh4'")

psql -At <<SQL
SELECT * FROM __neki.reshard_create('consolidate', 'postgres', 'by_customer',
  '{"default_shard_index":"xxhash_customer_id","key_ranges":[{"shard_uid":"$SHARD_4"}]}',
  'by_customer_v2', '{}');
SQL
until [ "$(psql -At -c "SELECT phase FROM __neki.list_workflows() WHERE workflow = 'consolidate'")" = "streaming" ]; do sleep 10; done
psql -At -c "SELECT message FROM __neki.workflow_switch_traffic('consolidate')"
psql -At -c "SELECT * FROM __neki.workflow_complete('consolidate', '{\"drop_source_data\": true}')"
psql -At -c "SELECT count(*) FROM orders" -c "SELECT count(*) FROM customers"

Real output (workflow_complete prints an empty line):

consolidate
Reads and Writes switched for workflow consolidate

200000
5000

All rows are now served from sh4. The target group's single key range has no start or end, so it covers the whole hash space.

3. Try to delete a shard that is still referenced

pscale branch shard list fleetops main --org "$PLANETSCALE_ORG" --format json | jq -r '.[] | "\(.display_name) id=\(.id) uid=\(.name)"'
SHARD_2_ID=$(pscale branch shard list fleetops main --org "$PLANETSCALE_ORG" --format json | jq -r --arg u "$SHARD_2" '.[] | select(.name == $u) | .id')
SHARD_3_ID=$(pscale branch shard list fleetops main --org "$PLANETSCALE_ORG" --format json | jq -r --arg u "$SHARD_3" '.[] | select(.name == $u) | .id')
pscale branch shard delete fleetops main "$SHARD_2_ID" --force --org "$PLANETSCALE_ORG" 2>&1 | tail -1

Real output:

sh1 id=k7yufbqa6oal uid=shr9zrpb4hga9j
sh2 id=xx9aoht1fwsf uid=shdbvzegs8hucg
sh3 id=rpkg784wpry6 uid=shfcgnqd1jgijs
sh4 id=88jtic5s29zh uid=shnq5kzkqwz8l6
Error: Cannot remove a shard that is still referenced in the data topology

The old by_customer group still lists sh2 and sh3, so the delete is refused.

4. Remove the old group and check the shards are empty

pscale branch data-topology get fleetops main --org "$PLANETSCALE_ORG" --format json \
  | jq '.data_topology | .shard_groups |= map(select(.uid != "by_customer"))' > topology-final.json
jq -c '[.shard_groups[].uid]' topology-final.json
pscale branch data-topology update fleetops main --org "$PLANETSCALE_ORG" --format json < topology-final.json | jq -r '.status // "applied"'
sleep 10
for n in sh2 sh3; do echo -n "$n: "; ./shard-rows.sh $(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'"); done

Real output:

["shr9zrpb4hga9j","by_customer_v2"]
applied
sh2: customers=0 orders=0 
sh3: customers=0 orders=0 

Every table on both shards holds 0 rows. Only then delete them.

5. Delete the shards

pscale branch shard delete fleetops main "$SHARD_2_ID" --force --org "$PLANETSCALE_ORG" 2>&1 | tail -1
pscale branch shard delete fleetops main "$SHARD_3_ID" --force --org "$PLANETSCALE_ORG" 2>&1 | tail -1
t0=$(date +%s)
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "2" ]; do sleep 10; done
echo "router lists 2 shards after $(( $(date +%s) - t0 )) s"
psql -At -c "SELECT name || ' ' || uid FROM __neki.list_shards() ORDER BY name"

Real output:

Shard xx9aoht1fwsf was successfully deleted from fleetops/main.
Shard rpkg784wpry6 was successfully deleted from fleetops/main.
router lists 2 shards after 54 s
sh1 shr9zrpb4hga9j
sh4 shnq5kzkqwz8l6

Verify the result

psql -At -c "SELECT count(*) FROM orders" -c "SELECT count(*) FROM customers"
echo -n "sh4: "; ./shard-rows.sh "$SHARD_4"
pscale branch shard list fleetops main --org "$PLANETSCALE_ORG" --format json | jq -r '.[].display_name'

Expected: 200000 and 5000 through the router, the same counts on sh4, and only sh1 and sh4 in the shard list.

How it works

A Reshard workflow copies every table in the source shard group to the target group's shards, streams later changes, and after the traffic switch serves the tables from the target. drop_source_data removes the source copies, but the source group stays in the data topology until you remove it, and that reference is what pscale branch shard delete checks. Deleting a shard removes its nodes and every row on them, whatever the topology says, which is why the per-shard count runs immediately before the delete. Workflows are described on the Neki data migration page.

Common issues

The CLI deletes a shard that still holds rows

In testing, after the old group was removed from the topology, pscale branch shard delete deleted a shard that still held one row that had been inserted directly on it. The CLI checks only topology references. Run the per-shard count every time.

Shard ids in the CLI and in SQL differ

pscale branch shard delete expects the CLI id, such as z9vq8s2m2jqx. The uid that __neki.list_shards() returns appears as name in pscale branch shard list --format json.

The delete returns before the shard is gone

The delete command returned in about a second, while __neki.list_shards() still listed the shard for 30 to 55 seconds afterwards in testing and the configuration profile showed applying. Wait for both before changing the topology again.

Next steps

  • Delete the database with pscale database delete fleetops --force --org "$PLANETSCALE_ORG".
  • Read the Neki data migration guide for the Reshard workflow options.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "router counts of orders and customers, per-table counts on sh4, pscale branch shard list"
  expected_result: "200000 and 5000 through the router and on sh4; only sh1 and sh4 remain."