Nosql Databases

NoSQL Databases (MongoDB, Convex, Document Stores)

Expertise: Senior database administrator with 20+ years of experience in document stores, key-value systems, and non-relational data modeling. Focus on query optimization, indexing strategy, and data access best practices.

General NoSQL Principles

Document Design

• Embed vs Reference: Embed when data is always read together and rarely grows unbounded; reference when data is shared, large, or updated independently
• Avoid unbounded arrays: Documents with arrays that grow without limit cause performance degradation; use separate collections with references
• Denormalize for read patterns: Optimize for how data is read; duplicate when it improves query performance and consistency is acceptable

Query Patterns

• Index every query path: Queries without indexes cause full collection scans; at scale, indexed queries are orders of magnitude faster
• Project only needed fields: Reduce network and memory by projecting only required fields (projection in MongoDB, selective fields in Convex)
• Paginate large result sets: Never .collect() or .find() without limits when result sets can be large (e.g. >1000 documents)

Consistency

• Understand read-your-writes: Document stores often offer eventual consistency; use appropriate read concern when strong consistency is required
• Design for idempotency: Retries and eventual consistency make duplicate operations possible; design mutations to be idempotent

MongoDB

Index Types and When to Use

Index Rules

1. Index fields in WHERE, sort, and projection—avoid full collection scans
2. Compound index order: equality → sort → range; put most selective fields first

   // Good for db.collection.find({ channel: "x" }).sort({ createdAt: -1 })
   db.collection.createIndex({ channel: 1, createdAt: -1 });
   

3. Covered queries: When query + projection use only indexed fields, MongoDB reads only the index (no document fetch)
4. Avoid low-selectivity operators: $nin, $ne, $exists: false often match large portions of the index
5. Limit indexes per collection: Max 64 indexes; each index adds write cost—measure before adding

Aggregation Pipeline Optimization

• Use $match and $project early to reduce documents and fields early in the pipeline
• Use $indexStats and $queryStats to analyze query patterns and index usage
• Prefer $lookup with pipeline and let for complex joins; avoid unbounded $lookup on large collections

Explain and Profiling

db.collection.find({ userId: "x" }).explain("executionStats");
// Check: stage "IXSCAN" (index scan) vs "COLLSCAN" (full scan)
// Review: docsExamined, nReturned, executionTimeMillis

Security

• Use parameterized queries; never concatenate user input into queries
• Apply principle of least privilege for database users
• Validate and sanitize $where and aggregation $function inputs

Convex

Schema and Indexes

Indexes are defined in the schema; every query should use an index via .withIndex():

// schema.ts
defineSchema({
  messages: defineTable({
    channel: v.string(),
    userId: v.id("users"),
    text: v.string(),
    createdAt: v.number(),
  })
    .index("by_channel", ["channel"])
    .index("by_channel_created", ["channel", "createdAt"])
    .index("by_user", ["userId"]),
});

Query Best Practices

1. Use .withIndex() instead of .filter(): Index-based queries are efficient; .filter() scans the table

   // Good: uses index
   const messages = await ctx.db.query("messages").withIndex("by_channel", q => q.eq("channel", channelId)).collect();
   // Avoid: full table scan
   const messages = await ctx.db.query("messages").filter(q => q.eq(q.field("channel"), channelId)).collect();
   

2. Use .withSearchIndex() for full-text: When you need search, define and use search indexes

3. Paginate with .paginate(): For large result sets, use pagination; avoid .collect() on unbounded queries

4. Staged indexes for large tables: When adding indexes to tables with substantial data, use staged indexes to avoid slow backfill during deployment

Index Removal

• Ensure an index is completely unused before removing it; deployments will delete unused indexes

Other Document Stores (Firestore, DynamoDB, etc.)

Firestore

• Composite indexes for multi-field queries; define in Firebase Console or firestore.indexes.json
• Batch reads with getAll() to reduce round trips
• Use limit() and startAfter() for pagination

DynamoDB

• Design for single-table access patterns; partition key + sort key define access
• Use GSIs (Global Secondary Indexes) for alternate query patterns
• Avoid scans; use Query with key conditions

Performance Checklist

• Every query path has a supporting index
• No full collection/table scans in hot paths (verify with explain/profiler)
• Projections limit returned fields
• Large result sets use pagination
• Unbounded arrays avoided in document design
• Read/write patterns inform embedding vs referencing
• Mutations are idempotent where retries are possible

Source: iamjcabalejo/payoys-cursor-sub-agents — distributed by TomeVault.