Database Concurrency & Scaling interview question · Most asked
How do you choose a shard key for a multi-tenant SaaS?
Summary
Pick the entity almost every query and transaction is scoped to — usually the tenant/workspace — and co-locate related tables by it. Use many logical shards on fewer physical databases, and directory overrides for whale tenants.
Longer Answer
A key misaligned with access patterns forces fan-out queries and cross-shard transactions. Notion sharded by workspace ID into 480 logical shards over 32 physical databases; Figma separated logical from physical sharding with a routing proxy. Keep global, cross-tenant tables (users, billing) unsharded or in a separate store.
Key Points
- Shard by the scope of queries/transactions
- Co-locate related tables
- Logical vs physical shards
- Directory overrides for whales
Deep dive: Sharding Strategies
More most-asked Database Concurrency & Scaling questions
- How do you prevent overselling in a flash sale with 50,000 buyers in the first minute?
- After a deploy, logs show hundreds of 'deadlock detected' errors per hour on checkout. Walk through how you'd investigate.
- Two customers buy the last item at the same moment and both orders are confirmed. What happened, and how do you prevent it?
- Your service throws 'connection pool exhausted' errors but database CPU is only 20%. What's going on?
- Why shouldn't you call a payment API inside a database transaction?
- What's the difference between Read Committed and Repeatable Read in practice?
- Database CPU is at 85%. How do you decide what to optimise before buying a bigger server?
- A user saves their profile and the next page shows the old data. Reads go to replicas. How do you fix it?