Your Scenario & Problem #

You're using a MongoDB schema where each entity is stored as a top-level document with an entity_id (UUID) and an updates array containing tens of thousands of time-sorted records. To count entities updated in the last n hours, you wrote this aggregation:

db.getCollection('updates').aggregate([
  {"$project": {last_update: {"$arrayElemAt": ["$updates", -1]}}},
  {"$replaceRoot": {newRoot: "$last_update"}},
  {"$match": {timestamp: {"$gte": new Date(...)}}},
  {"$count": "count"}
])

But the query times out after 15 seconds—while the $project step alone takes <2 seconds, adding $match causes massive CPU/IO spikes. You suspect MongoDB is loading full updates arrays instead of just accessing the last element.

Step 1: Optimize the Current Aggregation Query #

The root issue here is that your current pipeline processes all documents first (extracting the last update) before filtering, which forces MongoDB to load every large updates array into memory. Here's how to fix this:

a. Filter First with $expr

Use $expr in an initial $match stage to filter documents where the last element of updates falls within your time window. This reduces the number of documents you need to process in later stages:

db.getCollection('updates').aggregate([
  {
    "$match": {
      "$expr": {
        "$gte": [{"$arrayElemAt": ["$updates.timestamp", -1]}, new Date(...)]
      }
    }
  },
  {"$count": "count"}
])

This way, MongoDB only touches documents that could potentially qualify, instead of loading every single large array upfront.

b. Precompute & Index the Last Update Time

For even better performance, add a dedicated last_updated field to each entity document that you update every time you append to the updates array. Then create a single-field index on last_updated:

// When adding a new update to an entity
db.updates.updateOne(
  { entity_id: "your-uuid" },
  {
    "$push": { "updates": { timestamp: new Date(), value: 42 } },
    "$set": { "last_updated": new Date() }
  }
)

// Create the index
db.updates.createIndex({ last_updated: 1 })

Now your query becomes trivial and lightning-fast:

db.updates.countDocuments({ last_updated: { "$gte": new Date(...) } })

This eliminates the need for aggregation entirely and leverages the index to scan only relevant documents.

Step 2: Evaluate Your Array-Based Storage Pattern #

Your suspicion is correct: MongoDB can't optimize access to the last element of an array efficiently when the array is massive. Even if you use $arrayElemAt, MongoDB still needs to load the entire array into memory to access the last element—this is why your CPU/IO spikes when adding the $match stage.

This pattern has other downsides:

• Write amplification: Appending to a large array requires rewriting the entire document, which gets slower as the array grows.
• Memory pressure: Processing these documents forces MongoDB to load huge arrays into RAM, which can degrade overall cluster performance.
• Limited query flexibility: You can't easily query for updates within a time range for a subset of entities without scanning full arrays.

Step 3: Revisiting the Per-Update Top-Level Document Pattern #

You mentioned this pattern performed poorly before, but that's almost certainly due to missing or inefficient indexes. Let's fix that:

Schema for Per-Update Documents

Each update is a top-level document with:

{
  "entity_id": "uuid",
  "timestamp": Date(...),
  "value": 10
}

Optimized Query with Proper Indexing

Create a compound index on entity_id (ascending) and timestamp (descending):

db.updates.createIndex({ entity_id: 1, timestamp: -1 })

Then use this aggregation to count entities with updates in the last n hours:

db.updates.aggregate([
  { "$match": { timestamp: { "$gte": new Date(...) } } },
  { "$group": { "_id": "$entity_id" } },
  { "$count": "count" }
])

MongoDB can use the compound index to quickly find all updates in the time window, then group by entity_id to count unique entities. If you need even better performance, you can use a covered query (since entity_id is already in the index), so MongoDB doesn't need to load the full documents.

When to Prefer This Pattern

Use this per-update pattern if:

• You need to query individual updates frequently.
• Your updates arrays are growing to tens of thousands of elements (or more).
• You want to avoid write amplification and memory pressure from large arrays.

Final Recommendation #

If you can modify your schema, adding a last_updated field to your current array-based documents is the quickest win for your specific count query. However, long-term, the per-update top-level document pattern (with proper indexing) will be more scalable and flexible as your data grows.

内容的提问来源于stack exchange，提问作者Ameo