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

Reference

Reserve Flash-Sale Inventory Without Overselling Hot Rows on Neki

This guide reserves limited stock for a flash sale on Neki with one conditional UPDATE ... WHERE stock > 0 RETURNING, so stock never goes below zero however many buyers arrive together. It then splits one product's stock across several rows that land on different shards, so a single hot row is not the only place buyers queue.

What you will build

sale_stock (sku_id, stock CHECK >= 0) + reservations (sku_id, buyer_id)     -> shard group by_sku
UPDATE sale_stock SET stock = stock - 1 WHERE sku_id = 1 AND stock > 0 RETURNING stock
  row returned -> INSERT reservation        no row -> sold out

sale_stock_buckets ('sku1:0' ... 'sku1:7') + bucket_reservations            -> shard group by_bucket
  each buyer tries a random bucket, then the next, until one has stock

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0), psql and python3 with psycopg, reserve flash-sale stock on Neki without overselling.

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.
- Keep stock in a row with CHECK (stock >= 0). Reserve with UPDATE ... SET stock = stock - 1 WHERE sku_id = $1 AND stock > 0 RETURNING stock, and insert the reservation in the same transaction only when a row came back. Shard the stock and reservation tables by sku_id.
- Run the buyers from a bounded pool of workers. With 200 simultaneous connections on one hot row, most connections failed with "connection pool timed out", so use 16 workers and treat that error as retryable.
- To spread one product over several shards, store its stock in bucket rows keyed by a text bucket_key such as sku1:3, shard that table by bucket_key, and have each buyer try buckets in turn.
- 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 with psycopg 3.3.6:

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
pip install "psycopg[binary]==3.3.6"
  • A Neki database with three shards. Create it with these commands:
pscale database create flashsale --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

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

1. Create the tables and stock

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE sale_stock (sku_id bigint PRIMARY KEY, stock int NOT NULL CHECK (stock >= 0))" \
  -c "CREATE TABLE reservations (sku_id bigint NOT NULL, buyer_id bigint NOT NULL, reserved_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (sku_id, buyer_id))" \
  -c "CREATE TABLE sale_stock_buckets (bucket_key text PRIMARY KEY, sku_id bigint NOT NULL, stock int NOT NULL CHECK (stock >= 0))" \
  -c "CREATE TABLE bucket_reservations (bucket_key text NOT NULL, buyer_id bigint NOT NULL, PRIMARY KEY (bucket_key, buyer_id))" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": {
    "sku":    { "type": "xxhash", "columns": ["sku_id"] },
    "bucket": { "type": "xxhash", "columns": ["bucket_key"] }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_sku", "default_shard_index": "sku",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
    { "uid": "by_bucket", "default_shard_index": "bucket",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "sale_stock":          { "shard_group": "by_sku" },
    "reservations":        { "shard_group": "by_sku" },
    "sale_stock_buckets":  { "shard_group": "by_bucket" },
    "bucket_reservations": { "shard_group": "by_bucket" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update flashsale main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

psql -q -c "INSERT INTO sale_stock VALUES (1, 50)"
psql -q -c "INSERT INTO sale_stock_buckets SELECT 'sku1:' || b, 1, CASE WHEN b < 2 THEN 7 ELSE 6 END FROM generate_series(0, 7) b"
psql -At -c "SELECT stock FROM sale_stock WHERE sku_id = 1" -c "SELECT sum(stock) FROM sale_stock_buckets"
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(*) || ' buckets, ' || sum(stock) || ' units' FROM sale_stock_buckets" | tail -1)"
done

Real output:

applied
50
50
sh2: 4 buckets, 25 units
sh3: 4 buckets, 25 units

Product 1 starts with 50 units, once as a single row and once split into eight bucket rows of 6 or 7 units. The eight bucket keys hash to both data shards.

2. Write the buyers

cat > sale.py <<'EOF'
import psycopg, sys, threading, collections, random
from concurrent.futures import ThreadPoolExecutor

mode, workers, buyers = sys.argv[1], int(sys.argv[2]), int(sys.argv[3])
BUCKETS = 8
outcome, errors, lock = collections.Counter(), collections.Counter(), threading.Lock()

def buy_single(c, buyer):
    with c.transaction():
        row = c.execute("UPDATE sale_stock SET stock = stock - 1 WHERE sku_id = 1 AND stock > 0 RETURNING stock").fetchone()
        if row is None:
            return "sold_out"
        c.execute("INSERT INTO reservations (sku_id, buyer_id) VALUES (1, %s)", (buyer,))
        return "reserved"

def buy_bucketed(c, buyer):
    start = random.randrange(BUCKETS)
    for k in range(BUCKETS):
        key = f"sku1:{(start + k) % BUCKETS}"
        with c.transaction():
            row = c.execute("UPDATE sale_stock_buckets SET stock = stock - 1 WHERE bucket_key = %s AND stock > 0 RETURNING stock", (key,)).fetchone()
            if row is not None:
                c.execute("INSERT INTO bucket_reservations (bucket_key, buyer_id) VALUES (%s, %s)", (key, buyer))
                return "reserved"
    return "sold_out"

def buyer(b):
    try:
        with psycopg.connect(autocommit=True) as c:
            result = (buy_single if mode == "single" else buy_bucketed)(c, b)
        with lock: outcome[result] += 1
    except Exception as e:
        with lock: errors[str(e).splitlines()[0][:60]] += 1

with ThreadPoolExecutor(workers) as pool:
    list(pool.map(buyer, range(1, buyers + 1)))
print(" ".join(f"{k}={v}" for k, v in sorted(outcome.items())), "| errors:", dict(errors) or "none")
EOF

python3 sale.py single 16 200
psql -At -c "SELECT stock FROM sale_stock WHERE sku_id = 1" -c "SELECT count(*) FROM reservations WHERE sku_id = 1"

Real output:

reserved=50 sold_out=150 | errors: none
0
50

Two hundred buyers competed for 50 units. Exactly 50 reserved, 150 found the product sold out, and the stock row ended at 0.

3. Check the constraint as a backstop

psql -At -c "UPDATE sale_stock SET stock = stock - 1 WHERE sku_id = 1" 2>&1 | grep -oE 'violates check constraint "[a-z_]+"'

Real output:

violates check constraint "sale_stock_stock_check"

The stock is 0 now, and an unconditional decrement fails the CHECK, so a bug in the reservation statement cannot oversell silently.

4. Reserve from bucket rows spread over shards

python3 sale.py buckets 16 200
psql -At -c "SELECT sum(stock) FROM sale_stock_buckets" -c "SELECT count(*) FROM bucket_reservations"
psql -At -F' ' -c "SELECT bucket_key, count(*) FROM bucket_reservations GROUP BY bucket_key ORDER BY bucket_key"

Real output:

reserved=50 sold_out=150 | errors: none
0
50
sku1:0 7
sku1:1 7
sku1:2 6
sku1:3 6
sku1:4 6
sku1:5 6
sku1:6 6
sku1:7 6

The same 200 buyers reserved exactly the 50 units again, and every bucket was drained to its starting stock of 6 or 7.

Verify the result

psql -At -c "SELECT stock FROM sale_stock WHERE sku_id = 1" -c "SELECT count(*) FROM reservations" \
        -c "SELECT sum(stock) FROM sale_stock_buckets" -c "SELECT count(*) FROM bucket_reservations"

Expected: 0, 50, 0, 50. Reservations equal the starting stock and nothing went below zero, in both layouts.

How it works

UPDATE ... WHERE stock > 0 RETURNING takes the row lock, checks the stock and decrements it in one statement, so concurrent buyers on the same row run one after another on the shard and each sees the result of the previous one. Both tables are sharded by sku_id, so the stock update and the reservation insert are one single-shard transaction. A hot product is still one row on one shard. Splitting its stock into bucket rows with distinct text keys hashes the rows across both data shards, and buyers spread over eight row locks. The Neki data topology guide describes how a shard key is hashed and placed.

Common issues

Hundreds of simultaneous connections on one hot row

With all 200 buyers running at once against the same row, 145 of them failed with connection pool timed out in one run and 149 in another. The reservations that did succeed were still correct: 50 reserved and no oversell. The failed buyers got no reservation, so treat that error as retryable and cap the workers. Runs with 16 and 32 workers had no errors, and a run with 64 workers had 15.

Sold-out buyers scan every bucket

A buyer who finds the product sold out in the bucket layout runs one transaction per bucket before giving up. In testing the 200-buyer bucket run took longer than the single-row run (about 27 seconds against 18), so use buckets only when a single row is the limit.

One row per buyer

reservations has PRIMARY KEY (sku_id, buyer_id), so it holds at most one reservation per buyer and product. Change the key if buyers may reserve more than one unit.

Next steps

  • Delete the database with pscale database delete flashsale --force --org "$PLANETSCALE_ORG".
  • Read the guide on allocating ticketing seats without double-booking for reserving specific rows rather than a count.
  • 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: "remaining stock and reservation count for the single-row layout and for the bucket layout"
  expected_result: "0, 50, 0, 50"