hardBackend EngineerData
How do database indexes work, and what are the performance trade-offs of adding too many indexes?
Posted 18/04/2026
by Mehedy Hasan Ador
Question Details
At a data-heavy company interview:
> "Our query that filters by
> "Our query that filters by
status and created_at takes 8 seconds on 5M rows. Adding an index made it 50ms, but now our INSERT performance dropped 40%. How do we find the right balance?"Suggested Solution
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 idxappstatusdate ON applications (status, createdat DESC);
CREATE INDEX idxapppartial ON applications (status) WHERE status = 'APPLIED';
-- Full-text search
CREATE INDEX idxquestionssearch ON questions USING GIN(to_tsvector('english', question));
The Write Performance Trade-off
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 match2. 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 document4. Monitor with
$indexStats — Find unused indexes and remove themdb.collection.aggregate([{ $indexStats: {} }]);
// Shows access count per index — remove indexes with ops: 0