A multi-tenant SaaS product has outgrown one database. You must shard. How do you choose the partition key, and what makes this decision so expensive to get wrong?

What the interviewer is testing

Whether you exhaust cheaper options first, and whether you understand that a shard key is close to irreversible.

First: do not shard yet

Sharding is a one-way door. Before it, in order of preference:

• Read replicas, if reads are the pressure. Cheap, reversible, no application change beyond routing.
• Caching in front of the hot read paths.
• Archiving cold data out of the operational store. Most tables are dominated by rows nobody reads.
• A bigger instance. Modern managed databases go a very long way, and an instance upgrade is an afternoon.
• Splitting by function — moving one high-volume table or bounded context to its own database. Easier to reverse than sharding and often enough.

If write throughput or dataset size still exceeds what one primary can do, shard.

Choosing the key

For multi-tenant SaaS, tenant_id is almost always right, because it satisfies the criterion that matters most: nearly every query already filters by it, so almost no query becomes a scatter-gather, and a transaction rarely needs to span tenants.

The problem it creates is skew. Tenant sizes in B2B SaaS follow a power law — the largest tenant is routinely 100× the median — so hashing tenants into shards produces one shard doing most of the work.

The mitigations, in increasing order of effort:

• Directory-based mapping. Keep a lookup of tenant to shard rather than hashing. This lets you place large tenants deliberately and move them later, and it is the single most valuable design choice here because it preserves optionality.
• Dedicated shards for the largest tenants.
• Sub-sharding a very large tenant by a secondary key, accepting that cross-shard queries within that tenant now exist.

The criteria, generally

1. Present in almost every query. Otherwise every read fans out to all shards.
2. High cardinality and even distribution, after accounting for skew in the real data.
3. Co-locates data accessed together, so transactions and joins stay within a shard.
4. Stable. A key whose value changes requires moving the row between shards, which is a distributed transaction you did not want.

What you lose

Cross-shard joins, cross-shard transactions, global auto-increment IDs, global unique constraints other than on the key, and simple ORDER BY ... LIMIT across the whole dataset. Each has a workaround; each workaround is application complexity you now own forever.

What a strong answer adds

A resharding plan stated up front. Choosing a fixed large number of logical shards — say 1,024 — and mapping many logical shards to each physical one means growing the cluster is a remapping rather than a rehash of every row. Deciding this at design time costs nothing; retrofitting it costs a migration.