Sharding: a decision guide

A wrong index you drop and rebuild. A wrong cache you turn off. A wrong shard key you carry for years, and fixing it is a project with a dedicated team, not a sprint task.

So this piece is less about how to do it and more about how to decide. And the first piece of advice is unpopular:

The four techniques, and three of them you should exhaust first#

Vertical partitioning splits columns. The ones that are always read together stay in one table; the big, rarely read ones (the blob, the long text, the audit JSON) go into another.

The gain: each disk page holds more useful rows, and scans get faster. It is the cheapest technique and the most forgotten. On tables with one giant text column, the gain is immediate.

Functional partitioning splits by domain. Orders in one database, catalogue in another, authentication in another. It is the natural path towards splitting into services, and it usually brings more relief than sharding, with far less pain, because each piece is still an ordinary database with ordinary transactions.

Native horizontal partitioning is what Postgres, MySQL and others offer as table partitions. You break one table into pieces inside the same database, usually by date. The query that filters by date reads only the relevant partitions. And removing old data becomes a partition DROP, which is instant, instead of a mass DELETE, which is painful.

Only after all that comes sharding, which is distributing rows across different databases.

1. Vertical partitioningcheapestSplits columns. The big, rarely read ones leave the hot table.
2. Functional partitioningby domainOrders in one database, catalogue in another. Each piece is still an ordinary database.
3. Native horizontal partitioningsame databaseTable partitions, usually by date. Removing history becomes a DROP.
4. Shardinglast resortRows across databases. Transactions, JOINs and automatic keys all go away.

Range or hash#

Within sharding, the first choice.

By range: customers A to M on one shard, N to Z on another. Or by date.

• Upside: range queries stay efficient, because neighbours sit together.
• Downside: uneven distribution. If you partition by date and everyone writes today, one shard takes the entire write load and the others sit idle.

By hash: you hash the key and distribute.

• Upside: uniform distribution almost for free.
• Downside: you lost range queries, for the same reason a hash table does not do ranges: the function exists to scatter.

The shard key: three criteria#

This is the decision that dominates all the others.

Criterion 1: does it distribute well?#

If ninety per cent of the traffic carries the same key value, you did not shard, you just added complexity. Look at the real distribution, with production data, before deciding.

Criterion 2: can your most frequent queries hit a single shard?#

If the main query has to ask every shard and merge the answers, you traded one fast query for ten slow ones plus an aggregation. That is called scatter-gather, and it is the most common way to make everything worse.

And remember tail amplification: with ten shards, your query's latency becomes the latency of the slowest of the ten.

Criterion 3: is it stable?#

A key whose value changes forces the record to move shards. Prefer something immutable. A customer identifier is good; an order status is terrible.

In practice, in a business system, the key is usually the customer or account identifier, because almost every query is "this customer's things", and that lands on a single shard.

The three problems sharding creates#

And that rarely make it into the decision meeting.

Cross-shard transactions are gone. The database guarantees ACID within a shard. Across shards, you need a saga or two phase commit. Every operation touching two customers at once (a transfer, say) became a project instead of a BEGIN/COMMIT.

Cross-shard JOINs are gone. You are going to denormalise on purpose, duplicating data, and live with the inconsistency that brings. That is not a design failure; it is the price.

Automatic primary keys are gone. A database sequence does not work distributed. You will use UUID, ULID or something Snowflake-like.

One important detail: if you use UUID version 4, purely random, inside a B-tree index, get ready for fragmentation and scattered writes. Prefer something time sortable, like ULID or UUIDv7, which keeps insertion locality.

Rebalancing and consistent hashing#

The naive way to distribute is hash of the key modulo the number of nodes. It works beautifully until you add a node.

With four nodes, the key goes to hash mod four. With five, hash mod five. Practically every key moves. If it is a cache, you invalidated everything at once. If it is a database, you need to move almost all the data.

Consistent hashing solves this. Picture a numbered circle; each node occupies several points on that circle, and each key belongs to the first node going clockwise. When you add a node, only the keys between it and the previous one change owner: roughly one in N of the keys instead of all of them.

The multiple points per node (the virtual replicas) exist to even out the distribution. With few points, bad luck leaves the distribution lopsided; with a hundred and fifty per node, it comes out well balanced.

Distributing data is not distributing load#

Even with perfectly distributed keys, traffic can be uneven.

Hot key: one specific record takes disproportionate traffic: the influencer's post, the product on sale, the customer who is ten per cent of revenue. The shard hosting that key saturates while the others sit idle.

Fixes, in order of simplicity: a cache in front to absorb reads; replicate that key with suffixes and pick one at random; for concentrated writes, split into subkeys and aggregate afterwards; and, as a last resort, give the giant customer a dedicated shard, a common practice in multi-tenant systems.

Hot shard: by design, one range concentrates the load. The classic case is partitioning by date.

And detecting this requires per-shard metrics. A dashboard showing the average across shards hides exactly the problem you are looking for. Look at the maximum and the spread.

The decision checklist#

Before sharding, answer in writing:

1. Have I exhausted indexes, caching, replicas, vertical and functional partitioning?
2. What is the key, and how does it distribute across my real data?
3. Which of my five most frequent queries stop working on a single shard?
4. Which operations now need a saga?
5. How do I rebalance when I add a shard?
6. How do I detect a hot key?