Sharding

// "Newest 20 posts" with no shard key in the predicate. Each shard must
// return 20 rows, because all 20 winners can live on one shard; the merge
// then discards most of what it fetched. Cost grows with the shard count,
// which is why a feed usually gets its own index rather than this query.
async function newestPosts(pools, limit = 20) {
  const sql = `SELECT id, author_id, created_at
                 FROM posts
                ORDER BY created_at DESC, id DESC
                LIMIT $1`;
  const results = await Promise.all(pools.map((p) => p.query(sql, [limit])));
  return results
    .flatMap((r) => r.rows)
    // Same tie-break as the SQL, or the merged order is not deterministic.
    .sort((a, b) => b.created_at - a.created_at || (a.id < b.id ? 1 : -1))
    .slice(0, limit);
}

// Page 50 is far worse: OFFSET cannot be pushed down, so every shard would
// have to return offset + limit rows. Use a keyset cursor instead -- pass the
// last (created_at, id) you showed and add
//   WHERE (created_at, id) < ($2, $3)
// so each shard still returns at most `limit` rows however deep you go.

// mongosh. The same collection, sharded two different ways.

// Ranged: documents with nearby userId values sit together, so a scan over a
// range of users touches one shard. A monotonically increasing shard key
// sends every insert to whichever shard holds the top range.
sh.shardCollection("app.events", { userId: 1 });

// Hashed: MongoDB hashes the key before placing the document, so inserts
// spread evenly and range scans become scatter-gather across every shard.
sh.shardCollection("app.events", { userId: "hashed" });

// Compound with a hashed prefix: spread across shards by user, ordered by
// time within one user. Usually the shape you actually want.
sh.shardCollection("app.events", { userId: "hashed", ts: 1 });

// The default range (chunk) size is 128 MB, settable from 1 to 1024 MB.
// From MongoDB 6.0 a cluster only splits a range when it has to migrate one,
// so an on-disk range can be far larger than the configured size and that
// alone is not a symptom of anything.

import { createHash } from 'node:crypto';

// 1. SERIAL and AUTO_INCREMENT are per shard, so ids collide across shards.
//    A snowflake id is generated locally, fits in 64 bits and still sorts by
//    time. This is Discord's layout: 42 bits of millisecond timestamp, then
//    5 bits of worker id, 5 of process id, 12 of increment.
const DISCORD_EPOCH = 1420070400000n; // 2015-01-01T00:00:00Z
let increment = 0n;

function nextId(workerId, processId) {
  const ms = BigInt(Date.now()) - DISCORD_EPOCH;
  increment = (increment + 1n) & 0xfffn; // 12 bits, wraps within a millisecond
  return (ms << 22n)
    | ((BigInt(workerId) & 0x1fn) << 17n)
    | ((BigInt(processId) & 0x1fn) << 12n)
    | increment;
}

// 2. A UNIQUE index is unique within one shard only. To keep emails globally
//    unique while the data is sharded by user_id, give the email its own
//    table sharded by email, and claim it before writing the user row.
async function claimEmail(emailPool, email, userId) {
  const { rowCount } = await emailPool.query(
    `INSERT INTO email_owners (email, user_id) VALUES ($1, $2)
     ON CONFLICT DO NOTHING`,
    [email.toLowerCase(), userId],
  );
  if (rowCount === 0) throw new Error('email already taken');
}