🛡️

MongoDB Query Best Practices

← Voltar ao Índice

MongoDB: Query Best Practices

Status: Active best practices
Last updated: 2026-04-09
Applies to: API (apps/api/), Worker (apps/worker/)

Background

Audissey uses MongoDB Atlas (M10+ cluster). Query hygiene remains critical for performance: unbounded queries, missing indexes, and full collection scans cause slow responses and memory pressure.

Root Causes We've Hit

Root Cause Impact Resolution
3-second HTTP polling on /radios/:id/now-playing ~2,500 calls/2h → excessive DB load Replaced with SSE push (see SSE_NOW_PLAYING.md)
Missing indexes on filter/sort fields Full collection scans → slow queries Created indexes via ensure-atlas-indexes.js
countDocuments() on large collections (unfiltered) Scans entire collection Replaced with estimatedDocumentCount()
Admin dashboard loading all fields from large docs High bandwidth per read Added .select() projections
Redundant musiclink upserts (same track re-detected) ~71% of all writes from musiclinks In-memory dedup cache in MusicLinkService.upsert() (see below)

Mandatory Query Rules

These rules are also codified in .github/copilot-instructions.md under "Mongoose / MongoDB performance rules (MANDATORY)".

1. Always add `.limit()`

No .find() without a .limit(). Choose a ceiling appropriate to the use case:

Context Limit
User-facing list endpoints 100–200
Internal/admin scans 500–2,000
Analytics aggregations 50,000 max

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

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

// Good
const radios = await this.radioModel.find({ is_active: true }).lean().limit(100).exec();

// Bad — full Mongoose documents allocated for no reason
const radios = await this.radioModel.find({ is_active: true }).limit(100).exec();

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

Explicitly project only the fields the DTO needs. Critical for documents with large text fields or embedded arrays.

// Good — only pulls the fields we need
.select('name stream_url now_playing now_playing_updated_at genres')

// Bad — pulls entire document including description, fallback_urls[], etc.
.find({ is_active: true })

4. Paginate list endpoints

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

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

Default: 50, max: 200.

5. 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.

// Good — sort field has an index
.find(filter).sort({ createdAt: -1 }).lean().limit(200).exec();

6. No full-collection scans at request time

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

7. Prefer `estimatedDocumentCount()` for unfiltered counts

countDocuments({}) triggers a full collection scan. estimatedDocumentCount() uses collection metadata — O(1) and near-zero cost.

// Good
const total = await this.radioModel.estimatedDocumentCount().exec();

// Bad — scans entire collection
const total = await this.radioModel.countDocuments({}).exec();

File: radio-collector/src/services/music-link.service.ts

The ICY collector detects the same (artist, track) pair on a radio every 2 minutes. Without deduplication, each detection triggers a updateOne() with upsert: true on the musiclinks collection — even when the document already exists. This made musiclinks the #1 write-heavy collection and a major source of DB pressure.

How It Works

An in-memory Map<string, number> caches recently-seen (normalized_artist, normalized_track) pairs. Before calling updateOne(), the upsert() method checks the cache. If the key was seen within the TTL, the DB call is skipped entirely.

upsert(artist, track)
  ├── normalize → build cache key "artist|track"
  ├── cache HIT within TTL? → return immediately (no DB call)
  └── cache MISS → updateOne(upsert:true) → cache key → return
      └── E11000 duplicate? → cache key anyway (doc already exists)

Configuration

Constant Value Description
UPSERT_DEDUP_TTL_MS 24h How long a cached key suppresses duplicate writes
UPSERT_DEDUP_MAX_SIZE 50,000 Max entries before forced eviction
Eviction frequency ~1/hour Lazy eviction runs at most once per hour

Expected Impact

Eliminates ~80–90% of musiclinks updateOne calls. The same song played on a radio for hours only triggers one DB write per 24h instead of one every 2 minutes.

Limitations

In-Memory Caching Pattern

For high-traffic endpoints, add an in-memory cache layer to avoid hitting MongoDB on every request.

Now-Playing Cache (reference implementation)

private readonly cache = new Map<string, { data: T; timestamp: number }>();
private readonly CACHE_TTL_MS = 30_000; // 30 seconds

// Fast path: return from memory
const cached = this.cache.get(key);
if (cached && Date.now() - cached.timestamp < this.CACHE_TTL_MS) {
  return cached.data;
}

// Slow path: query DB, then write back to cache
const result = await this.model.findById(key).lean().exec();
this.cache.set(key, { data: result, timestamp: Date.now() });
return result;

When to use

When NOT to use

HTTP Cache-Control Headers

Add Cache-Control headers on cacheable GET endpoints to reduce origin hits from CDN and browser caches.

@Header('Cache-Control', 'public, max-age=15')

Use public only for unauthenticated or non-user-specific responses. Use private for user-specific data.

Monitoring Query Performance

MongoDB Atlas

Log Analytics (KQL)

// Slow query warnings from application logs
ContainerAppConsoleLogs_CL
| where Log_s contains "slow query" or Log_s contains "MongoServerError"
| summarize count() by bin(TimeGenerated, 1h)
| order by TimeGenerated desc

Checklist for New Endpoints

Before merging a new endpoint that queries MongoDB: