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

Reference

Predict Identity Gaps and Set the Sequence Cache Size on Neki with the PlanetScale CLI

On a Neki database, identity and sequence values come from routers, and each router reserves a block of values at a time. This guide measures the block size, shows how CACHE changes it, and predicts the gap a router restart leaves by comparing the sequence's last_value with the number of values actually used.

What you will build

Two identity columns on a sharded tickets table and a ticket_events table, inserts from separate connections that reveal each router's block, and a router restart that confirms the predicted gap.

connection -> router A  (reserved 1..1000)      -> 1, 2, 3 ...
connection -> router B  (reserved 1001..2000)   -> 1001, 1002 ...
connection -> router C  (reserved 2001..3000)   -> 2001 ...
pg_sequences.last_value = 3000   reserved but unused = last_value - values used
router restart -> unused values are skipped, next id = last_value + 1

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, measure how a Neki database hands out identity values, set the sequence cache size, and predict the gap a router restart leaves.

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.
- Each router reserves a block of max(CACHE, 1000) values. A smaller CACHE does not shrink the block. Set CACHE when the identity column is created.
- Insert from separate psql connections, one statement per connection, and record the ids. Values from one connection are consecutive within that router's block.
- Read pg_sequences.last_value through the router. Reserved but unused values are last_value minus the number of values used (count(*) when no rows were deleted and no inserts rolled back).
- Sequence state lives on the authoritative shard only.
- Resize the router group with pscale branch router update and wait for state ready. After the restart, the next id is last_value + 1.
- 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 tickets table sharded by account_id. Create them with these commands:
pscale database create helpdesk --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

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

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": "tickets_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": {
    "tickets": { "shard_group": "tickets_by_account" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update helpdesk main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE tickets (ticket_id bigint GENERATED ALWAYS AS IDENTITY, account_id bigint NOT NULL, title text NOT NULL, PRIMARY KEY (account_id, ticket_id))" \
  2>&1 | grep -v NOTICE

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. Step 5 resizes the router group to NKR-2, which lists at $22 per month. Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete helpdesk --force --org "$PLANETSCALE_ORG".

1. Insert from separate connections

Each psql call opens a new connection, which may land on a different router:

for a in 1 2 3 4 5 6; do
  psql -At -c "INSERT INTO tickets (account_id, title) VALUES ($a, 'first') RETURNING ticket_id" | head -1
done | tr '\n' ' '; echo

Real output (the order depends on which router each connection reaches):

1 1001 2 2001 3 1002

The ids fall into blocks that start at 1, 1001 and 2001. Each block of 1,000 belongs to one router, so this database has three routers. Six connections do not always reach all three; run more inserts if you see only one or two blocks. Within one connection, values stay consecutive:

psql -At -c "INSERT INTO tickets (account_id, title) SELECT g, 'bulk' FROM generate_series(1,5) g RETURNING ticket_id" | head -5 | tr '\n' ' '; echo

Real output:

1003 1004 1005 1006 1007

2. Measure what is reserved

psql -At -c "SELECT last_value, cache_size FROM pg_sequences WHERE sequencename = 'tickets_ticket_id_seq'"
psql -At -c "SELECT count(*), max(ticket_id) FROM tickets"

Real output:

3000|1
11|2001

last_value counts every value handed to a router, not every value used. last_value minus the number of rows is how many reserved values are still unused, spread over the unused part of each router's block. A router restart skips them.

The sequence itself is stored on the authoritative shard only:

for n in sh1 sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  echo -n "$n: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT coalesce(last_value::text, 'null') FROM pg_sequences WHERE sequencename = 'tickets_ticket_id_seq'" | tail -1
done

Real output:

sh1: 3000
sh2: null
sh3: null

3. Set a larger cache when you create the column

CACHE sets the size of each router's block when it is larger than 1,000:

psql -q -c "CREATE TABLE ticket_events (event_id bigint GENERATED ALWAYS AS IDENTITY (CACHE 5000) PRIMARY KEY, note text)" 2>&1 | grep -v NOTICE
for i in 1 2 3 4 5 6; do
  psql -At -c "INSERT INTO ticket_events (note) VALUES ('e$i') RETURNING event_id" | head -1
done | tr '\n' ' '; echo
psql -At -c "SELECT last_value, cache_size FROM pg_sequences WHERE sequencename = 'ticket_events_event_id_seq'"

Real output:

1 5001 10001 5002 10002 5003
15000|5000

The blocks now start at 1, 5001 and 10001, and three routers hold 15,000 values between them.

4. Check that a smaller cache does not shrink the block

psql -q -c "CREATE TABLE ticket_tags (tag_id bigint GENERATED ALWAYS AS IDENTITY (CACHE 10) PRIMARY KEY, tag text)" 2>&1 | grep -v NOTICE
for i in 1 2 3 4; do
  psql -At -c "INSERT INTO ticket_tags (tag) VALUES ('t$i') RETURNING tag_id" | head -1
done | tr '\n' ' '; echo

Real output:

1 2 1001 2001

The blocks still start at 1, 1001 and 2001.

5. Predict and confirm the gap after a router restart

Record the prediction, then resize the router group, which replaces the routers:

LAST=$(psql -At -c "SELECT last_value FROM pg_sequences WHERE sequencename = 'tickets_ticket_id_seq'")
USED=$(psql -At -c "SELECT count(*) FROM tickets")
echo "last_value=$LAST used=$USED predicted_next=$((LAST + 1)) values_skipped=$((LAST - USED))"

pscale branch router update helpdesk main default --size NKR-2 --org "$PLANETSCALE_ORG" > /dev/null
until [ "$(pscale branch router show helpdesk main default --org "$PLANETSCALE_ORG" --format json | jq -r '.sku.display_name + " " + .state')" = "NKR-2 ready" ]; do sleep 20; done

for a in 1 2 3; do
  psql -At -c "INSERT INTO tickets (account_id, title) VALUES ($a, 'after restart') RETURNING ticket_id" | head -1
done | tr '\n' ' '; echo

Real output:

last_value=3000 used=11 predicted_next=3001 values_skipped=2989
3001 4001 5001

The lowest new id is last_value + 1. The 2,989 values reserved before the restart and never used were skipped, and the new blocks start at 3001, 4001 and 5001.

Verify the result

Confirm the lowest id issued after the restart is the predicted value:

psql -At -c "SELECT min(ticket_id) FROM tickets WHERE title = 'after restart'"
echo "$((LAST + 1))"

Expected: both lines print the same number (3001 in this run).

How it works

A router takes a range of sequence values from the authoritative shard and hands them out from memory, so the shard is not consulted on every insert. Ids are unique across the whole database, but they are not ordered by time across connections, and they are not dense. Restarting a router (a resize, as here) skips the values it had not used, so a restart skips last_value minus the values already used. With one router per cell, more cells or router autoscaling mean more blocks in use at once. The behavior in this guide was measured directly; the router's role in the system is described on the Neki architecture page.

Common issues

Changing CACHE on an existing column

ALTER TABLE ... ALTER COLUMN ... SET CACHE 5000 on a column that was already serving values did not change the ids routers handed out next, because they kept using the blocks they had. last_value still moved ahead. Set CACHE when you create the column, or expect the new size only after the current blocks run out.

Ids are not in insert order

Two inserts a second apart can return 2 and then 1001. Sort by a timestamp column when you need insert order.

A rolled-back insert still uses a value

As in standard Postgres, BEGIN; INSERT ... RETURNING; ROLLBACK; used up the returned id, and the next insert on that connection got the following value.

Altering a table created by another role

ALTER TABLE fails with must be owner of table tickets when the table was created by a different role. Use the same role for every step, as in the prerequisites.

Gaps are larger with a larger cache

A CACHE 5000 column on three routers can skip up to 15,000 values in one restart. Size CACHE with that bound in mind if your ids must stay within a fixed range.

Next steps

  • Delete the database with pscale database delete helpdesk --force --org "$PLANETSCALE_ORG".
  • Read the Neki data topology guide for how shard groups and the authoritative shard are defined.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "SELECT min(ticket_id) FROM tickets WHERE title = 'after restart'"
  expected_result: "Equal to the pg_sequences.last_value recorded before the router resize, plus 1."