Mongodb Database Architect

Abort Conditions:

• No workload type or access patterns provided → emit TODO list with required inputs.
• Data volume completely unknown → warn that sizing recommendations will be generic.

Procedure

T1: Quick Schema Review & Index Recommendations (≤2k tokens)

Use Case: Fast path for common scenarios (80% of requests).

Steps:

1. Analyze Access Patterns: Identify top 3-5 most frequent queries.
2. Document Modeling Decision:
   • Embed if 1-to-1 or 1-to-many relationships, low cardinality, read-heavy, atomic updates needed.
   • Reference if many-to-many, frequently changing data, high cardinality, document size >16 MB risk.
3. Index Recommendations (ESR Rule):
   • Equality fields first (exact match filters: {status: "active"})
   • Sort fields next (sort order: .sort({created_at: -1}))
   • Range fields last (range queries: {age: {$gt: 18}})
   • Example: db.users.createIndex({status: 1, created_at: -1, age: 1})
4. Top 3 Bottlenecks: Identify missing indexes, sequential scans, document size issues.
5. Quick Wins: Provide 1-3 immediate optimizations with estimated speedup (e.g., "Add index on email → 100x faster login").

Output: Schema design decision, 3-5 index recommendations with ESR justification, top 3 bottlenecks.

T2: Complete Architecture Design (≤6k tokens)

Use Case: Comprehensive architecture for production deployments.

Steps:

1. Document Schema Design

Embedding vs Referencing Table:

Schema Validation (MongoDB 8.0):

db.createCollection("users", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["email", "created_at"],
      properties: {
        email: {bsonType: "string", pattern: "^.+@.+$"},
        age: {bsonType: "int", minimum: 0, maximum: 150},
        created_at: {bsonType: "date"}
      }
    }
  }
})

2. Advanced Indexing Strategy

Index Types and Use Cases:

Covered Query Optimization:

// Covered query: all fields in index, no document access
db.orders.createIndex({user_id: 1, status: 1, total: 1})
db.orders.find({user_id: 12345, status: "shipped"}, {_id: 0, user_id: 1, status: 1, total: 1})
// explain() shows: totalDocsExamined: 0 (covered by index)

ESR Rule Application:

// Query: Find active users, sort by created_at descending, filter age > 18
db.users.find({status: "active", age: {$gt: 18}}).sort({created_at: -1})

// Correct index (ESR):
// Equality: status (exact match)
// Sort: created_at (sort field)
// Range: age (range filter)
db.users.createIndex({status: 1, created_at: -1, age: 1})

3. Sharding Strategy

Shard Key Selection (MongoDB 8.0):

Hashed Sharding Example:

// Enable sharding on database
sh.enableSharding("myapp")

// Shard collection with hashed shard key
sh.shardCollection("myapp.users", {user_id: "hashed"})

// MongoDB 8.0: Move unsharded collection to specific shard
db.adminCommand({moveCollection: "myapp.analytics", toShard: "shard02"})

Ranged Sharding Example (Time-Series):

// Shard by timestamp for time-series data
sh.shardCollection("myapp.events", {timestamp: 1})

// Zone sharding (MongoDB 8.0): Route data by date ranges to specific shards
sh.addShardToZone("shard01", "recent")
sh.updateZoneKeyRange("myapp.events", {timestamp: ISODate("2025-01-01")}, {timestamp: MaxKey}, "recent")

Avoid Scatter-Gather Queries:

• Include shard key in queries: db.users.find({user_id: 12345}) → targets single shard
• Without shard key: db.users.find({email: "[email protected]"}) → scatter-gather across all shards (slow)
• Exception: Large aggregations benefit from parallelism across shards

4. Aggregation Pipeline Optimization

Pipeline Stages (Execution Order Matters):

// Optimized aggregation: $match early, $project late, use indexes
db.orders.aggregate([
  // Stage 1: $match FIRST (uses index, reduces documents)
  {$match: {status: "shipped", created_at: {$gte: ISODate("2025-01-01")}}},

  // Stage 2: $sort (uses index if compound index exists)
  {$sort: {created_at: -1}},

  // Stage 3: $lookup (join with users collection)
  {$lookup: {
    from: "users",
    localField: "user_id",
    foreignField: "_id",
    as: "user_details"
  }},

  // Stage 4: $group (aggregation after filtering)
  {$group: {
    _id: "$user_id",
    total_spent: {$sum: "$total"},
    order_count: {$sum: 1}
  }},

  // Stage 5: $project LAST (reduce network transfer)
  {$project: {_id: 1, total_spent: 1, order_count: 1}}
])

Index Sort Optimization:

• If {status: 1, created_at: -1} index exists, $match + $sort uses index (no in-memory sort).
• Without index, MongoDB sorts in memory (limited by 100 MB unless allowDiskUse: true).

Sharded Aggregation (MongoDB 8.0):

• Aggregations run in parallel on each shard, then merge results.
• Use $match early to enable shard targeting.
• MongoDB 8.0: 32% overall performance improvement in aggregations.

5. Replica Set Configuration

Standard 3-Member Replica Set:

rs.initiate({
  _id: "myReplicaSet",
  members: [
    {_id: 0, host: "mongo1.example.com:27017", priority: 2},  // Primary (high priority)
    {_id: 1, host: "mongo2.example.com:27017", priority: 1},  // Secondary
    {_id: 2, host: "mongo3.example.com:27017", arbiterOnly: true}  // Arbiter (no data)
  ]
})

Read Preferences:

• primary (default): All reads from primary (strong consistency).
• primaryPreferred: Read from primary, fallback to secondary if unavailable.
• secondary: Read from secondary (may read stale data).
• secondaryPreferred: Read from secondary, fallback to primary.
• nearest: Read from lowest-latency member.

Write Concerns:

• w: 1 (default): Acknowledge after primary write (fast, risk of data loss on primary failure).
• w: "majority": Acknowledge after majority of replica set members (slower, durable).
• w: 3: Acknowledge after 3 members (explicit count).
• j: true: Wait for write to journal (disk) before acknowledging.

MongoDB 8.0 Replica Set Enhancements:

• Faster concurrent writes during replication.
• Disable "majority" read concern for PSA (Primary-Secondary-Arbiter) to avoid cache pressure.

6. WiredTiger Cache & Connection Pool Tuning

WiredTiger Cache Sizing (MongoDB 8.0):

# Default: 50% of RAM - 1 GB