---
title: Indexes
description: Secondary indexes in OMGDB — single-field, compound, unique, and partial indexes, the predicates they accelerate, and how the planner proves an index safe before using it.
---

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](/docs/query-operators/); for inspecting which plan a query uses, see [introspection](/docs/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:

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

A single field creates a single-field index:

```sh
omgdb create-index app.omgdb users age
```

```text
created index on `users.age`
```

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

```sh
omgdb create-index app.omgdb users tenant email
```

```text
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:

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

```text
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:

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

```text
created partial index on `users.email`
```

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

```text
created partial unique index on `users.email`
```

| Argument | Description |
| --- | --- |
| `path` | Store directory, e.g. `app.omgdb`. |
| `collection` | Collection (namespace) name. |
| `field`... | Field(s) to index. One field creates a single-field index; several create a compound index. |
| `--unique` | Reject duplicate present scalar values (or tuples) for this index. |
| `--partial '<json>'` | Partial-index predicate over the supported query operators; only matching documents enter the index. |

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](/docs/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 `_id`s 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](#candidate-supersets-and-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:

```json
{"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:

```json
{"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:

```json
{"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:

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

Because a single document can appear in several buckets (one per element), scans deduplicate document `_id`s 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](/docs/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](/docs/storage/) 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:

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

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

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

```text
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:

```text
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:

```sh
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](/docs/introspection/) for `explain`, the `diagnose` "why-not" debugger, and the other planning tools.

## Limitations

Be explicit about what indexes do **not** do:

| Constraint | Behavior |
| --- | --- |
| Top-level keys only | Index keys are single-segment field names. A dotted-path predicate such as `{"addr.city":"athens"}` never selects an index (partial-index *predicates* may be dotted, but the indexed fields may not). |
| Planner visibility | Equality/`$in`/range predicates select an access path only from the root `AND` of the filter. A predicate nested inside `$or`, `$nor`, or `$not` does not pick an index (partial-index implication does reason branch-wise about `$or`, but the access-path predicate must still sit in the root `AND`). |
| Range bounds | Range acceleration requires scalar bounds; an array or object bound cannot bound an index scan. |
| Candidate supersets | Index scans return a superset and always re-filter — by design, never optional. |
| Direct-path decode | Direct CLI/MCP reads decode the `secondary-index.cache` once per process before the first candidate fetch (candidate documents themselves are now fetched lazily through per-entry offset tables); that one remaining whole-file read dominates direct indexed-read latency today. In-process reads on an open store do not pay it. |

For the full operator reference and array (multikey) matching semantics, see [query operators](/docs/query-operators/).