🗄️

MongoDB Mongoose Query Optimization Guide

← Voltar ao Índice

MongoDB Mongoose Query Optimization Guide

Audience: All developers writing Mongoose queries in the Audissey API and Worker codebases.
Last updated: June 2025

Why This Matters

Audissey uses MongoDB Atlas. These rules prevent unbounded memory growth and slow responses:

Operation Risk
$group aggregation Full-collection scan without a preceding $match
countDocuments({}) Full-collection scan
.distinct(field) Full-collection scan
sort() on unindexed field In-memory sort (slow for large result sets)
_id index Always exists (use as time-based bound via ObjectId)

An unbounded $group on a large collection (e.g., 2M events) is expensive and can degrade overall cluster performance.

Mandatory Rules

Rule 1: Always `.limit()` on `.find()`

Every .find() call must have a .limit(). Choose a ceiling appropriate to the use case:

// ❌ BAD — unbounded, can return millions of documents
const users = await this.userModel.find({ active: true }).exec();

// ✅ GOOD — bounded
const users = await this.userModel.find({ active: true }).limit(200).exec();

Suggested limits by context:

Context Limit
User-facing list endpoints 100–200
Internal/admin scans 500–2000
Analytics aggregations 50000 max

Rule 2: Always `.lean()` on read-only queries

.lean() skips Mongoose document hydration → ~40% faster, ~60% less memory. Only omit when you need .save() on the returned documents.

// ❌ BAD — returns full Mongoose documents unnecessarily
const radio = await this.radioModel.findOne({ stream_url: url }).exec();

// ✅ GOOD — returns plain JS object
const radio = await this.radioModel.findOne({ stream_url: url }).lean().exec();

Rule 3: Use `.select()` on high-traffic endpoints

Only project the fields your DTO actually needs. Especially important for documents with large text fields or embedded arrays.

// ✅ Existence check — only need _id
const existing = await this.radioModel
  .findOne({ stream_url: station.url_resolved || station.url })
  .lean()
  .select('_id')
  .exec();

Rule 4: Paginate list endpoints

Every GET that returns an array must support ?page&pageSize query params. Use the shared utilities:

import { PaginationQueryDto, PaginatedResult, resolvePagination } from '../common/dto/pagination.dto';

// In your controller/service:
const { page, pageSize, skip } = resolvePagination(query.page, query.pageSize);
const items = await this.model.find(filter).skip(skip).limit(pageSize).lean().exec();
const total = await this.model.countDocuments(filter).exec();

Defaults: page size = 50, max = 200.

Rule 5: Always `$match` before `$group` in aggregations (CRITICAL)

On MongoDB, an unscoped $group does a full-collection scan. The only way to limit it is to add a $match as the first pipeline stage to prune documents before the scan.

Use the _id field as a time-based bound — it's always indexed and encodes a timestamp.

import { Types } from 'mongoose';

// Helper: build an ObjectId from N days ago
private objectIdFromDaysAgo(days: number): Types.ObjectId {
  const ts = Math.floor((Date.now() - days * 86_400_000) / 1000);
  return new Types.ObjectId(
    ts.toString(16).padStart(8, '0') + '0000000000000000'
  );
}

// Usage:
const minId = this.objectIdFromDaysAgo(30); // 30-day window
const matchRecent = { $match: { _id: { $gte: minId } } };

// ❌ BAD — scans entire collection
const result = await this.model.aggregate([
  { $group: { _id: '$type', count: { $sum: 1 } } },
]);

// ✅ GOOD — scans only last 30 days
const result = await this.model.aggregate([
  matchRecent,
  { $group: { _id: '$type', count: { $sum: 1 } } },
  { $sort: { count: -1 } },
]);

Why _id? MongoDB ObjectIds encode a 4-byte timestamp in their first bytes. _id is always indexed, so $match: { _id: { $gte } } is an efficient range scan. This avoids the need for a separate timestamp index.

Rule 6: Use `estimatedDocumentCount()` for total counts

When you need the total number of documents in a collection (no filter), use estimatedDocumentCount() which reads collection metadata (~0 RU) instead of scanning:

// ❌ BAD — full-collection scan
const total = await this.model.countDocuments({}).exec();

// ✅ GOOD — uses collection metadata, ~0 RU
const total = await this.model.estimatedDocumentCount().exec();

Note: estimatedDocumentCount() cannot take a filter. If you need a filtered count, use countDocuments(filter) — but ensure the filter uses an indexed field.

Rule 7: Replace `.distinct()` with bounded aggregation

.distinct(field) does a full-collection scan. Replace with a bounded $group + $count:

// ❌ BAD — full-collection scan, returns every unique userId
const uniqueUsers = await this.model.distinct('userId');
const count = uniqueUsers.length;

// ✅ GOOD — bounded to 30 days, only returns count
const minId = this.objectIdFromDaysAgo(30);
const result = await this.model.aggregate([{ $match: { _id: { $gte: minId } } }, { $group: { _id: '$userId' } }, { $count: 'n' }]).exec();
const count = result[0]?.n ?? 0;

Rule 8: Ensure indexes for sort fields

Ensure an index exists for any field used in .sort(). When adding a new sort, verify the index exists in deploy/config/indexes/indexes.json.

Rule 9: No full-collection scans in request-time code

If you need all documents (e.g., recommendation scoring), load them at startup or cache them — not per-request. If per-request is unavoidable, add a .limit() and document why.

Patterns Quick Reference

Pattern When to use Example file
objectIdFromDaysAgo(N) Bound any aggregation to a time window admin.controller.ts, events.service.ts
estimatedDocumentCount() Unfiltered total count admin.controller.ts, data-integrity.service.ts
$match → $group Every aggregation with $group admin.controller.ts (all 5 analytics endpoints)
$group + $count Replace .distinct(field).length admin.controller.ts, events.service.ts
.lean().select('_id') Existence checks search.service.ts
.lean().limit(N) Every read-only .find() podcasts.service.ts
Sort try-catch fallback (Removed — Atlas supports sorts natively) —
resolvePagination() All list GET endpoints pagination.dto.ts

Testing Mocks

When writing unit tests for services that use these patterns, ensure your mocks replicate the full Mongoose method chain:

// Mock for .find().sort().lean().limit().exec()
const mockModel = {
  find: jest.fn().mockReturnValue({
    sort: jest.fn().mockReturnValue({
      lean: jest.fn().mockReturnValue({
        limit: jest.fn().mockReturnValue({
          exec: jest.fn().mockResolvedValue([]),
        }),
      }),
    }),
  }),
};

// Mock for estimatedDocumentCount()
const mockModel = {
  estimatedDocumentCount: jest.fn().mockReturnValue({
    exec: jest.fn().mockResolvedValue(42),
  }),
};

// Mock for aggregate()
const mockModel = {
  aggregate: jest.fn().mockReturnValue({
    exec: jest.fn().mockResolvedValue([{ _id: 'radio', count: 10 }]),
  }),
};

Tip: Missing chain methods cause TypeError: x is not a function at test runtime. If a prod method adds .lean() to an existing chain, the mock must also add it.

Checklist for Code Review

Before approving any PR that adds or modifies Mongoose queries, verify:

Further Reading