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

Reference

Shard IoT Telemetry by Device Id and Roll Up Hourly Aggregates on Neki

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.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, jq, and python3:

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-NEKI lists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish with pscale 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"