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

Reference

Keep a Multi-Tenant Audit Log Sharded by Tenant with a Retention Job on Neki

This guide shards an audit log by tenant_id on Neki and runs a retention job that deletes each tenant's expired rows with one single-shard DELETE at a time. Every tenant can have its own retention window, and the job can be re-run safely.

What you will build

tenants   (tenant_id, retention_days)            --+-- shard group by_tenant, xxhash(tenant_id)
audit_log (tenant_id, event_id, created_at, ...) --+

retention.sh: for each tenant -> DELETE ... WHERE tenant_id = t AND created_at < cutoff   (one shard per statement)

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, shard a multi-tenant audit log by tenant on Neki and write a retention job.

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 tenants and audit_log by tenant_id with {"type":"xxhash","columns":["tenant_id"]}, in one shard group, with PRIMARY KEY (tenant_id, event_id) and an index on (tenant_id, created_at).
- Store a retention_days value for each tenant. The retention job reads the tenants, then runs one DELETE per tenant with tenant_id in the WHERE clause, in batches of event ids, so each statement runs on one shard.
- Compute the cutoff once per run and pass it to every statement as a literal timestamp. A DELETE ... USING join that calls now() fails with a router error, and a literal timestamp works.
- COPY into a table whose column default is an identity column needs that column in the COPY column list.
- 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 auditlog --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create auditlog 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 auditlog 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 auditlog --force --org "$PLANETSCALE_ORG".

1. Create the tables, shard them by tenant and load events

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE tenants (tenant_id bigint PRIMARY KEY, name text NOT NULL, retention_days int NOT NULL)" \
  -c "CREATE TABLE audit_log (tenant_id bigint NOT NULL, event_id bigint GENERATED ALWAYS AS IDENTITY, created_at timestamptz NOT NULL DEFAULT now(), actor text NOT NULL, action text NOT NULL, PRIMARY KEY (tenant_id, event_id))" \
  -c "CREATE INDEX audit_log_created_idx ON audit_log (tenant_id, created_at)" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": { "tenant": { "type": "xxhash", "columns": ["tenant_id"] } },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_tenant", "default_shard_index": "tenant",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "tenants":   { "shard_group": "by_tenant" },
    "audit_log": { "shard_group": "by_tenant" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update auditlog main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

export AS_OF=$(psql -At -c "SELECT now()")
psql -q -c "INSERT INTO tenants SELECT t, 'tenant' || t, CASE WHEN t % 2 = 1 THEN 30 ELSE 90 END FROM generate_series(1, 6) t"
psql -q -c "INSERT INTO audit_log (tenant_id, created_at, actor, action)
  SELECT t, '$AS_OF'::timestamptz - ((e * 7919 + t * 104729) % 120000 + 0.5) * interval '86.4 seconds', 'user' || (e % 9 + 1), 'login'
  FROM generate_series(1, 6) t, generate_series(1, 3000) e"
psql -At -c "SELECT count(*) FROM audit_log"
psql -At -c "SELECT t.retention_days || ' day tenants: ' || count(*) || ' expired rows' FROM audit_log a JOIN tenants t USING (tenant_id) WHERE a.created_at < '$AS_OF'::timestamptz - make_interval(days => t.retention_days) GROUP BY t.retention_days ORDER BY t.retention_days"

Real output:

applied
18000
30 day tenants: 6750 expired rows
90 day tenants: 2250 expired rows

The table holds 18,000 events spread over the 120 days before AS_OF. The odd-numbered tenants keep 30 days and the even-numbered tenants keep 90, so each group has a different number of rows past its window.

2. Check how the router sends audit queries

for q in "tenant_id = 3" "tenant_id = 3 AND created_at < now()" "created_at < now()"; do
  echo "$q | $(psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*) FROM audit_log WHERE $q" | grep -oE 'Route \[[A-Za-z]+\]')"
done

Real output:

tenant_id = 3 | Route [EqualUnique]
tenant_id = 3 AND created_at < now() | Route [EqualUnique]
created_at < now() | Route [Scatter]

A filter on tenant_id is one shard. A filter on time alone has no shard key, so the router sends it to every shard.

3. Write the retention job

cat > retention.sh <<'EOF'
#!/bin/bash
# retention.sh: delete expired audit rows one tenant at a time, in batches
AS_OF=${AS_OF:-$(psql -At -c "SELECT now()")}
BATCH=${BATCH:-1000}
psql -At -F' ' -c "SELECT tenant_id, retention_days FROM tenants ORDER BY tenant_id" | while read t days; do
  total=0
  while :; do
    out=$(psql -At < /dev/null -c "DELETE FROM audit_log WHERE tenant_id = $t AND event_id IN (SELECT event_id FROM audit_log WHERE tenant_id = $t AND created_at < '$AS_OF'::timestamptz - make_interval(days => $days) ORDER BY event_id LIMIT $BATCH)")
    n=${out#DELETE }
    [ "$n" -gt 0 ] || break
    total=$((total + n))
  done
  echo "tenant $t (keep ${days}d): deleted $total"
done
EOF
chmod +x retention.sh

./retention.sh

Real output:

tenant 1 (keep 30d): deleted 2251
tenant 2 (keep 90d): deleted 750
tenant 3 (keep 30d): deleted 2250
tenant 4 (keep 90d): deleted 750
tenant 5 (keep 30d): deleted 2249
tenant 6 (keep 90d): deleted 750

Each tenant's DELETE runs on that tenant's shard, in batches of 1,000 event ids, until no expired rows remain. The 30-day tenants lose more rows than the 90-day tenants because their window is shorter.

4. Run the job again

./retention.sh

Real output:

tenant 1 (keep 30d): deleted 0
tenant 2 (keep 90d): deleted 0
tenant 3 (keep 30d): deleted 0
tenant 4 (keep 90d): deleted 0
tenant 5 (keep 30d): deleted 0
tenant 6 (keep 90d): deleted 0

A second run deletes nothing, so the job is safe to re-run after a failure or on a schedule.

Verify the result

psql -At -c "SELECT count(*) FROM audit_log"
psql -At -c "SELECT count(*) FROM audit_log a JOIN tenants t USING (tenant_id) WHERE a.created_at < '$AS_OF'::timestamptz - make_interval(days => t.retention_days)"

Expected: 9000 and 0. The job deleted the 9,000 rows that were past their tenants' windows and left the other 9,000.

How it works

xxhash on tenant_id puts every row of a tenant on one shard, so a DELETE that filters on tenant_id is one single-shard statement. The job therefore never holds a statement open across shards, and a tenant with a large backlog does not delay the others. The IN (SELECT ... LIMIT) form caps each statement, which matters because a statement that runs on a shard was cancelled after 30 seconds in testing. A router session can raise that limit with SET statement_timeout. The cutoff is computed once, so every tenant is measured against the same AS_OF instant. The Neki data topology guide describes how a table is placed in a shard group.

Common issues

A scatter DELETE with a join and now()

A single DELETE FROM audit_log a USING tenants t WHERE a.tenant_id = t.tenant_id AND a.created_at < now() - make_interval(days => t.retention_days) failed with [103] router-only expressions in DELETE...USING bridge predicates are not yet supported. The same statement with a literal timestamp in place of now() ran as one Scatter route, with the join resolved on each shard. The per-tenant loop above avoids the question and keeps each statement small.

COPY with an identity column

\copy audit_log (tenant_id, created_at, actor, action) FROM ... failed with [51] COPY FROM against table "audit_log" cannot draw the default of column "event_id". List event_id in the column list and supply values, or load with INSERT ... SELECT as in step 1. Event ids you supply do not advance the identity sequence, so choose values that cannot collide with generated ones.

Loading too many rows in one INSERT

A 40,000-row INSERT ... SELECT into this table took about 28 seconds, close to the 30-second shard statement limit. Load larger volumes in several statements, or with COPY.

Next steps

  • Delete the database with pscale database delete auditlog --force --org "$PLANETSCALE_ORG".
  • Read the Neki data topology guide for placing related tables in one shard group.
  • Read the guide on declarative table partitioning inside a sharded Neki table if you want to drop whole time partitions instead of deleting rows.

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "count of remaining audit rows; count of rows older than each tenant's retention window"
  expected_result: "9000 remaining rows and 0 expired rows"