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

Reference

Spread a Hot Account's Writes with a Suffixed Distribution Key

You will spread a very hot account's writes across shards by adding a small numeric suffix, called a salt, to its distribution key, and you will keep point lookups working by querying every salt bucket and merging the results. The example uses plain PostgreSQL SQL and a small Python script, so it runs against any sharded Postgres setup, including Neki, PlanetScale's sharded Postgres platform.

What You'll Build

A tiny event-tracking schema plus two query helpers:

  1. A table where each row for a hot account carries a salt value from 0 to N-1. The distribution key becomes (account_id, salt) instead of account_id alone, so a single account's writes land on several shards instead of one.
  2. A write helper that picks a salt at random.
  3. A lookup helper that reads all N buckets for the account and merges the rows, so callers still behave as if the account lived in one place.

The tradeoff is deliberate: writes get parallelism across shards, and reads pay for one extra query per salt bucket. For a hot account that is usually the right trade, because the write load is what threatens the shard.

Prerequisites

  • A PostgreSQL server you can reach with psql (any recent 14+ version works). The sandbox used here starts with no client installed, so install it first:

    sudo apt-get update && sudo apt-get install -y postgresql-client
    
  • Python 3 with the psycopg driver:

    python3 -m pip install "psycopg[binary]"
    
  • Connection details for your database. The example uses these environment variables:

    export PGHOST=localhost
    export PGPORT=5432
    export PGUSER=postgres
    export PGPASSWORD=postgres
    export PGDATABASE=appdb
    
  • N = 4 salt buckets. Any small number works; pick one that divides reasonably across your shard count.

All object names below carry a run-unique suffix (_7c41) so they never collide with anything already in the database.

Implementation

Step 1: Create the salted table

psql -c "
CREATE TABLE events_7c41 (
  id         bigserial PRIMARY KEY,
  account_id bigint NOT NULL,
  salt       smallint NOT NULL CHECK (salt BETWEEN 0 AND 3),
  payload    text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX events_7c41_account_idx ON events_7c41 (account_id, salt, created_at);
"

In a sharded deployment the distribution key is the leading column(s) of your sharding choice. With account_id alone, every write for one big tenant hits the same shard. With (account_id, salt), the four salt values fan those writes out.

Step 2: Seed data so the counts are real

Before observing anything, insert rows for one hot account and one quiet account:

psql -c "
INSERT INTO events_7c41 (account_id, salt, payload)
SELECT 9001, g % 4, 'hot-' || g FROM generate_series(1, 40) g;
INSERT INTO events_7c41 (account_id, salt, payload)
SELECT 9002, 0, 'quiet-' || g FROM generate_series(1, 5) g;
"

Step 3: Write with a random salt

# write_events.py
import os, random
import psycopg

SALT_BUCKETS = 4

def write_event(conn, account_id: int, payload: str) -> int:
    salt = random.randrange(SALT_BUCKETS)
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO events_7c41 (account_id, salt, payload) VALUES (%s, %s, %s) RETURNING id",
            (account_id, salt, payload),
        )
        return cur.fetchone()[0]

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    ids = [write_event(conn, 9001, f"run-{i}") for i in range(8)]
    conn.commit()
    print(f"inserted {len(ids)} rows")
export DATABASE_URL="host=$PGHOST port=$PGPORT dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD"
python3 write_events.py

Observed output:

inserted 8 rows

Step 4: Look up all buckets and merge

# read_events.py
import os
import psycopg

SALT_BUCKETS = 4

def read_events(conn, account_id: int, limit: int = 20):
    placeholders = ",".join(["%s"] * SALT_BUCKETS)
    sql = f"""
      SELECT id, salt, payload, created_at
      FROM events_7c41
      WHERE account_id = %s AND salt IN ({placeholders})
      ORDER BY created_at DESC
      LIMIT %s
    """
    with conn.cursor() as cur:
        cur.execute(sql, (account_id, *range(SALT_BUCKETS), limit))
        return cur.fetchall()

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    rows = read_events(conn, 9001)
    print(f"fetched {len(rows)} rows for account 9001")
python3 read_events.py

Observed output:

fetched 20 rows for account 9001

Complete Example

Save both snippets above as write_events.py and read_events.py, then run the whole flow end to end:

set -e
export PGHOST=localhost PGPORT=5432 PGUSER=postgres PGPASSWORD=postgres PGDATABASE=appdb
export DATABASE_URL="host=$PGHOST port=$PGPORT dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD"

psql -c "
CREATE TABLE events_7c41 (
  id         bigserial PRIMARY KEY,
  account_id bigint NOT NULL,
  salt       smallint NOT NULL CHECK (salt BETWEEN 0 AND 3),
  payload    text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX events_7c41_account_idx ON events_7c41 (account_id, salt, created_at);
INSERT INTO events_7c41 (account_id, salt, payload)
SELECT 9001, g % 4, 'hot-' || g FROM generate_series(1, 40) g;
INSERT INTO events_7c41 (account_id, salt, payload)
SELECT 9002, 0, 'quiet-' || g FROM generate_series(1, 5) g;
"
python3 write_events.py
python3 read_events.py
psql -c "SELECT salt, count(*) FROM events_7c41 WHERE account_id = 9001 GROUP BY salt ORDER BY salt;"

Observed output:

inserted 8 rows
fetched 20 rows for account 9001
 salt | count
------+-------
    0 |    12
    1 |    12
    2 |    12
    3 |    12
(4 rows)

The counts are even because the seed used g % 4 and the writer picks salts uniformly at random; with only eight appended rows the per-bucket totals can differ by a row or two, which is fine. The point is that no single bucket holds everything.

Cleanup

Drop everything this walkthrough created so the next run starts clean:

psql -c "DROP TABLE events_7c41;"

How It Works

Why the suffix spreads writes. A sharder routes by hashing or ranging the distribution key. Changing the key from account_id to (account_id, salt) multiplies one routing identity into N, so one tenant's insert stream is split across N shards. This is the classic salting pattern for hot keys, and it is the application-level answer when a single tenant outgrows a shard. On a platform like Neki you can also split a hot shard online without downtime, but salting fixes the cause at the schema level: the tenant itself is the hotspot, not just one shard.

Why lookups still work. Every row for the account is still identifiable by account_id; the salt only adds routing entropy. The read helper queries all buckets in one statement with salt IN (0, 1, 2, 3), so callers see a single merged result. The index on (account_id, salt, created_at) keeps each bucket's scan cheap.

Edge cases to plan for.

  • Aggregations that must be exact need to touch every bucket; a LIMIT over the merged set is correct here because the query covers all salts, but a per-bucket LIMIT N merged in application code would not be.
  • Uniqueness across buckets is hard: a UNIQUE constraint on a payload column cannot span shards. Keep global uniqueness on a key that includes the salt, or generate IDs centrally (the bigserial here is per-shard).
  • Choosing N: too few buckets leaves the account hot; too many multiplies read fan-out. Four to sixteen is a common range. Changing N later means rewriting rows, so pick with headroom.
  • Only salt the accounts that need it. Quiet accounts pay a fan-out tax on reads for no benefit, so apply the suffix selectively, for example for accounts whose write rate crosses a threshold.

Conclusion

Adding a salt suffix to the distribution key turns one scorching routing identity into several, spreading a hot account's writes across shards, while a simple all-buckets query keeps lookups transparent to callers. Combine this schema-level fix with a platform that can rebalance shards online, such as Neki, and a runaway tenant stops being an incident. Start with a handful of buckets, salt only the accounts that need it, and measure the read fan-out before widening it.

verification:
  status: unverified
  tested_at: null
  product_version: null
  command: null
  expected_result: null