On a sharded Neki table, how you paginate decides how much work each page costs. This guide compares three patterns on a 5,000-row orders table using the router's own counters: keyset pagination within one customer, which touches one shard; OFFSET pagination across all customers, which makes every shard return every row up to the page; and keyset pagination across all customers, which makes each shard return at most one page.
What you will build
one customer, keyset: WHERE customer_id = 42 AND order_id > :last ORDER BY order_id LIMIT 5
-> 1 shard, 5 rows over the network
all customers, OFFSET: ORDER BY placed_at, customer_id, order_id OFFSET 4000 LIMIT 20
-> each shard returns 4,020 rows
all customers, keyset: WHERE (placed_at, customer_id, order_id) > (:cursor) ORDER BY ... LIMIT 20
-> each shard returns at most 20 rows
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, paginate and sort results on a sharded Neki table without making every shard return every earlier row on every page.
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.
- When a page belongs to one shard key value, filter on it and use keyset pagination on a column of the primary key: one shard per page.
- For pages across all shard key values, sort by a unique key (a time column plus the primary key) and use keyset pagination with a row comparison, WHERE (a, b, c) > (...). The router pushes ORDER BY and LIMIT to each shard and merges the results.
- Avoid OFFSET across shards: the router asks each shard for OFFSET + LIMIT rows.
- Add an index on the sort key so each shard reads only one page.
- Measure with EXPLAIN (NEKI_PLAN, ANALYZE): RemoteCalls and NetworkRows.
- 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 an
orderstable with 5,000 rows for 500 customers, sharded bycustomer_id, with one order per minute inplaced_at. Create them with these commands:
pscale database create feeddb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create feeddb 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 feeddb 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 orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, placed_at timestamptz NOT NULL, PRIMARY KEY (customer_id, order_id))" \
-c "CREATE INDEX orders_placed_at_idx ON orders (placed_at, 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": {
"orders": { "shard_group": "by_customer" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update feeddb main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
psql -At -c "INSERT INTO orders SELECT (g % 500) + 1, g, timestamptz '2026-10-01' + g * interval '1 minute' 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 feeddb --force --org "$PLANETSCALE_ORG".
1. Write a helper that shows the router's counters
cat > cost.sh <<'EOF'
#!/bin/bash
psql -At -c "EXPLAIN (NEKI_PLAN, ANALYZE) $1" \
| grep -E "^ *(-> )?(Limit|Route)|RemoteCalls:|NetworkRows| Query:" \
| sed 's/ (cost=[^)]*)//; s/ (actual[^)]*)//' | head -8
EOF
chmod +x cost.sh
2. Page through one customer's orders
psql -At -c "SELECT order_id FROM orders WHERE customer_id = 42 ORDER BY order_id LIMIT 5" | tr '\n' ' '; echo
psql -At -c "SELECT order_id FROM orders WHERE customer_id = 42 AND order_id > 2041 ORDER BY order_id LIMIT 5" | tr '\n' ' '; echo
./cost.sh "SELECT order_id FROM orders WHERE customer_id = 42 AND order_id > 2041 ORDER BY order_id LIMIT 5"
Real output:
41 541 1041 1541 2041
2541 3041 3541 4041 4541
Route [EqualUnique]
RemoteCalls: 1
CostAtActualRows: Total=1005 RemoteCalls=1 NetworkRows=5
Query: SELECT order_id FROM public.orders WHERE customer_id = $1 AND order_id > $2 ORDER BY 1 LIMIT 5
The second page starts after the last order_id of the first. The filter on customer_id sends each page to the one shard that holds that customer.
3. Page across all customers with OFFSET
./cost.sh "SELECT customer_id, order_id, placed_at FROM orders ORDER BY placed_at, customer_id, order_id OFFSET 4000 LIMIT 20"
Real output:
Limit
CostAtActualRows: Total=7551 RemoteCalls=2 RowsProcessed=5000 NetworkRows=5000 MemoryRows=1002
CostAtActualRows: Total=7551 RemoteCalls=2 RowsProcessed=5000 NetworkRows=5000 MemoryRows=1002
-> Route [Scatter]
RemoteCalls: 2
CostAtActualRows: Total=7000 RemoteCalls=2 NetworkRows=5000
Query: SELECT customer_id, order_id, placed_at FROM public.orders ORDER BY 3, 1, 2 LIMIT 4020
The router cannot know which shard holds rows 4,001 to 4,020 of the merged order, so it asks each shard for its first 4,020 rows and discards most of them. Every row in the table crossed the network to return 20.
4. Page across all customers with a keyset cursor
./cost.sh "SELECT customer_id, order_id, placed_at FROM orders WHERE (placed_at, customer_id, order_id) > (timestamptz '2026-10-03 18:40:00+00', 1, 0) ORDER BY placed_at, customer_id, order_id LIMIT 20"
Real output:
Limit
CostAtActualRows: Total=2060 RemoteCalls=2 RowsProcessed=40 NetworkRows=40 MemoryRows=40
CostAtActualRows: Total=2060 RemoteCalls=2 RowsProcessed=40 NetworkRows=40 MemoryRows=40
-> Route [Scatter]
RemoteCalls: 2
CostAtActualRows: Total=2040 RemoteCalls=2 NetworkRows=40
Query: SELECT customer_id, order_id, placed_at FROM public.orders WHERE (placed_at, customer_id, order_id) > ($1::timestamp with time zone, $2, $3) ORDER BY 3, 1, 2 LIMIT 20
Each shard returns at most 20 rows after the cursor, and the router merges 40 rows into one page of 20, however deep the page is.
5. Confirm the keyset pages match OFFSET
psql -At -F, -c "SELECT customer_id, order_id, placed_at FROM orders ORDER BY placed_at, customer_id, order_id LIMIT 60" > by-offset.txt
: > by-keyset.txt
cursor="'-infinity'::timestamptz, 0, 0"
for page in 1 2 3; do
psql -At -F, -c "SELECT customer_id, order_id, placed_at FROM orders WHERE (placed_at, customer_id, order_id) > ($cursor) ORDER BY placed_at, customer_id, order_id LIMIT 20" > page.txt
cat page.txt >> by-keyset.txt
IFS=, read c o t < <(tail -1 page.txt)
cursor="'$t'::timestamptz, $c, $o"
done
wc -l < by-keyset.txt
diff by-offset.txt by-keyset.txt && echo "three keyset pages match the first 60 rows by OFFSET"
Real output:
60
three keyset pages match the first 60 rows by OFFSET
Verify the result
./cost.sh "SELECT customer_id, order_id, placed_at FROM orders WHERE (placed_at, customer_id, order_id) > (timestamptz '2026-10-03 18:40:00+00', 1, 0) ORDER BY placed_at, customer_id, order_id LIMIT 20" | grep -o "NetworkRows=[0-9]*" | head -1
./cost.sh "SELECT order_id FROM orders WHERE customer_id = 42 AND order_id > 2041 ORDER BY order_id LIMIT 5" | grep -o "RemoteCalls=[0-9]*" | head -1
Expected: NetworkRows=40 for the cross-shard keyset page and RemoteCalls=1 for the single-customer page.
How it works
For a sorted query across shards, the router sends each shard the query with its ORDER BY and a LIMIT, then merges the sorted streams. With OFFSET n LIMIT k, the only safe limit for each shard is n + k, so deep pages get more expensive with every page. With a keyset cursor, each shard filters to rows after the cursor and returns at most k, so the cost of a page does not depend on its position. A unique sort key, here placed_at plus the primary key, keeps the cursor unambiguous when two rows share a timestamp. A cross-shard page still calls every shard; only a filter on the shard key narrows it to one. The router is described on the Neki architecture page.
Common issues
Sorting by a non-unique column
Paginating by placed_at alone can skip or repeat rows that share a timestamp across a page boundary. Add the primary key columns to the ORDER BY and to the cursor.
Single-shard pages under fanout=single
With __neki.fanout = 'single', the cross-shard patterns are rejected as scatters, while the single-customer keyset page runs. That setting is a good fit for endpoints that should only ever page within one customer.
No index on the sort key
Without an index on (placed_at, customer_id, order_id), each shard sorts all of its rows to find one page, even with a keyset cursor. The index lets it read only the page.
Next steps
- Delete the database with
pscale database delete feeddb --force --org "$PLANETSCALE_ORG". - Read the Neki data topology guide for choosing a shard key that matches how pages are filtered.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "EXPLAIN (NEKI_PLAN, ANALYZE) of the cross-shard keyset page and the single-customer page"
expected_result: "NetworkRows=40 and RemoteCalls=1"