This guide shards a payments ledger by account_id on Neki and uses the __neki.tx_mode and __neki.fanout session settings so that a transaction touching a second shard, or a query that scatters across every shard, fails instead of running. A cross-account transfer is then written as two single-shard transactions linked by a transfer_id.
What you will build
A two-table ledger (accounts and ledger_entries) distributed across two shards by a hash of account_id, with checks that show which statements stay on one shard.
account_id --xxhash--> key range --> shard
00..7f -> sh2 accounts + ledger_entries rows for an account share a shard
80..ff -> sh3 tx_mode = 'single' : a second shard in a transaction is an error
fanout = 'single' : a scatter query is an error
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, build a sharded ledger on a Neki database and show which statements are confined to one shard.
Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until SELECT count(*) FROM __neki.list_shards() returns 3. The CLI reports shards ready before the router lists them.
- Create accounts and ledger_entries, then apply a topology that binds both tables to one shard group on the two new shards, using an xxhash shard index on account_id. A topology update is a complete replacement, and the authoritative shard group stays on the original shard.
- Apply the topology before inserting rows. The default transaction mode is multi and the default fanout is scatter.
- Use SET __neki.tx_mode = 'single' before BEGIN. A transaction that reaches a second shard fails with an error and the whole transaction rolls back. Use SET __neki.fanout = 'single' to reject scatter queries.
- Find the shard that owns an account by querying each shard with SET __neki.shard. Choose two accounts on different shards and two on the same shard.
- Do not assert atomicity of a multi-shard commit in the default mode. Only a statement error inside the transaction was tested.
- 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,psql(versions 18.6 and 16.15 both produced the documented output), 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, the two ledger tables, and the topology. Create all of it with these commands. The topology file is the complete replacement document; the shard ids come from the router.
pscale database create ledger --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create ledger 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 ledger 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 <<'SQL'
CREATE TABLE accounts (
account_id bigint PRIMARY KEY,
owner_ref text NOT NULL,
balance_cents bigint NOT NULL DEFAULT 0 CHECK (balance_cents >= 0)
);
CREATE TABLE ledger_entries (
entry_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_id bigint NOT NULL,
amount_cents bigint NOT NULL,
memo text,
transfer_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ledger_entries_account_idx ON ledger_entries (account_id, created_at);
SQL
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_account_id": { "type": "xxhash", "columns": ["account_id"] }
},
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{
"uid": "ledger_by_account",
"default_shard_index": "xxhash_account_id",
"key_ranges": [
{ "shard_uid": "$SHARD_2", "end": "80" },
{ "shard_uid": "$SHARD_3", "start": "80" }
]
}
],
"databases": {
"postgres": {
"schemas": {
"public": {
"tables": {
"accounts": { "shard_group": "ledger_by_account" },
"ledger_entries": { "shard_group": "ledger_by_account" }
}
}
}
}
},
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update ledger main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
Note: Output samples are trimmed to the relevant lines, and shard ids differ per database. 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 ledger --force --org "$PLANETSCALE_ORG".
1. Seed accounts and confirm routing
psql -At <<'SQL'
INSERT INTO accounts (account_id, owner_ref, balance_cents) SELECT g, 'acct-' || g, 100000 FROM generate_series(1, 20) g;
SELECT count(*) FROM accounts;
EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT balance_cents FROM accounts WHERE account_id = 7;
SQL
Real output:
INSERT 0 20
20
Route [EqualUnique]
Query: SELECT balance_cents FROM public.accounts WHERE account_id = $1
ShardGroup: ledger_by_account
Values: $1
Route [EqualUnique] means a statement with account_id = <value> goes to one shard.
2. Find which shard owns which account
__neki.shard sends statements to one shard and bypasses the topology.
for a in 1 2 3 4 5 6; do
for u in "$SHARD_2" "$SHARD_3"; do
c=$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM accounts WHERE account_id=$a;" | tail -1)
[ "$c" = "1" ] && echo "account $a -> $u"
done
done
Real output (the shard ids differ per database):
account 1 -> shyy1bv1tlrz57
account 2 -> shmdk8xxpm73cy
account 3 -> shyy1bv1tlrz57
account 4 -> shyy1bv1tlrz57
account 5 -> shyy1bv1tlrz57
account 6 -> shmdk8xxpm73cy
Accounts 1 and 3 share a shard. Accounts 1 and 2 are on different shards. The placement comes from the hash of account_id and the key ranges, so it is the same in any database created with this topology; only the shard ids change.
3. Post transfers with tx_mode = 'single'
Define a helper that prints the three balances, then transfer 500 from account 1 to account 3. Both accounts are on the same shard.
bal() { psql -At -c "SELECT account_id||':'||balance_cents FROM accounts WHERE account_id IN (1,2,3) ORDER BY 1;" | tr '\n' ' '; echo; }
bal
psql -At <<'SQL'
SET __neki.tx_mode = 'single';
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 500 WHERE account_id = 1;
INSERT INTO ledger_entries (account_id, amount_cents, memo) VALUES (1, -500, 'transfer to 3');
UPDATE accounts SET balance_cents = balance_cents + 500 WHERE account_id = 3;
INSERT INTO ledger_entries (account_id, amount_cents, memo) VALUES (3, 500, 'transfer from 1');
COMMIT;
SQL
bal
Real output:
1:100000 2:100000 3:100000
SET
BEGIN
UPDATE 1
INSERT 0 1
UPDATE 1
INSERT 0 1
COMMIT
1:99500 2:100000 3:100500
Now try the same transfer from account 1 to account 2, which is on a different shard:
psql -At <<'SQL'
SET __neki.tx_mode = 'single';
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 500 WHERE account_id = 1;
INSERT INTO ledger_entries (account_id, amount_cents, memo) VALUES (1, -500, 'transfer to 2');
UPDATE accounts SET balance_cents = balance_cents + 500 WHERE account_id = 2;
INSERT INTO ledger_entries (account_id, amount_cents, memo) VALUES (2, 500, 'transfer from 1');
COMMIT;
SQL
bal
Real output, trimmed to the error lines:
UPDATE 1
INSERT 0 1
ERROR: __neki.tx_mode = 'single' forbids a transaction from touching more than one shard
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
1:99500 2:100000 3:100500
The statement that reached the second shard failed, the later statements were rejected, and the transaction rolled back. Account 1 still shows 99500, so the debit that ran before the error was not kept.
4. Compare with the default mode
The defaults are multi and scatter:
psql -At -c "SHOW __neki.tx_mode;" -c "SHOW __neki.fanout;"
multi
scatter
In the default mode the same cross-shard transfer commits:
psql -At <<'SQL'
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 500 WHERE account_id = 1;
UPDATE accounts SET balance_cents = balance_cents + 500 WHERE account_id = 2;
COMMIT;
SQL
bal
BEGIN
UPDATE 1
UPDATE 1
COMMIT
1:99000 2:100500 3:100500
A statement error inside a cross-shard transaction in the default mode still rolls back both sides. The second update here violates the CHECK (balance_cents >= 0) constraint:
psql -At <<'SQL'
BEGIN;
UPDATE accounts SET balance_cents = balance_cents + 10 WHERE account_id = 1;
UPDATE accounts SET balance_cents = balance_cents - 999999999 WHERE account_id = 2;
COMMIT;
SQL
bal
BEGIN
UPDATE 1
ERROR: new row for relation "accounts" violates check constraint "accounts_balance_cents_check"
ROLLBACK
1:99000 2:100500 3:100500
Account 1 stays at 99000, so the + 10 was rolled back. This test covers an error raised by a statement. It does not cover a failure during commit on one shard after another shard has committed, which PlanetScale documents as non-atomic in the default mode. That case was not reproduced here.
5. Reject scatter queries with fanout = 'single'
psql -At <<'SQL'
SET __neki.fanout = 'single';
SELECT balance_cents FROM accounts WHERE account_id = 1;
SELECT sum(balance_cents) FROM accounts;
SELECT count(*) FROM ledger_entries WHERE created_at > now() - interval '1 day';
SELECT sum(amount_cents) FROM ledger_entries WHERE account_id = 1;
SQL
Real output:
SET
99000
ERROR: statement's fan-out (scatter) exceeds __neki.fanout (single)
ERROR: statement's fan-out (scatter) exceeds __neki.fanout (single)
-500
The two keyed statements ran. The two statements without an account_id predicate were rejected.
6. Write a cross-account transfer as two single-shard transactions
Each leg is a separate transaction on the shard that owns that account, and both carry the same transfer_id.
psql -At <<'SQL'
SET __neki.tx_mode = 'single';
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 700 WHERE account_id = 1;
INSERT INTO ledger_entries (account_id, amount_cents, memo, transfer_id) VALUES (1, -700, 'debit', '00000000-0000-0000-0000-0000000000a1');
COMMIT;
BEGIN;
UPDATE accounts SET balance_cents = balance_cents + 700 WHERE account_id = 2;
INSERT INTO ledger_entries (account_id, amount_cents, memo, transfer_id) VALUES (2, 700, 'credit', '00000000-0000-0000-0000-0000000000a1');
COMMIT;
SQL
psql -At -c "SELECT transfer_id, count(*) AS legs, sum(amount_cents) AS net FROM ledger_entries WHERE transfer_id IS NOT NULL GROUP BY transfer_id;"
Real output:
00000000-0000-0000-0000-0000000000a1|2|0
The reconciliation query has no account_id predicate, so it runs with the default scatter fanout:
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT transfer_id, count(*), sum(amount_cents) FROM ledger_entries WHERE transfer_id IS NOT NULL GROUP BY transfer_id;" | head -4
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT * FROM ledger_entries WHERE account_id = 1 ORDER BY created_at DESC LIMIT 20;" | head -3
Aggregate [Hash]
-> Route [Scatter]
Query: SELECT ledger_entries.transfer_id, count(*), sum(amount_cents) FROM public.ledger_entries WHERE transfer_id IS NOT NULL GROUP BY ledger_entries.transfer_id
ShardGroup: ledger_by_account
Route [EqualUnique]
Query: SELECT ledger_entries.entry_id, ledger_entries.account_id, ledger_entries.amount_cents, ledger_entries.memo, ledger_entries.transfer_id, ledger_entries.created_at FROM public.ledger_entries WHERE account_id = $1 ORDER BY ledger_entries.created_at DESC LIMIT 20
ShardGroup: ledger_by_account
Verify the result
Confirm that a cross-shard transaction is rejected in single mode and leaves balances unchanged:
before=$(psql -At -c "SELECT string_agg(account_id||':'||balance_cents, ' ' ORDER BY account_id) FROM accounts WHERE account_id IN (1,2);")
psql -At <<'SQL' 2>&1 | grep -E "forbids|ROLLBACK"
SET __neki.tx_mode = 'single';
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 1 WHERE account_id = 1;
UPDATE accounts SET balance_cents = balance_cents + 1 WHERE account_id = 2;
COMMIT;
SQL
after=$(psql -At -c "SELECT string_agg(account_id||':'||balance_cents, ' ' ORDER BY account_id) FROM accounts WHERE account_id IN (1,2);")
[ "$before" = "$after" ] && echo "balances unchanged: $after"
Real output:
ERROR: __neki.tx_mode = 'single' forbids a transaction from touching more than one shard
ROLLBACK
balances unchanged: 1:98300 2:101200
Expected: the output contains the forbids a transaction from touching more than one shard error, ROLLBACK, and a balances unchanged: line with the same values that were read before the attempt. Account balances depend on the earlier steps, so the numbers vary with what has run before.
How it works
The router hashes account_id with xxhash and maps the result to a key range, so every row with the same account_id in both tables lands on the same shard. __neki.tx_mode = 'single' makes the router reject any statement that would add a second shard to the open transaction. __neki.fanout = 'single' rejects any statement whose plan is not a single-shard route. Both settings apply to the session. PlanetScale's documentation says to set tx_mode before starting the transaction, and every example here does. With the default settings, statements may reach several shards, and PlanetScale documents that each shard commits separately. Query planning in the Neki documentation covers routes, fanout levels and cross-shard transactions in full.
Common issues
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 shard list is shorter than the CLI says
pscale branch shard list reported two new shards as ready: false and later ready: true, and the router needed about 20 more seconds to list all three in __neki.list_shards(). Poll the router before applying a topology that names the new shards.
data-topology ls shows tables as unresolved
pscale branch data-topology ls ledger main printed accounts → <unresolved> and ledger_entries → <unresolved> after the topology was applied, while EXPLAIN (NEKI_PLAN) showed ShardGroup: ledger_by_account. Use EXPLAIN to confirm placement.
A failover test was not run
The CLI offers pscale branch maintenance run, which starts an image upgrade, and no command that forces a primary failover on Neki. This guide does not make claims about failover behavior.
Next steps
- Move an existing table onto shards with a live writer: see "Reshard a Live Table onto New Neki Shards with the PlanetScale CLI".
- Pin one large customer to its own shard: see "Isolate a Large Tenant on Its Own Neki Shard with the PlanetScale CLI".
- To design your own ledger, see Neki query planning and Neki best practices.
verification:
status: verified
tested_at: "2026-10-12"
product_version: "pscale 0.344.0; Neki PostgreSQL 18.6 (Platform Preview)"
command: "SET __neki.tx_mode = 'single'; BEGIN; UPDATE account 1; UPDATE account 2; COMMIT; then compare balances before and after"
expected_result: "The transaction fails with 'forbids a transaction from touching more than one shard', rolls back, and balances are unchanged. Absolute balances vary with earlier steps."