Question

Design secondary indexing for a multi-tenant e-commerce order store that is primary-keyed and partitioned by (tenant_id, order_id). Merchants need to query orders by customer_email, by status, and by a date range — none of which is the partition key — across potentially millions of orders per large tenant, with p95 under 150ms. The store does ~80k order writes/sec globally. Compare global vs local secondary indexes for this access pattern, pick one (or a mix), and explain the consistency and write-cost trade-offs.