Database Scalability: Scenario-Based Questions

Sharding and partitioning distribute data across multiple databases or storage units to scale systems horizontally and avoid performance bottlenecks. The goal is to balance load while preserving data locality and query performance.

🧩 Definitions

• Sharding: Horizontal partitioning where rows are distributed across databases based on a shard key (e.g., user ID).
• Partitioning: Dividing data within a single database table — can be horizontal (row-based) or vertical (column-based).

🎯 Sharding Strategies

• Hash-Based Sharding: Uniform distribution but hard to range query or reshard.
• Range-Based Sharding: Intuitive partitioning by value ranges (e.g., dates, customer tiers).
• Directory-Based Sharding: Uses a lookup service to find the shard (e.g., per-tenant DB).

🛠️ Partitioning Techniques

• PostgreSQL: Native range/list/hash partitioning with partitioned tables.
• MySQL: Range and key-based partitioning on InnoDB tables.
• NoSQL: Native sharding in MongoDB, Cassandra, DynamoDB with partition keys.

✅ Best Practices

• Pick shard keys that minimize cross-shard joins and ensure even distribution.
• Use connection pooling and query routing logic to abstract sharding from app logic.
• Monitor hot shards and implement auto-rebalancing if possible.
• Plan for resharding and migration paths from the start.

🚫 Common Pitfalls

• Poor shard key choice leading to hotspots (e.g., timestamp).
• No clear resharding process or shard versioning strategy.
• Over-reliance on cross-shard transactions — poor performance and complexity.

📌 Real-World Insight

Successful sharding is about foresight and tooling. At scale, systems like Instagram, Slack, and Twitter run thousands of shards — managed by automation, service layers, and observability infrastructure.