Index Design for Predictable OLTP Performance

Why Index Design Matters
Index design determines whether queries execute in 5ms or 5 seconds. For OLTP workloads (Online Transaction Processing: many small, fast operations serving web requests), predictable low latency is critical. Missing indexes cause latency to scale with collection size.
Compound Index Ordering
Field order in compound indexes matters. The rule: equality filters first, range filters second, sort fields last. Index (country, status, createdAt) efficiently supports filtering by country and status, sorted by date. Reversing to (createdAt, country, status) breaks this.
Array Index Complexity
Indexing arrays creates one entry per element (multi-key index). A document with 500 tags generates 500 index entries. Writing multiplies latency by element count. Keep indexed arrays under 100 elements, or move to separate collections.
Pagination Strategies
Offset pagination skip(N) is O(N): skipping 10,000 walks 10,000 entries. Cursor pagination uses last seen key: createdAt < lastSeen jumps to that position, O(1) regardless of depth.
Index Selectivity
Selectivity measures how well an index narrows results. Low-selectivity status with 3 values scans 33% of collection. High-selectivity userId scans only that users documents. Put high-selectivity fields first.

💡 Key Takeaways

✓Compound index order: equality filters first, range filters second, sort fields last

✓Missing indexes cause collection scans: 5ms query becomes 5 seconds on 1M documents

✓Multi-key indexes on arrays create one entry per element: 500 tags means 500 index writes

✓Offset pagination skip(N) is O(N); cursor-based pagination using last seen key is O(1)

✓Index selectivity matters: low-selectivity field first still scans 33% of collection

📌 Interview Tips

1Explain compound index ordering when discussing query optimization: equality, then range, then sort

2Propose cursor-based pagination for any feed or list with potential deep scrolling

3Ask about array sizes when indexing: unbounded arrays multiply write costs

← Back to Document Databases (MongoDB, Firestore) Overview