# MongoDB Schema & Performance Guide

## Overview

This guide integrates schema design principles with performance optimization strategies for MongoDB. It follows a workflow-based approach: analyze workload → design schema → plan indexes → optimize queries.

**Scope:**
- MongoDB collections, data models, and query optimization
- Backend code in `src/` directory and database models
- All MongoDB-related schema definitions and queries

## 1. Workload Analysis

Before designing schemas or indexes, analyze how your application accesses data.

### Required Actions

- [ ] Document all critical read and write operations
- [ ] Measure query frequency and performance requirements
- [ ] Identify dominant queries (20% that drive 80% of traffic)
- [ ] Estimate data volume and growth patterns
- [ ] Define acceptable latency for each operation type

### Analysis Guidelines

- Focus on how data is accessed, not just its logical structure
- Consider peak load scenarios and scaling requirements
- Document query patterns with expected frequency (per second/day)
- Identify critical vs. non-critical operations

### Workload Analysis Template

```javascript
const workloadAnalysis = {
  operations: [
    {
      name: 'getUserWithPosts',
      type: 'read',
      frequency: '1000/day',
      latency: '< 50ms',
      critical: true,
      pattern: 'find user with recent 10 posts',
    },
    {
      name: 'createPost',
      type: 'write',
      frequency: '100/day',
      latency: '< 200ms',
      critical: true,
      pattern: 'insert post with user reference',
    },
  ],
  dominantQueries: ['getUserWithPosts', 'searchPostsByTag'],
  estimatedDocumentSize: '2KB average, 16KB max',
  expectedGrowth: '1M documents/year',
};
```

## 2. Schema Design

Design schemas based on workload patterns, not just logical data relationships.

### Embed vs Reference Decision

| Factor | Embed | Reference |
|--------|-------|-----------|
| Cardinality | One-to-Few | One-to-Many (high cardinality) |
| Access pattern | Data accessed together | Data accessed independently |
| Update frequency | Infrequent updates | Frequent updates |
| Document size | Small subdocuments | Large subdocuments |

```javascript
// Embedding - One-to-Few relationship
const userWithEmbeddedProfile = {
  _id: ObjectId,
  email: String,
  profile: {
    name: String,
    avatar: String,
    preferences: { theme: String, language: String },
  },
  createdAt: Date,
};

// Reference - One-to-Many with high cardinality
const postSchema = {
  _id: ObjectId,
  user_id: ObjectId, // Reference to user
  title: String,
  content: String,
  createdAt: Date,
};
```

### Schema Design Patterns

#### Computed Pattern
Pre-calculate aggregated values to avoid expensive runtime calculations.

```javascript
const productWithStats = {
  _id: ObjectId,
  name: String,
  price: Number,
  // Computed fields updated on write
  averageRating: Number,
  totalReviews: Number,
  totalSales: Number,
  lastUpdated: Date,
};
```

#### Subset Pattern
Embed frequently accessed subset to avoid separate queries.

```javascript
const productWithRecentReviews = {
  _id: ObjectId,
  name: String,
  price: Number,
  recentReviews: [
    // Last 5 reviews embedded
    { user_name: String, rating: Number, comment: String, date: Date },
  ],
  // Full reviews stored in separate collection
};
```

#### Extended Reference Pattern
Duplicate frequently needed fields to avoid lookups.

```javascript
const orderWithUserInfo = {
  _id: ObjectId,
  user_id: ObjectId,
  user_email: String, // Duplicated for quick access
  user_name: String,  // Avoid lookup for common displays
  items: [],
  total: Number,
  createdAt: Date,
};
```

#### Bucket Pattern
Group time-series data to reduce document count.

```javascript
const sensorDataBucket = {
  _id: ObjectId,
  sensor_id: String,
  bucket_date: '2024-01-15', // Daily bucket
  readings: [
    { hour: 0, temperature: 23.5, humidity: 65, timestamp: Date },
  ],
  count: Number,
  min_temp: Number,
  max_temp: Number,
};
```

#### Time-Series Collections (MongoDB 5.0+)

Native time-series collections are the recommended approach for time-series data, replacing manual bucket patterns.

```javascript
// Create a native time-series collection (recommended)
db.createCollection('sensor_data', {
  timeseries: {
    timeField: 'timestamp',     // Required: field containing timestamp
    metaField: 'sensor_id',     // Optional: field for metadata (e.g., device ID)
    granularity: 'hours'        // Optional: 'seconds', 'minutes', 'hours'
  },
  expireAfterSeconds: 2592000   // Optional: auto-delete after 30 days
});

// Insert data as regular documents - MongoDB handles bucketing automatically
db.sensor_data.insertMany([
  { sensor_id: 'sensor_001', timestamp: new Date(), temperature: 23.5, humidity: 65 },
  { sensor_id: 'sensor_001', timestamp: new Date(), temperature: 24.0, humidity: 63 },
]);

// Query like regular collections
db.sensor_data.aggregate([
  { $match: { sensor_id: 'sensor_001', timestamp: { $gte: ISODate('2024-01-01') } } },
  { $group: { _id: { $dateToString: { format: '%Y-%m-%d', date: '$timestamp' } }, avgTemp: { $avg: '$temperature' } } }
]);
```

**Benefits over manual Bucket Pattern:**
- Automatic internal bucketing and compression
- Optimized storage (up to 90% less disk usage)
- Better query performance for time-range queries
- Native support for time-series aggregations

#### Schema Versioning
Track schema versions for migrations.

```javascript
const versionedDocument = {
  _id: ObjectId,
  schema_version: 2,
  email: String,
  profile: {
    name: String,
    phone: String, // Added in version 2
  },
  migrated_at: Date,
  previous_version: 1,
};
```

## 3. Index Strategy

Design indexes based on query patterns identified in workload analysis.

### Index Design Guidelines

#### ESR Rule (Equality → Sort → Range)

Order compound index fields following the ESR rule for optimal performance:

1. **Equality** - Fields with exact match conditions (`field = value`)
2. **Sort** - Fields used in ORDER BY clauses
3. **Range** - Fields with range conditions (`$gt`, `$lt`, `$gte`, `$lte`, `$in`)

```javascript
// ESR Rule Example
// Query: status = 'active' AND price > 100 ORDER BY createdAt DESC
db.orders.createIndex({
  status: 1,      // Equality: status = 'active'
  createdAt: -1,  // Sort: ORDER BY createdAt DESC
  price: 1        // Range: price > 100
});
```

#### General Guidelines

- Apply ESR rule for compound index field ordering
- Prioritize most selective equality fields first
- Create separate indexes for different query patterns
- Consider index intersection for complex queries

### Index Types

#### Standard Indexes

```javascript
// Compound index for filtering and sorting (ESR applied)
db.collection.createIndex({
  status: 1,      // Equality field first
  createdAt: -1,  // Sort field second
  price: 1        // Range field last
});

// User collection indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.users.createIndex({ 'profile.name': 1 });

// Post collection indexes (for referenced relationship)
db.posts.createIndex({ user_id: 1, createdAt: -1 });
db.posts.createIndex({ tags: 1 });

// Bucket pattern indexes
db.sensor_data.createIndex({ sensor_id: 1, bucket_date: 1 });
```

#### Sparse and Partial Indexes

Use sparse or partial indexes when only a subset of documents need indexing.

```javascript
// Sparse Index - Only indexes documents where the field exists
// Useful for optional fields
db.users.createIndex({ secondaryEmail: 1 }, { sparse: true });

// Partial Index - Only indexes documents matching the filter expression
// More flexible than sparse, allows custom conditions
db.orders.createIndex(
  { status: 1, createdAt: -1 },
  { partialFilterExpression: { status: 'active' } }
);

// Partial index for large collections - index only recent data
db.logs.createIndex(
  { level: 1, timestamp: -1 },
  { partialFilterExpression: { timestamp: { $gte: ISODate('2024-01-01') } } }
);
```

#### TTL Indexes (Time-To-Live)

Automatically delete documents after a specified period.

```javascript
// Delete sessions after 24 hours (86400 seconds)
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 86400 });

// Delete logs after 30 days (2592000 seconds)
db.logs.createIndex({ timestamp: 1 }, { expireAfterSeconds: 2592000 });

// Note: TTL index field must be a Date type
// Documents are deleted by a background task (runs every 60 seconds)
```

### Covered Query Design

Include all queried and returned fields in the index to avoid document fetches.

```javascript
// Covered query example
db.users
  .find({ status: 'active' }, { name: 1, email: 1, _id: 0 })
  .sort({ createdAt: -1 })
  .explain('executionStats');
```

## 4. Query Optimization

Verify and optimize query performance using explain analysis.

### Performance Analysis

```javascript
const stats = db.collection.find(query).explain('executionStats');

// Verify performance indicators
console.log('Stage:', stats.executionStats.executionStages.stage); // Should be "IXSCAN"
console.log('Docs examined:', stats.executionStats.totalDocsExamined);
console.log('Docs returned:', stats.executionStats.totalDocsReturned);
console.log('Execution time:', stats.executionStats.executionTimeMillis);
```

### Performance Targets

- `executionTimeMillis` < 100ms for simple queries
- `totalDocsExamined` ≈ `totalDocsReturned`
- Stage progression shows index usage (IXSCAN, not COLLSCAN)

### Aggregation Pipeline Optimization

Order pipeline stages to reduce data volume early:

```
$match → $sort → $project → $group/$lookup
```

```javascript
db.collection.aggregate([
  // 1. Filter early with indexed fields
  { $match: { status: 'active', createdAt: { $gte: startDate } } },

  // 2. Sort on indexed fields
  { $sort: { createdAt: -1 } },

  // 3. Reduce document size
  { $project: { name: 1, email: 1, status: 1, createdAt: 1 } },

  // 4. Expensive operations last
  { $group: { _id: '$status', count: { $sum: 1 } } },
]);
```

### Materialized Views ($merge)

Pre-compute and store aggregation results for expensive queries.

```javascript
// Create materialized view for dashboard statistics
db.orders.aggregate([
  { $match: { createdAt: { $gte: ISODate('2024-01-01') } } },
  {
    $group: {
      _id: { status: '$status', date: { $dateToString: { format: '%Y-%m-%d', date: '$createdAt' } } },
      count: { $sum: 1 },
      totalAmount: { $sum: '$amount' },
    },
  },
  { $merge: { into: 'order_daily_stats', whenMatched: 'replace', whenNotMatched: 'insert' } },
]);

// Query the materialized view instead of running expensive aggregation
db.order_daily_stats.find({ '_id.status': 'completed' });
```

**Use cases:**
- Dashboard statistics
- Reporting data
- Expensive aggregations run on schedule (cron job)

### Connection Pooling

Configure connection pools to optimize database connections.

```javascript
// Mongoose connection pooling
const mongoose = require('mongoose');

mongoose.connect(uri, {
  maxPoolSize: 100,      // Maximum connections in pool
  minPoolSize: 10,       // Minimum connections maintained
  maxIdleTimeMS: 30000,  // Close idle connections after 30s
  serverSelectionTimeoutMS: 5000,
  socketTimeoutMS: 45000,
});

// MongoDB Node.js driver
const { MongoClient } = require('mongodb');

const client = new MongoClient(uri, {
  maxPoolSize: 100,
  minPoolSize: 10,
  maxIdleTimeMS: 30000,
});
```

**Guidelines:**
- Set `maxPoolSize` based on expected concurrent operations
- Start with 100 and adjust based on monitoring
- Monitor connection usage with `db.serverStatus().connections`

## 5. MongoDB 8.0 Features

### Express Path Optimization

MongoDB 8.0 introduces optimized `_id` queries (IDHACK improvements):
- **36% faster reads** for `_id` lookups
- **59% improvement** in update throughput

```javascript
// These queries benefit from Express Path in 8.0
db.users.findOne({ _id: ObjectId('...') });
db.users.updateOne({ _id: ObjectId('...') }, { $set: { ... } });
```

### Query Settings (Replaces Index Filters)

Index Filters are deprecated in 8.0. Use Query Settings instead.

```javascript
// MongoDB 8.0: Set query settings for specific query shapes
db.adminCommand({
  setQuerySettings: {
    find: 'orders',
    filter: { status: 'active' },
    $db: 'mydb'
  },
  settings: {
    indexHints: {
      ns: { db: 'mydb', coll: 'orders' },
      allowedIndexes: ['status_1_createdAt_-1']
    }
  }
});

// View current query settings
db.adminCommand({ showQuerySettings: true });
```

### Null Query Behavior Change

```javascript
// MongoDB 8.0 changes null matching behavior

// Before 8.0: Matches documents where field is null OR field doesn't exist
db.users.find({ middleName: null });

// MongoDB 8.0+: Only matches documents where field is explicitly null
// To match missing fields, use:
db.users.find({ $or: [{ middleName: null }, { middleName: { $exists: false } }] });
```

### Reject Duplicate Indexes

MongoDB 8.0 prevents creating duplicate indexes:

```javascript
// 8.0 will reject this if an equivalent index already exists
db.collection.createIndex({ field: 1 }); // Already exists
// Error: Index already exists with a different name
```

## 6. Checklist

### P0 (Required)

**Schema:**
- [ ] Workload analysis completed with documented query patterns
- [ ] Embed vs reference decisions justified for all relationships
- [ ] Schema avoids unbounded arrays and excessive nesting
- [ ] Document size stays well under 16MB limit
- [ ] Schema versioning implemented for all production collections

**Index:**
- [ ] Compound indexes follow ESR rule (Equality → Sort → Range)
- [ ] All queries use indexes (no COLLSCAN in production)
- [ ] TTL indexes configured for time-bound data (sessions, logs)

**Query:**
- [ ] Critical queries execute under 100ms
- [ ] Aggregation pipelines start with indexed $match
- [ ] All production queries have been explained and optimized

### P1 (Recommended)

**Schema:**
- [ ] Appropriate design patterns applied for optimization
- [ ] Schema supports expected data growth patterns
- [ ] Migration strategy planned for schema changes
- [ ] Documentation includes design rationale and trade-offs

**Index:**
- [ ] Covered queries implemented where possible
- [ ] Sparse/Partial indexes used for optional or filtered data
- [ ] Index usage monitoring configured
- [ ] Index maintenance strategy defined

**Query:**
- [ ] Pipeline stages optimally ordered for data reduction
- [ ] Materialized views used for expensive aggregations
- [ ] Connection pooling properly configured
- [ ] Performance baselines documented
- [ ] Query patterns documented for team reference

**Schema (Time-series data):**
- [ ] Time-series collections used instead of manual bucket patterns (MongoDB 5.0+)

## 7. Anti-patterns

### Don't Do

| Anti-pattern | Problem |
|--------------|---------|
| Design schema based solely on logical relationships | Ignores actual access patterns |
| Create unbounded arrays | Document size can exceed 16MB |
| Embed large or frequently changing data | Causes document growth and update overhead |
| Reference data always accessed together | Requires unnecessary lookups |
| Create indexes without analyzing query patterns | Wastes resources, slows writes |
| Use COLLSCAN in production | Full collection scan is slow |
| Place $group/$lookup early in pipeline | Processes more data than necessary |
| Use $regex without anchoring (^pattern) | Cannot use index efficiently |
| Use excessive $lookup (multiple joins) | MongoDB is not optimized for joins |

### Do Instead

| Best Practice | Benefit |
|---------------|---------|
| Design schema based on workload patterns | Optimizes for actual usage |
| Limit array sizes, use bucketing for large datasets | Keeps documents manageable |
| Reference large or independently accessed data | Reduces document size |
| Embed data always read together | Single query retrieves all needed data |
| Design indexes based on actual query patterns | Targeted optimization |
| Ensure all queries use appropriate indexes | Fast query execution |
| Order pipeline stages to reduce data volume early | Efficient aggregation |
| Use indexed prefix matching for text searches | Leverages indexes |
| Limit $lookup usage and optimize join conditions | Minimizes performance impact |