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

[daslaller]

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)

[daslaller]

Workaround for anyone hitting this now

You don't need to wait for a code fix to get relief. This is a one-command install — it patches every currently-affected collection immediately, and sets up a cron job so any collection created afterward gets patched automatically too (since the bug is in createCollection() itself, every new collection Appwrite creates gets the same broken index by default until this is fixed upstream):

curl -fsSL https://gist.githubusercontent.com/daslaller/f13cca7339404c7a4a188ce0a7fc1ae0/raw/install-uid-collation-fix.sh | bash

Run it from the directory containing your Appwrite .env (or set APPWRITE_DIR=/path/to/appwrite first if it's elsewhere). Full source: https://gist.github.com/daslaller/f13cca7339404c7a4a188ce0a7fc1ae0

What it actually does, in plain terms: it forces _uid lookups back onto MongoDB's native (case-sensitive) collation instead of the artificial MariaDB-style case-insensitive one the internal index was built with — by adding a second _uid index with no collation override alongside the existing one. This is a deliberate semantic choice, not just a technical patch: it drops the case-insensitive-duplicate-ID guarantee (matching MariaDB's default behavior) in favor of restoring fast, native Mongo lookups. If your application genuinely depends on case-insensitive ID collisions being rejected, this workaround isn't a fit for you — wait for or help implement the query-side collation fix in the section above instead. For everyone else, this is almost certainly what you want.

What it does, mechanically, before you run it:

• Adds a corrected _uid index (no collation) alongside the existing broken one, on any collection that needs it — it never drops, modifies, or touches any existing index or document data. Fully additive, safe on a live instance.
• Runs once immediately for instant relief, then installs a cron entry (default every 5 minutes, override with CRON_SCHEDULE) so newly-created collections get caught quickly rather than sitting broken until someone notices.
• Uses your existing _APP_DB_USER/_APP_DB_PASS from .env — no elevated/root credentials needed.
• Defaults assume the standard docker-compose container name (appwrite-mongodb); override with MONGO_CONTAINER=, AUTH_DB=, or APPWRITE_DIR= env vars if your setup differs.

Verify it worked on any collection with:

db.getCollection("<name>").find({_uid:"x"}).explain("executionStats").queryPlanner.winningPlan
// before: { stage: "COLLSCAN", ... }
// after:  { stage: "IXSCAN" or "EXPRESS_IXSCAN", indexName: "_uid_ci_fix" }

Ran this across a full production instance (288 collections, every project) — 287 fixed cleanly in one pass, zero downtime, zero data loss. Load average dropped from 10.17 to 0.68 on an 8-core box once the fix was in, and a real ~29,000-document seed run that previously took 60+ minutes (unfinished, killed) completed in 6m29s afterward — roughly a 10x wall-time improvement from this alone. The one exception was a genuine pre-existing duplicate _uid value in an unrelated collection (needs a manual dedup decision, not something this script can auto-resolve — it will just report it and move on rather than guess).

This is a workaround, not a fix — the real fix is the upstream code change described above. Happy to open a PR for it if that'd help move this along.