Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting

What it means

When no index gives documents in the order you asked for, MongoDB sorts them itself, in memory. That sort may use at most 100 MB (104,857,600 bytes). Past that it can write temporary files to disk and carry on, but only if the query allows disk use. This one didn’t, so the server stopped with code 292, QueryExceededMemoryLimitNoDiskUseAllowed.

Since MongoDB 6.0, disk use is allowed by default (the server parameter allowDiskUseByDefault is true), so on a self-hosted server you usually see this error only when something turns it off. On Atlas free and Flex clusters, Atlas ignores allowDiskUse and every query behaves as if it were false; free clusters also have a 32 MB sort limit instead of 100 MB.

An aggregation’s $sort gives the same message after PlanExecutor error during aggregation :: caused by ::. The same limit for $group has its own message.

Common causes

1. No index for the sort. find().sort({ placedAt: -1 }) on a field without an index sorts the whole result in memory.
2. Disk use turned off: allowDiskUse(false) on the query, { allowDiskUse: false } on an aggregation, allowDiskUseByDefault set to false on the server, or a server older than 6.0 where nothing was passed.
3. An Atlas free or Flex cluster, which never writes temporary files.
4. Whole documents sorted when a few fields would do. Every field of every document counts towards the 100 MB, including large text and arrays you don’t display.
5. No limit. Sorting everything to show the first page keeps every document in memory; with a limit, MongoDB keeps only the top documents.

How to fix it

Give the sort an index

An index on the sort fields returns documents already in order, so there’s no in-memory sort at all:

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

When you also filter, put the equality fields first and the sort fields after them: for find({ status: "paid" }).sort({ placedAt: -1 }), index { status: 1, placedAt: -1 }. Check the plan with .explain(): an IXSCAN and no SORT stage means the index does the sorting.

Sort less

Add a limit when you only need the first page, and project only the fields you need:

db.orders.find({}, { customer: 1, total: 1, placedAt: 1 }).sort({ placedAt: -1 }).limit(50)

In a pipeline, put $match and $project before $sort, and $limit straight after it.

Allow disk use

On a self-hosted server or a dedicated Atlas cluster (M10 and up), let the sort spill to disk:

db.orders.find().sort({ placedAt: -1 }).allowDiskUse()
db.orders.aggregate([{ $sort: { placedAt: -1 } }], { allowDiskUse: true })

It works, but it’s slower than an index. To see the server’s default:

db.adminCommand({ getParameter: 1, allowDiskUseByDefault: 1 })

On Atlas free or Flex clusters

Disk use isn’t available, so an index, a limit or a smaller projection are the only fixes, or a dedicated cluster.

Reproduce it

A temporary MongoDB 8.0.32 container of our own (mongo:8.0, removed afterwards), with 150,000 orders of about 1 KB each (161 MB) and no index on placedAt. The server’s defaults: allowDiskUseByDefault: true and internalQueryMaxBlockingSortMemoryUsageBytes: 104857600.

db.orders.find().sort({ placedAt: -1 }).allowDiskUse(false)

MongoServerError[QueryExceededMemoryLimitNoDiskUseAllowed]: Executor error during find command: seo_err_mongo.orders :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting.

The same sort as an aggregation with { allowDiskUse: false }:

MongoServerError[QueryExceededMemoryLimitNoDiskUseAllowed]: PlanExecutor error during aggregation :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting.

Each of these worked with allowDiskUse(false) still set:

• .limit(20) after the sort;
• a projection of three fields before the sort, in find and in a pipeline.

Without allowDiskUse(false) (the default), the full sort returned all 150,000 documents in 1.8 seconds, and explain("executionStats") showed usedDisk: true. After createIndex({ placedAt: -1 }), the plan was an IXSCAN with no sort stage and the query returned everything with allowDiskUse(false).

In Inlet

Filters and sorting in Inlet’s grid run on the server, so an index on the field you sort by helps there too. Queries use mongosh syntax, so db.orders.find().sort({ placedAt: -1 }).limit(50) and db.orders.createIndex({ placedAt: -1 }) run as they do in mongosh, and a refused query shows the server’s message.