RFC: make MongoDB localized index counts independent of locale count

[cbratschi]

Decision requested

Agree on an opt-in normalized MongoDB index strategy for localized scalar
fields, initially covering unique: true and indexed equality queries. The
strategy should preserve the existing public document shape while keeping the
number of MongoDB indexes constant as locales are added.

The first code contribution should be a focused integration test and prototype,
not an automatic migration or an immediate change of the default. Extending the
strategy to general index: true, range queries, and sorting should follow only
after query plans and benchmarks are accepted.

Summary

Payload currently expands index: true and unique: true on a localized
field into one MongoDB index per configured locale. The number of indexes
therefore grows with both the number of localized indexed fields and the
number of locales.

This RFC proposes an adapter-level normalized index strategy whose index count
does not grow when locales are added. The first implementation should focus on
localized scalar fields with unique: true, then extend the same representation
to non-unique indexed fields once its query and sorting semantics are agreed.

The change is specific to MongoDB. Payload's PostgreSQL adapter stores
localized scalar values as rows in a locale table and creates one index on the
field and _locale, independent of the number of configured locales.

References

• #13464: Localized text field with index/unique creates one index per locale
• #13469: document the current limitation
• #14025: collection-level locale-path indexes are not supported
• #14071: remove the unsupported locale-path workaround from the documentation
• MongoDB limits and thresholds
• MongoDB unique compound indexes
• MongoDB multikey indexes
• MongoDB write-operation performance
• MongoDB equality, sort, and range guideline
• MongoDB unique-index restrictions for sharded collections

Problem

The MongoDB adapter currently copies a localized field's index and unique
options to every locale-specific Mongoose schema path. For L locales and F
localized indexed fields, those fields consume:

L × F indexes

The collection's total is approximately:

1 _id index + ordinary collection indexes + (L × F)

MongoDB permits at most 64 indexes per collection. A collection with 25
locales and two localized unique fields already consumes 50 indexes before its
ordinary indexes and the _id index are counted. This makes the supported
number of languages an accidental schema limit.

A focused probe against the current remote Payload main at
b2379e821234f8a0475ac10e115ac4d771034916 confirmed the current behavior:
four locales and two localized unique fields generated eight sparse unique
indexes.

The multiplier also applies to Payload-owned fields. A draft-enabled collection
with localized content gets a localized _status field with index: true.
Version collections copy the original indexed fields into version even though
uniqueness is disabled there.

An 11-locale schema probe produced these representative totals, including the
automatic _id index:

Collection pattern	Current	Unique-only milestone	Full normalized target
Draft content with localized unique path, localized _status, timestamps, and trash	26	16	6
Corresponding autosave/version collection	32	22	11
Non-draft category with localized unique name and path	26	5	5
Tag with one localized unique name	14	4	4

The unique-only milestone assumes one shared normalized unique index for the
main collection and one shared non-unique index for the corresponding fields in
version collections, where Payload disables uniqueness. Payload's localized
_status remains per-locale in that milestone. The full target also normalizes
ordinary localized indexes such as _status.

The targets are illustrative until the representation is integrated into
Payload. Eleven locales do not by themselves exhaust MongoDB's limit, but the
version pattern already consumes half of it. Adding three more localized
indexed fields to that version schema raises the total from 32 to 65.

The existing issue was closed after a documentation-only change. A later issue
confirmed that collection-level locale paths are not accepted by Payload's
index configuration, and the documentation removed that workaround. No open
issue or pull request found during the current recheck implements a scalable
replacement. The limitation remains in the current MongoDB adapter.

Correctness requirements

Any replacement must preserve these semantics:

• A value must be unique for the same field and locale across documents.
• The same value may be used in different locales.
• Values from different fields must not collide.
• Missing, null, and empty values must retain the current sparse-index
  behavior.
• Concurrent writes must be protected by a database constraint, not only by
  application validation.
• Create, update, delete, bulk, Local API, REST, GraphQL, drafts, autosave,
  publish, restore, and trash flows must maintain the normalized data.
• Adding or removing a locale must not require another MongoDB index.
• Internal index metadata must not appear in Payload's public document shape,
  generated types, REST responses, or GraphQL schema.
• Duplicate-key errors must still identify the public field and locale rather
  than exposing an adapter-owned path.
• Version collections must retain their current index and uniqueness behavior.
• Case sensitivity and collation must match the configured field/index
  behavior.

For non-unique index: true fields, equality and range filters as well as
sorting need explicit acceptance criteria. A representation that enforces
uniqueness but makes normal localized queries fall back to collection scans is
not a complete replacement.

Proposed direction

Add an opt-in MongoDB adapter strategy, with a tentative configuration shape
such as:

mongooseAdapter({
  localizedIndexStrategy: 'normalized',
})

'per-locale' would describe the existing behavior and provide a compatibility
path. Whether 'normalized' becomes the 4.x default should be decided only
after migration and performance results are available.

For normalized localized unique fields, keep the public localized value in its
existing location and persist adapter-owned shadow entries in each MongoDB
document. One possible representation is:

_payloadLocalizedUnique: [
  { path: 'path', locale: 'de', value: '/kontakt' },
  { path: 'path', locale: 'en', value: '/contact' },
  { path: 'seo.name', locale: 'de', value: 'Kontakt' },
]

path must be the canonical flattened Payload field path without the locale,
not only the field's leaf name, so identically named nested fields cannot
collide. The adapter can then create one sparse unique compound multikey index:

schema.index(
  {
    '_payloadLocalizedUnique.path': 1,
    '_payloadLocalizedUnique.locale': 1,
    '_payloadLocalizedUnique.value': 1,
  },
  {
    name: 'payload_localized_unique',
    sparse: true,
    unique: true,
  },
)

Including path prevents values from different fields from colliding, while
including locale permits the same value in different languages. The adapter
must define the shadow path in the Mongoose schema, reserve it from user field
configuration, and remove it from every public read shape. It must own
generation of this data after Payload field hooks and before the database write
so it always reflects the final persisted values.

Partial writes must update the public value and its shadow entry atomically.
In particular, changing one locale must replace exactly one matching path and
locale entry without discarding other locales. The prototype should determine
whether Payload's current update shapes can express that as one MongoDB update
pipeline or whether the adapter must read and rebuild the shadow entries inside
the existing request transaction. updateMany, upsert, draft, and version
writes need explicit coverage rather than inheriting this behavior by accident.

An equality query for a normalized field must be translated together with its
public value sanitization:

{
  _payloadLocalizedUnique: {
    $elemMatch: {
      path: 'path',
      locale: 'de',
      value: '/kontakt',
    },
  },
}

Removing the per-locale index without this query translation would preserve the
constraint but turn existing indexed lookups into collection scans. Operators
unsupported by the first milestone must continue using a compatible per-locale
index or reject normalized configuration clearly.

Non-unique localized indexed fields would require a separate internal array
and non-unique compound index. They cannot share the unique index because the
index's uniqueness applies to every entry. Queries would need to be translated
to target an entry with the selected field and locale, normally using
$elemMatch.

Version schemas need the non-unique representation even during the unique-only
milestone. Payload copies indexed source fields below version with uniqueness
disabled, so leaving those paths per-locale would retain the locale multiplier
for every version collection.

The first milestone should be limited to localized scalar fields with one BSON
value per locale. Arrays, blocks, JSON, rich text, points, hasMany fields, and
polymorphic relationships should remain on the existing strategy until their
value encoding and query semantics are designed explicitly.

This representation is a design starting point, not yet a final API. A small
prototype should confirm Mongoose schema behavior, generated index keys,
collation, query plans, array update costs, and sorting semantics before the
public configuration is accepted.

Verified representation probe

A MongoDB 7.0.14 probe verified the proposed embedded-array shape with the
sparse unique compound multikey index:

• The same path, locale, and value in a second document failed with MongoDB
  error E11000.
• The same value in another locale was accepted.
• The same value for another field path was accepted.
• Multiple documents without the shadow field, including one with an empty
  shadow array, were accepted.
• An exact $elemMatch equality lookup used the compound index and examined one
  index key and one document for one returned document.

A partial index filtered by the existence of the first array entry also
enforced uniqueness, but MongoDB did not select it for the equality query
without a redundant filter predicate. The sparse index both preserved the
missing-value behavior in the probe and supported the translated query. Public
null and empty-value behavior still needs comparison with Payload's current
sparse per-locale indexes in the integration test.

Performance considerations

A normalized compound index is not automatically faster for every operation.
Its main performance advantage is reducing the number of independent B-trees.
MongoDB must maintain every affected index on writes, so inserts and deletes
should benefit from touching one or two normalized index structures instead of
one structure per locale.

The total number of logical index entries does not fall by the same factor. A
document with values in 11 locales still contributes roughly 11 keys for that
field, and normalized keys also carry field and locale information. Disk usage
therefore needs measurement; it cannot be inferred from the index count alone.

For exact lookups, an index ordered by path, locale, and value follows
MongoDB's equality-prefix guidance when the query supplies all three values.
It should remain selective, but the current direct path.de index is narrower
and avoids an array predicate, so lower lookup latency must be demonstrated
with explain() and a benchmark rather than assumed.

Sorting is the larger risk. The normalized representation is multikey, and
MongoDB documents cases where sorting on a multikey path requires an in-memory
sort. Current locale-specific single-field indexes can directly provide sort
order. The initial implementation should therefore focus on unique/equality
lookups such as paths and names, or retain an explicit strategy for fields that
must support indexed localized sorting.

Write behavior also depends on how the shadow entries are maintained. If a
single-locale update rewrites the entire internal array, MongoDB may need to
recalculate more multikey entries than the current direct field update. The
prototype must compare single-locale updates, complete-document imports, and
concurrent conflicting writes.

For small CMS collections, these differences are unlikely to produce a visible
latency change. The immediate benefit is predictable index headroom and fewer
index structures; throughput and storage improvements are secondary and must
be benchmarked.

Alternatives and limitations

One compound index over every locale path

An index over field.de, field.en, and every other locale creates tuple
uniqueness rather than enforcing uniqueness independently for each locale. It
also has leftmost-prefix query limitations, still changes whenever locales are
added, and MongoDB permits at most 32 fields in a compound index.

Wildcard index

A wildcard index can cover dynamic locale paths, but MongoDB does not support
unique wildcard indexes. It therefore cannot preserve unique: true.

Index only selected locales

This reduces the index count but weakens correctness or continues to impose a
language limit. Payload would also need different validation behavior for
indexed and unindexed locales.

Application-only uniqueness validation

A query followed by a write has a race window. It cannot replace a unique
database constraint for concurrent requests.

Atlas-specific automation

Triggers or other Atlas services would be asynchronous, deployment-specific,
and outside the database adapter's transaction. Payload should provide the
same correctness on self-hosted MongoDB.

Transactionally maintained side collection

A side collection with one document per source document, field path, locale,
and value could use an ordinary unique compound index and avoid multikey sorting
limitations. It would, however, turn a source-document write into a
cross-collection write, require reliable transaction behavior from every
supported MongoDB-compatible backend, and require a join or two-phase lookup
for normal Payload queries. It remains a viable fallback if atomic shadow-array
maintenance or required sorting cannot be made safe, but it is a larger change
to Payload's query and transaction model.

Migration and rollout

Existing collections need an explicit, resumable migration:

1. Deploy code that maintains normalized entries while retaining the existing
   per-locale indexes.
2. Backfill normalized entries for existing documents in bounded batches.
3. Detect and report any conflicting legacy data before creating the new
   unique index.
4. Create the normalized index and verify representative queries with
   explain().
5. Switch supported queries to the normalized representation.
6. Stop creating per-locale indexes for the opted-in collection.
7. Drop obsolete per-locale indexes only through an explicit migration step.

A rollback may leave the internal normalized data in place, but it must be able
to recreate the former per-locale indexes before switching queries back or
disabling normalized writes. Locale additions and removals after migration
should change document entries, not database indexes.

Migration state must be collection-specific. Application startup must not
silently drop legacy indexes or begin querying the shadow representation before
its backfill and index build are complete.

Sharded collections

MongoDB requires a unique index on a sharded collection to include the full
shard key as its prefix. Multikey and shard-key restrictions may prevent the
proposed document-array representation from providing cluster-wide uniqueness
for some sharding strategies.

The implementation must either support an explicitly documented compatible
shard-key/index shape or reject the normalized unique strategy for incompatible
sharded collections. It must not silently claim global uniqueness where MongoDB
cannot enforce it.

Suggested implementation sequence

1. Agree on the normalized representation, adapter configuration, and sharding
   scope in an RFC or design issue.
2. Add a failing MongoDB integration fixture that demonstrates index growth and
   independent per-locale uniqueness.
3. Prototype normalized entries for localized scalar unique: true fields,
   including concurrent writes and null/missing values.
4. Add equality-query translation and explain() assertions in the same
   prototype so the constraint does not replace an index with a collection
   scan.
5. Cover version schemas with the non-unique normalized representation.
6. Add dual-write and migration/index-lifecycle support.
7. Decide whether non-unique range and sort support belongs in the first PR or
   a follow-up.
8. Document compatibility, migration, rollback, and sharding constraints.

Splitting the work after the design is accepted should make review safer than
combining schema generation, data migration, query translation, and every
localized index use case in one initial PR.

Acceptance tests

• The number of generated MongoDB indexes is constant when locales are added.
• Two documents cannot use the same value for the same field and locale.
• Two locales can use the same value.
• Two fields can use the same value without colliding.
• Multiple localized unique fields still use one normalized unique index.
• Missing, null, and empty values match the documented sparse semantics.
• Concurrent conflicting writes produce exactly one successful write.
• Duplicate-key errors report the public field path and locale.
• Adding and removing locales updates normalized entries without schema-index
  changes.
• Draft, publish, restore, trash, bulk, REST, GraphQL, and Local API operations
  cannot leave stale entries.
• Internal entries never leak through public Payload APIs or generated types.
• Existing data can be backfilled and old per-locale indexes can be removed
  safely.
• Queries switch to normalized paths only after the collection's backfill and
  normalized index are ready.
• Equality queries use the normalized index according to MongoDB explain().
• Localized sorts either use an index or have an explicitly documented fallback
  and performance boundary.
• Benchmarks compare index size, keys examined, documents examined, insert,
  single-locale update, bulk import, and equality lookup performance with the
  current per-locale strategy.
• Supported sharded configurations enforce uniqueness; unsupported ones fail
  configuration validation clearly.

Open questions for maintainers

• Should the first implementation support only localized unique: true, or
  both unique: true and index: true?
• Should the strategy be configured per adapter, collection, or field?
• Is a hidden document array preferable to a transactionally maintained side
  collection?
• Can partial localized updates, updateMany, and upserts maintain the shadow
  entries atomically without requiring a full source-document read?
• Which scalar field types and collations must the first version support? A
  single MongoDB index has one collation, so locale-specific collation rules
  cannot be varied per shadow entry.
• Must localized range filters and sorting use the normalized index in the
  first release?
• What sharding configurations are officially supported?
• Should Payload provide the backfill/index migration, generate one for the
  application, or document adapter APIs that migrations can call?
• Is the existing behavior retained by default for upgraded applications, with
  normalized indexing enabled by default only for new 4.x projects?

Implementation scope

A production-ready fix is medium-to-large rather than trivial. It crosses the
MongoDB schema builder, adapter-owned document transforms, query translation,
index lifecycle/migrations, configuration types, integration tests, and
documentation. A reproduction or schema-only prototype is a small contribution.
An opt-in implementation for localized unique scalar fields is a focused first
slice. A complete replacement for both unique: true and index: true also
needs range/sort guarantees, migration tooling, and explicit sharding behavior.

A small reproduction/test PR is straightforward and useful, but it would not
fix the limit. The safest contribution path is to get agreement on this RFC,
then implement the normalized unique-field slice with migration boundaries
that allow the remaining query/index behavior to follow separately.

Recommendation

Proceed with the RFC and request agreement on the normalized representation and
rollout boundary before opening an implementation PR. The verified sparse
compound multikey representation makes a useful first slice technically
credible, while the unresolved migration, collation, partial-update, and sort
questions are large enough that implementing the complete feature without
maintainer direction would create avoidable review risk.

[cbratschi]

Prototype results

I implemented the first opt-in prototype locally in the MongoDB adapter. I have not opened a PR yet.

The prototype:

• preserves the existing public localized field shape;
• stores normalized { path, locale, value } entries in an adapter-owned field;
• creates one sparse unique compound index regardless of locale count;
• creates one corresponding non-unique index for version collections;
• translates localized equals queries to the normalized representation;
• maps duplicate-key errors back to the public field path;
• removes the internal field from returned documents.

Verified so far:

• 2 and 11 locales both produce exactly one normalized unique index;
• the same value is allowed in different locales and fields;
• duplicates in the same field and locale are rejected;
• concurrent conflicting creates produce exactly one success and one duplicate error;
• updating one locale preserves the other locale entries;
• version persistence and equality queries work;
• MongoDB explain() selects payload_localized_unique and examines one key and one document;
• 124 localization tests, 218 field tests, and the new schema tests pass;
• the MongoDB package build and type declarations pass.

The remaining important boundaries are update atomicity and migration:

• The current updateOne prototype reads the existing normalized entries and rebuilds them inside the request transaction. Should the production implementation instead use a single MongoDB update pipeline?
• updateMany is not implemented because rebuilding document-specific entries requires different handling.
• Existing collections need dual-write, bounded backfill, conflict detection, index creation, query cutover, and explicit removal of the legacy indexes.
• Missing, null, empty-string, collation, sharding, and unsupported field-type behavior still need final coverage.

Before I turn this into a PR, which scope would be preferable?

1. An opt-in unique/equality implementation with atomic update handling and integration tests, followed by migration tooling in a separate PR.
2. A single PR that also includes dual-write, backfill, cutover, and legacy-index removal.
3. A smaller schema/query integration-test PR before the implementation.

I favour the first option because it provides a reviewable working slice without combining the adapter implementation and index-lifecycle migration in one change.