Performance

Performance

This page covers how to make file-backed DynamoDbLite fast. In-memory stores are already fast enough for most tests; the levers here matter when you persist to a file. They are listed roughly in order of impact.

For the writer-serialization model and how to tune waiting under contention (busy_timeout, CommandTimeout), see Concurrency — this page is about throughput, not locking.

The short version

• Enable WAL on file-backed stores — the single biggest win for write throughput.
• Batch writes when you can — faster on every store, and it makes the journal/checkpoint settings stop mattering.
• Leave synchronous at the default (NORMAL) — don't trade durability for speed you don't need.
• mmap_size is a read lever, not a write one.
• wal_autocheckpoint is a size-vs-throughput dial — the default is fine; lower it only to bound the WAL file.

Enable WAL for file-backed writes

By default a file-backed store uses SQLite's rollback (delete) journal, which forces a disk sync on every commit. Because DynamoDbLite runs each write as its own transaction, that is a sync per write — the dominant cost of file-backed writes.

Write-Ahead Logging removes it. Under WAL at the default synchronous=NORMAL, commits append to the WAL and sync only at checkpoints, so individual writes get dramatically cheaper. WAL also lets readers proceed alongside a writer (see Concurrency).

Turn it on with WithWriteAheadLog():

builder.Services.AddDynamoDbLite(o => o
    .WithConnectionString("Data Source=myapp.db")
    .WithWriteAheadLog());

WAL has no effect on in-memory stores, and it is persistent on the file once enabled — see DI and Configuration for the full contract.

Batch your writes

A single PutItem opens a pooled connection, applies the per-connection pragmas, and runs its own transaction — once per item. BatchWriteItem amortizes all of that: one connection, one set of pragmas, and a single transaction wrapping up to 25 items. On a file store that one transaction also collapses many commits (and their syncs) into one.

Both effects compound, so batching is faster on every store — including in-memory, where there is no disk sync to save and the win comes purely from the amortized connection and transaction overhead. It is also the most robust optimization: once you batch, the WAL and checkpoint settings below barely move throughput, because there are far fewer commits to tune.

The 25-item cap matches DynamoDB and is configurable — see WithMaxBatchWriteItems and Batch Operations.

Leave synchronous at NORMAL

DynamoDbLite applies synchronous=NORMAL to every connection, and that is the right setting — leave it alone.

• NORMAL (the default) is durable across application and process crashes. Only a power loss or OS-level crash can drop the most recent commit(s), and even then it never corrupts the database file.
• FULL adds a sync on every commit for durability you rarely need in a local/test/mobile store, and gives most of WAL's gain back.
• OFF is the only setting that risks corruption on a crash — avoid it.

Once WAL is on, lowering synchronous buys almost nothing (commits already skip the per-commit sync), so there is no reason to take the durability hit.

mmap_size helps reads, not writes

mmap_size maps part of the database file into memory, so page reads avoid read() syscalls. That helps read-heavy file workloads; it does nothing for write throughput, because writes go through the WAL rather than the mapped region. Set it for read-dominated file stores with WithPragma:

builder.Services.AddDynamoDbLite(o => o
    .WithConnectionString("Data Source=myapp.db")
    .WithWriteAheadLog()
    .WithPragma("mmap_size", "268435456")); // 256 MB

It has no effect on in-memory stores.

wal_autocheckpoint: WAL size vs write throughput

A checkpoint merges the WAL back into the main database file. wal_autocheckpoint sets how many WAL pages accumulate before SQLite checkpoints automatically — the default is 1000 pages (about 4 MB at SQLite's default 4 KB page size). It is a dial between WAL size and write throughput:

• Lower (e.g. 500) keeps the WAL small and reads and recovery quick, but checkpoints more often, and each checkpoint syncs — so writes slow down. Very low values (around 100) checkpoint so often that write latency suffers badly and turns erratic; avoid them.
• Higher, or 0 to disable auto-checkpointing, makes write bursts faster but lets the WAL grow (with 0, until a connection closes or you checkpoint manually).

The default suits most workloads. Lower it only when you need a bounded, small WAL and can accept some write cost; a moderate value beats an aggressive one. If you batch your writes, this knob barely matters. Set it with WithPragma:

o.WithConnectionString("Data Source=myapp.db")
 .WithWriteAheadLog()
 .WithPragma("wal_autocheckpoint", "500");

In-memory stores

The levers above are file-store knobs. On an in-memory store WAL, synchronous, mmap_size, and wal_autocheckpoint are no-ops or inert — there is no journal, no file to map, and nothing to sync. In-memory writes are already fast; when you need more throughput, batch them. See Concurrency for the in-memory serialization model.

See also

• Concurrency — writer serialization and tuning the wait under contention.
• DI and Configuration — WithWriteAheadLog, WithPragma, and the options surface.
• Batch Operations — BatchWriteItem and the batch size cap.
• Storage Architecture — the store implementations and SQLite schema.