This guide shards conversations and their messages by conversation_id on Neki, so reading a thread and sending a message each run on one shard. A second table sharded by user_id holds each user's inbox, so listing a user's conversations is also a single-shard query.
What you will build
conversations (conversation_id, user_lo, user_hi, last_message_id) --+-- shard group by_conversation
messages (conversation_id, message_id, sender_id, body) --+ xxhash(conversation_id)
inbox (user_id, conversation_id, peer_id, last_message_id) ----- shard group by_user, xxhash(user_id)
conversation_id = lower user id * 2^32 + higher user id (the same id for either direction)
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, keep each direct-message conversation on one shard of a Neki database.
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 conversations and messages by conversation_id with {"type":"xxhash","columns":["conversation_id"]}, in one shard group, with PRIMARY KEY (conversation_id, message_id).
- Derive conversation_id from the two user ids (lower id times 2^32 plus higher id) and create the conversation with INSERT ... ON CONFLICT DO NOTHING, so both participants find the same row.
- Allocate message ids from conversations.last_message_id inside the same transaction that inserts the message. Concurrent senders then get unique, gapless ids.
- Keep an inbox table sharded by user_id, updated with INSERT ... ON CONFLICT DO UPDATE and GREATEST(), so listing a user's conversations does not scan every shard.
- 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.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
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 dmapp --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create dmapp 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 dmapp 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-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete dmapp --force --org "$PLANETSCALE_ORG".
1. Create the tables and shard them
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE conversations (conversation_id bigint PRIMARY KEY, user_lo bigint NOT NULL, user_hi bigint NOT NULL, last_message_id bigint NOT NULL DEFAULT 0, CHECK (user_lo < user_hi))" \
-c "CREATE TABLE messages (conversation_id bigint NOT NULL, message_id bigint NOT NULL, sender_id bigint NOT NULL, body text NOT NULL, sent_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (conversation_id, message_id))" \
-c "CREATE TABLE inbox (user_id bigint NOT NULL, conversation_id bigint NOT NULL, peer_id bigint NOT NULL, last_message_id bigint NOT NULL, PRIMARY KEY (user_id, conversation_id))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": {
"conversation": { "type": "xxhash", "columns": ["conversation_id"] },
"user": { "type": "xxhash", "columns": ["user_id"] }
},
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_conversation", "default_shard_index": "conversation",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
{ "uid": "by_user", "default_shard_index": "user",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"conversations": { "shard_group": "by_conversation" },
"messages": { "shard_group": "by_conversation" },
"inbox": { "shard_group": "by_user" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update dmapp main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
Real output:
applied
2. Create a conversation from either side
cid() { echo $(( ($1 < $2 ? $1 : $2) * 4294967296 + ($1 < $2 ? $2 : $1) )); }
conversation() {
psql -At -c "INSERT INTO conversations (conversation_id, user_lo, user_hi) VALUES ($(cid $1 $2), $(( $1 < $2 ? $1 : $2 )), $(( $1 < $2 ? $2 : $1 ))) ON CONFLICT (conversation_id) DO NOTHING"
}
conversation 42 7
conversation 7 42
echo "conversation id: $(cid 42 7)"
psql -At -c "SELECT count(*) FROM conversations"
Real output:
INSERT 0 1
INSERT 0 0
conversation id: 30064771114
1
User 42 opened the conversation and user 7 tried to open it again. Both calls compute the same conversation_id, so the second insert changed nothing and there is one row.
3. Send messages
cat > send.sh <<'EOF'
#!/bin/bash
# send.sh <conversation_id> <sender_id> <peer_id> <body>
psql -q -At -v ON_ERROR_STOP=1 -v conv="$1" -v sender="$2" -v peer="$3" -v body="$4" <<'SQL'
BEGIN;
UPDATE conversations SET last_message_id = last_message_id + 1 WHERE conversation_id = :conv RETURNING last_message_id \gset
INSERT INTO messages (conversation_id, message_id, sender_id, body) VALUES (:conv, :last_message_id, :sender, :'body');
COMMIT;
INSERT INTO inbox (user_id, conversation_id, peer_id, last_message_id) VALUES (:sender, :conv, :peer, :last_message_id)
ON CONFLICT (user_id, conversation_id) DO UPDATE SET last_message_id = GREATEST(inbox.last_message_id, EXCLUDED.last_message_id);
INSERT INTO inbox (user_id, conversation_id, peer_id, last_message_id) VALUES (:peer, :conv, :sender, :last_message_id)
ON CONFLICT (user_id, conversation_id) DO UPDATE SET last_message_id = GREATEST(inbox.last_message_id, EXCLUDED.last_message_id);
SQL
EOF
chmod +x send.sh
C=$(cid 42 7)
for i in $(seq 1 20); do
if [ $((i % 2)) = 0 ]; then ./send.sh $C 7 42 "message $i" & else ./send.sh $C 42 7 "message $i" & fi
done
wait
psql -At -c "SELECT count(*) || ' messages, ids ' || min(message_id) || ' to ' || max(message_id) || ', ' || count(DISTINCT message_id) || ' distinct' FROM messages WHERE conversation_id = $C"
psql -At -c "SELECT last_message_id FROM conversations WHERE conversation_id = $C"
Real output:
20 messages, ids 1 to 20, 20 distinct
20
Twenty senders ran at the same time. Each one took the next number from the conversation row and inserted its message in the same transaction, so the ids are unique and have no gaps. The numbers come from the order the transactions ran, not the order the loop started them.
4. Read a conversation and an inbox
route() { psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) $1" | grep -oE 'Route \[[A-Za-z]+\]' | tr '\n' ' '; }
echo "last 5 messages: $(route "SELECT message_id, body FROM messages WHERE conversation_id = $C ORDER BY message_id DESC LIMIT 5")"
echo "conversation joined to messages: $(route "SELECT c.last_message_id, m.body FROM conversations c JOIN messages m USING (conversation_id) WHERE c.conversation_id = $C")"
echo "inbox of user 7: $(route "SELECT peer_id, conversation_id, last_message_id FROM inbox WHERE user_id = 7")"
echo "conversations of user 7, by participant columns: $(route "SELECT conversation_id FROM conversations WHERE user_lo = 7 OR user_hi = 7")"
psql -At -c "SELECT user_id || ' sees peer ' || peer_id || ', last message ' || last_message_id FROM inbox ORDER BY user_id"
psql -At -c "SELECT message_id || ': ' || body FROM messages WHERE conversation_id = $C ORDER BY message_id DESC LIMIT 2"
Real output:
last 5 messages: Route [EqualUnique]
conversation joined to messages: Route [EqualUnique]
inbox of user 7: Route [EqualUnique]
conversations of user 7, by participant columns: Route [Scatter]
7 sees peer 42, last message 20
42 sees peer 7, last message 20
20: message 19
19: message 12
Reading a thread, joining the conversation row to its messages, and reading an inbox are each one shard. Finding conversations by the participant columns has no shard key and scans every shard, which is why the inbox table exists.
Verify the result
psql -At -c "SELECT count(*), min(message_id), max(message_id), count(DISTINCT message_id) FROM messages WHERE conversation_id = $C"
psql -At -c "SELECT user_id, last_message_id FROM inbox ORDER BY user_id"
Expected: 20|1|20|20, then the inbox rows 7|20 and 42|20.
How it works
conversation_id is the shard key for both the conversation row and its messages, so one conversation lives on one shard and the router can hash it from the WHERE clause. Allocating each message id from last_message_id makes the conversation row a lock that serializes senders on that shard, so ids come out in commit order without gaps. The inbox is a second copy of the pairing keyed by user_id: each user's row lands on the shard for that user, which may differ from the conversation's shard, so the inbox update runs as separate statements after the message commits. GREATEST() keeps last_message_id from moving backwards when two updates arrive out of order. The Neki data topology guide describes shard groups and index types.
Common issues
The pairing formula assumes user ids below 2^32
conversation_id is lower * 4294967296 + higher, so user ids must stay below 4,294,967,296 for two different pairs never to share an id. Use a different pairing, such as a hash stored with the pair as a unique key, if your ids are larger.
The inbox is written after the message commits
The message and the inbox rows are on different shards in general, so this guide writes them in separate statements. If a process stops between them, the inbox row is behind until the conversation's next message updates it. Re-running the two inbox statements with the last message id repairs it, because the update is idempotent.
Looking up conversations by participant scatters
WHERE user_lo = 7 OR user_hi = 7 ran as a scatter in step 4. Read the inbox table for that question.
Identity columns leave gaps
A GENERATED ... AS IDENTITY message id is not gapless on Neki, because each router reserves a block of sequence values and a restart skips the unused part of it. The last_message_id counter in the conversation row avoids that, and it is the reason this guide does not use an identity column for message_id.
Next steps
- Delete the database with
pscale database delete dmapp --force --org "$PLANETSCALE_ORG". - Read the guide on keeping a player's wallet and inventory in one transaction for the same one-shard transaction pattern.
- 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 and id range of the conversation's messages; inbox last_message_id per user"
expected_result: "20|1|20|20, then 7|20 and 42|20"