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:
- A table where each row for a hot account carries a
saltvalue from0toN-1. The distribution key becomes(account_id, salt)instead ofaccount_idalone, so a single account's writes land on several shards instead of one. - A write helper that picks a salt at random.
- A lookup helper that reads all
Nbuckets 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-clientPython 3 with the
psycopgdriver: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=appdbN = 4salt 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
LIMITover the merged set is correct here because the query covers all salts, but a per-bucketLIMIT Nmerged in application code would not be. - Uniqueness across buckets is hard: a
UNIQUEconstraint on a payload column cannot span shards. Keep global uniqueness on a key that includes the salt, or generate IDs centrally (thebigserialhere 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. ChangingNlater 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