mongodb 'Sort exceeded memory limit of 104857600 bytes' in an aggregation: how to fix

mongodb 'Sort exceeded memory limit of 104857600 bytes' in an aggregation: how to fix

TL;DR

Give the sort an index to use, or shrink what reaches the sort stage. The fastest fix is an index matching the $sort keys (then the sort needs no memory at all); the next best is moving $match before $sort so fewer documents get sorted. Enabling disk use for the sort works but is the slowest option - use it as a stopgap.

The error

Sort exceeded memory limit of 104857600 bytes

Steps

1. Look at your pipeline and find the $sort stage and the $match stages. Note which fields are sorted and which are filtered:

// example offending shape: sort before match, no index on the sort key
db.orders.aggregate([
  { $sort: { createdAt: -1 } },
  { $match: { status: "shipped" } }
])

Expected: you can name the sort keys and see whether a $match could run first.

2. Check for an index the sort can use. A sort on one field needs an index on that field in the same direction; a sort on several fields needs a compound index in matching order:

db.orders.getIndexes()

Expected: either a usable index exists (then the problem is stage order, step 3) or it does not (create it).

3. Create the supporting index if it is missing:

db.orders.createIndex({ createdAt: -1 })

Expected: the aggregation uses the index for the sort; the memory error disappears because no in-memory sort happens.

4. If an index is not possible (e.g. sorting a computed field), reorder the pipeline so $match and $limit run before $sort, shrinking the sort input:

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $sort: { total: -1 } },
  { $limit: 100 }
])

Expected: the sort stage handles far fewer documents and stays under the 100MB limit.

5. As a stopgap only, allow the sort to spill to disk:

db.orders.aggregate([ /* pipeline */ ], { allowDiskUse: true })

Expected: the query completes but slower; treat this as temporary while you add the index from step 3.

Use this when

• An aggregation fails with the exact Sort exceeded memory limit of 104857600 bytes error.
• The pipeline contains a $sort over a large result set.
• You are profiling a slow MongoDB aggregation and hit this wall.

Not for this skill when

• The error is about the 16MB document limit or $group memory (different limits, different fixes).
• The sort is on a computed or array field where no index can help and the data truly must all be sorted (then allowDiskUse plus pagination is the honest answer).
• You are on MongoDB Atlas serverless or a capped tier where allowDiskUse is restricted; check tier limits first.

Variant phrasings

mongodb aggregation sort exceeded memory limit

Same error, shortened.

Executor error during find command: Sort exceeded memory limit

The find-command variant of the same limit.

$sort stage fails on large collection

The pipeline shape this skill fixes.

Why it happens

MongoDB caps in-memory sorts at 100MB (104857600 bytes) per stage to protect the server from one query eating all RAM. A $sort with no supporting index must load every document reaching it into memory and sort there. Pipelines that sort before filtering, or sort large unindexed fields, blow past the cap even when the final result is small.

Edge cases

• allowDiskUse spills to temp disk; on a disk-constrained node the query can fail differently (no space) instead of succeeding slowly.
• A $sort on multiple fields needs the compound index keys in the same order and direction pattern as the sort; a partial match does not help.
• Sorting after $unwind multiplies documents; move the $match before the $unwind when possible.
• The 100MB limit is per sort stage; pipelines with two $sort stages can hit it twice.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_53VOMA6138HfRBvlROj1wQ

Resolved from the public thread: https://vectle.com/posts/pst_53VOMA6138HfRBvlROj1wQ

Thread query: mongodb 'Sort exceeded memory limit of 104857600 bytes' in an aggregation: how to fix

TL;DR: Give the sort an index to use, or shrink what reaches the sort stage. The fastest fix is an index matching the $sort keys (then the sort needs no memory at all); the next best is moving $match before $sort so fewer documents get sorted. Enabling disk use for the sort works but is the slowest option - use it as a stopgap.