MongoDB adapter: _uid index has a hardcoded case-insensitive collation, but no document lookup ever sets a matching collation — causes universal COLLSCAN on every ID-based read/write

Start in src/Database/Adapter/Mongo.php, reading createCollection(), getTransactionOptions(), and the document-access methods named in the issue. Reproduce the _uid lookup with MongoDB explain("executionStats") and compare it with the explicitly collated query. Confirm the chosen fix preserves the intended behavior, uses the _uid index, and has coverage for the affected reads and writes.

Summary

On the MongoDB adapter, every collection's internal _uid unique index is created with an explicit, hardcoded collation ({locale: 'en', strength: 1}), but createCollection() never gives the collection itself a matching default collation, and none of the document-access methods (getDocument, find, updateDocument, upsertDocuments) ever attach a matching collation to their queries either. Per MongoDB's index-selection rules, an index can only serve a query whose effective collation matches its own — since the collection's effective collation is the server default ("simple"/binary) and the _uid index's is {locale:'en', strength:1}, the _uid index can never be used for any lookup by ID, on any collection, on any MongoDB-backed Appwrite install. Every such lookup falls back to a full collection scan (COLLSCAN).

This is not limited to bulk/upsert operations — it affects plain getDocument() (fetch-by-ID) and find() filtering on _uid identically, which is some of the hottest code in the whole system (auth/session checks, every relationship lookup, etc.). Cost scales with collection size, so it's invisible on small/fresh collections and becomes a severe, unmistakable production issue once a collection grows large — we saw MongoDB consuming 600%+ CPU on an 8-core box from this alone, on a collection of ~16k documents.

Worth flagging for priority: MongoDB is the pre-selected default in Appwrite's own self-hosting setup wizard ("Database - Choose between MongoDB or MariaDB as your database backend. MongoDB is selected by default."). This isn't a niche adapter path — it's plausibly the most common outcome for new self-hosted installs.

Root cause (exact code references, checked against the commit Appwrite 1.9.5 pins: utopia-php/database v6.0.0 @ fff9f0effbd40359ff925741ff9424856d8b4fde)

src/Database/Adapter/Mongo.php, createCollection() (~L461–531): the internal index array hardcodes collation only on _uid:

$internalIndex = [
    [
        'key' => ['_uid' => $this->getOrder(Database::ORDER_ASC)],
        'name' => '_uid',
        'unique' => true,
        'collation' => ['locale' => 'en', 'strength' => 1],
    ],
    [ 'key' => ['_createdAt' => ...], 'name' => '_createdAt' ], // no collation
    ...

The createCollection Mongo command itself is issued right above with options built solely from getTransactionOptions() — no collation is ever passed at the collection level.

The shared options helper, getTransactionOptions() (~L327–334):

private function getTransactionOptions(array $options = []): array
{
    if ($this->inTransaction > 0 && $this->session !== null) {
        $options['session'] = $this->session;
    }
    return $options;
}

This is the only source of $options for getDocument() (~L1237), find() (~L2476), updateDocument() (~L1643), and upsertDocuments() (~L2051). It only ever adds session. collation never appears in any of these methods.

Confirmed the low-level utopia-php/mongo client (Client::find()/Client::update()/Client::upsert()) faithfully forwards a collation option into the raw command if given one — nothing downstream strips it. The adapter simply never constructs the key.

Why it's there in the first place

Traced via git blame to ea2bf442, part of PR #49 ("Throw exception on case-insensitive duplicate IDs", merged 2021-08-14), which deliberately made Mongo's _uid index case-insensitive to match MariaDB's default case-insensitive duplicate-ID behavior — a real, intentional cross-adapter consistency decision, not a mistake. The bug isn't the collation choice itself; it's that no corresponding query-time or collection-level collation was ever added to make that choice actually usable.

Reproduction

// on any Mongo-backed Appwrite collection with a reasonable number of documents:
db.getCollection(coll).find({_uid: "some-id"}).explain("executionStats").queryPlanner.winningPlan
// → { stage: "COLLSCAN", ... }

// same query, with the index's own collation explicitly supplied:
db.getCollection(coll).find({_uid: "some-id"}).collation({locale:"en", strength:1}).explain("executionStats").queryPlanner.winningPlan
// → { stage: "EXPRESS_IXSCAN", indexName: "_uid" }

Suggested fix

Recommended: drop the artificial case-insensitivity and let Mongo use its native collation. The 2021 rationale (matching MariaDB's default case-insensitive duplicate-ID behavior) made sense when both adapters were on comparably equal footing, but MongoDB is now the default self-host choice, which changes the calculus — preserving byte-for-byte behavioral parity with a secondary/legacy adapter is a much weaker justification for eating a full collection scan on every ID lookup than it would be between two equally-primary options. Concretely: remove the collation key from the _uid index definition in createCollection(), so it matches the collection's own (unset/simple) default like every other index already does. This requires no query-side changes at all — the mismatch disappears because there's nothing left to mismatch. Verified this exact change on a production instance: 287 collections rebuilt this way, explain() confirms IXSCAN/EXPRESS_IXSCAN on every one, load average dropped from 10.17 to 0.68 on an 8-core box, and a real ~29,000-document seed workload that previously took 60+ minutes (unfinished) completed in 6m29s afterward.

Trade-off to be upfront about: this changes observable behavior for anyone currently relying on case-insensitive duplicate-ID rejection on Mongo-backed installs ("ABC" and "abc" would become distinct, valid, coexisting IDs instead of colliding) — worth a changelog callout if adopted, and clearly not appropriate to silently backport to a patch version without one.

If cross-adapter parity with MariaDB needs to be preserved instead (e.g. for existing installs that already depend on the case-insensitive behavior), either of these would also work, implemented at different layers:

1. Attach collation: ['locale' => 'en', 'strength' => 1] to the $options array in getDocument(), find(), updateDocument(), updateDocuments(), and upsertDocuments() in Mongo.php, or
2. Give every collection a matching default collation in createCollection()'s Mongo command, so per-query collation becomes unnecessary.

A pragmatic, no-behavior-change workaround for anyone hitting this right now (drop-collation approach, additive-only, doesn't touch existing data or indexes) is in the comment below, including a one-command installer.

Happy to open a PR for whichever approach the maintainers prefer.

Environment

• Appwrite 1.9.5, self-hosted, MongoDB adapter (MongoDB 8.2.5)
• utopia-php/database 6.0.0, utopia-php/mongo 1.1.0 (per composer.lock)