Indexes & Transactions

Index Management

For frequently queried JSON properties, create expression indexes to speed up lookups. These methods are on DocumentStore directly (not on IDocumentStore).

// Create an index — up to 30x faster queries
await store.CreateIndexAsync<User>(u => u.Name, ctx.User);

// Nested properties
await store.CreateIndexAsync<Order>(o => o.ShippingAddress.City, ctx.Order);

// Drop a specific index
await store.DropIndexAsync<User>(u => u.Name, ctx.User);

// Drop all indexes for a type
await store.DropAllIndexesAsync<User>();

CreateIndexAsync uses IF NOT EXISTS, so calling it multiple times is safe. Index names are deterministic (idx_json_{typeName}_{jsonPath}).

Generated SQL per provider

CreateIndexAsync emits a partial expression / functional index on each SQL provider — same C# call, different DDL.

Provider	Emitted DDL
SQLite / SQLCipher	CREATE INDEX IF NOT EXISTS idx_json_User_name ON documents (json_extract(Data, '$.name')) WHERE TypeName = 'User';
PostgreSQL	CREATE INDEX IF NOT EXISTS idx_json_User_name ON documents ((Data #>> '{name}')) WHERE TypeName = 'User';
MySQL	CREATE INDEX idx_json_User_name ON documents ((CAST(JSON_EXTRACT(Data, '$.name') AS CHAR(255))));
SQL Server	ALTER TABLE [documents] ADD cc_idx_json_User_name AS CAST(JSON_VALUE(Data, '$.name') AS NVARCHAR(450)); CREATE INDEX idx_json_User_name ON [documents] (cc_idx_json_User_name) WHERE TypeName = N'User';

Note

SQL Server cannot index a JSON_VALUE expression directly, so the provider adds a PERSISTED computed column (cc_{indexName}) and indexes that. DropIndexAsync removes both the index and the computed column.

Note

When using table-per-type mapping, the index is created on the mapped table and the WHERE TypeName = '...' clause is omitted — it’s already implicit. The index becomes a plain expression index rather than a partial one.

Performance impact

JSON property indexes dramatically speed up equality queries by letting the database use a B-tree lookup instead of scanning every row with JSON extraction.

Flat query (filter by name, 1,000 records):

Method	Mean
Without index	270 us
With index	8.52 us

~32x faster. Indexes give the biggest wins on selective queries that return few results.

When indexes don’t help

Some predicates cannot use an expression index, even if one exists for the property. Either rewrite the query, accept the scan cost, or denormalize.

Pattern	Reason
Where(u => u.Name.Contains("li"))	Leading wildcard (LIKE '%li%') — B-trees can’t satisfy this.
Where(u => u.Name.EndsWith("son"))	Same — LIKE '%son'.
Where(o => o.Lines.Any(l => l.Qty > 1))	Requires expanding the child collection per row. Consider promoting the predicate’s outcome into a top-level property (e.g. a denormalized HasLargeLine boolean) and indexing that.
Case-insensitive matches	The functional index would need to match the case-folded expression, which is not emitted.
Where(... && unindexedExpr)	Database may still pick the index, but only if the indexed predicate is selective enough.
All predicates on LiteDB and IndexedDB	These providers do not push predicates down — see Provider Reference.

StartsWith("Al") does use an index — it translates to LIKE 'Al%', which a B-tree can satisfy with a range scan.

Composite / multi-column indexes

Pass multiple property-access expressions to index over several JSON paths in a single B-tree:

// Composite index on (Country, Age) for User
await store.CreateIndexAsync(ctx.User, u => u.Country, u => u.Age);

// Drop the same composite by repeating the key in order
await store.DropIndexAsync(ctx.User, u => u.Country, u => u.Age);

Composite indexes use the naming convention idx_json_{typeName}__{path1}__{path2}… (paths joined with double underscore) and follow standard B-tree leftmost-prefix rules: a query filtering only on Country can still use the composite, but one filtering only on Age cannot.

Provider	Emitted DDL
SQLite / SQLCipher	CREATE INDEX IF NOT EXISTS idx_json_User__country__age ON documents (json_extract(Data, '$.country'), json_extract(Data, '$.age')) WHERE TypeName = 'User';
PostgreSQL	CREATE INDEX IF NOT EXISTS idx_json_User__country__age ON "documents" ((Data #>> '{country}'), (Data #>> '{age}')) WHERE TypeName = 'User';
MySQL	CREATE INDEX idx_json_User__country__age ON \documents` ((CAST(JSON_EXTRACT(Data, ‘$.country’) AS CHAR(255))), (CAST(JSON_EXTRACT(Data, ‘$.age’) AS CHAR(255))));`
DuckDB	CREATE INDEX IF NOT EXISTS idx_json_User__country__age ON "documents" (json_extract_string(Data, '$.country'), json_extract_string(Data, '$.age'));
SQL Server	One PERSISTED computed column per path (cc_{indexName}_0, cc_{indexName}_1, …), then a single CREATE INDEX over all of them filtered by TypeName. DropIndexAsync discovers the backing columns via sys.index_columns and removes them after the index.

If most of your workloads filter on just one of the columns, prefer two single-column indexes and let the planner combine — composite indexes earn their keep when you consistently filter (or sort) on the leftmost prefix.

Write amplification

Every expression index adds a B-tree write to every Insert / Update / Upsert / BatchInsert. Five indexes ≈ 6× the write cost of an unindexed table. Index the properties you actually query; do not index defensively. See Performance for more.

Transactions — Unit of Work

Grouping writes into a single transaction is done through a UnitOfWork created from the store. It buffers Add/AddRange/Update/Upsert/Remove operations and applies them atomically when you call SaveChanges — all commit or all roll back. This is the only way to open a transaction (there is no RunInTransaction).

var uow = store.CreateUnitOfWork()
    .Add(new User { Id = "u1", Name = "Alice" })
    .Update(existingUser)
    .Remove<User>("u2");

await uow.SaveChanges();
// All three operations committed in a single transaction.
// On success the queue is cleared automatically.

Contiguous same-type inserts are coalesced into the batch-insert fast path, so grouping inserts in a unit is as fast as BatchInsert. A unit is a write buffer, not a change tracker: reads against the store don’t see operations still buffered in an uncommitted unit. For read-modify-write atomicity, use ETag/CAS (IfMatch) + retry.

Member	Purpose
Add<T>(document)	Queue an Insert
AddRange<T>(documents)	Queue a batch Insert
Update<T>(document)	Queue a full-document Update (Id required)
Upsert<T>(patch)	Queue an Upsert (Id required)
Remove<T>(id)	Queue a Remove by Id
SaveChanges(ct)	Apply all queued operations atomically, then clear
Clear()	Discard the queue without executing
PendingCount	Number of operations currently queued

Error handling. If SaveChanges() fails the transaction is rolled back and the queue is preserved, so you can inspect or amend it and retry:

try
{
    await uow.SaveChanges();
}
catch (InvalidOperationException)
{
    // Nothing was written. uow.PendingCount still reflects the queued operations.
    uow.Clear();
}

You only ever inject IDocumentStore — the unit is created from it, never registered in DI.

Note

A UnitOfWork instance is not thread-safe. Use one per logical operation or per request scope.