Indexes

OMGDB supports secondary indexes in four variants: single-field, compound (multi-field), unique (single-field and compound), and partial (including partial unique). An index is an ordered structure that lets find answer a query by examining a small set of candidate documents instead of scanning the whole collection. Like every other piece of logical state in OMGDB, an index is defined in the append-only operation log; its buckets are additionally checkpointed to the derived secondary-index.cache sidecar, which — like every cache — is rebuildable and deletable at any time.

This page explains how to create each index variant, which predicates an index accelerates, how the planner stays honest, how indexes are persisted, and the current limitations. For the predicate operators themselves, see query operators; for inspecting which plan a query uses, see introspection.

Creating an index

Create a secondary index with the create-index command. It takes the store path, the collection (namespace) name, and one or more fields to index:

omgdb create-index <path> <collection> <field>... [--unique] [--partial '<filter-json>']

A single field creates a single-field index:

omgdb create-index app.omgdb users age

created index on `users.age`

Multiple fields create a compound index, used for equality-prefix and range planning:

omgdb create-index app.omgdb users tenant email

created compound index on `users.(tenant,email)`

--unique makes the index a constraint as well as an accelerator — a write that would duplicate an already-present scalar value (or tuple of values, for a compound unique index) is rejected. Documents that lack the indexed field are not constrained:

omgdb create-index app.omgdb users email --unique

created unique index on `users.email`

--partial restricts the index to documents matching a predicate. Combined with --unique, uniqueness is enforced only among the matching documents:

omgdb create-index app.omgdb users email --partial '{"status":"active"}'

created partial index on `users.email`

omgdb create-index app.omgdb users email --unique --partial '{"status":"active"}'

created partial unique index on `users.email`

The command backfills the index from all existing documents in the collection, appends a create_index operation to the log, fsyncs, and applies the index. Creating an index over a populated collection is therefore a one-time cost paid at creation; subsequent inserts, replaces, and deletes maintain the index incrementally.

Note: Index keys are top-level field names — a dotted path such as addr.city cannot be an index key. The --partial predicate, by contrast, may use dotted paths.

Unique constraints can also be declared in a collection spec’s unique array at create --from time — see schema validation.

What the index accelerates

The index is an ordered structure built on each value’s order-preserving key: a map from order key to a set of document _ids per index. Because it is ordered, the same structure serves point lookups, bucket unions, and range scans. The planner — used identically by in-process find and the direct CLI/MCP read paths — picks the most selective safe indexed predicate when one is available, and every index path re-filters its candidates with the full filter (see the mandatory re-filter).

Equality

A top-level equality predicate ({ field: value }, which compiles to $eq) on an indexed field is answered by a direct lookup into the index’s bucket:

{"age": 30}
{"role": "admin"}

$in

A top-level $in on an indexed field plans as a union of per-element equality buckets — one bucket lookup per listed value, deduplicated, then re-filtered:

{"role": {"$in": ["admin", "editor"]}}
{"tags": {"$in": ["db", "rag"]}}

Range

A top-level range predicate on an indexed field is answered by an ordered scan of the index. $gt/$gte form the lower bound and $lt/$lte the upper:

{"age": {"$gte": 18, "$lte": 65}}
{"age": {"$gt": 18}}
{"age": {"$gte": 40.5}}

A two-sided range is deliberately answered by scanning a single bound side: the planner builds both one-sided plans, keeps the more selective one, and lets the mandatory re-filter apply the other bound exactly. The reason is multikey safety — in an array field, one element can satisfy the lower bound while a different element satisfies the upper ({"v": [5, 99]} matches {"$gte": 18, "$lte": 65} on neither single element), so a two-sided key scan could miss documents.

Limitation: Range acceleration requires scalar bounds. If a bound value is an array or object, that predicate cannot bound an index scan.

Multikey (array fields)

When a document’s indexed field is an array, the index stores one key per array element plus one key for the whole array’s order key. This makes the index multikey: equality accelerates both array-contains and whole-array matches, and $in unions the element buckets:

{"tags": "rag"}
{"tags": ["rag", "db"]}
{"tags": {"$in": ["db"]}}

Because a single document can appear in several buckets (one per element), scans deduplicate document _ids before returning candidates.

Compound

A compound index on (a, b, ...) serves several query shapes:

• Full equality — every indexed field equality-bound: {"tenant":"acme","email":"a@x"}.
• Equality prefix — a leading run of fields equality-bound, when the query proves the remaining fields are present.
• Equality prefix + range(s) — an equality-bound prefix followed by range predicates on later fields.

explain labels these compound index scan and compound index range scan. As with every index path, the candidates are a superset that is re-filtered.

Index-ordered sorts

When a query’s sort matches a safe present-key index — the sort fields in index-key order, optionally after an equality-bound prefix — results can be read directly in index order instead of being sorted after the fact. suggest-indexes --sort recommends compound shapes for exactly this (kind compound_equality_sort).

Partial

A partial index contains only the documents matching its predicate, so the planner must prove it safe before using it: a partial index is chosen only when the query provably implies the partial predicate — including branch-wise reasoning about $or (every branch must imply it). A query that does not imply the predicate simply doesn’t use that index. Partial predicates built from non-indexable expressions (for example $expr) fall back to scans.

Candidate supersets and the mandatory re-filter

Every index path intentionally returns a candidate superset of the exact answer, then re-applies the full filter:

• the order key is order-preserving but not injective (2 and 2.0 share a key);
• index bounds are inclusive at the key level, so strict $gt/$lt over-fetch by one key;
• a two-sided range scans only one bound side (multikey safety, above);
• compound prefix and range scans admit rows the trailing predicates then reject.

The re-filter corrects all of it: the result is always identical to a full scan — only faster. Indexed lookups answer in well under a millisecond in-process (0.22 ms measured).

Primary _id lookups

You never need an index on _id. Documents are keyed by _id in the primary structure, and a find whose filter is an exact top-level _id equality short-circuits to the primary key lookup — explain will never suggest a secondary index on _id. Direct CLI/MCP point reads (get) seek the log through the primary-id.cache sidecar instead of scanning.

How indexes are persisted

An index definition is authoritative state and lives in the op-log: create-index appends a create_index record — {ns, field} for a single field, a fields array for a compound index, plus unique: true and/or a partialFilter object when set — and fsyncs it before the index is applied.

The index contents are a derived artifact. They are rebuilt from the log and checkpointed to the secondary-index.cache sidecar so direct CLI/MCP reads can fetch candidates without replaying the whole log. Like all five cache sidecars, it holds zero authoritative bits: delete it at any time and the next reader rebuilds it. Checkpoints may lag the durable log (readers replay the small tail across the gap); index DDL persists its refresh eagerly. See storage for the full cache model.

Indexes are part of OMGDB’s core invariants:

• I1 (text completeness): the log fully determines logical state — including which indexes exist and their contents.
• I2 (rebuild equivalence): reopening the store (or compacting and reopening) reproduces the same indexes, and verify proves at runtime that checkpoint + tail replay == full replay.

Compaction rewrites the log to a minimal form that includes one create_index record per index (keys sorted), so an index survives compaction.

Inspecting the plan with explain

Use explain to see whether a filter will use an index or fall back to a full scan. It compiles the filter and reports the chosen plan — including the candidate count the index would examine:

omgdb explain app.omgdb users '{"age":{"$gte":18,"$lte":65}}'

index scan: equality on `users.age` (secondary index, 12 candidate(s)), then filter

index range scan: range on `users.age` (secondary index, 240 candidate(s)), then filter

compound index scan: equality on `users.(tenant,email)` (secondary index, 3 candidate(s)), then filter

When no usable index exists, it reports a full scan — and if a top-level equality is present on an un-indexed field, it appends a suggestion:

full collection scan of `users` (1000 documents), then filter; no index on `age` — suggest `omgdb create-index users age`

Asking the planner: suggest-indexes

suggest-indexes turns the planner’s own reasoning into machine-readable recommendations. Pass a filter (and optionally a sort) and it prints a canonical-JSON array of index shapes the current planner could actually use:

omgdb suggest-indexes app.omgdb users '{"tenant":"acme","age":{"$gte":18}}' --sort '{"age":1}'

Each suggestion carries fields (in index-key order), a kind (equality, range, compound_equality, compound_equality_prefix_range, or compound_equality_sort), a human-readable reason, and a ready-to-run command such as omgdb create-index users tenant age. Suggestions are conservative: only shapes the planner can use today, and nothing already covered by an existing index.

See introspection for explain, the diagnose “why-not” debugger, and the other planning tools.

Limitations

Be explicit about what indexes do not do:

For the full operator reference and array (multikey) matching semantics, see query operators.