How do you optimize indexing, sharding, and replication?

Long Answer

Delivering low latency at scale in NoSQL (MongoDB, CouchDB) requires three levers—indexing, sharding, and replication—guided by clear workload SLOs and careful data modeling. My strategy is measurement-first: capture real query shapes, read/write ratios, and growth curves; then iterate safely.

1) Indexing for real query shapes

Indexes must mirror filters and sort orders. In MongoDB, I build compound indexes in the same order as equality → range → sort, so the planner can satisfy queries and sorting from one structure. I avoid wide indexes with low selectivity, drop unused ones via system.profile and usage stats, and cap total index size to fit hot sets in RAM. For mixed filters, I consider partial indexes (e.g., active status) and sparse ones for optional fields to keep b-trees dense. For time-series or append-heavy data, I prioritize ts leading keys with covered queries that return only indexed fields. In CouchDB, where map/reduce views and Mango indexes drive lookups, I keep deterministic map functions, emit composite keys for common ranges, and precompute reduce views only when aggregation latency matters. Across both, I test with realistic cardinalities and ensure collations and case-insensitive needs are handled by explicit index options, not post-filtering.

2) Data modeling to reduce index pressure

NoSQL rewards read-optimized documents. I denormalize judiciously (embed small, immutable subdocs; reference large, volatile ones) to keep working sets small and minimize multi-collection fan-out. I bound document size, avoid unbounded arrays, and store “latest” projections alongside history to keep hot reads single-touch. For query flexibility, I maintain read models (materialized projections) keyed by the same access patterns as indexes; this often removes the need for additional secondary indexes.

3) Sharding for balanced writes and targeted reads

Sharding succeeds or fails on the shard key. Good keys have high cardinality, even write distribution, and enable targeted queries (route to one or few shards). In MongoDB, I prefer hashed keys to defeat monotonic inserts or compound keys like {tenantId, hashed(userId)} so multi-tenant reads can be routed by tenant while spreading within tenant. For time-heavy workloads, I avoid raw timestamps as leading keys (write hot-spot) and use {bucket(ts), hashed(id)} or zoned sharding to keep recent buckets on stronger hardware while allowing historical scans elsewhere. I monitor chunk distribution, enable auto-split/auto-balance, and proactively pre-split for launch spikes. Query code must include the shard key in filters; otherwise queries scatter-gather and tail latency spikes.

4) Replication for durability, locality, and read scale

Replication topology follows failure domains. In MongoDB, a 5–7 member replica set with voting across zones tolerates AZ loss. I size oplog for sustained peak plus catch-up windows and tune write concern per path: w:majority for money-moving writes; lower for idempotent telemetry. For reads, I use read preferences: primaryPreferred for strongly consistent paths; nearest or hedged reads to reduce tail latency on tolerant endpoints. In CouchDB, clustered nodes replicate via DCP/replicator, and I segregate writer/reader nodes for predictable throughput; conflict resolution rules are tested for offline/edge sync flows. Cross-region replication adopts delayed secondaries (for rollback safety) and compression to shrink WAN costs.

5) Caching and TTL to protect the core

Indexes are not a cache. I front hot keys with Redis (or CDN for APIs) using versioned keys and short TTLs to cut read amplification. For ephemeral data, I use TTL indexes (MongoDB) or time-partitioned databases to age out noise, keeping working sets tight and memory hit ratios high.

6) Operational guardrails and backpressure

I set connection pool limits and timeouts to avoid thundering herds. Slow queries log with sampling; I cap maxTimeMS to kill runaways. Large bulk updates are chunked with rate limits, and background index builds run online with priority lowered. For CouchDB compaction, I schedule windows and monitor disk I/O to avoid read starvation. Everywhere, observability includes per-shard p95 latency, queue depth, cache hit rate, and chunk balance. Autoscaling triggers on CPU, page faults, and queue age—not just QPS.

7) Proving improvements

Every change ships with explain plans, index stats deltas, and load test curves. I compare p50/p95 with and without the new index or shard key, validate scatter-gather counts, and watch replication lag. If an index improves one path but hurts writes or memory, I re-evaluate or add projections/read models to split concerns.

The result: horizontal scalability with even write distribution, low-latency queries via tight, coverage-oriented indexes, and replication that serves both durability and geo-local reads—validated continuously under production-like load.

‍

Table

‍

Common Mistakes

• Building indexes per complaint without removing unused ones, blowing RAM.
  
• Using monotonic shard keys (raw timestamp, auto-increment) that hotspot a single shard.
  
• Relying on scatter-gather queries because filters do not include the shard key.
  
• Assuming replication equals read scale without tuning read preferences or hedged reads.
  
• Ignoring oplog/DCP sizing, causing lag during spikes and slow failovers.
  
• Over-embedding volatile arrays, creating jumbo documents and large index churn.
  
• Rebuilding big indexes during peak hours; running compaction without I/O guards.
  
• Failing to validate with explain and tail latency, celebrating mean improvements only.
  
  

Sample Answers

Junior:
“I align compound indexes with filters and sort order, and use partial indexes for active items. For sharding I prefer hashed keys to spread writes. Replication uses majority writes and primaryPreferred reads for critical paths. I verify with explain and p95 latency.”

Mid:
“I design compound shard keys like {tenantId, hashed(userId)} so multi-tenant reads target few shards while writes spread evenly. I prune unused indexes, use covered queries, and size the oplog for spikes. For tolerant reads I enable hedged reads and nearest preference. Changes ship with before/after plans.”

Senior:
“I start from workload SLOs, build coverage-oriented indexes, and maintain read models to keep hot reads single-touch. Sharding uses zoned + pre-split ranges and compound keys to avoid hot shards. Replication is region-aware with delayed secondaries, tuned write concerns, and geo-local reads. Guardrails (pool caps, maxTimeMS, online index builds) keep the system stable. Every step is proven with profiler traces, scatter counts, and p95/p99 deltas.”

‍

Evaluation Criteria

Strong answers show:

• Indexes aligned to query+sort (compound, partial/sparse, covered queries).
  
• Data models reducing fan-out (embed vs reference, read models).
  
• Thoughtful shard key selection (high cardinality, balanced writes, targeted reads), pre-split and zoning.
  
• Replication tuned for durability and locality (write concerns, read prefs, sized oplog/replicator, hedged reads).
  
• Operational guardrails (pool limits, timeouts, online maintenance) and proof via explain, scatter metrics, replication lag, and p95/p99.
  Red flags: monotonic shard keys, uncontrolled index sprawl, scatter-gather everywhere, ignoring replication lag, and no before/after evidence.
  
  

Preparation Tips

• Capture top queries and sorts; design compound indexes that cover them; drop one unused index.
  
• Run explain to confirm IXSCAN + SORT covered; measure p95 before/after.
  
• Prototype two shard keys (hashed vs compound); load test writes and count scatter shards per query.
  
• Enable zoned sharding or buckets for time data; validate balanced chunks under burst inserts.
  
• Tune oplog/replicator size; simulate spike and check catch-up lag and failover time.
  
• Add read preferences and test hedged reads on tolerant endpoints; compare tail latency.
  
• Introduce a TTL index for ephemeral data; confirm working set and cache hit ratio improve.
  
• Schedule online index builds/compaction in a maintenance window; record impact.
  
  

Real-world Context

A marketplace suffering 99th-percentile spikes replaced a monotonic createdAt shard key with {tenantId, hashed(orderId)} and pre-split chunks; write hotspots vanished and scatter queries dropped 70%. A gaming telemetry pipeline moved to covered queries on {gameId, ts} with TTL on raw events; RAM pressure fell and p95 halved. A global SaaS enabled hedged reads with region-local secondaries; long tails during AZ brownouts improved dramatically. A content platform split oversized documents, moved counts to a read model, and scheduled online index builds—stability returned during promotions with zero emergency reindexes.

‍

Key Takeaways

• Design compound, coverage-oriented indexes that mirror filters and sort order.
  
• Choose shard keys with high cardinality and targeted-read capability; avoid hot shards.
  
• Tune replication for durability and locality; size oplog/replicator and use hedged reads.
  
• Keep working sets tight with TTL and caches; model for single-touch reads.
  
• Prove every change with explain, scatter counts, replication lag, and p95/p99 deltas.
  
  

Practice Exercise

Scenario:
Your multi-tenant analytics cluster (MongoDB) handles bursty writes and dashboards with filters by tenant, time, and status. p95 reads spike during peaks; primaries show oplog lag after batch loads.

Tasks:

1. Capture top dashboard queries and current sort orders; design a compound index that covers {tenantId, status, ts:-1} and add a partial index for status:'active'.
   
2. Evaluate sharding: compare hashed(orderId) versus {tenantId, hashed(userId)}. Load test both and record write distribution and scatter shards per query.
   
3. For time filtering, implement bucket(ts) logic or zoned ranges for “recent” data; pre-split chunks ahead of traffic spikes.
   
4. Introduce a small read model for dashboard cards (latest aggregates per tenant) to cut payload and index pressure.
   
5. Tune replication: increase oplog to cover peak + recovery; switch tolerant dashboards to nearest with hedged reads and keep money paths on primary.
   
6. Add a TTL index on raw events older than N days; verify cache hit ratio and RAM usage improve.
   
7. Prove gains: explain shows IXSCAN + covered, scatter count <2 shards, replication lag < target, and p95/p99 drop versus baseline under the same load profile.
   
   

Deliverable:
A measured plan and before/after report demonstrating scalable indexing, balanced sharding, and tuned replication that achieve horizontal scalability and low-latency queries under heavy load for your NoSQL cluster.