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 compounds with WAL rather than replacing it (each batch is still a commit, so a rollback-journal store still syncs).
• 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

Prefer BatchWriteItem for bulk writes — single-call writes are the slow path. A single PutItem opens a pooled connection, applies the per-connection pragmas, and runs its own transaction, once per item, and on the default journal each call forces its own commit sync. BatchWriteItem amortizes all of that: one connection, one set of pragmas, and a single transaction wrapping the whole batch. 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. Batching is not a substitute for WAL, though: each batch is still a commit, so on a rollback-journal store every batch still syncs. Enable WAL and batch — the two compound. The checkpoint dial below still moves sustained batch throughput as well, just much less than the journal-mode choice does.

Batching gains the most on tables without secondary indexes. For an index-free table a batched put is just the write. A table with a GSI or LSI reads each item's prior value and maintains its index entries per item, so an indexed table does not reach the same batch throughput.

Chunk your items and send each chunk as one batch:

foreach (var chunk in items.Chunk(25))
{
    var writes = chunk
        .Select(item => new WriteRequest { PutRequest = new PutRequest { Item = item } })
        .ToList();

    await client.BatchWriteItemAsync(new BatchWriteItemRequest
    {
        RequestItems = new() { ["MyTable"] = writes },
    });
}

The 25-item cap matches DynamoDB. Larger batches mean fewer transactions and fewer commits, so for local bulk loads you can raise the cap and chunk larger — with diminishing returns, because per-item work (serializing each item to JSON, then the upsert) is a floor batching cannot remove:

builder.Services.AddDynamoDbLite(o => o
    .WithConnectionString("Data Source=myapp.db")
    .WithWriteAheadLog()
    .WithMaxBatchWriteItems(500)); // local bulk-load only

Raising the cap diverges from DynamoDB, which rejects batches over 25 — keep it for seeding and tests, not for code you also run against real DynamoDB. 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. Batching cuts the number of commits but not the per-checkpoint cost, so this dial still affects sustained batch throughput — just far less than the WAL-versus-journal choice. 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.