Sharding & Partitioning

In plain words

When one database machine cannot hold all the data, or cannot serve all the reads and writes, you split the data across several machines. Each piece is a shard. The rule that decides which row lives on which shard is the whole design: it decides how evenly the load lands and which queries stay cheap.

Three ways to split

Turn the skew slider up in the widget to watch range sharding pile onto one shard, then switch to hash and watch it flatten — and watch the range query get expensive at the same moment.

Choosing the shard key

This is the decision you cannot cheaply undo. A good key does three things that pull against each other:

1. Spreads writes

   Many distinct values, none of them dominant. customer_id yes; country no (one shard for the US, one for everyone else).

2. Keeps your main queries on one shard

   If the account page runs "all orders for customer 42", shard by customer and that query is one shard, every time.

3. Does not create a giant

   One customer with 40% of all orders is a shard on their own whatever you do. Know who they are.

The usual escape from the range-versus-hash trade is a compound key: hash the first part for spread, keep order inside it for locality.

-- Shard chosen by hash(customer_id); inside a shard, rows are clustered by time.
-- "All of customer 42's orders in March" = one shard, one range scan.
CREATE TABLE orders (
  customer_id  bigint      NOT NULL,   -- shard key: many values, spreads writes
  created_at   timestamptz NOT NULL,   -- sort key: keeps a customer's rows together
  order_id     bigint      NOT NULL,
  total        numeric(12, 2),
  PRIMARY KEY (customer_id, created_at, order_id)
) PARTITION BY HASH (customer_id);

function shardFor(customerId: bigint): Shard {
  const n = Number(customerId % BigInt(SHARDS.length));  // or a consistent-hash ring
  return SHARDS[n];
}

// One shard: cheap.
const orders = await shardFor().query(
  "SELECT * FROM orders WHERE customer_id = $1 AND created_at >= $2", [, "2026-03-01"]
);

// Every shard: scatter, then gather and sort. Avoid on the hot path.
const recent = (await Promise.all(SHARDS.map((s) => s.query("SELECT * FROM orders ORDER BY created_at DESC LIMIT 20"))))
  .flat().sort(byCreatedAtDesc).slice(0, 20);

Where it goes wrong

• Resharding. Going from 4 shards to 8 means moving data while still serving traffic. Plan for it on day one: start with more logical shards than machines (say 64), several per server, so growth is moving whole shards rather than splitting rows.
• Hot tenants. An even distribution of keys is not an even distribution of activity. Watch per-shard load, not just per-shard size.
• Joins. A join across shards is a scatter-gather on both sides. Denormalise (copy the columns you need) or keep the tables you join on the same shard key.
• The one query nobody thought of. Sharding by customer makes "top 10 products today" a query across every shard. Analytics gets its own copy of the data.

Take this with you

• The one idea: the shard key decides both how evenly load lands and which queries stay cheap, and it is very hard to change later.
• In an interview, contrast range vs hash, show the monotonic-key trap, and propose a compound key that fits the main query.
• At work, look at your biggest customer. If their data is one shard, that shard is your ceiling.