Database Sharding Diagram: Shard Keys, Routing and Resharding

Sharding splits one database into several, each holding part of the data. It is the answer when one machine can no longer handle the writes or hold the data, and it is also one of the most expensive decisions in a system's life: queries, transactions, schema changes and operations all get harder.

This post covers when sharding is worth it, how to choose a shard key, the main sharding schemes, how queries find the right shard, and how to reshard without downtime. The diagram shows a hash-sharded order database with logical shards.

Try everything else first

Before sharding, check the cheaper options in roughly this order:

1. Fix queries and indexes. Most "database too slow" problems are a missing index or a query that reads too much.
2. Scale up. A larger machine with more memory and faster disks buys years for many systems.
3. Add read replicas for read-heavy load; the database replication post covers the lag that comes with them.
4. Cache hot reads, as in the caching strategies post.
5. Split by function: move the analytics, search or audit tables to their own databases.

Shard when writes or data size exceed what one primary can handle, even after those steps, and the trend says it will get worse.

Choosing a shard key

The shard key decides which shard a row lives on. A good key has three properties:

• Every hot query includes it. If the order list query filters by customer, customer_id lets the router send it to one shard. A query without the key has to ask every shard.
• It spreads load evenly. Many distinct values, with no single value producing a large share of traffic.
• It keeps related data together. All of a customer's orders, items and payments on one shard means transactions and joins stay local.

For a multi-tenant SaaS, the tenant ID is usually the key. For a consumer app, the user or customer ID. Beware of keys that look good but concentrate load, such as a timestamp (all new writes go to one shard) or a country code (one country dominates).

Range, hash or directory

A common hybrid: hash the key into a fixed number of logical shards (say 1,024), and keep a small directory that maps logical shards to physical hosts. Keys never move between logical shards; only whole logical shards move between hosts. Instagram's engineering team described this approach in 2012, with thousands of logical shards on far fewer database servers.

Routing queries

Something has to know where each key lives:

• In the application, through a library that hashes the key and looks up the host. Simple, but every service and language needs the same logic.
• In a proxy between the application and the databases. Vitess does this for MySQL with its VTGate proxy, and Citus does it for Postgres through a coordinator node.

Either way, cache the shard map and reload it when it changes. A stale map during a move is the classic resharding bug: one router writes to the old host after the cutover, and the row quietly disappears from the new one. Version the map and reject writes that carry an old version. Queries without the shard key fan out to all shards and merge results, which works for rare admin tasks and fails as a normal access path.

Resharding without downtime

With logical shards, adding capacity means moving some logical shards to a new host:

1. Copy the logical shards' data to the new host while the old one keeps serving.
2. Stream ongoing changes, using the database's replication or change data capture, until the copy is caught up.
3. Briefly block writes for those logical shards, let the last changes arrive, update the shard map, and resume.
4. Verify, then delete the moved data from the old host.

Vitess and Citus automate versions of this workflow. Rehearse it before you need it; resharding under load for the first time is a bad day.

IDs that work across shards

A sequence per database hands out the same IDs on every shard. Use IDs that are unique without coordination: UUIDs (version 7 keeps them roughly time-ordered, which is kinder to indexes), or Snowflake-style 64-bit IDs that combine a timestamp, a shard or machine number and a counter. Embedding the logical shard number in the ID has a bonus: given only an order ID, the router knows where to look without asking anyone.

The architecture diagram

How to read it:

• Left: the order service sends every query with a customer ID to the shard router.
• Middle: the router hashes the customer ID to one of 1,024 logical shards and finds the host in the cached shard map (dashed).
• Right: four Postgres hosts hold 256 logical shards each. Host 4 is moving its upper half to a new host 5, the resharding step in progress.
• Top right: reports that span customers read an analytics warehouse fed by change data capture, so they never fan out across shards.
• The Markdown block lists the rules everyone writing queries must follow.

The costs you accept

The multi-tenant SaaS architecture diagram shows the tenant-per-shard variant of this design, where the trade-offs below apply per tenant.

• Cross-shard transactions become distributed transactions or sagas. Design so they are rare.
• Unique constraints across shards need a global ID scheme instead of a sequence per database.
• Schema changes run on every shard, so migrations need tooling and must tolerate partial completion.
• Operations multiply: backups, upgrades and monitoring for each shard.

What to put on a sharding diagram

• The shard key and the scheme (hash, range, directory).
• Logical and physical shards, if you separate them.
• Where routing happens: library, proxy or coordinator.
• The path for queries without the key, such as a warehouse or search index.
• Resharding state, when a move is in progress.

Build it in Archboard

1. Sign in with Google or GitHub and create a diagram.
2. Add Container nodes from the C4 Model pack for the service and the shard router, and a Database for the shard map.
3. Add Postgres nodes from the Data pack for the hosts, labeled with their logical shard ranges, plus one for the new host.
4. Add a Data Warehouse from the Data pack for reports, fed by a dotted CDC edge.
5. Draw the resharding move as a dashed edge and add a Markdown block from the Core pack with the sharding rules.

The example uses 10 diagram nodes, within the Free plan.

FAQ

What is the difference between sharding and partitioning? Partitioning usually means splitting a table inside one database server. Sharding spreads the data across several servers, so each holds only part of it.

How do I choose a shard key? Pick the attribute that every hot query filters on and that keeps related data together, with many distinct values and even load, such as a tenant or customer ID.

What are logical shards? A fixed, large number of hash buckets that are mapped to fewer physical servers. Moving whole logical shards between servers avoids rehashing every key when you add capacity.

How do I run queries across all shards? Avoid them on hot paths. For reports, replicate data into a warehouse; for rare admin tasks, a fan-out query through the router is acceptable.

When should I shard? When write volume or data size exceed what a well-tuned single primary can handle and the trend continues, after indexing, scaling up, replicas, caching and functional splits.

Start free — one seat, no credit card.