E-commerce index strategy

DesignMedium35 min

Your task

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?

It covers

  • Composite index
  • Write amplification
  • Covering index