Benchmarks

Why are file-backed and in-memory reads identical?

On warm lookups, all pages are already cached in the IPageCache. The Pager resolves from cache without touching the storage device or WAL, so the I/O backend makes no difference. The B+tree leaf-hint cache often skips root-to-leaf traversal entirely for clustered access patterns, which is why lookups stay under 500 ns even at 10K rows.

The Collection API is ~2x faster than SQL because it bypasses SQL parsing, planning, and row decoding — it goes straight from key to B+tree to JSON deserialization.

Why the 15–18x difference?

With only 16 cached pages and 200K rows, almost every lookup is a cache miss. File-backed lookups must read 4 KB pages from the FileStorageDevice via RandomAccess — each page read involves a kernel syscall and disk I/O. In-memory lookups hit the MemoryStorageDevice which is a simple array copy, staying in user-space memory.

This benchmark uses PagerOptions.MaxCachedPages = 16 to simulate cache pressure. In production, set MaxCachedPages large enough to hold your hot working set and you will see warm-cache performance (sub-500 ns) for most lookups.

Why is file-backed 21–27x slower for writes?

Every auto-committed write must durably flush the WAL to disk before returning. This means at minimum one fsync per insert — the dominant cost is waiting for the NVMe write to complete (~50–60 μs on this hardware). In-memory mode skips the WAL entirely, so inserts are just B+tree mutations in RAM.

Optimization tip: If you need file-backed write throughput, batch multiple inserts into a single explicit transaction. The WAL flush cost is amortized across all rows in the batch (see Batch Inserts below).

The batching effect

Compare single inserts (16,300 ops/s file-backed) to batched inserts (223,000 rows/s file-backed) — that is a 13.7x throughput improvement just from batching 100 rows per transaction. The cost of one WAL flush (~60 μs) is now shared across 100 rows instead of paid per row.

In-memory mode sees a smaller improvement (349K → 584K) because the baseline was already fast. The remaining overhead per row is SQL parsing and B+tree page splits. Use the PrepareInsertBatch API to skip repeated parsing for maximum throughput.

The takeaway

Reads are free — once pages are cached, file-backed and in-memory are identical. The page cache is the performance equalizer.

Writes pay for durability — file-backed writes are 21–27x slower because each commit requires flushing the WAL to disk. Batching reduces the gap to 2.6x. If you do not need crash recovery, use in-memory mode for write-heavy workloads.

Cache sizing matters — if your hot working set fits in MaxCachedPages, even file-backed reads stay at ~460 ns. Cold reads are 15–18x slower because each cache miss is a kernel I/O call.

What to expect at scale

All scan benchmarks run at three dataset sizes (1K, 10K, 100K rows) to show how performance scales. Full scans are O(n) — they touch every leaf page sequentially. LIMIT queries are effectively O(1) because they stop early. ORDER BY + LIMIT is O(n) but with a constant factor much smaller than full sort because only the top-N rows are retained in memory.

The PointLookupBenchmarks suite additionally tests indexed vs non-indexed column lookups, projected columns, and miss-path performance at the same three scales.

SQL vs Collection: which is faster?

For point operations, the Collection API is consistently ~2x faster than SQL (217 ns vs 461 ns for reads, 2.1 μs vs 2.9 μs for writes). The Collection API bypasses SQL parsing, AST construction, query planning, and row-level RecordEncoder decoding. It goes directly from key → B+tree lookup → JSON deserialization.

Use SQL when you need joins, aggregates, or complex predicates. Use the Collection API when you know the key and want maximum single-document throughput.

What to expect in production

In an 80/20 mix, read latency stays near warm-cache levels (~460 ns SQL, ~220 ns Collection) because the hot working set fits in cache. Write latency is dominated by WAL flush in file-backed mode (~60 μs) or is near-instant in-memory mode (~2–3 μs).

The single-writer lock means writes are serialized, but reads proceed concurrently via snapshot isolation. Read throughput scales linearly with reader count (verified by the ReaderScalingBenchmark), while write throughput remains constant.