Database Sharding: Shard Keys, Routing & Resharding

Q: What's the difference between sharding and partitioning?

Partitioning splits a table into pieces on one database server (Postgres declarative partitioning, MySQL partitions) — it improves pruning and maintenance but shares the same CPU, RAM, and disk. Sharding puts the pieces on different servers, which is what actually scales writes.

Database Sharding

Sharding is horizontal partitioning across machines: you split one logical database into many physical databases ("shards"), each holding a disjoint subset of the rows, and route every query to the shard that owns the data. It is how you scale writes past what a single primary can absorb.

It is also the most expensive architectural decision in the data layer, and the hardest to reverse. Sharding gives up the two things that make relational databases pleasant — cross-table joins over the whole dataset, and multi-row ACID transactions — in exchange for near-linear write scaling. Exhaust the cheaper options first. Most teams that shard early did not need to; most that needed it knew for months in advance.

TL;DR

Sharding splits rows across machines; partitioning splits them across tables on one machine. Different problems.
Shard when writes or working-set size exceed one primary — not for read load, which replicas and caching solve far more cheaply.
The shard key is the whole design. Pick one that appears in nearly every query and distributes evenly; changing it later means rewriting your data layout.
Hash routing spreads load evenly but kills range scans; range routing preserves them but creates hotspots; directory routing is flexible but adds a lookup you must keep available.
Cross-shard joins, transactions, and unique constraints stop working. Design so the common paths are single-shard.
Use consistent hashing or a virtual-bucket layer so resharding moves a fraction of the data instead of all of it.

Quick Example

Application-level routing with a hashed shard key:

The indirection through virtual buckets is the important part: adding a ninth shard remaps a few buckets rather than rehashing every row.

Core Concepts

Sharding vs. partitioning vs. replication

The usual correct order is: index and tune → cache → read replicas → vertical split by service → partition → then shard.

The shard key

Every row belongs to exactly one shard, determined by its shard key. A good key satisfies four properties:

Present in nearly every query. A query without the key must fan out to all shards.
High cardinality. country_code gives you ~200 possible values and therefore ~200 hotspots.
Even distribution. No single value should hold a disproportionate share of the data.
Stable. Changing a row's shard key means deleting it from one shard and inserting it into another — a cross-shard move.

Common good keys: tenant_id / org_id (B2B SaaS — natural isolation boundary), user_id (consumer apps), (tenant_id, entity_id) composites. Common bad keys: auto-increment id (all new writes land on the last shard under range routing), created_at (same, plus the newest shard takes all traffic), low-cardinality enums.

💡 Tip: In multi-tenant SaaS, tenant_id is almost always the right key. Tenants rarely query across each other, so nearly every query is single-shard by construction — and it composes neatly with row-level security.

Routing strategies

Hash-based — shard = hash(key) % N. Even distribution, no hotspots, no range scans, and naive modulo rehashes almost everything when N changes.

Range-based — contiguous key ranges per shard. Range scans stay efficient and rebalancing is a range split, but sequential keys create a permanent write hotspot on the last shard.

Directory-based — an explicit lookup table mapping key → shard. Maximum flexibility (pin a whale tenant to its own hardware, migrate one tenant at a time) at the cost of a lookup on the hot path that must be cached and highly available.

Geographic — shard by region for data-residency law and latency. Often layered on top of one of the others.

Consistent hashing

Consistent hashing maps both keys and shards onto a ring so that adding or removing a shard relocates roughly 1/N of the keys rather than nearly all of them. Virtual nodes (many ring positions per physical shard) smooth out the distribution.

The virtual-bucket variant in the Quick Example achieves the same goal with simpler operations: hash into a fixed large number of buckets, then maintain a bucket → shard map. Most application-level sharding uses this.

What You Give Up

Cross-shard joins

A join is only possible within one shard. Cross-shard "joins" become application-level work:

Co-location is the main defense: shard related tables by the same key so they land together.

Cross-shard transactions

There is no BEGIN spanning two shards. Options, in increasing order of complexity:

Avoid it — design so multi-row writes stay inside one shard. This is worth real schema effort.
Saga pattern — a sequence of local transactions with compensating actions on failure. Eventual consistency, no distributed locks.
Two-phase commit — real atomicity, but a coordinator that can block every participant, poor availability, and latency proportional to the slowest shard. Rarely worth it.

Global constraints and IDs

UNIQUE (email) cannot be enforced across shards by the database. Either make the constraint tenant-scoped (UNIQUE (tenant_id, email) — usually the honest requirement), or maintain a separate global uniqueness service.

Auto-increment IDs also break. Use UUIDv7 or a Snowflake-style ID (timestamp + shard ID + sequence) so IDs are globally unique, roughly time-ordered, and index-friendly:

Fan-out queries

Any query without the shard key hits every shard. Run them concurrently, cap the per-shard cost, and accept that p99 latency is now the slowest shard's latency — a single degraded shard drags every fan-out query with it.

Better: keep a secondary index for these access paths in Elasticsearch or a denormalized read model, and leave the shards for keyed access.

Resharding

Adding shards to a live system is the hardest ongoing cost of sharding. The standard playbook, with no downtime:

Two things make this survivable:

Split, don't rehash. With virtual buckets you move whole buckets; only the affected keys are touched.
Double the shard count. Going 4 → 8 means each old shard donates exactly half its buckets, and routing is a one-line map change.

Managed systems automate much of this: Vitess for MySQL, Citus for PostgreSQL, and native rebalancing in MongoDB, Cassandra, and DynamoDB.

Hot shards

Even with a good key, one tenant will eventually outgrow a shard. Responses, in order of preference:

Pin the whale. Directory routing lets you move one tenant onto dedicated hardware.
Sub-shard the key. (tenant_id, hash(user_id) % 16) spreads a single tenant across 16 buckets — at the cost of making tenant-wide queries fan out.
Cache aggressively in front of the hot shard.

Alternatives to Sharding

Ordered by how much cheaper they are:

Distributed SQL deserves emphasis: CockroachDB, Google Cloud Spanner, TiDB, and Yugabyte shard automatically while preserving SQL joins and ACID transactions. You pay in latency for cross-range transactions and in dollars, but you keep the programming model. For most teams facing a shard decision in 2026, evaluating distributed SQL first is the right move.

Best Practices

Prove you need it with numbers

Write down the metric that forced the decision — sustained write IOPS at the primary's ceiling, working set exceeding RAM by 5×, storage past the largest instance. If you can't state the number, you're sharding on vibes.

Put routing behind one layer, from day one

Every query must go through a single function that resolves the shard. Scattered if tenant in (...) routing is how sharding becomes permanent.

Make the shard key non-negotiable in the schema

Include it in every table's primary key and every foreign key. A table without the shard key is a future fan-out query.

Instrument per-shard, not just in aggregate

Aggregate dashboards hide the hot shard that's about to fall over. Emit shard ID as a label on latency, error, connection-pool, and disk metrics. See Monitoring and Metrics.

Rehearse resharding before you need it

Run the dual-write → backfill → cutover drill in staging on realistic data volume. Doing it for the first time during a capacity emergency is how you lose data.

Keep enough shards to split cleanly

Starting with 2 shards means your first growth event is a full rebalance. Starting with 8 (or 1024 virtual buckets over 4 physical shards) means growth is a map edit.

Common Mistakes

Sharding to solve a read problem

Choosing a monotonic shard key

Naive modulo routing

Queries that forget the shard key

Assuming cross-shard transactions still work

FAQ

When should I shard?

When a single primary can no longer absorb your write throughput, your working set no longer fits in the largest affordable instance's RAM, or your dataset exceeds the largest available volume — and you've already done indexing, caching, replicas, and partitioning. For most applications that threshold is far higher than expected: a well-tuned Postgres on modern hardware handles tens of thousands of writes per second and multi-terabyte datasets.

What's the difference between sharding and partitioning?

Partitioning splits a table into pieces on one database server (Postgres declarative partitioning, MySQL partitions) — it improves pruning and maintenance but shares the same CPU, RAM, and disk. Sharding puts the pieces on different servers, which is what actually scales writes.

Can I shard PostgreSQL?

Yes, three ways: application-level routing (most control, most code), the Citus extension (distributed tables with a coordinator, largely transparent), or foreign data wrappers (workable, rarely the best choice). Many teams instead move to a distributed SQL engine to get sharding without owning the routing. See PostgreSQL.

How do I generate unique IDs across shards?

UUIDv7 (time-ordered, index-friendly, no coordination) or a Snowflake-style 64-bit ID combining timestamp, node ID, and sequence. Avoid random UUIDv4 as a primary key on large tables — the random insertion order fragments B-tree indexes.

What happens to my analytics queries?

They break, and that's expected. Cross-shard aggregation belongs in an analytics system, not the OLTP shards: stream changes out via change data capture into a warehouse or lakehouse and query there.

Is sharding reversible?

Technically yes — consolidate shards back onto one machine — but by then your application code, schema, and every query assume the shard key. Budget the same effort as the original migration.