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

Reference

Run a Debugging Query Against One Specific Neki Shard

The __neki.shard session setting sends every statement on a Neki connection straight to one shard, bypassing the router's planner. This guide uses it to count rows per shard, find which shard holds a key, read a shard's replica, and guard the session against writes, because a write on a pinned session skips routing and can put a row where the router will never look for it.

What you will build

SET __neki.shard = '<shard uid>'           -> every statement runs on that shard only
SET __neki.target = 'REPLICA'              -> ... on that shard's replica
SET default_transaction_read_only = on     -> writes rejected while debugging
psql -v ON_ERROR_STOP=1                    -> a failed SET stops the script instead of running unpinned

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, run debugging queries against one specific Neki shard by bypassing normal routing.

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.
- Get shard uids from SELECT uid, name FROM __neki.list_shards(). SET __neki.shard takes the uid; a shard name such as sh2 is rejected with "shard ... not found".
- Run psql with -v ON_ERROR_STOP=1 so a failed SET __neki.shard stops the script instead of running the next statements unpinned.
- Set default_transaction_read_only = on on debugging sessions. A write on a pinned session is not routed and can land on a shard that does not own the key.
- Combine with SET __neki.target = 'REPLICA' to read a shard's replica.
- 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, and jq:

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 customers table with 5,000 rows, sharded by customer_id. Create them with these commands:
pscale database create debugdb --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create debugdb 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 debugdb 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)" 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" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update debugdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
psql -At -c "INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 5000) g"

The insert prints INSERT 0 5000.

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

1. Count rows on each shard

psql -At -c "SELECT name || ' ' || uid FROM __neki.list_shards() ORDER BY name"
for n in sh1 sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  echo "$n: $(psql -At -v ON_ERROR_STOP=1 -c "SET __neki.shard = '$u'" -c "SELECT count(*) FROM customers" | tail -1)"
done
echo "router: $(psql -At -c "SELECT count(*) FROM customers")"

Real output (shard ids differ per database):

sh1 sh8eflr723cwus
sh2 sh4zdn5c93bous
sh3 shajk3xs8dn4f9
sh1: 0
sh2: 2455
sh3: 2545
router: 5000

The authoritative shard has the table but no rows; the two data shards add up to the router's total.

2. Find the shard that holds a key

for n in sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  echo "$n: $(psql -At -v ON_ERROR_STOP=1 -c "SET __neki.shard = '$u'" -c "SELECT count(*) FROM customers WHERE customer_id = 42" | tail -1)"
done

Real output:

sh2: 1
sh3: 0

3. Stop on a failed pin

A shard name is not a valid value. Without ON_ERROR_STOP, psql reports the error and runs the next statement unpinned:

psql -At -c "SET __neki.shard = 'sh2'" -c "SELECT count(*) FROM customers" 2>&1 | grep -v CONTEXT
psql -At -v ON_ERROR_STOP=1 -c "SET __neki.shard = 'sh2'" -c "SELECT count(*) FROM customers" 2>&1 | grep -v CONTEXT

Real output:

ERROR:  shard "sh2" not found
5000
ERROR:  shard "sh2" not found

The first command printed the error and then the router's total, 5,000, not a count from sh2. With ON_ERROR_STOP the script stops at the failed SET.

4. Read a shard's replica

psql -At -v ON_ERROR_STOP=1 \
  -c "SET __neki.shard = '$SHARD_2'" -c "SET __neki.target = 'REPLICA'" \
  -c "SELECT count(*), bool_and(pg_is_in_recovery()) FROM customers" | tail -1

Real output:

2455|t

pg_is_in_recovery() is true, so the count came from a replica of sh2.

5. Guard the session against writes

psql -At -v ON_ERROR_STOP=1 \
  -c "SET __neki.shard = '$SHARD_2'" -c "SET default_transaction_read_only = on" \
  -c "INSERT INTO customers VALUES (900001, 'debug')" 2>&1 | grep ERROR

Real output:

ERROR:  cannot execute INSERT in a read-only transaction

Here is what the guard prevents. Find a key that belongs on sh3, then insert it on a session pinned to sh2:

K=$(for c in $(seq 900002 900100); do
  psql -At -c "INSERT INTO customers VALUES ($c, 'probe')" > /dev/null
  on3=$(psql -At -c "SET __neki.shard = '$SHARD_3'" -c "SELECT count(*) FROM customers WHERE customer_id = $c" | tail -1)
  psql -At -c "DELETE FROM customers WHERE customer_id = $c" > /dev/null
  [ "$on3" = "1" ] && echo $c && break
done)
echo "key $K belongs on sh3"
psql -At -c "SET __neki.shard = '$SHARD_2'" -c "INSERT INTO customers VALUES ($K, 'misplaced')" | tail -1
echo "keyed lookup through the router: $(psql -At -c "SELECT count(*) FROM customers WHERE customer_id = $K")"
echo "scan through the router: $(psql -At -c "SELECT count(*) FROM customers WHERE name = 'misplaced'")"
psql -At -c "SET __neki.shard = '$SHARD_2'" -c "DELETE FROM customers WHERE customer_id = $K" | tail -1

Real output:

key 900002 belongs on sh3
INSERT 0 1
keyed lookup through the router: 0
scan through the router: 1
DELETE 1

The pinned insert succeeded on sh2. A lookup by key went to sh3, which owns the key, and found nothing, while a scan of every shard did find the row. The last statement removes it.

Verify the result

total=0
for n in sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  total=$(( total + $(psql -At -v ON_ERROR_STOP=1 -c "SET __neki.shard = '$u'" -c "SELECT count(*) FROM customers" | tail -1) ))
done
echo "$total $(psql -At -c "SELECT count(*) FROM customers")"
psql -At -c "SHOW __neki.shard"

Expected: 5000 5000, then an empty line: the per-shard counts add up to the router's count, and a new connection is not pinned.

How it works

__neki.shard makes the router forward each statement unchanged to the named shard's primary, or to a replica when __neki.target is REPLICA, without planning it against the data topology. Queries see only that shard's rows, which is what debugging needs, and writes land on that shard whatever their key, which is why the session should be read-only. The setting lasts for the session, can also be passed as a startup option (PGOPTIONS='-c __neki.shard=<uid>'), and is cleared with RESET __neki.shard. The router is described on the Neki architecture page.

Common issues

Router EXPLAIN on a pinned session

EXPLAIN (NEKI_PLAN) fails on a pinned session because it describes the router's plan over every shard. Plain EXPLAIN there returns the pinned shard's Postgres plan.

Writes on a pinned session

Besides misplaced rows, a pinned UPDATE or DELETE only touches the pinned shard. Make debugging sessions read-only, and do repairs through the router unless you are deliberately cleaning up a misplaced row, as in step 5.

A pinned session in a connection pool

The setting stays on the connection. Run RESET __neki.shard before returning a pinned connection to a pool, or use a separate connection for debugging.

Next steps

  • Delete the database with pscale database delete debugdb --force --org "$PLANETSCALE_ORG".
  • Read the Neki data topology guide for how keys map to shards.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "sum of per-shard counts pinned with SET __neki.shard, router count, SHOW __neki.shard on a new connection"
  expected_result: "5000 5000 and an empty value"