A Neki table can be sharded by one column and range-partitioned by another. This guide shards an events table by tenant_id, partitions it by month on created_at, adds a new month, and retires an old one. Rows inserted through the parent always reach the right shard. The partitions themselves also need entries in the data topology, or direct queries against them go to the wrong shard.
What you will build
after step 5 (step 6 then retires events_2026_09):
events (sharded by tenant_id across sh2, sh3; partitioned by created_at)
sh2: events_2026_09 events_2026_10 events_2026_11
sh3: events_2026_09 events_2026_10 events_2026_11
data topology: events, events_2026_09, events_2026_10, events_2026_11 -> events_by_tenant
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, create a range-partitioned table inside a sharded Neki table, add a partition later, and retire an old one.
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.
- Shard by tenant_id with a shard group whose default_shard_index is an xxhash index on tenant_id. A table entry in the data topology has only a shard_group field.
- Partition by RANGE (created_at). The primary key must include created_at.
- List the parent and every partition in the data topology under the same shard group. A partition missing from the topology is routed to the authoritative shard when queried by name, and DETACH PARTITION fails.
- A data topology update replaces the whole topology. Start from the current one and add entries.
- Inserts through the parent route correctly whether or not the partition is listed.
- Retire a month with ALTER TABLE ... DETACH PARTITION, then DROP TABLE.
- 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. Create it with these commands:
pscale database create telemetry --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create telemetry 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 telemetry 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'")
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 telemetry --force --org "$PLANETSCALE_ORG".
1. Create the partitioned table
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE events (tenant_id bigint NOT NULL, event_id bigint NOT NULL, created_at timestamptz NOT NULL, kind text NOT NULL, PRIMARY KEY (tenant_id, event_id, created_at)) PARTITION BY RANGE (created_at)" \
-c "CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')" \
-c "CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01')" \
2>&1 | grep -v NOTICE
2. Place the parent and its partitions
cat > data-topology.json <<JSON
{
"shard_indexes": { "xxhash_tenant_id": { "type": "xxhash", "columns": ["tenant_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "events_by_tenant", "default_shard_index": "xxhash_tenant_id",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"events": { "shard_group": "events_by_tenant" },
"events_2026_09": { "shard_group": "events_by_tenant" },
"events_2026_10": { "shard_group": "events_by_tenant" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update telemetry main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
Real output:
applied
3. Load September and October
psql -At -c "INSERT INTO events SELECT (g % 50) + 1, g, timestamptz '2026-09-15' + (g % 40) * interval '1 day', 'view' FROM generate_series(1, 4000) g"
Real output:
INSERT 0 4000
Count rows per shard and partition:
cat > placement.sh <<'EOF'
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(string_agg(t || '=' || c, ' '), '(none)') FROM (SELECT tableoid::regclass::text AS t, count(*) AS c FROM events GROUP BY 1 ORDER BY 1) x" | tail -1
done
EOF
bash placement.sh
Real output:
sh1: (none)
sh2: events_2026_09=640 events_2026_10=960
sh3: events_2026_09=960 events_2026_10=1440
Both partitions exist on both data shards, and the authoritative shard holds no rows.
4. Check routing and pruning
psql -c "EXPLAIN (NEKI_PLAN) SELECT count(*) FROM events WHERE tenant_id = 7 AND created_at >= '2026-10-01'"
Real output:
Route [EqualUnique] (cost=0.00..1001.00 rows=1 width=8)
Query: SELECT count(*) FROM public.events WHERE tenant_id = $1 AND created_at >= $2::timestamp with time zone
ShardGroup: events_by_tenant
Values: $1
The router sends the query to one shard. On that shard, Postgres prunes partitions as usual:
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
psql -At -c "SET __neki.shard='$u';" -c "EXPLAIN SELECT count(*) FROM events WHERE created_at >= '2026-10-01'" | tail -n +2
Real output:
Aggregate (cost=22.93..22.94 rows=1 width=8)
-> Seq Scan on events_2026_10 events (cost=0.00..22.12 rows=323 width=0)
Filter: (created_at >= '2026-10-01 00:00:00+00'::timestamp with time zone)
5. Add a partition for November
Rows outside every partition are rejected:
psql -At -c "INSERT INTO events VALUES (1, 999999, '2026-11-05', 'view')" 2>&1 | head -2
Real output:
ERROR: no partition of relation "events" found for row
DETAIL: Partition key of the failing row contains (created_at) = (2026-11-05 00:00:00+00).
Create the partition and load it through the parent:
psql -q -c "CREATE TABLE events_2026_11 PARTITION OF events FOR VALUES FROM ('2026-11-01') TO ('2026-12-01')" 2>&1 | grep -v NOTICE
psql -At -c "INSERT INTO events SELECT (g % 50) + 1, 100000 + g, timestamptz '2026-11-01' + (g % 20) * interval '1 day', 'view' FROM generate_series(1, 1000) g"
bash placement.sh
Real output:
INSERT 0 1000
sh1: (none)
sh2: events_2026_09=640 events_2026_10=960 events_2026_11=400
sh3: events_2026_09=960 events_2026_10=1440 events_2026_11=600
The new partition inherited the parent's placement. Querying it by name is different until it is in the topology:
psql -At -c "SELECT count(*) FROM events WHERE created_at >= '2026-11-01'"
psql -At -c "SELECT count(*) FROM events_2026_11"
psql -c "EXPLAIN (NEKI_PLAN) SELECT count(*) FROM events_2026_11" | sed -n 3,5p
Real output:
1000
0
Route [AnyShard] (cost=0.00..2000.00 rows=1000 width=8)
Query: SELECT count(*) FROM public.events_2026_11
ShardGroup: shu7l93qz8enrv
shu7l93qz8enrv is the authoritative shard (sh1, the id differs per database). The router sent the query there, where the partition has no rows, and returned 0 without an error. Add the partition to the topology, starting from the current topology:
pscale branch data-topology get telemetry main --org "$PLANETSCALE_ORG" --format json \
| jq '.data_topology | .databases.postgres.schemas.public.tables.events_2026_11 = {"shard_group": "events_by_tenant"}' > data-topology.json
pscale branch data-topology update telemetry main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -At -c "SELECT count(*) FROM events_2026_11"
Real output:
applied
1000
6. Retire September
psql -q -c "ALTER TABLE events DETACH PARTITION events_2026_09" 2>&1 | grep -v NOTICE
psql -At -c "SELECT count(*) FROM events" -c "SELECT count(*) FROM events_2026_09"
psql -q -c "DROP TABLE events_2026_09" 2>&1 | grep -v NOTICE
bash placement.sh
Real output:
3400
1600
sh1: (none)
sh2: events_2026_10=960 events_2026_11=400
sh3: events_2026_10=1440 events_2026_11=600
The detached table kept its 1,600 rows until it was dropped. Remove its topology entry:
pscale branch data-topology get telemetry main --org "$PLANETSCALE_ORG" --format json \
| jq '.data_topology | del(.databases.postgres.schemas.public.tables.events_2026_09)' > data-topology.json
pscale branch data-topology update telemetry main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
Real output:
applied
Verify the result
Compare a partition read by name with the same rows read through the parent:
psql -At -c "SELECT count(*) FROM events WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01'" -c "SELECT count(*) FROM events_2026_10"
Expected: both counts are the same (2400 in this run). If the second count is 0, the partition is missing from the data topology.
How it works
The router routes each statement by the table it names. For the parent, it uses the parent's shard group and the shard key in the row or WHERE clause. On each shard, the table is an ordinary Postgres partitioned table, so CREATE TABLE ... PARTITION OF, partition pruning and DETACH PARTITION behave as they do on a single server. A partition named directly in a query is routed by its own topology entry, and without one it goes to the default shard group. The topology format is described in the Neki data topology guide.
Common issues
Detaching a partition that is not in the topology
ALTER TABLE events DETACH PARTITION events_2026_10 failed with not implemented: [347] ALTER TABLE partition attach and detach is not supported because the parent and partition do not have the same write routing while the partition was missing from the topology. It succeeded after the partition was listed under the parent's shard group.
No shard_index field on a table entry
A table entry with "shard_index": "xxhash_tenant_id" fails with invalid data topology JSON: proto: ... unknown field "shard_index". Put the index on a shard group as default_shard_index and point the table at that group.
The primary key must include the partition column
PRIMARY KEY (tenant_id, id) on a table partitioned by created_at fails with unique constraint on partitioned table must include all partitioning columns. This is a Postgres rule.
Next steps
- Delete the database with
pscale database delete telemetry --force --org "$PLANETSCALE_ORG". - Read the Neki data topology guide for shard groups, shard indexes and key ranges.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "SELECT count(*) FROM events WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01'; SELECT count(*) FROM events_2026_10"
expected_result: "Both counts are equal."