Index Strategy for an 85 Million Order Collection
An order management system stores 85 million orders in a single orders collection; documents average 4 KB. The service sustains about 500 order inserts and updates per second. Three query patterns dominate. (A) Customer portal: find orders for one customer with status SHIPPED, sorted by createdAt descending, limit 20. (B) Operations dashboard: find status PENDING, optionally filtered by a createdAt range, sorted by createdAt ascending, limit 50. (C) Finance reporting: aggregate orders by region for a createdAt range and sum totals. Existing indexes are single-field: status, customerId, and createdAt. The customer query shows a SORT stage in explain(); the dashboard scans far more documents than it returns; finance reporting is slow. The team wants fewer, better indexes and is worried about write overhead.
1. Design indexes for query patterns A and B. Explain the field order using the equality-sort-range rule and identify which existing indexes become redundant.
Read the worked answer
Pattern A has equality on customerId and status, then a sort on createdAt descending. Put equality fields first and the sort field last: { customerId: 1, status: 1, createdAt: -1 }. customerId is the high-cardinality equality field, so placing it first makes the index useful for customer-only queries and narrows the scan quickly. status remains an equality field, and createdAt supports the sort without an in-memory SORT stage.
Pattern B has equality on status, an optional range on createdAt, and a sort on createdAt ascending. Use { status: 1, createdAt: 1 }. Because the range and sort are on the same field, the index scan returns documents in createdAt order, satisfying the sort while applying the range. If the date range is omitted, the prefix still works.
Redundancy: { status: 1 } is a prefix of the B index, and { customerId: 1 } is a prefix of the A index, so both can be dropped. Keep { createdAt: 1 } only if there is a genuine global date-range query with no leading equality field; otherwise it adds write cost without serving the three stated patterns. The finance query still needs its own index, discussed next.
2. How would you support finance reporting C, and what are the tradeoffs of adding a covering index or partial index?
Read the worked answer
Pattern C filters by region equality and a createdAt range, groups by region, and sums total. Create { region: 1, createdAt: 1 }. This narrows the scan to the region and date window. If finance runs this often and can tolerate a larger index, { region: 1, createdAt: 1, total: 1 } can cover the aggregation so the server does not fetch each 4 KB document. That is a real I/O win, but the index is wider and adds write cost on every order update, so it should be justified by reporting frequency or run against a secondary.
For pattern B, a partial index can reduce size and write cost if PENDING is a small fraction of orders, for example 2 percent. Use { status: 1, createdAt: 1 } with partialFilterExpression { status: 'PENDING' }. It serves the dashboard because the query includes status exactly. It does not help the customer query, which uses SHIPPED, so do not replace the general compound index unless measurements show the partial index is sufficient.
Finance reporting may also be a poor fit for the operational primary. Consider a secondary read preference or a separate reporting store if the aggregation scans large date ranges.
3. What steps would you take to validate the new index set and control write overhead at 500 writes/sec?
Read the worked answer
Validate with explain('executionStats') on representative queries. Check that totalKeysExamined is close to nReturned, that there is no COLLSCAN, and that there is no blocking SORT. Test with production-like data because status distribution matters: if PENDING is rare, the dashboard should examine few keys; if it is common, the index may not be selective enough.
Measure index size and usage. Use $indexStats to find indexes with zero operations over a full business cycle, and drop those. Track write latency, cache pressure, and replication lag after adding indexes. Each index adds work to 500 writes per second, so prefer two or three compound indexes over many single-field ones. Build indexes in a rolling manner on secondaries before the primary. Re-check query plans after dropping redundant indexes, because the optimizer may choose a different plan. Finally, confirm that the partial index is actually used by the dashboard query and that the finance covering index is not causing unacceptable write amplification.