Indexing Reference

ConjureDB provides a rich set of index structures that turn O(n) table scans into O(1) or O(log n) lookups. This page is the comprehensive reference for every index type, its declaration, API, complexity guarantees, query optimizer integration, and practical usage patterns.

Overview

Why Indexes Matter

ConjureDB is an in-memory database for game clients. Even though data lives in RAM, scanning every row in a DbSet<T> for each query wastes CPU budget that should go to rendering and gameplay. Indexes provide sub-linear access paths that the compiled query engine uses to answer queries without full-table scans.

You declare which access paths exist; the compiler decides which one a query uses. A query never names an index: there is no lookup, no on index, no plan pin and no per-query index hint in the language. The cost-based optimizer proves that a filter, join or ordering matches a declared index and routes to it, or falls back to a scan — and the plan explain shows you which it picked. (Frame system bodies are the one place an access path is written explicitly — join … on ref, join … on index, lookup … on id; see Ephemeral Tables.) When no index covers a filter over a table PGO knows to be large, the compiler emits the UM7001 (FullScanOnLargeTable) warning; unindexed access is not a hard error.

Declaring the index set is still yours. The compiler's auto-index advisor is deliberately report-only — it recommends candidates from profile evidence and never creates, mutates or silently materializes an index. See Auto-index governance.

Index Categories

ConjureDB organizes indexes into two categories with distinct consistency models:

Category	Consistency	Lifecycle
PrimaryIndex	Immediate (synchronous)	Automatically created for every `DbSet<T>`. Mutations take effect instantly.
SecondaryIndex	Deferred-commit	Declared via `@index` on a field or `@@index` at the table level in `.conjure` schema files. Maintained by the serialized background worker lane after each transaction commit. Secondary index reads always reflect committed data.

Index Types at a Glance

IndexType	Data Structure	Lookup	Insert	Supported Queries	Best For
Primary	Dual-array (sparse + dense)	O(1)	O(1) amort.	Key lookup by `Id`	Every table (automatic)
Lookup	Type-specialized hash map	O(1)	O(1) amort.	Equality (`==`), `IN`	Non-unique key lookups
Unique	`Dictionary<TKey, int>`	O(1)	O(1)	Equality; uniqueness enforced	Unique constraints
SortedSet	`SortedSet<Group>` + cached arrays	O(log n)	O(log n)	Equality, range, ordering	Range scans, ORDER BY
SortedList	Sorted dense array	O(log n)	O(n)	Equality, range, ordering	Small ordered sets
RangeLookup	Sparse min/max arrays	O(1)	O(1)	EXISTS with range predicates	"Does any X exist where Y > Z?"
GroupedSorted	Sparse sorted lists	O(1) group	O(log n) insert	Group + sort pattern	Top-N per group
Aggregation	Running scalars	O(1)	O(1)	Global Sum, Count, Min, Max	Table-wide metrics
UniversalAggregation	`Dictionary<TKey, Stats>`	O(1)	O(1)	Grouped Sum, Avg, Count, Min, Max	Per-group metrics
CrossTableArray	Dense array cross-reference	O(1)	O(1)	Foreign-key array access	Cross-table lookups
SpatialGrid	Uniform spatial hash grid	O(cells × ents/cell)	O(1)	Radius, bounds, nearest-K	2D/3D proximity queries

Index Types

PrimaryIndex<T>

Every DbSet<T> automatically receives a PrimaryIndex<T> — the authoritative store for all entities, keyed by their integer Id. You never declare it explicitly; it exists the moment you define a table.

Storage

The primary index uses dense, contiguous storage for entities with a separate ID-to-slot mapping. This provides O(1) lookups by ID while keeping iteration dense (no gaps to skip).

API

Method	Complexity	Description
`Contains(objectId)`	O(1)	Returns `true` if the entity exists.
`this[objectId]`	O(1)	Read entity by ID.
`Count`	O(1)	Number of stored entities.
`All`	O(n)	`IEnumerable<T>` over all entities (dense, no gaps).
`AsMemory`	O(1)	`ReadOnlyMemory<T>` zero-copy view.
`Upsert(objectId, item)`	O(1) amort.	Insert or update.
`Remove(objectId)`	O(1)	Swap-and-pop removal — no compaction needed.

Direct foreach over PrimaryIndex<T> uses the same stable transaction-visible enumeration contract as All: it selects its source on the first movement. If an eligible synchronized committed shadow is available, the enumerator retains that shadow and fails closed if a later commit changes its generation. Every other source, including uncommitted, shadowless, or stale-shadow state, is copied into an owned snapshot that remains stable across later changes. The current row is returned by value; enumeration never grants writable access to the primary storage slot. Perform every row change through the owning DbSet<T> mutation API inside its transaction contract.

AsMemory is the explicit zero-copy alternative, and DenseIndexById is its sparse-map counterpart. Both are borrows of live primary storage: they allocate nothing, they are invalidated by the next mutation of the owning table, and they must not be retained across a mutation boundary or written through with MemoryMarshal/Unsafe. Call ToArray() on either result for an owned snapshot.

Capacity Growth

When storage is exhausted, capacity is doubled. Set an accurate capacity in the table options to avoid resizing in steady state:

table Player(persistence: local, capacity: 10000) {
    // fields...
}

Thread Safety

PrimaryIndex<T> is not thread-safe. All mutations must be serialized by the caller — typically through the DbContext transaction model (BeginTransaction() / Commit()).

Complexity

Operation	Time	Space per entry
Lookup	O(1) average	4 bytes (objectId in list) + hash map overhead
Insert	O(1) amortized	—
Remove	O(1) (swap in group list)	—
Update (key change)	O(1) remove + O(1) insert	—

When to Use

You don't choose PrimaryIndex — it's always there. Use it for:

Direct entity lookup by Id (the most common access pattern).
Full-table iteration when no filter applies.
As the backing store that all secondary indexes reference.

LookupIndex<TKey>

A hash-based equality index for non-unique keys. The workhorse for filter Column == value queries.

Declaration

// Field-level (.conjure)
table Player {
  guild_id: int @index(name: "PlayersByGuild", kind: lookup)
}

// Composite key, table-level:
table Player {
  guild_id: int
  role: string
  @@index(fields: [guild_id, role], name: "PlayersByGuildAndRole", kind: lookup)
}

Data Structure

LookupIndex is type-specialized to avoid boxing and virtual GetHashCode calls:

Key Type	Backing Structure	Hash Strategy
`int`, `uint`	Integer-optimized hash map	Multiplicative (Fibonacci) hashing
`ulong`	Long-optimized hash map	High-quality integer finalizer
Other	`Dictionary<TKey, List<int>>`	Default `GetHashCode`

Each bucket maps a key value to a list of matching objectId values.

API

Method	Complexity	Description
`Query(key)`	O(1) amort.	Returns `IEnumerable<T>` of all entities with the given key.
`QueryDirect(key)`	O(1) amort.	Zero-allocation struct enumerable (used by generated code).
`QueryDirectByRef(key)`	O(1) amort.	Zero-allocation by-ref enumerable; avoids struct copies.
`ContainsKey(key)`	O(1) amort.	Returns `true` if any entity has this key value.
`Query(from, to)`	O(k)	Range query over sorted key cache (k = number of keys in range).
`All()`	O(n)	All entities in the index.

Null Handling

Entities with null keys are tracked separately, avoiding null-key issues in hash maps. Queries for null keys work transparently.

Partial Index Support

An index can declare a typed predicate over fields of its table. The predicate is a .conjure expression (not a quoted C# lambda), and binding derives its column dependencies from the resolved expression:

table Player {
  id: int @id
  guild_id: int
  status: int
  is_active: bool
  is_deleted: bool
  region: string

  @@index(fields: [guild_id], name: "ActivePlayersByGuild",
          kind: lookup, filter: is_active == true)
}

The maintenance-supported predicate is a conjunction of typed equality, boolean, and not-boolean facts on declared scalar columns:

Form	Example
Column equality to a compatible literal	status == 1
Boolean column (equivalent to true)	is_active
Negated boolean column (equivalent to false)	`!is_deleted`
Conjunction of supported terms	`is_active && region == "EU"`

Parameters, navigation/member paths, functions, subqueries, arithmetic, inequalities, and disjunction are rejected with SCH2022. Do not add a separate list of predicate columns: the binder derives typed dependencies from the predicate itself.

The optimizer uses the bound equality facts to prove whether a query filter implies the partial-index predicate. When it cannot prove the implication, the index is not eligible. Only rows for which the predicate is true belong to the index; updates that change the predicate reevaluate membership. A filter on the index predicate itself is a straightforward example:

query ActivePlayers() -> Player[] {
  from Player
  | filter is_active == true
}

Unique and SortedList indexes reject partial predicates because they do not have filtered-membership maintenance. Other kinds remain subject to their kind-specific binding and maintenance contracts.

This makes the supported fragment deliberately narrow. For a condition such as amount >= 100, index the column and let the query's range filter do the work, or introduce a derived boolean column such as is_large_order and index that fact.

When to Use

Equality filters: filter GuildId == 42
IN queries: filter Status in ("Active", "Pending")
Non-unique keys with multiple matches per value
High-frequency lookups where O(1) amortized is critical

Limitations

No ordered iteration guarantee (hash-based).
Not suitable for range queries (use SortedSet instead).
Memory grows with number of distinct key values × entries per key.

UniqueIndex<TKey>

A uniqueness-constrained equality index. Guarantees exactly one entity per key value.

Declaration

table Player {
  username: string @index(name: "PlayerByUsername", kind: unique)
}

Data Structure

Dictionary<TKey, int> — each key maps to exactly one objectId. Pending keys from the current transaction are tracked separately for pre-commit duplicate detection.

API

Method	Complexity	Description
`Query(key)`	O(1)	Returns the single matching entity. Throws `KeyNotFoundException` if absent.
`TryQuery(key, out item)`	O(1)	Returns `false` if absent (no exception).
`TryGetValue(key, out value)`	O(1)	Compatibility alias for dictionary-like API.
`Contains(key)`	O(1)	`true` if the key exists.
`this[key]`	O(1)	Indexer — same as `Query(key)`.
`All()`	O(n)	All entities in the table.

Uniqueness Enforcement

Insert with a duplicate key throws SecondaryIndexValidationException.
Validation happens at two levels:
1. Pre-commit (ValidateAdd): checks both committed keys and pending keys (from the current transaction).
2. Worker sync (AddInternal): final check when the background worker applies changes.
Key changes during updates are handled atomically: the old mapping is removed and the new mapping is inserted in a single operation.

Example

table Player(plural: Players, persistence: local, capacity: 1000) {
  Id: int @id
  Email: string @index(name: "PlayerByEmail", kind: unique)
  Name: string
}

// Usage in query:
// from Players | filter Email == "alice@example.com" | single

When to Use

Natural keys (email, username, external ID) that must be unique.
Foreign key targets where the relationship is 1:1.
Any column where duplicate values represent a data integrity violation.

Limitations

Exactly one entity per key — not suitable for non-unique lookups.
Duplicate detection adds slight overhead to inserts.

SortedSetIndex<TKey>

An ordered index supporting equality lookups, range predicates, and ordered iteration. The most versatile secondary index type.

Declaration

table Player {
  level: int @index(name: "Level_Sorted", kind: sorted_set)
}

// With partial filter
table Player {
  id: int @id
  level: int @index(name: "ActivePlayersByLevel", kind: sorted_set, filter: is_active == true)
  is_active: bool
}

Data Structure

SortedSet<Group> where each Group holds a List<int> of member objectId values sharing the same key. Groups are ordered by key value.

Cached orderings: The index maintains pre-computed ascending and descending orderings, lazily rebuilt after mutations. Ordered iteration is O(1) per element after the first access following a mutation batch.

Group boundaries: For descending access, the index also caches group boundary data, enabling O(log n) slicing for predicates like key > threshold without per-query allocations.

Zero-allocation lookups: Reusable Group objects (_searchGroup, _searchGroupUpper) are mutated in-place for search and range queries. No heap allocation per lookup.

API

Method	Complexity	Description
`Query(key)`	O(log n)	All entities with the exact key.
`QueryDirect(key)`	O(log n)	Zero-allocation struct enumerable.
`QueryDirectByRef(key)`	O(log n)	Zero-allocation by-ref enumerable.
`Query(from, to)`	O(log n + k)	Range query: all entities with keys in `[from, to]` inclusive.
`QueryGreaterThan(key)`	O(log n + k)	All entities with key > threshold.
`QueryGreaterThanOrEqual(key)`	O(log n + k)	All entities with key ≥ threshold.
`QueryLessThan(key)`	O(log n + k)	All entities with key < threshold.
`QueryLessThanOrEqual(key)`	O(log n + k)	All entities with key ≤ threshold.
`QueryGreaterThanReverse(key)`	O(log n + k)	Descending order, key > threshold.
`QueryGreaterThanOrEqualReverse(key)`	O(log n + k)	Descending order, key ≥ threshold.
`QueryLessThanReverse(key)`	O(log n + k)	Descending order, key < threshold.
`QueryLessThanOrEqualReverse(key)`	O(log n + k)	Descending order, key ≤ threshold.
`ContainsKey(key)`	O(log n)	`true` if any entity has this key.
`All()`	O(n)	All entities in ascending key order.
`AllReverse()`	O(n)	All entities in descending key order.
`EnumerateAllReverse()`	O(n)	Zero-allocation descending struct enumerable.
`EnumerateAllReverseByRef()`	O(n)	Zero-allocation descending by-ref enumerable.

(Where n = number of distinct key values, k = number of matching entities.)

What a by-ref view promises

Every ...ByRef method on a sorted index returns the same kind of value: a view that has not read anything yet. It resolves the window it describes on FIRST USE — the first foreach, the first Count, the first positional read — and not at the call that produced it. A view created before a commit and read after it sees the state at first use.

A started view is a snapshot. Once resolved, it does not change and it does not throw mid-read:

A foreach over it copies its rows into a POOLED buffer that the loop returns when it ends. A commit that happens while you are iterating cannot be observed through that loop, and the copy costs nothing after the pool is warm — this is what makes an emitted by-ref loop allocate zero bytes in steady state. A ref readonly taken from the loop is valid only INSIDE it: when the loop ends the buffer goes back to the pool.
Count and the positional reads answer from the window itself, without copying it. Asking a window how large it is costs nothing however large the index is.

Rows are handed out as ref readonly, so a caller can read a large struct entity without copying it and cannot replace an indexed slot through the view.

foreach (ref readonly var item in ctx.Items.ByPriceIndex.QueryGreaterThanByRef(100))
    total += item.Price;   // no enumerator allocation, no row copy, no per-row branch

Complexity

Operation	Time
Point lookup	O(log n)
Range scan	O(log n + k)
Ordered iteration (cached)	O(1) per element
Insert / Remove	O(log n)
Update (key change)	O(log n) remove + O(log n) insert

Example

table Item(plural: Items, persistence: local, capacity: 50000) {
  Id: int @id
  Name: string
  Price: int @index(name: "ItemsByPrice", kind: sorted_set)
}

// DSL queries that use this index:
// Range: from Items | filter Price >= 100 && Price <= 500
// Top-K: from Items | sort -Price | take 10
// Minimum: from Items | sort Price | take 1

When to Use

Range filters: filter Level >= 10 && Level <= 50
ORDER BY queries: sort -Price
Min/Max queries (via ordered access)
Any query combining equality, range, and ordering on the same column

Limitations

O(log n) per operation vs O(1) for Lookup — use Lookup for pure equality.
Cached snapshot rebuild after mutations is O(n) — acceptable for batch mutations, but consider GroupedSorted for frequently-mutated per-group sorted data.

SortedListIndex<TKey>

Functionally similar to SortedSet, using a dense sorted array instead of a tree. Supports the same query patterns with different performance trade-offs.

Declaration

table Player {
  score: int @index(name: "PlayersByScore", kind: sorted_list)
}

Data Structure

Uses a dense sorted array of object IDs kept in sorted order by key. Range queries use binary search for O(log n) bound location.

API

Supports the same range query methods as SortedSet:

Method	Complexity	Description
`QueryGreaterThan(key)`	O(log n + k)	All entities with key > threshold.
`QueryLessThanOrEqual(key)`	O(log n + k)	All entities with key ≤ threshold.
Range queries	O(log n + k)	All standard range predicates.
Ordered iteration	O(n)	Dense array traversal.

Complexity

Operation	Time
Point lookup	O(log n) via binary search
Range scan	O(log n + k)
Insert	O(n) — requires array shifting
Remove	O(n) — requires array shifting

When to Use

Small collections where insert/remove is infrequent.
Queries that benefit from cache-friendly dense array iteration.
When the compiler maps sorted_list schema declarations.

SortedSet vs SortedList

Property	SortedSet	SortedList
Insert/Remove	O(log n)	O(n)
Range lookup	O(log n + k)	O(log n + k)
Memory layout	Tree nodes (scattered)	Dense array (cache-friendly)
Best for	Large, frequently mutated sets	Small, rarely mutated sets

RangeLookupIndex<TRangeKey>

Answers O(1) EXISTS queries for group-specific range predicates. Designed for patterns like "does any order in group X have quantity > 100?" without scanning.

Declaration

table OrderItem {
  OrderId: int @index(name: "OrderQuantityRange", kind: range_lookup, range: Quantity)
}

Data Structure

Three sparse arrays indexed by int group key, tracking the minimum value, maximum value, and data presence for each group. Initial capacity: 1024 slots. Arrays grow automatically as higher group keys are observed.

API

All query methods are O(1):

Method	Semantics
`ExistsGreaterThan(groupKey, threshold)`	`max > threshold`
`ExistsGreaterThanOrEqual(groupKey, threshold)`	`max >= threshold`
`ExistsLessThan(groupKey, threshold)`	`min < threshold`
`ExistsLessThanOrEqual(groupKey, threshold)`	`min <= threshold`
`ExistsInRange(groupKey, lo, hi)`	`min <= hi && max >= lo` (range overlap)
`ContainsGroup(groupKey)`	`true` if the group has any items
`GetMax(groupKey)`	Maximum range value in the group
`GetMin(groupKey)`	Minimum range value in the group
`EnsureSynchronized()`	Force sync barrier before bulk checks
`EnumerateGroupsGreaterThan(threshold)`	Zero-allocation scan of groups with max > threshold
`EnumerateGroupsGreaterThanOrEqual(threshold)`	Zero-allocation scan of groups with max ≥ threshold

"AssumeSynced" variants (ExistsGreaterThanAssumeSynced, etc.) skip the sync barrier for generated code that has already called EnsureSynchronized() once before a loop.

Incremental Maintenance

Adds update min/max inline — O(1).
Removes that hit a min/max boundary mark the group dirty. Only dirty groups are rebuilt on the next query — not the entire index.

Example

table OrderItem(plural: OrderItems, persistence: local, capacity: 50000) {
  Id: int @id
  OrderId: int @index(name: "OrderQuantityRange", kind: range_lookup, range: Quantity)
  Quantity: int
}

// Query: "Does order #42 have any item with quantity > 100?"
// Compiles to: index.ExistsGreaterThan(42, 100)  → O(1)

When to Use

EXISTS-style range checks within groups (semi-join patterns).
Cross-table filtering: "does the related table have any row matching a range predicate?"
Any pattern where you need a boolean answer about range existence, not the actual rows.

Limitations

Only answers EXISTS questions — does not return matching entities.
Group key must be int (designed for entity ID foreign keys).
Min/max approximation: reports that a range may contain matches (no false negatives, but removing the actual min/max triggers a group rescan).

GroupedSortedIndex<TRangeKey>

Optimized for the common game-client pattern: group by an integer key, then iterate each group in sorted order by a range key (e.g., "top scores per guild", "items by category sorted by price").

Declaration

table Score {
  guild_id: int
  score: int
  @@index(fields: [guild_id], name: "ScoresByGuild", kind: grouped_sorted, range: score)
}

Data Structure

Each group is a list kept in descending order by TRangeKey. Insertion uses binary search for O(log k) placement within a group of k elements, and a parallel tracking array maps each entity to its position for O(1) removal.

How a group key reaches its list is the one thing the two published shapes differ in, and it is a struct policy on one shared implementation rather than two implementations:

Shape	Group storage	Materialized-list cache
`GroupedSortedIndex<TRangeKey>` (int key)	sparse `List<Entry>?[]` — the key IS the slot	yes, addressed by that same slot
`GroupedSortedIndex<TKey, TRangeKey>` (composite key)	`Dictionary<TKey, List<Entry>>`	no

Both inherit one declaration of the whole query surface, so every method below — and every by-ref view type it returns — behaves identically on both, except where the int key's domain differs: a NEGATIVE int group key is outside the dense store's domain and holds no rows, where any composite key is admissible.

API

Method	Complexity	Description
`QueryDirectDescending(groupKey)`	O(1) group access	Zero-allocation descending iteration.
`QueryDirectDescendingByRef(groupKey)`	O(1) group access	By-ref descending (no struct copies).
`QueryDirectAscending(groupKey)`	O(1) group access	Zero-allocation ascending iteration.
`QueryDirectAscendingByRef(groupKey)`	O(1) group access	By-ref ascending.

All enumerables are zero-allocation struct types supporting foreach.

What a grouped by-ref view promises

A grouped by-ref view resolves its group on FIRST USE, like the sorted-index views above, and a started view is a snapshot of what it resolved. It differs from them in two ways, both deliberate: it reads the group's rows in place rather than copying them into a pooled buffer, and a walk fails closed — it throws InvalidOperationException if committed state changes under it, rather than continuing over a snapshot of what was there.

Those two are why this family publishes its own by-ref views instead of the OrderedWindowRefEnumerable the two global-order sorted families share:

Copy-own does not amortise for this shape. The window cursor copies the whole window into a pooled buffer on its first move. That is the right trade for one large ordered read, which is what a sorted-set or sorted-list range is. Generated join code walks a grouped group once per outer row, and a Top-1 or Top-8 lookup measures in tens of nanoseconds end to end — there is no room in it for a pool rent, k struct copies and a return, where the grouped cursor does zero copies.
It would turn a fail-fast contract into a snapshot. The window family deliberately removed per-read revalidation and replaced it with ownership: a cursor that owns its rows cannot be invalidated, so it never throws. Moving the grouped views onto it would silently downgrade the guard above to "you get a copy of what was there", which is a semantics change on a public surface, not a refactor.

There is no impossibility here, and a future reader should not conclude one. If the families are ever merged, the concrete route is a maintained int[] denseSlots per group — patched in the same places _indexInGroup already is — which is exactly the permutation an ordered window wants; the cost is one int per row plus a dense→entry map so a row move can find the slot to patch. The argument above is that the trade is not worth taking, not that it cannot be taken.

Target Query Pattern

from Scores
| filter GuildId == @guildId
| sort -Score
| take 10

The compiler recognizes this filter + sort + take pattern and routes it to a GroupedSortedIndex. The group is accessed in O(1), and iteration is already in the required descending order — no runtime sort needed.

Example

table Score(plural: Scores, persistence: local, capacity: 100000) {
  Id: int @id
  GuildId: int @index(name: "ScoresByGuild", kind: grouped_sorted, range: Points)
  Points: int
}

// "Top 10 scores in guild #7" → O(1) group access + O(10) iteration
// No sorting at query time — data is pre-sorted.

When to Use

Top-N per group queries (leaderboards, category rankings).
Patterns combining filter GroupKey == X | sort ±RangeKey | take N.
When the group key is a dense integer (entity ID, category enum).

Limitations

Group key must be int (sparse array addressing).
Insertion is O(log k) per group (binary search + list insert with shifting).
Not suitable for groups with very frequent mutations and large group sizes — consider SortedSet in that case.

GlobalAggregationIndex<TValue> (IndexType.Aggregation)

O(1) global (ungrouped) aggregations over an entire DbSet. Pre-computes Sum, Count, Min, Max as entities are added, updated, and removed.

Declaration

table Player {
  Gold: int @index(name: "GlobalGold", kind: aggregation, value: Gold)
}

Data Structure

Maintains running sum, count, min, and max values that are updated incrementally as entities are added, updated, and removed.

API

Method	Complexity	Description
`GetGlobalSum()`	O(1)	Sum of all values.
`GetGlobalAverage()`	O(1)	`Sum / Count`.
`GetGlobalCount()`	O(1)	Number of entities.
`GetGlobalMin()`	O(1) amort.	Minimum value.
`GetGlobalMax()`	O(1) amort.	Maximum value.
`GetAllGlobalStats()`	O(1) amort.	Returns `(sum, avg, min, max, count)` tuple.

Min/Max Tracking

Min/Max tracking is lazy: the frequency map (SortedDictionary<TValue, int>) is only materialized when a removal hits the current min or max. This avoids the cost of maintaining a sorted structure for workloads dominated by inserts.

Once materialized, the sorted dictionary provides O(log n) min/max recomputation via its first/last key.

Example

// O(1) access to the total gold in the game economy:
var totalGold = goldIndex.GetGlobalSum();
var avgGold = goldIndex.GetGlobalAverage();

When to Use

Dashboard metrics: total currency, average player level, global min/max score.
Any ungrouped aggregate that would otherwise require a full-table scan.

Limitations

Only supports numeric value types (struct with IComparable<TValue>).
Sum uses long — overflow is possible for very large values.
Value type must be convertible via Convert.ToInt64().

UniversalAggregationIndex<TKey, TValue> (IndexType.UniversalAggregation)

Pre-computed grouped aggregations — O(1) per metric per group. The grouped equivalent of GlobalAggregationIndex.

ConjureDB provides three aggregation index variants, automatically selected based on the query's aggregation needs:

Variant	Class	Per-Key Footprint	Aggregations
Full stats	`AllStatsAggregationIndex<TKey, TValue>`	~40 bytes	Sum, Avg, Count, Min, Max
Sum + Count	`SumCountAggregationIndex<TKey>`	12 bytes	Sum, Avg, Count
Count only	`CountOnlyAggregationIndex<TKey>`	4 bytes	Count

Declaration

table Score {
  GuildId: int @index(name: "TotalScoreByGuild", kind: universal_aggregation, value: Score)
}

Data Structure (AllStatsAggregationIndex)

Dictionary<TKey, AggregationStats> where each stats entry tracks sum, count, min, max, and dirty flags for lazy min/max recomputation.

API

Method	Complexity	Description
`GetSum(key)`	O(1)	Sum for the group.
`GetAverage(key)`	O(1)	`Sum / Count` for the group.
`GetCount(key)`	O(1)	Number of entities in the group.
`GetMin(key)`	O(1) amort.	Minimum value; rescans group if dirty.
`GetMax(key)`	O(1) amort.	Maximum value; rescans group if dirty.
`Keys`	—	`IEnumerable<TKey>` of all group keys.
`EnumerateSums()`	O(n)	Zero-allocation struct enumerable of `(Key, Sum)` pairs.
`EnumerateAllStats()`	O(n)	Zero-allocation struct enumerable of `(Key, Sum, Min, Max, Count)`.
`EnumerateTopSums(limit)`	O(k)	Top-K groups by sum (descending), pre-sorted.
`EnumerateTopAllStats(limit)`	O(k)	Top-K with full stats, pre-sorted.

Lazy Min/Max Recomputation

When an element is removed, the index cannot cheaply determine whether it held the current min or max. Instead, it sets a dirty flag. On the next GetMin or GetMax call, the index rescans the group. Insertions that extend the current extrema clear the flag immediately.

Trade-off: If your workload has frequent deletes and frequent GetMin/GetMax calls, consider a SortedSet index (O(log n) extrema via tree navigation) instead of an aggregation index (O(k) recomputation on dirty min/max, where k = group size).

Ranking Support

When enableRanking is true (default), the index maintains a pre-sorted ranking cache of groups by sum (descending) with ascending key tiebreak. EnumerateTopSums(limit) returns results in O(k) without any sorting at query time.

Example

table Score(plural: Scores, persistence: local, capacity: 100000) {
  Id: int @id
  GuildId: int @index(name: "ScoreStatsByGuild", kind: universal_aggregation, value: Points)
  Points: int
}

// O(1) queries:
// Total points for guild #7:  index.GetSum(7)
// Average points per player:  index.GetAverage(7)
// Top 10 guilds by total:     index.EnumerateTopSums(10)

When to Use

Real-time dashboards: per-guild scores, per-category totals.
Leaderboard aggregates: "which guild has the highest total score?"
Any grouped metric that would otherwise require GROUP BY + aggregation scan.

Limitations

Sum uses long — overflow possible for extremely large values.
Min/Max recomputation after deletes is O(k) per dirty group.
Memory scales with number of distinct groups.

Composite Indexes

Composite indexes use multiple columns as the key. Declared via the fields: list on a table-level @@index:

table Player {
  guild_id: int
  role: string
  @@index(fields: [guild_id, role], name: "PlayerByGuildAndRole", kind: lookup)
}

In schema files, use @@index at the table level:

table Player {
  id: int @id
  guild_id: int
  role: string
  level: int

  @@index(fields: [guild_id, role], name: "PlayerByGuildAndRole", kind: lookup)
  @@index(fields: [level], name: "Level_Sorted", kind: sorted_set)
}

Key Encoding Strategies

For composite keys with low-cardinality string components, packed encodings eliminate heap allocation:

Encoding	Key Layout	Resulting Key Type	Benefit
`Default`	Standard composite key	Tuple / struct	General purpose
`PackedInt32LowCardStringToUInt64`	`int + string` → `ulong`	`ulong`	8-byte key, no string alloc
`PackedUInt8LowCardStringToUInt16`	`byte + string` → `ushort`	`ushort`	2-byte key (≤255 int values, ≤255 strings)
`PackedUInt16LowCardStringToUInt32`	`ushort + string` → `uint`	`uint`	4-byte key (≤65535 int values, ≤65535 strings)

Packed encodings map the string portion to a dense integer via an interning table, then bit-pack it with the integer portion. This enables the composite key to use integer-specialized hash maps for maximum performance.

table Player {
  GuildId: int
  Role: string

  @@index(fields: [GuildId, Role], name: "PlayerByGuildAndRole", kind: lookup,
          encoding: PackedInt32LowCardStringToUInt64)
}

Precondition: The planner must prove that the integer and string values fit within the encoding's range. If selected without satisfying preconditions, runtime will fail fast.

Index Declaration

Indexes are declared in .conjure schema files, either at the field level with @index or at the table level with @@index. Multiple indexes can be declared on the same field by chaining @index(...) annotations:

table Player(plural: Players, persistence: local, capacity: 10000) {
  Id: int @id
  GuildId: int @index(name: "PlayersByGuild", kind: lookup) @index(name: "GuildScoreAgg", kind: universal_aggregation, value: Score)
  Email: string @index(name: "PlayerByEmail", kind: unique)
  Level: int @index(name: "PlayersByLevel", kind: sorted_set)
  Score: int
}

`@index` / `@@index` Option Reference

Option	Applies to	Description
`name:`	both	Optional. Defaults to the field or key columns; explicit names must be unique within the table.
`kind:`	both	Index kind: `lookup`, `unique`, `sorted_set`, `sorted_list`, `range_lookup`, `grouped_sorted`, `aggregation`, `universal_aggregation`, `spatial_grid`.
`fields: [...]`	`@@index`	Key columns (composite keys).
`keys: [...]`	both	Additional group/key columns.
`value:`	both	Value column for aggregation indexes (bare identifier).
`range:`	both	Range column for `range_lookup` / `grouped_sorted` (bare identifier).
`included: [...]`	both	Covering columns for index-only scans.
`filter:`	both	Unquoted .conjure boolean expression for a partial index; bound field dependencies are derived from the expression.
`encoding:`	both	Composite key packing strategy.
`dimensions:` / `cell_size:` / `coordinates: [...]`	both	`spatial_grid` layout, cell size, and coordinate columns.

Schema-Based (`.conjure`)

In schema files, indexes can be declared at the field level or table level:

Field-level:

table Player {
  name: string @index(name: "Name_Idx", kind: lookup)
  level: int   @index(name: "Level_Sorted", kind: sorted_set)
}

Table-level (@@index):

table Player {
  id: int @id
  guild_id: int
  level: int

  @@index(fields: [guild_id, level], name: "GuildLevel_Lookup", kind: lookup)
}

Available kind values: lookup, unique, sorted_set, sorted_list, range_lookup, grouped_sorted, aggregation, universal_aggregation, spatial_grid.

Covering Indexes and Index-Only Scans

When a query only accesses columns that are part of the index key plus IncludedColumns, the optimizer can perform an index-only scan — reading data directly from the index without dereferencing the main table. This is significant I/O savings on mobile devices.

table Player {
  GuildId: int @index(name: "PlayersByGuild", kind: lookup, included: [Name, Level])
}

// This query can use an index-only scan:
// from Players | filter GuildId == 42 | select { Name, Level }
// No table dereference needed — Name and Level are in the index.

Partial (Filtered) Indexes

Partial indexes include only a subset of rows, reducing memory footprint and maintenance cost:

table Player {
  Id: int @id
  Level: int
  IsActive: bool

  @@index(fields: [Level], name: "ActivePlayersByLevel",
          kind: sorted_set, filter: IsActive == true)
}

The optimizer uses the typed facts derived from filter: to determine whether a query's filter predicate implies the index predicate. If the query filters on IsActive == true, the partial index is eligible; otherwise it is not.

Unique and SortedList indexes reject partial predicates because they have no filtered-membership maintenance; other kinds retain their kind-specific admission contract.

Query Optimizer Integration

The ConjureDB compiler automatically selects the optimal index for each query. Understanding how this works helps you design effective indexes.

Scan Strategy Selection

The ScanStrategyPlanner generates multiple physical alternatives for every table access:

Strategy	Description	When Used
`FullScan`	Iterate all entities in the `PrimaryIndex`	Baseline (always generated)
`PrimaryKeyLookup`	Direct `TryFindById()` via PrimaryIndex	`filter Id == @param`
`SecondaryIndexScan`	Hash lookup via LookupIndex	`filter Column == value`
`SortedSetScan`	Ordered iteration via SortedSetIndex	`sort Column` without filter
`IndexedScan`	Range scan via SortedSetIndex	`filter Column >= X && Column <= Y`
`IndexOnlyScan`	Covering index scan (no table dereference)	Query uses only indexed + included columns

Filter Index Selection

The FilterStrategyPlanner analyzes filter predicates and maps them to index-backed strategies:

Predicate Type	Strategy	Index Type Used
Equality (`==`)	`IndexedLookup`	Lookup or Unique
Range (`>=`, `<=`, `BETWEEN`)	`IndexedRange`	SortedSet
Group + Sort	`GroupedSorted`	GroupedSorted
Primary key	`PrimaryKeyLookup`	PrimaryIndex

The optimizer extracts AND conjuncts from complex predicates and detects range patterns (e.g., X >= lo AND X <= hi → BETWEEN) to select the most efficient index.

Join Optimization

The JoinSecondaryIndexPlanner discovers candidate indexes on the lookup side of joins:

Index Kind	Predicate Type	Join Strategy
Unique	Equality	`UniqueIndexLookup` — always preferred
Lookup	Equality	`SecondaryIndexLookup` — hash probe per row
SortedSet	Range	`SortedSetRange` — range-based probe
SortedSet	Equality	`SecondaryIndexLookup` — hash probe fallback

Cost Model

The optimizer generates all viable alternatives and uses a cost model to select the cheapest plan. Key factors:

Selectivity estimates guide filter strategy ranking.
PGO hints (Profile-Guided Optimization) can override strategy selection based on runtime profiling.
The CascadesOptimizationStage explores the full search space before the PhysicalPlanningStage selects a winner.

Foreign Keys and References

Foreign Keys

A @relation annotation declares a foreign-key relationship used by the optimizer for join strategy selection:

table Item {
  Id: int @id
  PlayerId: int @relation(references: Player.Id)
  Name: string
}

The compiler uses relation declarations to:

Infer key bounds for optimization (e.g., DenseHeap strategy).
Select efficient join strategies (index-nested-loop joins via PrimaryIndex).

Injected References

For read-only config tables, declare a @relation to the target; the compiler resolves the reference through the target's PrimaryIndex as an O(1) pointer chase rather than a join:

TemplateId: int @relation(references: ItemTemplate.Id)

This resolves the referenced entity through the target table's PrimaryIndex — an O(1) pointer chase rather than a join. Use for immutable lookup tables (item templates, config data).

SpatialGridIndex

Uniform spatial hash grid for nearby-object queries in 2D or 3D worlds. Space is partitioned into fixed-size cells; entities are assigned to cells based on floor(coord / cellSize). Queries identify candidate cells, iterate their contents, and apply exact Euclidean-distance refinement. Designed for high-frequency proximity lookups typical in game clients — entity detection, area-of-effect, collision pre-filtering.

Declaration

Schema (.conjure):

table GameObject(plural: GameObjects, persistence: local, capacity: 50000) {
  Id: int @id
  PosX: float
  PosY: float
  Name: string

  @@index(fields: [PosX], name: "spatial_pos", kind: spatial_grid,
          coordinates: [PosX, PosY], dimensions: 2, cell_size: 100)
}

Schema (.conjure):

table GameObjects {
  id: int @id
  pos_x: float
  pos_y: float
  pos_z: float
  name: string

  @@index(fields: [pos_x], name: "spatial_pos", kind: spatial_grid,
          coordinates: [pos_x, pos_y], dimensions: 2, cell_size: 100)

  @@index(fields: [pos_x], name: "spatial_pos_3d", kind: spatial_grid,
          coordinates: [pos_x, pos_y, pos_z], dimensions: 3, cell_size: 50)
}

Parameters

Parameter	Type	Default	Description
`dimensions`	int (2 or 3)	`2`	`2` (X, Y) or `3` (X, Y, Z)
`cell_size`	number	`100`	Uniform side length of each grid cell
`coordinates`	column list	—	Coordinate columns; 2 elements for 2D, 3 for 3D

Data Structure

A hash map from integer cell keys (floor(x / cellSize), floor(y / cellSize), …) to lists of dense entity IDs. Only occupied cells consume memory — empty regions of the world have zero cost.

Query Types

Query Kind	Runtime Method	Semantics
Radius	`QueryWithinRadius2D` / `QueryWithinRadius3D`	All entities within Euclidean radius of a center point
Bounds	`QueryWithinBounds2D` / `QueryWithinBounds3D`	All entities inside an axis-aligned bounding box
NearestK	`QueryNearestK2D` / `QueryNearestK3D`	K nearest entities to a center point (exact, deterministic tie-breaking)

All query methods accept a caller-provided Span<int> buffer and return the count of matching entity IDs written into it. Generated code uses stackalloc buffers (default 256 elements) for zero-allocation queries.

DSL Queries

Spatial queries use built-in functions in filter expressions. The compiler detects these patterns and plans a PhysicalSpatialScan instead of a full table scan.

// Find all objects within 50 units of a point
from GameObjects
| filter within_radius(PosX, PosY, @centerX, @centerY, 50.0)
| select { Name, PosX, PosY }

// Find all objects in a bounding box
from GameObjects
| filter within_bounds(PosX, PosY, @minX, @minY, @maxX, @maxY)
| select { Name, PosX, PosY }

// 3D radius query
from GameObjects
| filter within_radius(PosX, PosY, PosZ, @cx, @cy, @cz, @radius)
| select { Name, PosX, PosY, PosZ }

// Combine spatial filter with other predicates
from GameObjects
| filter within_radius(PosX, PosY, @cx, @cy, @radius) && IsActive == true
| select { Name, PosX, PosY }

When a spatial predicate is combined with non-spatial predicates (e.g., && IsActive == true), the optimizer applies the spatial index for candidate selection and evaluates the remaining predicate as a residual post-filter.

Complexity

Operation	Complexity	Notes
Insert / Remove / Move	O(1)	Hash lookup + list append/remove
Radius / Bounds query	O(cells_in_range × entities_per_cell)	Exact refinement per candidate
NearestK query	O(cells_in_range × entities_per_cell)	Expanding search with exact sort

Cell Size Guidance

Cell size determines the trade-off between query precision and memory/iteration cost:

Cell Size vs. Query Radius	Effect
`cellSize ≈ typical radius`	Optimal — queries touch ~4 cells (2D) or ~8 cells (3D)
`cellSize ≪ radius`	Many cells iterated per query; higher overhead
`cellSize ≫ radius`	Few cells but many false-positive candidates; more refinement work

Rule of thumb: set CellSize close to your most common query radius. For a game with 100-unit interaction range, CellSize = 100 is a good starting point.

When to Use

Proximity queries in 2D/3D game worlds (nearby enemy detection, loot pickup range).
Area-of-effect targeting (splash damage, heal zones).
Collision detection broad phase.
K-nearest-neighbor queries (find closest N allies).
Any workload where entities have spatial coordinates and queries are position-based.

Limitations

Euclidean distance only — no geodesic (spherical) distance, no Manhattan distance.
No toroidal wrap — wrapping worlds are not supported; coordinates are treated as flat Cartesian.
No polygon / arbitrary geometry queries — only axis-aligned bounds and circular/spherical regions.
Coordinate properties must be explicit — auto-detection from composite structs is not supported; list individual float properties.
Static cell size — cell size is set at index declaration and cannot be changed at runtime.
No temporal dimension — moving queries (e.g., trajectory intersection) are not supported.

Custom Collection Types

ConjureDB ships low-level collection types in ConjureDB.Collections. These are the building blocks used internally by every index, and are available for direct use.

High-Performance Hash Maps

General-purpose open-addressing hash tables optimized for different key types.

Property	Value
Load factor	0.75
Collision resolution	Linear probing with displacement tracking

Integer-key variants use power-of-2 capacity with bitwise masking and type-specialized hash functions for optimal performance.

SparseSet<T>

Dual-array data structure: O(1) add, get, remove, contains via swap-and-pop.

Operation	Complexity
Add / Contains / Remove / Get	O(1)
Iteration	O(n) dense, no gaps

OrderStatisticTree

Augmented red-black tree with per-node size field:

Operation	Complexity
Insert / Remove	O(log n)
Select(k)	O(log n) — k-th smallest element
Rank(key)	O(log n) — position of a key

The tree is a multiset: each insert adds one occurrence and delete removes one matching occurrence. For duplicate keys, Rank(key) returns the first occurrence's 1-based rank.

Useful for leaderboard queries with positional lookups.

Pooled Hash Maps

ArrayPool-backed, append-only maps for short-lived aggregation contexts. Rent from the shared pool to avoid GC pressure.

Performance Considerations

Memory Overhead Per Index Type

Index Type	Per-Entity Overhead	Per-Key Overhead	Notes
PrimaryIndex	~12 bytes (dense slot + ID mapping)	—	Always present
Lookup	4 bytes (objectId in list)	Hash bucket overhead	Type-specialized maps reduce overhead
Unique	—	~20 bytes (dictionary entry)	One entry per entity
SortedSet	4 bytes (objectId in group)	~80 bytes (tree node + group)	Plus cached snapshot arrays
RangeLookup	—	~17 bytes (min + max + hasData)	Sparse arrays; empty groups cost nothing
GroupedSorted	~8 bytes (Entry + indexInGroup)	List overhead per group	Sparse array of lists
Aggregation	—	~48 bytes (stats fields)	Global only — no per-entity overhead
UniversalAggregation	—	12–40 bytes per group (variant-dependent)	Dictionary + stats struct
SpatialGrid	4 bytes (objectId in cell list)	Hash bucket per occupied cell	Only occupied cells consume memory

Update Cost

Index Type	Insert	Remove	Key Change
Lookup	O(1)	O(1)	O(1) + O(1)
Unique	O(1)	O(1)	O(1) + O(1)
SortedSet	O(log n)	O(log n)	O(log n) + O(log n)
SortedList	O(n)	O(n)	O(n) + O(n)
RangeLookup	O(1)	O(1) amort.	O(1) amort.
GroupedSorted	O(log k)	O(1)	O(log k) + O(1)
Aggregation	O(1)	O(1) amort.	O(1) amort.
UniversalAggregation	O(1)	O(1) amort.	O(1) amort.
SpatialGrid	O(1)	O(1)	O(1) (re-hash to new cell)

When NOT to Index

Very small tables (< 100 rows): Full scan is faster than index maintenance.
Write-heavy, read-light columns: Index update cost outweighs query benefit.
High-cardinality unique columns already covered by PrimaryIndex: Redundant.
Columns never used in filters, sorts, or joins: Dead index overhead.

Index Count Guidelines

1–3 secondary indexes per table is typical for game-client workloads.
Each index adds deferred-commit processing to every transaction.
The optimizer considers all declared indexes — unused indexes only waste memory and update time.
Use PGO recommendations to identify which indexes are actually exercised.

PGO Index Recommendations

Profile-Guided Optimization (PGO) can provide index guidance, but the current repository supports that through two bounded paths, not a single "complete index oracle".

How PGO Suggests Indexes

Profile-side evidence collection
- profiling/export produces canonical AutoIndexCandidates and GroupedIndexProfiles in the PgoProfile.
- these candidates carry normalized descriptors, evidence metadata, what-if deltas, and reason codes.
Compile-time what-if planning
- the planning-owned AutoIndexAdvisor builds hypothetical one-index schema overlays and re-plans the query to estimate marginal benefit.
- candidates are deterministically bucketed into recommended, held-back, and suppressed sets under explicit per-table/global/write/ memory budgets.
- the compiler writes a derived auto-index-report.json and exposes the same advisory summary through PlanExplain.AutoIndexAdvisory.

Today, the strongest supported claim is that PGO helps discover and rank useful indexes for covered shapes in a report-only workflow. The repository does not claim that PGO always discovers the full globally optimal index set for every query or workload.

Covered guidance today

Full table scans that could be avoided with an index.
Missing filter support for covered predicate shapes.
Join predicates without index support for covered join patterns.
Sort / TopN patterns where ordered access would be cheaper.
Grouped-index usage from runtime profile data.

Each recommendation/report includes rationale, evidence quality, status, and what-if metadata in structured output.

Accepting Recommendations

The AutoIndexingMode.ReportOnly advisor mechanism and the auto-index analyze / show / patch / decide CLI review loop are documented canonically in Profile-Guided Optimization. That advisor is report-only: it never auto-creates indexes, mutates schema, or routes candidates into runtime/codegen.

Index-selection-specific note: PGO advisory candidates are accepted through explicit schema ownership — once you decide to take a recommendation, add the chosen @index / @@index declaration to your .conjure schema yourself, then re-compile and re-profile to verify the improvement. See the CLI-first review loop for evidence inspection and patch/decision workflow.

Profile-Driven Index Lifecycle

Profile query workload  →  PGO/runtime advisor surfaces canonical candidates
      ↓
Compile with ReportOnly  →  what-if planning ranks candidates
      ↓
Accept explicitly via @index / @@index schema declarations
      ↓
Re-compile  →  Optimizer uses new indexes
      ↓
Re-profile  →  Verify improvement, remove unused indexes

Best Practices

Choosing the Right Index Type

Query Pattern	Recommended Index	Why
`filter X == value`	Lookup	O(1) hash lookup
`filter X == value` (unique)	Unique	O(1) + integrity guarantee
`filter X >= lo && X <= hi`	SortedSet	O(log n + k) range scan
`sort X` / `sort -X`	SortedSet	Cached ordered arrays
`sort X \| take 1` (min/max)	SortedSet	First/last element of cached array
`filter GroupKey == x \| sort -Y \| take N`	GroupedSorted	Pre-sorted groups, O(1) access
"Does group X have any Y > Z?"	RangeLookup	O(1) EXISTS check
`group Y (aggregate { Total = sum(X), C = count() })`	UniversalAggregation	Pre-computed O(1) per group
`aggregate Total = sum(X)`	Aggregation	Pre-computed O(1) global
Unique constraint enforcement	Unique	Throws on duplicate
`filter within_radius(X, Y, ...)`	SpatialGrid	Spatial hash grid, O(cells × ents/cell)
`filter within_bounds(X, Y, ...)`	SpatialGrid	Spatial hash grid, AABB candidate selection
Nearest-K entities to a point	SpatialGrid	Exact K-nearest with deterministic tie-breaking

Index Naming Conventions

Use descriptive names: PlayersByGuild, ItemsByPrice, ScoreStatsByGuild.
For composite keys: PlayerByGuildAndRole.
For aggregations: TotalScoreByGuild, GlobalGold.
For partial indexes: ActivePlayersByLevel.

Common Game Patterns

Inventory System

table Item(plural: Items, persistence: local, capacity: 5000) {
  Id: int @id
  PlayerId: int @relation(references: Player.Id) @index(name: "ItemsByPlayer", kind: lookup) @index(name: "ItemsByPlayerSortedByRarity", kind: grouped_sorted, range: Rarity)
  Rarity: int @index(name: "ItemsByRarity", kind: sorted_set)
  Name: string
  Quantity: int
}

// "All items for player #42":             filter PlayerId == 42
// "Player #42's rarest items":            filter PlayerId == 42 | sort -Rarity | take 5
// "All legendary items (rarity >= 90)":   filter Rarity >= 90

Leaderboard

table Score(plural: Scores, persistence: local, capacity: 100000) {
  Id: int @id
  PlayerId: int @index(name: "ScoresByPlayer", kind: lookup)
  Points: long @index(name: "ScoresSorted", kind: sorted_set)
  GuildId: int @index(name: "ScoreStatsByGuild", kind: universal_aggregation, value: Points) @index(name: "TopScoresByGuild", kind: grouped_sorted, range: Points)
}

// Global top 10:                   sort -Points | take 10
// Guild #7 total:                  index.GetSum(7)
// Guild #7 top 10:                 filter GuildId == 7 | sort -Points | take 10
// Top 5 guilds by total score:     index.EnumerateTopSums(5)

Config Lookup

table ItemTemplate(plural: ItemTemplates, persistence: none, capacity: 1000) {
  Id: int @id
  Code: string @index(name: "TemplateByCode", kind: unique)
  Name: string
  BasePrice: int
}

// Unique lookup by code:  filter Code == "SWORD_01" | single
// Never duplicates — UniqueIndex throws on insert conflict.

Getting Started — Setup and basic usage
Query Language — DSL syntax reference
Compiled Queries — How the compiler selects indexes
PGO — Profile-Guided Optimization and index recommendations

Overview​

Why Indexes Matter​

Index Categories​

Index Types at a Glance​

Index Types​

PrimaryIndex<T>​

Storage​

API​

Capacity Growth​

Thread Safety​

Complexity​

When to Use​

LookupIndex<TKey>​

Declaration​

Data Structure​

API​

Null Handling​

Partial Index Support​

When to Use​

Limitations​

UniqueIndex<TKey>​

Declaration​

Data Structure​

API​

Uniqueness Enforcement​

Example​

When to Use​

Limitations​

SortedSetIndex<TKey>​

Declaration​

Data Structure​

API​

What a by-ref view promises​

Complexity​

Example​

When to Use​

Limitations​

SortedListIndex<TKey>​

Declaration​

Data Structure​

API​

Complexity​

When to Use​

SortedSet vs SortedList​

RangeLookupIndex<TRangeKey>​

Declaration​

Data Structure​

API​

Incremental Maintenance​

Example​

When to Use​

Limitations​

GroupedSortedIndex<TRangeKey>​

Declaration​

Data Structure​

API​

What a grouped by-ref view promises​

Target Query Pattern​

Example​

When to Use​

Limitations​

GlobalAggregationIndex<TValue> (IndexType.Aggregation)​

Declaration​

Data Structure​

API​

Min/Max Tracking​

Example​

When to Use​

Limitations​

UniversalAggregationIndex<TKey, TValue> (IndexType.UniversalAggregation)​

Declaration​

Data Structure (AllStatsAggregationIndex)​

API​

Lazy Min/Max Recomputation​

Ranking Support​

Example​

When to Use​

Limitations​

Composite Indexes​

Key Encoding Strategies​

Overview

Why Indexes Matter

Index Categories

Index Types at a Glance

Index Types

PrimaryIndex<T>

Storage

API

Capacity Growth

Thread Safety

Complexity

When to Use

LookupIndex<TKey>

Declaration

Data Structure

API

Null Handling

Partial Index Support

When to Use

Limitations

UniqueIndex<TKey>

Declaration

Data Structure

API

Uniqueness Enforcement

Example

When to Use

Limitations

SortedSetIndex<TKey>

Declaration

Data Structure

API

What a by-ref view promises

Complexity

Example

When to Use

Limitations

SortedListIndex<TKey>

Declaration

Data Structure

API

Complexity

When to Use

SortedSet vs SortedList

RangeLookupIndex<TRangeKey>

Declaration

Data Structure

API

Incremental Maintenance

Example

When to Use

Limitations

GroupedSortedIndex<TRangeKey>

Declaration

Data Structure

API

What a grouped by-ref view promises

Target Query Pattern

Example

When to Use

Limitations

GlobalAggregationIndex<TValue> (IndexType.Aggregation)

Declaration

Data Structure

API

Min/Max Tracking

Example

When to Use

Limitations

UniversalAggregationIndex<TKey, TValue> (IndexType.UniversalAggregation)

Declaration

Data Structure (AllStatsAggregationIndex)

API

Lazy Min/Max Recomputation

Ranking Support

Example

When to Use

Limitations

Composite Indexes

Key Encoding Strategies