System designMediumsd-g676

Subject Database index designLevel Mid–Senior~35 minCommon in Databases & SQL interviewsIndustries Technology, Software development

Question

Design the indexing strategy for an e-commerce orders table (2 billion rows, 25k writes/sec) that must serve several query shapes fast: filter by customer_id + date range, filter by status + warehouse, full-text search on product name, and a dashboard counting orders per status per hour. Adding indexes blindly is killing write throughput. How do you choose which indexes to build, order their columns, and avoid index bloat?

What a strong answer looks like

Clarify scale and constraints first. Propose a clean component breakdown, then go deep on the hard parts — data model, bottlenecks, consistency, failure modes — and name the trade-offs you are making.

Learn the concepts

Narrate your design

Loading whiteboard…

Run or narrate your approach, then ask the coach.