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

Reference

Design an Ad Impression Table So Campaign Queries Stay Single-Shard on Neki

This guide shards an ad impression table by campaign_id on Neki, so every per-campaign report, join and spend total runs on one shard. It compares the same data sharded by impression_id, where each of those queries goes to every shard, and shows the one cost of the design: a very busy campaign makes its shard heavier than the others.

What you will build

campaigns   (campaign_id, ...)                     --+-- shard group by_campaign, xxhash(campaign_id)
impressions (campaign_id, impression_id, ...)      --+
impressions_by_id (impression_id, campaign_id ...) ----- shard group by_impression, xxhash(impression_id)

WHERE campaign_id = 7   on impressions        -> one shard
WHERE campaign_id = 7   on impressions_by_id  -> every shard

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0), psql and python3, design an ad impression table on Neki so queries for one campaign stay on one shard.

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 campaigns and impressions by campaign_id with {"type":"xxhash","columns":["campaign_id"]}, in one shard group, with PRIMARY KEY (campaign_id, impression_id) and an index on (campaign_id, served_at).
- Build a second copy of the data sharded by impression_id to compare routing. Check each query with EXPLAIN (NEKI_PLAN, COSTS OFF) and read the Route line.
- Load the data with COPY and list impression_id in the COPY column list, because COPY cannot draw an identity default.
- Report how the rows split across the two data shards, including the share held by the busiest campaign.
- 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 adserver --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

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

1. Create the tables and shard them two ways

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE campaigns (campaign_id bigint PRIMARY KEY, advertiser text NOT NULL, daily_budget_cents bigint NOT NULL)" \
  -c "CREATE TABLE impressions (campaign_id bigint NOT NULL, impression_id bigint GENERATED ALWAYS AS IDENTITY, served_at timestamptz NOT NULL, user_id bigint NOT NULL, cost_micros int NOT NULL, PRIMARY KEY (campaign_id, impression_id))" \
  -c "CREATE INDEX impressions_served_idx ON impressions (campaign_id, served_at)" \
  -c "CREATE TABLE impressions_by_id (impression_id bigint PRIMARY KEY, campaign_id bigint NOT NULL, served_at timestamptz NOT NULL, user_id bigint NOT NULL, cost_micros int NOT NULL)" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": {
    "campaign":   { "type": "xxhash", "columns": ["campaign_id"] },
    "impression": { "type": "xxhash", "columns": ["impression_id"] }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_campaign", "default_shard_index": "campaign",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
    { "uid": "by_impression", "default_shard_index": "impression",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "campaigns":         { "shard_group": "by_campaign" },
    "impressions":       { "shard_group": "by_campaign" },
    "impressions_by_id": { "shard_group": "by_impression" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update adserver main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

Real output:

applied

2. Load 30,000 impressions

python3 - <<'PY'
import random, datetime
random.seed(11)
base = datetime.datetime(2026, 10, 28)
with open("campaigns.csv", "w") as c:
    for k in range(1, 51): c.write(f"{k},advertiser{k},{100000 + k}\n")
with open("impressions.csv", "w") as a, open("impressions_by_id.csv", "w") as b:
    for i in range(1, 30001):
        camp = 1 if random.random() < 0.3 else random.randint(2, 50)
        ts = base + datetime.timedelta(seconds=random.randint(0, 86399))
        user, cost = random.randint(1, 5000), random.randint(100, 900)
        a.write(f"{camp},{i},{ts:%Y-%m-%d %H:%M:%S}+00,{user},{cost}\n")
        b.write(f"{i},{camp},{ts:%Y-%m-%d %H:%M:%S}+00,{user},{cost}\n")
PY
psql -c "\copy campaigns FROM 'campaigns.csv' WITH (FORMAT csv)"
psql -c "\copy impressions (campaign_id, impression_id, served_at, user_id, cost_micros) FROM 'impressions.csv' WITH (FORMAT csv)"
psql -c "\copy impressions_by_id FROM 'impressions_by_id.csv' WITH (FORMAT csv)"

Real output:

COPY 50
COPY 30000
COPY 30000

Campaign 1 receives about 30 percent of the traffic and the other 49 campaigns share the rest, so the data includes one hot campaign. The random generator is seeded, so the data is the same on every run.

3. Compare how queries route

route() { psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) $1" | grep -oE 'Route \[[A-Za-z]+\]' | tr '\n' ' '; }
echo "hourly spend, sharded by campaign:   $(route "SELECT date_trunc('hour', served_at), count(*), sum(cost_micros) FROM impressions WHERE campaign_id = 7 AND served_at >= '2026-10-28' GROUP BY 1")"
echo "hourly spend, sharded by impression: $(route "SELECT date_trunc('hour', served_at), count(*), sum(cost_micros) FROM impressions_by_id WHERE campaign_id = 7 AND served_at >= '2026-10-28' GROUP BY 1")"
echo "campaign joined to impressions:      $(route "SELECT c.advertiser, count(*) FROM campaigns c JOIN impressions i USING (campaign_id) WHERE c.campaign_id = 7 GROUP BY 1")"
echo "two campaigns:                       $(route "SELECT count(*) FROM impressions WHERE campaign_id IN (7, 8)")"
echo "one user across campaigns:           $(route "SELECT count(*) FROM impressions WHERE user_id = 42")"
echo "top campaigns:                       $(route "SELECT campaign_id, count(*) FROM impressions GROUP BY 1 ORDER BY 2 DESC LIMIT 3")"

Real output:

hourly spend, sharded by campaign:   Route [EqualUnique] 
hourly spend, sharded by impression: Route [Scatter] 
campaign joined to impressions:      Route [EqualUnique] 
two campaigns:                       Route [IN] 
one user across campaigns:           Route [Scatter] 
top campaigns:                       Route [Scatter] 

Filters and joins on campaign_id are one shard when the table is sharded by campaign_id, and the same query on the impression_id copy is a scatter. A lookup by user_id and a cross-campaign ranking have no campaign filter, so they scatter in both layouts.

4. Compare the work each layout does

Q="SELECT date_trunc('hour', served_at), count(*) FROM %s WHERE campaign_id = 7 AND served_at >= '2026-10-28' GROUP BY 1"
for t in impressions impressions_by_id; do
  echo "$t: $(psql -At -c "EXPLAIN (NEKI_PLAN, ANALYZE) $(printf "$Q" $t)" | grep -m1 'RemoteCalls:' | tr -s ' ')"
done

Real output:

impressions:  RemoteCalls: 1
impressions_by_id:  RemoteCalls: 2

The campaign layout needs a single call, to a single shard. The impression_id layout has to call both data shards.

5. Check how the rows split across shards

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(*) || ' impressions, ' || count(*) FILTER (WHERE campaign_id = 1) || ' from campaign 1' FROM impressions" | tail -1)"
done
psql -At -c "SELECT 'campaign ' || campaign_id || ': ' || count(*) FROM impressions GROUP BY campaign_id ORDER BY count(*) DESC LIMIT 3"

Real output:

sh2: 8677 impressions, 0 from campaign 1
sh3: 21323 impressions, 8962 from campaign 1
campaign 1: 8962
campaign 39: 477
campaign 27: 472

All of campaign 1's rows sit on one shard, so that shard holds far more rows than the other even though 49 other campaigns split between the two.

Verify the result

psql -At -c "SELECT count(*) FROM impressions"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*), sum(cost_micros) FROM impressions WHERE campaign_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*), sum(cost_micros) FROM impressions_by_id WHERE campaign_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'

Expected: 30000, Route [EqualUnique] for the table sharded by campaign, and Route [Scatter] for the table sharded by impression.

How it works

Putting campaign_id first in the primary key and in the shard index means the router can hash the value in the WHERE clause and pick one shard, and a join between campaigns and impressions on campaign_id stays inside that shard because both tables use the same shard group. An IN list counts as a multi-shard route even when its values hash to the same shard. The cost of the design is placement by campaign: a campaign's rows cannot spread over shards, so a campaign with a large share of traffic makes one shard heavier. The Neki data topology guide describes shard groups and index types.

Common issues

A busy campaign makes one shard heavier

In step 5, campaign 1 held about 30 percent of all rows and its shard held roughly 71 percent of the table. Check the row split before choosing the key, and plan to isolate the largest campaigns on their own shard; the guide on isolating a large tenant on its own Neki shard shows the mechanics.

Queries without a campaign filter scatter

Reports by user_id, and rankings across all campaigns, run on every shard in both layouts. Keep those off the hot path, or maintain a rollup table such as one row per campaign and day.

Two-column shard indexes

A data topology that declared a two-column index, {"type":"xxhash","columns":["campaign_id","slot"]}, returned an internal error from pscale branch data-topology update in testing, while the same topology with a one-column index applied. Use a one-column shard index.

Next steps

  • Delete the database with pscale database delete adserver --force --org "$PLANETSCALE_ORG".
  • Read the guide on isolating a large tenant on its own Neki shard for moving a hot campaign.
  • 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 impressions; EXPLAIN (NEKI_PLAN) of a campaign filter on each table"
  expected_result: "30000, Route [EqualUnique] for the table sharded by campaign, Route [Scatter] for the table sharded by impression"