Sharding Patterns Compared: What Actually Fits Your Data

Splitting data so each customer or organization's data lives in a specific shard maps naturally onto how most B2B products already query data, since almost every request is already scoped to one tenant. The tradeoff shows up with uneven tenant sizes: a handful of large customers can outgrow a single shard while hundreds of small ones sit mostly idle on others, which means you'll eventually need a plan for splitting an oversized tenant or migrating it to dedicated infrastructure.

Splitting data by a range, dates, alphabetical customer names, sequential IDs, is straightforward to reason about and makes range queries efficient, since related data tends to sit physically close together. The failure mode is hot spotting: if new data is always written to the most recent range, that one shard absorbs a disproportionate share of write load while older shards go quiet, which defeats the purpose of spreading load in the first place. This pattern works best when you can predict and actively manage how ranges grow, not when growth is unpredictable.

Hashing a key, a user ID or an order ID, to pick a shard distributes both storage and write load close to evenly regardless of how the underlying data grows, which solves the hot spotting problem range sharding runs into. The cost is that any query needing a range or an ordered scan across the sharded key now has to fan out across every shard and merge results, since hashing intentionally scatters related values. This pattern fits access patterns that are almost always single key lookups, and fits poorly with anything that wants sequential or range access.

Splitting by function, keeping your write heavy transactional tables on one database and read heavy analytics or reporting data on another, addresses a different problem: contention between very different workloads competing for the same resources. This doesn't solve the scaling problem within either workload on its own, and it's often combined with one of the other three patterns applied within a given functional split once that split alone stops being enough.

Each shard is a separate access surface that needs its own authentication, connection credentials, and audit logging, and a query spanning shards needs a coordination layer that itself becomes a piece of infrastructure worth securing carefully, since it typically has broader access than any single shard does on its own. Map out how access control and audit logging will work across shards before you commit to a pattern, not after the migration, since retrofitting consistent access control across an already sharded system is considerably harder than designing it in from the start. Give that coordination layer the same scrutiny you'd give any service holding broad read access, since it's effectively a new privileged component.

The right pattern follows directly from how your application actually queries data: mostly single tenant scoped, mostly single key lookups, mostly range scans, or a workload split. Write down your top ten most frequent query patterns before choosing, and test the candidate sharding scheme against those specific patterns rather than against a generic benchmark, since the pattern that wins on paper can still fail badly against your particular access shape. Revisit the choice if your query patterns shift meaningfully as the product grows, rather than assuming the original decision holds forever.

For example, suppose a B2B invoicing product lists its ten most frequent queries and finds that nearly all are scoped to one customer, with only an occasional report scanning by date. Tenant sharding fits the first group, and the date report can run against a separate read copy. A team that picked hash sharding because it spreads load evenly would then pay a fan-out cost on almost every report. The common mistake is choosing on theory. Test the candidate against your own list, and note which queries would cross shards.

How to match a pattern to your access pattern:

• Choose tenant sharding when almost every request is scoped to one customer, and plan ahead for a tenant that outgrows its shard.
• Choose range sharding when growth is predictable and range queries matter, and watch for hot spotting on the newest range.
• Choose hash sharding for mostly single key lookups, accepting that range queries must fan out across every shard.
• Choose functional sharding to separate write heavy transactional tables from read heavy analytics data.
• Map access control and audit logging across shards before committing to any pattern.