4 database sharding strategies. 4 completely different scaling outcomes. One wrong choice and you’re melting a primary on Black Friday.
Your Postgres orders table just crossed 500M rows.
Range scans that used to take 40ms are creeping past 800ms.
Vertical scaling is dead. You need to shard.
Here is the workload:
• orders = 500M rows, growing 3M/week
• 80% reads = “customer X’s last 30 days of orders”
• 15% reads = analytics joins on date ranges
• 5% writes = new orders (steady 400 RPS, 2x on sale days)
Which strategy do you pick?
A) Hash sharding on order_id — even distribution, no hotspots.
B) Range sharding on created_at — keeps time-series queries local.
C) Directory-based sharding — lookup table maps customer to shard.
D) Consistent hashing with virtual nodes — easy rebalancing on scale-up.
All four are real strategies you’ll find in production. But only one is the right fit for an e-commerce workload where 80% of reads are customer-centric.
One of the wrong answers is the senior engineer trap. It sounds sophisticated, shows up in every YouTube tutorial, and will quietly wreck your query latency on this exact workload.
I’ll drop the full breakdown and the correct answer in the comments.
If your team is debating a database migration, repost this — someone on your team is about to pick the wrong strategy.
What’s your answer: A, B, C, or D? 👇
#SystemDesign #Databases #PostgreSQL #BackendEngineering

You can share you thought with this squad 

Backend Developer 

A system design challenge exploring four database sharding strategies for a PostgreSQL orders table at 500M rows with a customer-centric read workload. The scenario presents hash sharding, range sharding, directory-based sharding, and consistent hashing with virtual nodes, asking readers to identify the best fit for an e-commerce workload where 80% of reads are customer-specific queries. The post teases a full breakdown in the comments without delivering the answer in the post itself.

5/30 Days System Design Questions!

Why B is wrong (Range on created_at):
Creates a hot shard by design. Every new order writes to the same shard. Your newest shard absorbs 100% of writes and 70% of reads. On Black Friday, this shard melts while others sit idle.

Why A is the trap (Hash on order_id):
The classic tutorial answer: “Hash the primary key!” It wrecks you here. A single customer’s 200 orders scatter across all shards. Every read becomes a 4-shard fan-out gated by your slowest node. P99 latency goes up.

Answer: C — Directory-based sharding ✅
Here is why the other three trick smart engineers:
Why C wins (Directory-based):
80% of reads are “customer X’s orders.” You want every order for a customer on exactly one shard. A customer-scoped read hits one node, no scatter-gather fan-out.
You keep a customer_shard_map table (cached in Redis). If shard 3 gets hot, you migrate specific heavy customers (whales) without rehashing anything. Targeted rebalancing is the superpower. Figma, Notion, and Slack do this.

Also, it would mean a lot to me if you could support my content and stay in touch 🙏
<ul>
<li>YouTube: <a href="https://www.youtube.com/@system-design-lab" target="_blank" rel="noopener nofollow">https://www.youtube.com/@system-design-lab</a></li>
<li>LinkedIn: <a href="https://www.linkedin.com/in/joud-awad/" target="_blank" rel="noopener nofollow">https://www.linkedin.com/in/joud-awad/</a></li>
<li>Medium Blog: <a href="https://joudwawad.medium.com/" target="_blank" rel="noopener nofollow">https://joudwawad.medium.com/</a></li>
<li>Substack: <a href="https://joudawad.substack.com/" target="_blank" rel="noopener nofollow">https://joudawad.substack.com/</a></li>
</ul>

Why D is wrong (Consistent hashing):
Great for uniform data, but you can’t migrate specific heavy tenants. If four whales land on the same shard, you are stuck.