Reshard a Live Table onto New Neki Shards with the PlanetScale CLI

This guide splits an existing, populated orders table across two new Neki shards while a writer keeps inserting rows, using a Reshard workflow driven from SQL on the Neki router. The workflow copies the rows, streams new writes, and moves traffic to the new shards in a cutover whose switch call took about three seconds.

What you will build

A Neki database whose orders table starts on one shard and ends up distributed by customer_id across two new shards, with no lost writes during the move.

sh1 (orders_source)                     sh2 (customer_id hash 00..7f)
  200,000 rows + live inserts  ----->   sh3 (customer_id hash 80..ff)
        copy -> stream -> differ -> switch traffic -> complete

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, reshard a populated table on a Neki database onto two new shards while writes continue.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2. Neki has no single-node configuration.
- Add two shards with: pscale branch shard create <db> main --config-profile default --count 2. The CLI reports shards as ready before the router lists them, so poll SELECT count(*) FROM __neki.list_shards() until it returns 3.
- Bulk-load with COPY, not INSERT ... SELECT. The statement timeout is 30 seconds. When COPY supplies explicit values for an identity column, advance the sequence afterward with setval.
- A topology update is a complete replacement. Bind the table to a named source group on the existing shard first, with the shard index declared, then call __neki.reshard_create with a target group made only of the two new shards.
- Reshard is driven by metafunctions on a SQL connection to the router: reshard_create, workflow_start, differ_create, differ_report, workflow_switch_traffic, workflow_complete. There is no CLI workflow for it.
- Run a continuous writer during the move and log every statement result.
- A workflow that has been completed no longer exists. Any source cleanup option must be passed on the single workflow_complete call.
- Verify with counts: the router total must equal the baseline plus the rows from successful writer statements, and the two target shards must sum to that total.
- 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 (versions 18.6 and 16.15 both produced the documented output), jq, 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 a populated table. Create everything this guide needs with these commands:

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

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

The orders table, loaded with COPY. The ids start at 1,000,001 and the sequence is advanced past them:

python3 - > orders.csv <<'PY'
import random
random.seed(7)
for i in range(1000001, 1200001):
    print(f"{i},{random.randint(1,10000)},{random.randint(100,50100)},2026-10-01 12:00:00+00")
PY

psql -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  amount_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
\copy orders (order_id, customer_id, amount_cents, created_at) FROM 'orders.csv' WITH (FORMAT csv)
SELECT setval('orders_order_id_seq', 1200000);
ANALYZE orders;
SELECT count(*) FROM orders;
SQL

The last query returns 200000. The INSERT statements in this guide then continue from id 1,200,001.

Note: Output samples are trimmed to the relevant fields. 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 shop --force --org "$PLANETSCALE_ORG".

1. Capture the shard ids

psql -At -c "SELECT uid, name FROM __neki.list_shards() ORDER BY name;"

Real output (ids differ per database):

shrohq7ej1izad|sh1
shdloxbb28euy9|sh2
shvpqst10dwnsi|sh3

sh1 is the authoritative shard that already holds orders. sh2 and sh3 are the destinations. Store them for the next steps:

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

2. Declare the source group

Creating shards does not move rows. Save the current topology, then replace it with one that binds orders to a named source group on sh1 and declares the shard index the target will use.

pscale branch data-topology get shop main --org "$PLANETSCALE_ORG" --format json | jq '.data_topology' > topology-before.json

cat > topology-source.json <<JSON
{
  "shard_indexes": {
    "xxhash_customer_id": { "type": "xxhash", "columns": ["customer_id"] }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "orders_source", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] }
  ],
  "databases": {
    "postgres": { "schemas": { "public": { "tables": { "orders": { "shard_group": "orders_source" } } } } }
  },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON

pscale branch data-topology update shop main --org "$PLANETSCALE_ORG" --format json < topology-source.json > /dev/null
sleep 10
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT count(*) FROM orders WHERE customer_id = 5;"

Real output:

Route [AnyShard]
  Query: SELECT count(*) FROM public.orders WHERE customer_id = $1
  ShardGroup: orders_source

ShardGroup: orders_source shows the table now resolves to the source group.

3. Create the Reshard workflow

The workflow is created in a stopped state so its streams can be inspected before any data moves. The target group uses only the two new shards and splits the hash keyspace at 80.

psql -At <<SQL
SELECT * FROM __neki.reshard_create(
  'reshard_orders', 'postgres', 'orders_source',
  '{"default_shard_index":"xxhash_customer_id","key_ranges":[{"shard_uid":"$SHARD_2","end":"80"},{"shard_uid":"$SHARD_3","start":"80"}]}',
  'orders_by_customer',
  '{"create_stopped": true}');
SQL
psql -x -c "SELECT * FROM __neki.workflow_status('reshard_orders');"

Real output, trimmed:

reshard_orders

workflow                   | reshard_orders
states_by_target_shard_uid | {"shdloxbb28euy9": {"state_by_stream_id": {"...": {"phase": "not_started", "status": "idle", "source_shard_uid": "shrohq7ej1izad"}}}, "shvpqst10dwnsi": {...}}
traffic_state              | not_switched

Each target shard has one stream reading from sh1, in the not_started phase.

4. Start the workflow with a writer running

Save this writer as writer.sh. It inserts ten rows per statement and logs one line per result, so failures are visible.

cat > writer.sh <<'EOF'
#!/bin/bash
while [ ! -f stop.flag ]; do
  out=$(psql -At -c "INSERT INTO orders (customer_id, amount_cents) SELECT (random()*9999)::bigint + 1, 4242 FROM generate_series(1,10);" 2>&1 | tail -1)
  echo "$(date -u +%T) $out" >> writer.log
  sleep 0.3
done
EOF
chmod +x writer.sh
rm -f stop.flag writer.log
psql -At -c "SELECT count(*) FROM orders;" > count-before-writer.txt
cat count-before-writer.txt

Real output:

Start the workflow and the writer, then check the phase after a short wait:

psql -At -c "SELECT * FROM __neki.workflow_start('reshard_orders');"
nohup ./writer.sh > /dev/null 2>&1 &
sleep 20
psql -At -c "SELECT workflow, status, traffic_state, phase, streams_streaming, streams_copying, streams_error FROM __neki.list_workflows();"

Real output:

reshard_orders|running|not_switched|streaming|2|0|0

Both streams are in the streaming phase: the initial copy is finished and new source writes are being applied to the targets. Reads and writes still go to sh1.

5. Compare source and target before cutover

A differ compares every row on the source with the target. Cutover does not require it, so run it deliberately.

psql -At -c "SELECT __neki.differ_create('reshard_orders','pre_cutover');"
sleep 15
psql -x -c "SELECT * FROM __neki.differ_status('reshard_orders','pre_cutover');"
psql -x -c "SELECT * FROM __neki.differ_report('reshard_orders','pre_cutover');"

Real output, trimmed to the report fields:

status       | completed
shard_errors | {}

report | {"table_results":[{"table_name":"orders","schema_name":"public","rows_compared":200240,"matching_rows":200240,"mismatched_rows":0,"extra_rows_source":0,"extra_rows_target":0,"completed":true,...}],"total_rows_compared":200240,"total_mismatched_rows":0,"mismatch":false,"complete":true}

complete is true and mismatch is false. The 240 rows beyond the original 200,000 are writer inserts made before the differ read the tables. Confirm the streams are still streaming again after the differ before continuing:

psql -At -c "SELECT workflow, status, phase, streams_streaming, streams_error FROM __neki.list_workflows();"

reshard_orders|running|streaming|2|0

6. Switch traffic and complete

date -u +%T.%N | cut -c1-12
psql -x -c "SELECT * FROM __neki.workflow_switch_traffic('reshard_orders');"
date -u +%T.%N | cut -c1-12
psql -At -c "SELECT workflow, status, traffic_state FROM __neki.list_workflows();"

Real output. The two timestamps bracket the switch call, which took about 3.3 seconds:

21:19:23.710
workflow | reshard_orders
message  | Reads and Writes switched for workflow reshard_orders

21:19:26.983
reshard_orders|stopped|reads_and_writes_switched

Stop the writer, then complete the workflow:

sleep 5; touch stop.flag; sleep 2
psql -At -c "SELECT * FROM __neki.workflow_complete('reshard_orders');"

The call returns no row. After it, list_workflows() shows no workflow.

Verify the result

Compare the router total against the baseline plus the rows from every successful writer statement, and compare the sum of the two target shards against the same number:

OKS=$(grep -c "INSERT 0 10" writer.log); OTHER=$(grep -vc "INSERT 0 10" writer.log)
echo "writer: $OKS successful statements, $OTHER other lines"
echo "expected total = $(( $(cat count-before-writer.txt) + OKS*10 ))"
psql -At -c "SELECT count(*) AS total FROM orders;"
for s in "$SHARD_1" "$SHARD_2" "$SHARD_3"; do
  echo -n "$s direct count: "
  psql -At -c "SET __neki.shard = '$s';" -c "SELECT count(*) FROM orders;" | tail -1
done
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT count(*) FROM orders WHERE customer_id = 5;"
awk '{split($1,t,":"); s=t[1]*3600+t[2]*60+t[3]; if (NR>1 && s-prev>m) {m=s-prev; at=$1}; prev=s} END{print "largest gap between consecutive writer statements:", m, "s, ending", at}' writer.log

Real output from the run (49 successful writer statements, 0 other lines):

writer: 49 successful statements, 0 other lines
expected total = 200490
200490
shrohq7ej1izad direct count: 200420
shdloxbb28euy9 direct count: 100225
shvpqst10dwnsi direct count: 100265
Route [EqualUnique]
  Query: SELECT count(*) FROM public.orders WHERE customer_id = $1
  ShardGroup: orders_by_customer
  Values: $1
largest gap between consecutive writer statements: 4 s, ending 21:19:27

Expected: the router total equals the expected total, the two target shards sum to it (100,225 + 100,265 = 200,490 in the run), OTHER is 0, and the keyed query shows Route [EqualUnique] on orders_by_customer. The counts depend on how many writer statements ran. The writer log has one-second resolution, so the 4 second gap spans the 3.3 second switch call. Direct shard counts use the __neki.shard setting, which sends statements to one shard and bypasses the topology.

How it works

Reshard creates one stream from the source shard to each target shard. Each stream copies existing rows in primary-key order, then applies new source changes until it reports streaming. At cutover the router buffers affected queries, stops new source writes to the table, waits for accepted changes to reach the targets, synchronizes identity sequences, and updates the topology so reads and writes use the target group. Source rows are not removed by default. The data migration reference covers staged read and write switches, cancel, and source cleanup in more detail.

Common issues

The old source shard keeps its rows

After completion sh1 still returned 200,420 rows when queried directly, while the router and the two target shards held the current data. The source rows are not served by the router after cutover. The Reshard documentation describes a drop_source_data option for workflow_complete. A second call to workflow_complete('reshard_orders', '{"drop_source_data": true}') after completion returned ERROR: workflow "reshard_orders" not found, so pass the option on the single completing call. That option was not run in this guide.

Statement timeout on bulk inserts

SHOW statement_timeout returns 30s. An INSERT ... SELECT of 200,000 generated rows into an unsharded table was cancelled after about 30 seconds, with either canceling statement due to user request or canceling statement due to statement timeout depending on the run, and 20,000-row inserts took 12 to 21 seconds. Load large tables with COPY, as in the prerequisites, and then advance the sequence. After inserting rows with explicit ids, SELECT setval('orders_order_id_seq', <max id>) returned the value, and the next INSERT received the following id.

Identity values are not contiguous

In an earlier load of 60,000 rows with INSERT ... SELECT into a table of this shape, the generated ids ran from 46,929 to 107,000, because routers reserve ranges of sequence values. Use explicit ids well above any id the sequence may already have issued, and run setval afterward, as the prerequisites do.

Roles without the operator privileges

A role created with --inherited-roles neki_viewer,pg_read_all_data could run SELECT count(*) FROM __neki.list_workflows() and received ERROR: permission denied for function workflow_start when calling __neki.workflow_start('does_not_exist'). The postgres admin role used in this guide reached the function and returned workflow "does_not_exist" not found. Use a role that inherits postgres or neki_operator for workflow changes.

`data-topology ls` shows the table as unresolved

pscale branch data-topology ls shop main printed orders → <unresolved> before and after the cutover, while EXPLAIN (NEKI_PLAN) showed the correct ShardGroup. Use EXPLAIN to confirm placement.

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

In one run the router listed all three shards, and the first CREATE TABLE then failed:

ERROR:  no healthy sidecars available for shard shaenzcm88g3e0 with type SIDECAR_TYPE_PRIMARY

About a minute later a direct SELECT 1 against each shard succeeded and DDL worked. The prerequisites wait for that direct check on every shard before creating tables, so the error does not occur there.

The router lists new shards later than the CLI

pscale branch shard list reported all three shards as ready: true while __neki.list_shards() still returned fewer rows. The router showed all three within about 20 seconds in this run, so poll the router before creating the workflow.

Next steps

Keep related transactions on one shard after resharding: see "Keep Ledger Transactions on One Neki Shard with the PlanetScale CLI".
Add a column to the resharded table without blocking writes: see "Run an Online Schema Change Across Neki Shards with the PlanetScale CLI".
To reshard a table of your own, read the Neki data migration guide and the data topology reference.

verification:
  status: verified
  tested_at: "2026-10-12"
  product_version: "pscale 0.344.0; Neki PostgreSQL 18.6 (Platform Preview)"
  command: "compare SELECT count(*) FROM orders with the baseline plus 10 rows per successful writer statement, and with the sum of the two target shards queried through __neki.shard"
  expected_result: "The router total equals the expected total, the two target shards sum to it, the writer log has no failed statements, and a keyed query routes with Route [EqualUnique] to orders_by_customer. Absolute counts vary with the number of writer statements that ran."