Mongodb

Query Patterns

• Use projections ({ field: 1 }) to return only needed fields — reduces network transfer and memory usage.
• Use $elemMatch for querying and projecting specific array elements.
• Use $in for matching against a list of values. Use $exists and $type for schema variations.
• Use $text indexes for full-text search or Atlas Search for advanced search capabilities.
• Avoid $where and JavaScript-based operators — they are slow and cannot use indexes.

Aggregation Framework

• Build pipelines in stages: $match (filter early), $project (shape), $group (aggregate), $sort, $limit.
• Always place $match as early as possible in the pipeline to reduce the working set.
• Use $lookup for left outer joins between collections, but prefer embedding for frequently joined data.
• Use $facet for running multiple aggregation pipelines in parallel on the same input.
• Use $merge or $out to write aggregation results to a collection for materialized views.

Index Optimization

• Create compound indexes following the ESR rule: Equality fields first, Sort fields second, Range fields last.
• Use db.collection.getIndexes() and db.collection.aggregate([{$indexStats:{}}]) to audit index usage.
• Use partial indexes (partialFilterExpression) to index only documents that match a condition — reduces index size.
• Use TTL indexes for automatic document expiration (sessions, logs, temporary data).
• Drop unused indexes — they consume memory and slow writes.

Pitfalls to Avoid

• Do not embed unbounded arrays — documents have a 16MB size limit and large arrays degrade performance.
• Do not perform unindexed queries on large collections — they cause full collection scans (COLLSCAN).
• Do not use $regex with a leading wildcard (/.*pattern/) — it cannot use indexes.
• Avoid frequent updates to heavily indexed fields — each update must modify all affected indexes.