MongoDB / Intermediate: aggregation and application contracts / Lesson 9

Compound, multikey and unique indexes

Design indexes around actual predicates and sorting while understanding arrays, uniqueness and expiry limits. Balance lookup gains against mutation and storage costs.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Relate compound key order to equality, sort and range work.
Explain unique, partial, multikey and TTL contracts.

An index is maintained structure, not free speed

Indexes can reduce examined documents and support a sort, but each additional structure increases write work, memory pressure and storage. A small collection or broad predicate may be cheaper to scan. Compound key order controls useful prefixes; evaluate equality, sort and range requirements together rather than memorizing one arrangement for every workload. Collation and data types must match the intended query semantics. A plan can use an index and still examine far more candidates than it returns, so “indexed” alone is not an acceptance criterion.

Match a customer timeline query

For a customer-specific descending timeline, the candidate below starts with customerId equality and follows the desired createdAt and _id order. The matching query projects indexed fields, but whether it is covered depends on all required fields and actual plan conditions. No examined-key count or latency is invented. Index creation is a non-runnable illustration for a disposable fixture, not an instruction to change a configured service. Existing overlapping indexes and write throughput belong in the review before adding another compound index.

javascript Illustrative mongosh; results are reasoned, not measured.

db.getSiblingDB('quay_lab').shipments.createIndex({ customerId: 1, createdAt: -1, _id: -1 });
db.getSiblingDB('quay_lab').shipments.find(
  { customerId: 7 }, { customerId: 1, createdAt: 1, _id: 1 }
).sort({ createdAt: -1, _id: -1 }).limit(20);

The key order is a proposal tied to one query shape; inspect the real plan on a representative fixture.

Uniqueness applies to indexed keys and documents

A unique index prevents conflicting indexed key values across documents, but its interaction with missing, null, arrays, collation and sharding needs explicit review. A single-field unique index can treat a missing or null indexed value as a null key, so several optional-email documents may conflict. A partial unique index can enforce a carefully defined subset, such as string-valued emails, while leaving other documents outside that rule. Partial-filter eligibility and allowed expressions belong to the exact server version. Do not confuse an application optional field with automatic sparse uniqueness.

Declare the constrained subset explicitly

This candidate indexes email only when its BSON type is string, and enforces uniqueness within that subset under the index’s comparison policy. It does not validate email syntax or require every customer to have an address. Building it fails if existing indexed values already conflict, so audit and resolve collisions on a copy before a reviewed rollout. Queries must imply the relevant partial predicate for safe use of the index. The resulting index name and operational timing are intentionally not fabricated here.

javascript Illustrative mongosh; results are reasoned, not measured.

db.getSiblingDB('quay_lab').customers.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $type: 'string' } } }
);

Expected contract: two documents with the same indexed email conflict; missing or nonstring email documents are outside this partial index.

Multikey and TTL have bounded guarantees

Array indexes become multikey automatically. Compound multikey indexes restrict indexing parallel array fields in one document, and unique multikey rules do not generally forbid repeated equal elements within the same document. TTL indexes support asynchronous expiry cleanup under documented index requirements; they do not guarantee deletion at the exact expiry instant or replace authorization that must immediately hide expired data. Review read filtering, retention and recovery separately. An index feature that accelerates one access pattern is not a universal data-validation or lifecycle policy.

Common errors and fixes

E11000 duplicate key error

Why it happens: An indexed unique value conflicts, possibly including null or missing-key behavior.
How to fix it: Inspect the named index and indexed subset before changing data.
Prevent it next time: Test optional, null and collation-equivalent values before rollout.

cannot index parallel arrays

Why it happens: A compound multikey definition encounters multiple indexed array fields in one document.
How to fix it: Redesign the index or document shape for the required query.
Prevent it next time: Include realistic array shapes in index acceptance checks.

Expired documents remain briefly visible

Why it happens: TTL background cleanup was mistaken for immediate deletion.
How to fix it: Apply the business expiry predicate in reads where immediate exclusion is required.
Prevent it next time: Separate expiry access semantics from physical cleanup timing.

Check your understanding

1. Customers may omit email, but present string emails must be unique. Why can a plain single-field unique index be unsuitable?

Show worked solution

Missing and null values can participate as a null indexed key, producing conflicts among otherwise valid optional records. A reviewed partial unique index restricted to the intended string subset can express the narrower uniqueness rule. Separately validate syntax and required fields. Test existing duplicates and case-equivalence policy before building it; an index build is not a safe automatic data-cleaning operation.

2. A token expires at noon and TTL deletion happens later. What must an authorization query do?