Sharding & Partitioning

Before sharding, squeeze the simple levers: indexes, query tuning, bigger machines, caching, and read replicas. Sharding is powerful, but it moves complexity from the database engine into your application, operations, and data model.

• Storage limit: the table, indexes, or backups no longer fit comfortably on one machine.
• Write throughput limit: replication helps reads, but every write still hits one primary.
• Maintenance limit: vacuuming, compaction, migrations, and restores become too slow on a single giant dataset.
• Blast radius: a shard failure can affect a fraction of users instead of the entire product, if the system is designed that way.

logical table: users

shard 0: users where hash(user_id) mod 4 = 0
shard 1: users where hash(user_id) mod 4 = 1
shard 2: users where hash(user_id) mod 4 = 2
shard 3: users where hash(user_id) mod 4 = 3

app router decides which physical database receives each query

The shard key decides where each record lives. It is the most important decision because changing it later means moving lots of data and rewriting query paths. A good key has high cardinality, spreads traffic evenly, and keeps data that is read together on the same shard.

Design from the common query

If the main query is "load all messages in this conversation", shard by conversation_id, not by random message_id. If the main query is "load a user profile and settings", shard byuser_id. The best key turns the hot path into a single-shard request.

function shardForUser(userId) {
  const bucket = hash(userId) % 1024;        // many logical buckets
  return shardMap[bucket];                  // bucket -> physical shard
}

SELECT * FROM orders WHERE user_id = $1;    // route only to that user's shard

A partitioning strategy maps a key to a shard. The strategy determines whether range scans are easy, whether load is balanced, and how painful it is to add capacity.

Range partitioning

Range partitioning is natural for time-series data and ordered keys. It shines when queries ask for contiguous ranges, such as logs from June. But if all writes go to "today", the newest range becomes a hotspot.

Hash partitioning

Hashing destroys order to gain even spread. It is a strong default for point lookups by user, account, or object id. The cost is that a query such as "all users created yesterday" may touch every shard unless you maintain another index or table.

Directory partitioning

A directory lets you place tenants deliberately. Big customers can get dedicated shards, small customers can share, and individual buckets can be moved during rebalancing. The directory itself must be cached, replicated, and updated safely.

Sharding assumes load is spread. Real products love to break that assumption. A celebrity account, viral post, hot product launch, or current timestamp can concentrate most traffic on one shard even when the overall cluster has plenty of capacity.

• Hot partition: one shard receives disproportionate reads or writes and becomes the bottleneck.
• Celebrity key: one logical key is so popular that hashing cannot help because all its traffic maps to one place.
• Monotonic key: keys such as increasing ids or timestamps send new writes to the same range.

normal users:
  shard = hash(user_id)

celebrity timeline reads:
  shard = hash(user_id + ':' + bucket_id)
  // duplicate or bucket the hot user's feed across N buckets
  // readers merge buckets or hit a cached fanout result

The day you add shards, data must move. That move is calledrebalancing or resharding. It is operationally risky because the system must continue serving reads and writes while ownership changes.

1. Create new shard and update metadata with a planned move.
2. Backfill existing rows for selected buckets to the new shard.
3. Dual-write or capture changes during the backfill window.
4. Verify counts, checksums, and latest change position.
5. Flip routing for those buckets to the new shard.
6. Keep old copy briefly for rollback, then delete it.

• Moving too much at once can saturate disks and networks.
• A stale router can send writes to the old owner after the move.
• Backfills must include writes that happened while the copy was running.
• Rollback plans need old data, routing metadata, and idempotent scripts.

The easiest sharded systems make most operations single-shard. The hard cases are the ones relational databases usually handled for you: joins, uniqueness, foreign keys, and transactions across unrelated keys.