This guide shards raw IoT readings and an hourly rollup table by device_id on Neki. The rollup is one INSERT ... SELECT ... GROUP BY that the router pushes to every shard, and it replaces an hour with a DELETE and INSERT in one transaction because INSERT ... SELECT ... ON CONFLICT DO UPDATE is not supported yet.
What you will build
readings (device_id, read_at, temp_c, humidity) --+-- shard group by_device, xxhash(device_id)
readings_hourly (device_id, hour, samples, avg, min, max) --+
rollup.sh <hour>: BEGIN; DELETE the hour; INSERT ... SELECT ... GROUP BY device_id, hour; COMMIT;
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, shard IoT telemetry by device on Neki and roll up hourly aggregates.
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 readings and readings_hourly by device_id with {"type":"xxhash","columns":["device_id"]}, in one shard group, with PRIMARY KEY (device_id, read_at) and PRIMARY KEY (device_id, hour).
- Roll up with INSERT INTO readings_hourly SELECT device_id, date_trunc('hour', read_at), count(*), avg(...), min(...), max(...) FROM readings WHERE read_at is inside the hour GROUP BY 1, 2. Because device_id is in the GROUP BY, each shard can aggregate its own devices.
- INSERT ... SELECT ... ON CONFLICT DO UPDATE fails with a not-implemented error. Replace an hour by running DELETE for that hour and the INSERT ... SELECT in one transaction, so re-running the rollup is safe.
- 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,jq, andpython3:
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 tables, shard them by device and load readings
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE readings (device_id bigint NOT NULL, read_at timestamptz NOT NULL, temp_c real NOT NULL, humidity real NOT NULL, PRIMARY KEY (device_id, read_at))" \
-c "CREATE TABLE readings_hourly (device_id bigint NOT NULL, hour timestamptz NOT NULL, samples int NOT NULL, avg_temp_c real NOT NULL, min_temp_c real NOT NULL, max_temp_c real NOT NULL, PRIMARY KEY (device_id, hour))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": { "device": { "type": "xxhash", "columns": ["device_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_device", "default_shard_index": "device",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"readings": { "shard_group": "by_device" },
"readings_hourly": { "shard_group": "by_device" }
} } } } },
"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
psql -q -c "INSERT INTO readings
SELECT d, timestamptz '2026-10-28 00:00+00' + i * interval '5 minutes', 15 + (d % 10) + i * 5 / 60.0, 40 + (i % 30)
FROM generate_series(1, 100) d, generate_series(0, 35) i"
psql -At -c "SELECT count(*) || ' readings from ' || count(DISTINCT device_id) || ' devices' FROM readings"
for n in sh2 sh3; do
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
echo "$n: $(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(DISTINCT device_id) || ' devices, ' || count(*) || ' readings' FROM readings" | tail -1)"
done
Real output:
applied
3600 readings from 100 devices
sh2: 48 devices, 1728 readings
sh3: 52 devices, 1872 readings
The 100 devices send a reading every five minutes for three hours, which is 36 readings each. The devices split between the two data shards.
2. Try the upsert form of the rollup
psql -At -c "INSERT INTO readings_hourly
SELECT device_id, date_trunc('hour', read_at), count(*), avg(temp_c), min(temp_c), max(temp_c)
FROM readings WHERE read_at >= '2026-10-28 00:00+00' AND read_at < '2026-10-28 01:00+00' GROUP BY 1, 2
ON CONFLICT (device_id, hour) DO UPDATE SET samples = EXCLUDED.samples" 2>&1 | grep -o 'not implemented.*'
Real output:
not implemented: [117] INSERT ... SELECT ... ON CONFLICT DO UPDATE is not yet supported
The router does not support INSERT ... SELECT ... ON CONFLICT DO UPDATE yet, so the rollup replaces an hour instead.
3. Write the rollup as delete and insert in one transaction
cat > rollup.sh <<'EOF'
#!/bin/bash
# rollup.sh '<hour start>': recompute one hour of readings_hourly
psql -q -v ON_ERROR_STOP=1 -v h="$1" <<'SQL'
BEGIN;
DELETE FROM readings_hourly WHERE hour = :'h';
INSERT INTO readings_hourly
SELECT device_id, date_trunc('hour', read_at), count(*), avg(temp_c), min(temp_c), max(temp_c)
FROM readings WHERE read_at >= :'h' AND read_at < :'h'::timestamptz + interval '1 hour'
GROUP BY 1, 2;
COMMIT;
SQL
EOF
chmod +x rollup.sh
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) INSERT INTO readings_hourly SELECT device_id, date_trunc('hour', read_at), count(*), avg(temp_c), min(temp_c), max(temp_c) FROM readings WHERE read_at >= '2026-10-28 00:00+00' AND read_at < '2026-10-28 01:00+00' GROUP BY 1, 2" | grep -E 'Insert|Route'
for h in '2026-10-28 00:00+00' '2026-10-28 01:00+00' '2026-10-28 02:00+00'; do ./rollup.sh "$h"; done
psql -At -c "SELECT count(*) || ' rollup rows, ' || sum(samples) || ' samples' FROM readings_hourly"
Real output:
-> Insert [Values]
-> Route [Scatter]
300 rollup rows, 3600 samples
The router plans the rollup as a scatter SELECT with the GROUP BY pushed to the shards, followed by the insert of the grouped rows. Three hours of 100 devices make 300 rollup rows that account for all 3,600 readings.
4. Compare a rollup row with the raw readings
psql -At -c "SELECT samples, round(avg_temp_c::numeric, 2), min_temp_c, max_temp_c FROM readings_hourly WHERE device_id = 5 AND hour = '2026-10-28 01:00+00'"
psql -At -c "SELECT count(*), round(avg(temp_c)::numeric, 2), min(temp_c), max(temp_c) FROM readings WHERE device_id = 5 AND read_at >= '2026-10-28 01:00+00' AND read_at < '2026-10-28 02:00+00'"
Real output:
12|21.46|21|21.916666
12|21.46|21|21.916666
The rollup row for one device and hour matches an aggregate computed directly from that device's raw readings.
5. Absorb a late reading
psql -q -c "INSERT INTO readings VALUES (5, '2026-10-28 00:07+00', 21.0, 50)"
./rollup.sh '2026-10-28 00:00+00'
./rollup.sh '2026-10-28 00:00+00'
psql -At -c "SELECT samples FROM readings_hourly WHERE device_id = 5 AND hour = '2026-10-28 00:00+00'"
psql -At -c "SELECT count(*) || ' rollup rows, ' || sum(samples) || ' samples' FROM readings_hourly"
Real output:
13
300 rollup rows, 3601 samples
The late reading raised the hour's sample count for device 5 from 12 to 13. Running the same hour a second time left the totals unchanged.
Verify the result
psql -At -c "SELECT count(*) FROM readings"
psql -At -c "SELECT sum(samples) FROM readings_hourly"
Expected: both numbers are 3601, so every raw reading, including the late one, is counted in exactly one rollup row.
How it works
device_id leads both primary keys and the shard index, so a device's readings and its rollup rows live on the same shard. The GROUP BY includes device_id, so each shard can finish its groups without seeing other shards' rows, and the router only collects the grouped rows and inserts them. Replacing an hour with DELETE and INSERT in one transaction makes the rollup idempotent: it can run again for the same hour after late data or a failure. The Neki data topology guide describes how shard groups keep related tables together.
Common issues
ON CONFLICT DO UPDATE on INSERT ... SELECT
The router returns [117] INSERT ... SELECT ... ON CONFLICT DO UPDATE is not yet supported. ON CONFLICT DO NOTHING is accepted, and a second run of it inserted 0 rows, so it keeps old rollup rows as they are and never recalculates an hour that received late data. Use the delete and insert pair.
The delete and insert span shards
In the default __neki.tx_mode=multi, the transaction in step 3 touched both data shards and committed. This guide did not test what happens if a shard fails partway through the commit, so keep the rollup re-runnable: if a run fails, run the same hour again.
Large rollups
An INSERT ... SELECT that returned about 150,000 rows failed with memory budget exceeded: account "insert_input" in testing. Roll up one hour at a time, or one range of devices at a time, so each statement stays small.
Next steps
- Delete the database with
pscale database delete telemetry --force --org "$PLANETSCALE_ORG". - Read the guide on keeping a multi-tenant audit log sharded by tenant with a retention job for deleting old raw readings in batches.
- Read the Neki data topology guide for placing related tables in one shard group.
verification:
status: verified
tested_at: "2026-10-29"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "count of raw readings; sum of samples across hourly rollup rows"
expected_result: "3601 and 3601"