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.
pscale0.344.0 with a service token that can create databases,psql18.6, andjq:
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
customerstable with 5,000 rows, sharded bycustomer_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-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale 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"