PostgreSQL & MongoDB Database Indexing Strategies for 35% Faster Aggregation Pipelines

Without strategic database indexing, query execution times scale linearly as table row counts grow into millions. By applying compound indexes tailored to query patterns, database engines perform logarithmic B-Tree lookups instead of expensive full collection scans.

During my engineering work at **Sheryians Pvt. Ltd.** on the HRECT recruitment SaaS platform, database optimization reduced query execution times by **35%**.

---

1. MongoDB Compound Index Rules (ESR Rule)

When creating MongoDB compound indexes, follow the **ESR Rule**: 1. **Equality:** Place exact match fields first (status: ACTIVE). 2. **Sort:** Place sort ordering fields second (createdAt: -1). 3. **Range:** Place range comparison fields last (age >= 18).

// MongoDB Index Definition enforcing ESR Rule
db.applications.createIndex(
  { status: 1, createdAt: -1, salaryExpectation: 1 },
  { background: true, name: 'idx_status_created_salary' }
);

---

2. Results Summary

- Reduced MongoDB aggregation pipeline execution time from **420ms to 270ms (35.7% speedup)**. - Reduced PostgreSQL query lock times under 500 active concurrent connections.

*Engineered by Sameer Khan — Full Stack Developer & Software Engineer.*