Clean up unused MongoDB indexes and declare the ones actually in use

[RodriSanchez1]

Context

The boards collection has an index tiles_1 (1.3 GB in production) that no query can use. Because tiles is an array of subdocuments containing image URLs and base64 payloads, the multikey index generates one key per tile, and large tiles exceed the index key size limit. The write is then rejected entirely — this is the "index key too large" error that silently drops board updates in production.

tiles_1 is not declared anywhere in the code. It is a leftover from old Mongoose versions (v3/v4), which created an index whenever the unique key was present in a path definition, even when set to false. api/models/Board.js and api/models/Communicator.js still carry several unique: false declarations that are no-ops under the current Mongoose 8.24.3.

The database also contains hand-made indexes that are actively used but not declared in any schema (e.g. isPublic_1 on boards). These must not be dropped — see "Undeclared but used" below.

Goal

1. Drop the dead indexes, starting with boards.tiles_1, to unblock board updates and reclaim storage.
2. Declare in the schemas every index that the database actually needs, so the set of indexes becomes reproducible from code.

How to investigate

Indexes must be judged by actual usage on production, not by whether they appear in a schema. Two independent signals are needed: $indexStats usage counters and a search of the codebase for queries touching the field.

1. Dump the indexes that are not declared in code

Run in the Compass shell (or mongosh) against production. ES5 syntax and single-line, because the Compass shell rejects template literals, ?? and multiline pastes:

var d=db.getSiblingDB("<DB_NAME>"); var keep={users:["_id_","email_1"],boards:["_id_","email_1","email_1_lastEdited_1__id_1","accessGateCode_1"],accessgates:["_id_","code_1"],accessclients:["_id_","slug_1"],languages:["_id_","locale_1"],resetpasswords:["_id_","userId_1"],subscribers:["_id_","userId_1"],subscriptions:["_id_","subscriptionId_1","name_1","plans.name_1","plans.planId_1","plans.paypalId_1"],communicators:["_id_","email_1"],analytics:["_id_"],settings:["_id_"]}; var have=d.getCollectionNames(); print("db="+d.getName()+" collections="+have.length); Object.keys(keep).forEach(function(c){if(have.indexOf(c)<0){print("-- missing collection: "+c);return}var sz={};try{sz=d[c].stats().indexSizes||{}}catch(e){}var st={};try{d[c].aggregate([{$indexStats:{}}]).forEach(function(s){st[s.name]=s.accesses.ops})}catch(e){}d[c].getIndexes().forEach(function(ix){if(keep[c].indexOf(ix.name)<0){print(c+"."+ix.name+"  size="+((sz[ix.name]||0)/1048576).toFixed(1)+"MB  ops="+(st[ix.name]===undefined?"?":st[ix.name])+"  unique="+(ix.unique?true:false))}})})

The keep map lists the indexes currently declared in the schemas; anything printed is undeclared and needs a decision. Cross-check getCollectionNames() against the keep keys — any collection missing from keep was never inspected and must be added.

2. Decide per index

• ops = 0, counter running for a long time → dead. Drop it.
• ops > 0 → a real query is behind it. Find that query before touching anything, and follow "Undeclared but used" below.

Caveats when reading ops:

• $indexStats counters reset on every mongod restart. Check the "since" date shown in the Compass Indexes tab; a fresh counter proves nothing.
• Only production numbers count. In dev an index can show ops = 0 simply because nobody exercised that endpoint.
• $or queries need an index on every branch to use index union. getORQuery (api/helpers/query.js:20-24) builds {$or: [...]} over the searchFields of each listing endpoint. Dropping one branch's index degrades the whole $or, so evaluate those fields as a group, not one by one.
• The regexes built by getORQuery are case-insensitive and unanchored, so they cannot seek — but a full scan of a small index is still far cheaper than a collection scan of boards, where each document carries the tiles array. Do not dismiss those indexes just because the regex is unanchored.

3. Confirm before dropping

Anchor the decision in the code, not only in the counters: grep the field name across api/ and check whether it appears in a query filter or sort, as opposed to only a projection. For example tiles appears in api/controllers/access.js:460 and :70, but only as a projection — it is never a predicate.

Undeclared but used

When an index exists in the database, has ops > 0, and is not declared in any schema:

Do not drop it. Mongoose will not recreate it, and the query behind it silently degrades to a collection scan.

Instead:

1. Locate the query it serves and confirm the field is used as a filter or a sort.
2. Declare the index in the corresponding schema so it is reproducible from code (autoIndex is on by default; the child schemas with autoIndex: false are the exception).
3. Consider whether a compound index fits the real query better than the existing single-field one. If a compound supersedes the old index as a prefix, create the compound first, let it accumulate ops for a few days, and only then drop the old one — so there is no window without an index if the planner chooses differently than expected.

Known case: isPublic_1 on boards, used by getPublicBoards (api/controllers/board.js:157). The full query is { isPublic: true, accessGateCode: null } sorted by -_id (the default from api/helpers/response.js:5). isPublic is a boolean, so on its own it filters almost nothing and the sort still runs in memory. A better fit:

boardSchema.index({ isPublic: 1, accessGateCode: 1, _id: -1 });

Findings so far (dev database, cboard-api)

Production numbers are still pending; these are from dev and are only indicative.

Index	Size	ops	Verdict
boards.tiles_1	7.5 MB (1.3 GB in prod)	0	Drop
boards.caption_1	0.1 MB	0	Drop — caption is in no searchFields
boards.description_1	0.1 MB	0	Drop — description is not searched on boards
boards.name_1	0.1 MB	4	Keep + declare — used by the $or search
boards.author_1	0.1 MB	4	Keep + declare — used by the $or search
communicators.rootBoard_1	0.0 MB	0	Drop — only findById; isRootBoard() filters in JS (Communicator.js:106)
communicators.defaultBoardsIncluded_1	0.0 MB	0	Drop — array of objects, same "key too large" risk as tiles_1
communicators.name_1	0.0 MB	0	Review on prod — the field is in searchFields (communicator.js:45,64)
communicators.author_1	0.0 MB	0	Review on prod — same
communicators.description_1	0.0 MB	0	Review on prod — same

Note: the dev run reported 12 collections while the keep map covers 11 (and analytics does not exist in dev), so two collections were never inspected. Run getCollectionNames() and extend the map.

Missing indexes found along the way

• settings.user — Settings.getOrCreate runs findOne({ user: user.id }) (api/models/Settings.js:40) on every login (api/controllers/user.js:75) and on every settings GET/PUT. The field has no index at all: a collection scan per request.

  settingsSchema.index({ user: 1 }, { unique: true });

  unique also closes the race in getOrCreate that can duplicate settings, but check for existing duplicates first or index creation will fail:

  db.settings.aggregate([{$group:{_id:"$user",n:{$sum:1}}},{$match:{n:{$gt:1}}}])

• communicators.email — used by Communicator.deleteMany({ email }) on account deletion (api/controllers/account.js:49-51). The index exists only as a leftover of the old Mongoose bug; it must be declared so it is not lost.

Tasks

• Run the investigation script against production and record the output
• List the collections not covered by the keep map and inspect their indexes
• Drop boards.tiles_1 (low-traffic window: 1.3 GB, takes a collection-level lock)
• Drop the remaining confirmed-dead indexes
• Declare in the schemas the indexes that are used but undeclared (boards.name, boards.author, boards.isPublic compound, communicators.email)
• Add the missing settings.user index (after checking for duplicates)
• Remove the dead unique: false declarations from Board.js and Communicator.js so the models stop suggesting indexes that do not exist