On Neki, a session that sets __neki.target = 'REPLICA' reads from each shard's replicas instead of its primary, which keeps report queries off the nodes that serve writes. This guide builds a report script that only runs on replicas when they are fresh enough: it checks replica health and lag, bumps a heartbeat on the primaries, waits until its replica session sees the new heartbeat on every shard, and reports when the replicas are not fresh in time so the job can run on the primaries instead.
What you will build
report.sh <max wait seconds>
1. __neki.list_sidecar_statuses(): every replica healthy, replay_lag known
2. UPDATE heartbeat SET beat = beat + 1 (primaries) -> target beat N
3. replica session (target REPLICA, affinity session): wait until min(beat) >= N on every shard
4. caught up in time -> run the report on that replica session; otherwise report "not fresh"
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, run read-only reports on Neki replicas only when they are fresh enough, and fall back to the primaries otherwise.
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.
- Send report queries with SET __neki.target = 'REPLICA'. Writes on that session fail with "session target is not primary".
- SET __neki.replica_affinity = 'session' so later statements in the session prefer the same replica on each shard as the freshness check. __neki.replica_recency accepts prefer, require and off.
- Check health and lag with SELECT ... FROM __neki.list_sidecar_statuses() WHERE type = 'replica'.
- Prove freshness with a heartbeat table in the same shard group, with one row on each data shard: increment it through the router, then read it on the replica session until every row has the new value.
- Do not use now() - pg_last_xact_replay_timestamp() as a freshness measure: on an idle shard it grows while the replica is fully caught up.
- 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, each with two replicas, an
orderstable with 50,000 rows sharded bycustomer_id, and aheartbeattable in the same shard group. Create them with these commands:
pscale database create reportsdb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create reportsdb 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 reportsdb 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, total_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_id))" \
-c "CREATE TABLE heartbeat (customer_id bigint PRIMARY KEY, beat bigint NOT NULL DEFAULT 0)" \
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": "by_customer", "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": "by_customer" },
"heartbeat": { "shard_group": "by_customer" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update reportsdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
psql -At -c "INSERT INTO orders SELECT (g % 1000) + 1, g, (g * 31) % 100000 FROM generate_series(1, 50000) g"
The insert prints INSERT 0 50000.
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, and the two replicas per shard are what this guide reads from. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete reportsdb --force --org "$PLANETSCALE_ORG".
1. Put one heartbeat row on each data shard
The heartbeat needs a row on every shard the report reads. Pick the first customer id that lands on each data shard:
for n in sh2 sh3; do
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
k=$(psql -At -v ON_ERROR_STOP=1 -c "SET __neki.shard = '$u'" -c "SELECT min(customer_id) FROM orders" | tail -1)
psql -At -c "INSERT INTO heartbeat VALUES ($k, 0)" > /dev/null
echo "$n: heartbeat key $k"
done
psql -At -c "SELECT count(*) FROM heartbeat"
Real output:
sh2: heartbeat key 2
sh3: heartbeat key 1
2
2. Read from replicas
psql -At \
-c "SET __neki.target = 'REPLICA'" \
-c "SELECT count(*), bool_and(pg_is_in_recovery()) FROM orders" \
-c "INSERT INTO heartbeat VALUES (999999, 0)" 2>&1 | grep -v -e '^SET' -e CONTEXT
Real output:
50000|t
ERROR: cannot execute INSERT: session target is not primary
pg_is_in_recovery() is true on every row, so the count came from replicas, and the session cannot write.
3. Write the report script
cat > report.sh <<'EOF'
#!/bin/bash
# usage: report.sh <max seconds to wait for fresh replicas>
max_wait=${1:-5}
# 1. Every replica must be healthy.
unhealthy=$(psql -At -c "SELECT count(*) FROM __neki.list_sidecar_statuses() WHERE type = 'replica' AND health_status <> 'healthy'")
echo "unhealthy replicas: $unhealthy; max replay_lag: $(psql -At -c "SELECT coalesce(max(replay_lag)::text, 'unknown') FROM __neki.list_sidecar_statuses() WHERE type = 'replica'")"
# 2. Bump the heartbeat on the primaries.
target=$(psql -At -c "UPDATE heartbeat SET beat = beat + 1 RETURNING beat" | grep -E '^[0-9]+$' | sort -n | head -1)
# 3. Wait for it on the replica session, then run the report there.
if [ "$unhealthy" = "0" ]; then
psql -At -v ON_ERROR_STOP=1 -v target="$target" -v max_wait="$max_wait" <<'SQL'
SET __neki.target = 'REPLICA';
SET __neki.replica_affinity = 'session';
SELECT clock_timestamp() AS started \gset
SELECT min(beat) >= :target AS fresh FROM heartbeat \gset
\if :fresh
\else
SELECT pg_sleep(0.2);
SELECT min(beat) >= :target AS fresh FROM heartbeat \gset
\endif
\if :fresh
SELECT 'replicas fresh after ' || round(extract(epoch FROM clock_timestamp() - :'started'::timestamptz) * 1000) || ' ms';
SELECT 'report on replicas: orders=' || count(*) || ' revenue_cents=' || sum(total_cents) FROM orders;
\else
SELECT 'replicas not fresh in time';
\endif
SQL
fi
EOF
chmod +x report.sh
The script checks once, waits 200 milliseconds and checks again. A production version would loop up to its deadline; this one keeps the logic visible.
4. Run the report
./report.sh 5
Real output:
unhealthy replicas: 0; max replay_lag: 00:00:00
SET
SET
replicas fresh after 158 ms
report on replicas: orders=50000 revenue_cents=2459775000
5. See a misleading freshness signal
sleep 20
psql -At -c "SET __neki.target = 'REPLICA'" -c "SELECT round(extract(epoch FROM now() - pg_last_xact_replay_timestamp())) || ' s since last replayed commit' FROM orders LIMIT 1" | tail -1
psql -At -c "SELECT max(replay_lag) FROM __neki.list_sidecar_statuses() WHERE type = 'replica'"
Real output:
22 s since last replayed commit
00:00:00
With no writes for 20 seconds, the time since the last replayed commit had grown to 22 seconds while replay_lag was zero. It measures how long the shard has been idle, not how far behind the replica is.
Verify the result
./report.sh 5 | tail -1
psql -At -c "SELECT 'report on primaries: orders=' || count(*) || ' revenue_cents=' || sum(total_cents) FROM orders"
Expected: the replica report and the primary report print the same order count and revenue (orders=50000, revenue_cents=2459775000 in this run).
How it works
Each Neki shard is a primary with replicas that replay its write-ahead log. With __neki.target = 'REPLICA', the router sends a session's reads to a replica of each shard it needs, and __neki.replica_affinity = 'session' makes the router prefer the same replica on each shard for the rest of the session, so the report reads from the replicas that passed the heartbeat check. A heartbeat written through the router lands on the primaries; once the replica session can read the new value from every heartbeat row, each replica it uses has replayed at least that far. The router settings are listed by SHOW ALL on a router connection, and the router is described on the Neki architecture page.
Common issues
replica_recency and replica_locality
__neki.replica_recency (prefer, require, off) and __neki.replica_locality (prefer, require, off) change how the router chooses among replicas. In testing, replica_recency = 'require' was accepted and reads were served normally; replica lag could not be produced on demand to observe what it rejects, so this guide does not rely on it.
pg_last_xact_replay_timestamp without a table
SELECT now() - pg_last_xact_replay_timestamp() on its own fails with not implemented: [200] Built-in function pg_last_xact_replay_timestamp is unavailable in the evaluation engine, because a statement without a table is answered by the router. Step 5 adds FROM orders to send it to a replica.
A heartbeat on only some shards
A heartbeat row on one shard proves nothing about the others. Keep one row per data shard, and add one when you add a shard.
Next steps
- Delete the database with
pscale database delete reportsdb --force --org "$PLANETSCALE_ORG". - Read the Neki architecture page for how replicas and durability policies work.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "./report.sh 5, then the same aggregate on the primaries"
expected_result: "The replica report is marked fresh and matches the primary aggregate."