A Neki database can run shards from more than one configuration profile, and each profile sets its own cluster size and replica count. This guide adds an archive profile with a single-node PS-10 shard, binds an orders_archive table to a shard group on that shard, moves orders older than a cutoff out of the hot table in verified batches, and reads both tiers with single-shard queries.
What you will build
profile default: PS-10, 2 replicas -> sh1 (authoritative), sh2, sh3: orders (hot)
profile archive: PS-10, 0 replicas -> sh4: orders_archive (cold)
batch: INSERT INTO orders_archive SELECT ... WHERE placed_at < cutoff -> compare counts -> DELETE FROM orders ...
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, move cold rows from a hot sharded table to an archive table on cheaper Neki shards.
Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, shard the hot table, load it, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Create a second configuration profile with pscale branch config-profile create <db> main archive --cluster-size PS_10_AWS_ARM_NEKI --replicas 0, then add a shard to it with pscale branch shard create <db> main --config-profile archive --count 1.
- Put the archive table in its own shard group on that shard, with the same shard index as the hot table. A single key range without start or end covers the whole hash space.
- A data topology update replaces the whole topology: start from the current one and add the group and table.
- Move rows in batches by shard key: INSERT INTO archive SELECT ... ON CONFLICT DO NOTHING, compare the counts, and only then DELETE from the hot table.
- Query the two tiers with UNION ALL and the shard key in each branch.
- 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_atstarting at 2026-10-01. Create them with these commands:
pscale database create coldstore --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create coldstore 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 coldstore 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 TABLE orders_archive (customer_id bigint NOT NULL, order_id bigint NOT NULL, placed_at timestamptz NOT NULL, PRIMARY KEY (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": "hot_orders", "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": "hot_orders" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update coldstore 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. orders_archive exists now but is not in the topology yet, so it sits on the authoritative shard, empty, until step 3 places it.
Note: Output samples are trimmed to the relevant lines.
PS-10-AWS-ARM-NEKIlists at $10 per month per node: a default shard with two replicas is three nodes ($30 per month), and the archive shard in this guide is one node ($10 per month), prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete coldstore --force --org "$PLANETSCALE_ORG".
1. Create the archive profile
pscale branch config-profile create coldstore main archive --cluster-size PS_10_AWS_ARM_NEKI --replicas 0 --org "$PLANETSCALE_ORG" > /dev/null
pscale branch config-profile list coldstore main --org "$PLANETSCALE_ORG" --format json | jq -c '.[] | {name, cluster_size, replicas, shards, state}'
Real output:
{"name":"default","cluster_size":"PS_10_AWS_ARM_NEKI","replicas":2,"shards":3,"state":"applying"}
{"name":"archive","cluster_size":"PS_10_AWS_ARM_NEKI","replicas":0,"shards":0,"state":"ready"}
The default profile showed applying for a short time after the new profile was created; step 2 waits for every profile to be ready.
2. Add a shard to it
t0=$(date +%s)
pscale branch shard create coldstore main --config-profile archive --count 1 --org "$PLANETSCALE_ORG" > /dev/null
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "4" ]; 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
until [ "$(pscale branch config-profile list coldstore main --org "$PLANETSCALE_ORG" --format json | jq -r '[.[].state] | unique | join(",")')" = "ready" ]; do sleep 15; done
echo "archive shard ready after $(( $(date +%s) - t0 )) s"
SHARD_4=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh4'")
pscale branch shard list coldstore main --org "$PLANETSCALE_ORG" --format json | jq -r '.[] | "\(.display_name) \(.configuration_profile)"'
psql -At -c "SELECT s.name, s.durability_policy, count(*) AS nodes FROM __neki.list_shards() s JOIN __neki.list_sidecar_statuses() n ON n.sidecar_shard_uid = s.uid GROUP BY 1, 2 ORDER BY 1"
Real output:
archive shard ready after 53 s
sh1 default
sh2 default
sh3 default
sh4 archive
sh1|sync|3
sh2|sync|3
sh3|sync|3
sh4|single|1
The archive shard runs one node with durability policy single; the default shards each run three.
3. Place the archive table on the archive shard
pscale branch data-topology get coldstore main --org "$PLANETSCALE_ORG" --format json \
| jq --arg s4 "$SHARD_4" '.data_topology
| .shard_groups += [{"uid": "cold_orders", "default_shard_index": "xxhash_customer_id", "key_ranges": [{"shard_uid": $s4}]}]
| .databases.postgres.schemas.public.tables.orders_archive = {"shard_group": "cold_orders"}' > data-topology.json
pscale branch data-topology update coldstore main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM orders_archive WHERE customer_id = 42" | grep -E "Route|ShardGroup"
Real output:
applied
Route [AnyShard]
ShardGroup: cold_orders
The archive group has a single shard, so a keyed lookup plans as Route [AnyShard] on cold_orders.
4. Move cold orders in verified batches
Orders placed before 2026-10-03 are cold here. Each batch handles 100 customers: copy, compare the counts for that batch, and delete from the hot table only when they match.
CUTOFF="2026-10-03"
echo "cold orders before: $(psql -At -c "SELECT count(*) FROM orders WHERE placed_at < '$CUTOFF'")"
for start in $(seq 1 100 500); do
ids=$(seq $start $((start + 99)) | paste -sd, -)
copied=$(psql -At -c "INSERT INTO orders_archive SELECT customer_id, order_id, placed_at FROM orders WHERE customer_id IN ($ids) AND placed_at < '$CUTOFF' ON CONFLICT DO NOTHING")
hot=$(psql -At -c "SELECT count(*) FROM orders WHERE customer_id IN ($ids) AND placed_at < '$CUTOFF'")
cold=$(psql -At -c "SELECT count(*) FROM orders_archive WHERE customer_id IN ($ids) AND placed_at < '$CUTOFF'")
if [ "$hot" = "$cold" ]; then
deleted=$(psql -At -c "DELETE FROM orders WHERE customer_id IN ($ids) AND placed_at < '$CUTOFF'")
else
deleted="skipped: hot=$hot archive=$cold"
fi
echo "customers $start-$((start + 99)): $copied, $deleted"
done
Real output:
cold orders before: 2879
customers 1-100: INSERT 0 599, DELETE 599
customers 101-200: INSERT 0 600, DELETE 600
customers 201-300: INSERT 0 600, DELETE 600
customers 301-400: INSERT 0 580, DELETE 580
customers 401-500: INSERT 0 500, DELETE 500
A batch that fails between the copy and the delete can be rerun: ON CONFLICT DO NOTHING skips rows that are already archived, and the delete only runs after the counts match.
5. Read both tiers
psql -At -c "SELECT 'hot', count(*) FROM orders WHERE customer_id = 42 UNION ALL SELECT 'archive', count(*) FROM orders_archive WHERE customer_id = 42"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT order_id, placed_at FROM orders WHERE customer_id = 42 UNION ALL SELECT order_id, placed_at FROM orders_archive WHERE customer_id = 42" | grep -E "Route|ShardGroup"
Real output:
archive|6
hot|4
-> Route [EqualUnique]
ShardGroup: hot_orders
-> Route [EqualUnique]
ShardGroup: cold_orders
Each branch names the shard key, so the hot branch reads one default shard and the archive branch reads the archive shard.
Verify the result
psql -At -c "SELECT (SELECT count(*) FROM orders) + (SELECT count(*) FROM orders_archive)"
psql -At -c "SELECT count(*) FROM orders WHERE placed_at < '2026-10-03'"
psql -At -v ON_ERROR_STOP=1 -c "SET __neki.shard = '$SHARD_4'" -c "SELECT count(*) FROM orders_archive" | tail -1
psql -At -c "SELECT count(*) FROM orders_archive"
Expected: 5000, 0, and the same archive count from sh4 directly and through the router (2879 in this run).
How it works
A configuration profile defines the hardware and replica count of the shards created in it, and a shard group can list any shards, so placing a table on cheaper hardware is a matter of giving it a group whose key ranges point at shards from a cheaper profile. Using the same shard index for the hot and archive tables keeps the key-to-shard mapping simple to reason about, although here the archive group has only one shard. The move itself is ordinary SQL: the router reads the batch from the hot group's shards and inserts it into the archive group's shard. Configuration profiles and shard groups are described in the Neki data topology guide.
Common issues
A single-node shard has no failover
With --replicas 0 the archive shard has no replica to promote if its node fails. Keep backups that include it, or use --replicas 1 for an archive that must stay available.
Child rows block the delete
In testing, deleting archived orders that still had line items failed with update or delete on table "orders" violates foreign key constraint "items_order_fk" on table "order_items". Archive child rows in the same batch, before their parents, or drop the key for the hot-to-cold move.
A view over both tiers scatters
A UNION ALL view over orders and orders_archive returned the right rows, but SELECT * FROM orders_all WHERE customer_id = 42 scattered across both groups. Write the UNION ALL with the shard key in each branch, as in step 5.
Large batches through the router
The router builds an INSERT ... SELECT batch in memory and enforces a per-query budget of about 6.4 MB. A 150,000-row INSERT ... SELECT failed with memory budget exceeded. Keep batches small, as here, or archive with COPY out and COPY in for very large moves.
Next steps
- Delete the database with
pscale database delete coldstore --force --org "$PLANETSCALE_ORG". - Read the Neki pricing page for the cluster sizes available to an archive profile.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "hot + archive row count; cold rows left in the hot table; archive count on sh4 and through the router"
expected_result: "5000, 0, and equal archive counts"