How do database indexes work, and what are the performance trade-offs of adding too many indexes?

How Indexes Work

An index is a separate data structure (typically B-tree) that allows fast lookups without scanning every row.

Without Index (Collection Scan)

Find { status: "APPLIED" } → Check EVERY document → 5M checks → 8 seconds

With Index (B-tree Lookup)

Index on { status: 1 } → B-tree traversal → O(log n) → ~23 steps → 50ms

Types of Indexes

MongoDB

// Single field
db.applications.createIndex({ status: 1 });

// Compound (order matters!)
db.applications.createIndex({ status: 1, createdAt: -1 });

// Text index
db.questions.createIndex({ question: "text", content: "text" });

// Partial index (smaller, faster)
db.applications.createIndex(
  { status: 1 },
  { partialFilterExpression: { status: "APPLIED" } }
);

PostgreSQL

CREATE INDEX idx_app_status_date ON applications (status, created_at DESC);
CREATE INDEX idx_app_partial ON applications (status) WHERE status = 'APPLIED';
-- Full-text search
CREATE INDEX idx_questions_search ON questions USING GIN(to_tsvector('english', question));

The Write Performance Trade-off

Indexes	Read Speed	Write Speed	Storage
0 indexes	Slow (scan)	Fast	Minimal
1-3 indexes	Fast	Moderate	+10-30%
10+ indexes	Very fast	Slow	+100%+

Every INSERT/UPDATE must update ALL indexes.

Finding the Right Balance

// MongoDB: Analyze slow queries
db.setProfilingLevel(1, { slowms: 100 }); // Log queries > 100ms
db.system.profile.find().sort({ ts: -1 }).limit(10);

// Explain query plan
db.applications.find({ status: "APPLIED" }).explain("executionStats");
// Look for: totalDocsExamined vs nReturned
// If ratio > 10:1, you need an index

Best Practices

1. Index for your queries, not your data — Start with slow queries, add indexes to match
2. ESR Rule (Equality, Sort, Range) for compound indexes:

   // Query: { status: "APPLIED" } .sort({ createdAt: -1 }) .where("salary > 50000")
   // Index:
   db.applications.createIndex({ status: 1, createdAt: -1, salary: 1 });
   //             equality ↑      sort ↑            range ↑
   

3. Covered queries — If all fields are in the index, MongoDB doesn't fetch the document
4. Monitor with $indexStats — Find unused indexes and remove them

db.collection.aggregate([{ $indexStats: {} }]);
// Shows access count per index — remove indexes with ops: 0