Benchmarks

Compare BoxDB with unmodified SQLite using Python 3.11+, the Rust build prerequisites and Just:

just bench-sqlite
just bench-sqlite --boxdb-format binary --schema ordinary --rows 10000
just bench-sqlite --help

Without Just, run python3 tools/benchmark_sqlite.py with the same arguments. Defaults cover binary and YAML storage, with ordinary and VARCHAR columns. No SQLite CLI installation is needed. --directory /existing/path selects the filesystem for temporary databases, which are removed after each sample.

The command builds two release executables before measuring. The reference lives in an isolated Cargo workspace, uses the registry SQLite package without BoxDB’s patch, and must match BoxDB’s original SQLite amalgamation byte for byte. Both use the same workload source and SQLite adapter. The reference mirrors the public Value enum to preserve owned-row allocation size without linking BoxDB’s modified engine. Runtime version, row layout and DECIMAL probes are checked before comparing results.

Each engine runs in a separate process for each sample. One warmup is discarded, then engine order alternates. Timings exclude process startup and compilation; read queries are also warmed inside each sample. Run on an idle machine.

The command prints microseconds, ratios, percentage changes and added latency. Positive change means BoxDB takes longer. Reports go to a new directory under target/performance/; --output /new/report/path selects another location:

• summary.md and summary.json: all measured samples and median comparisons.
• metadata.json: Git revision, dirty status, source hash and Rust toolchain.
• raw/: individual reports and logs, including discarded warmups.
• build/ and bin/: build logs and the measured executables.

This compares the complete BoxDB API with a direct SQLite client: modified engine, type checks, result allocation and storage publication all contribute. It does not isolate the cost of the native-type dispatch alone.

Client-only harness

The direct command below links both clients to BoxDB’s patched SQLite because both use boxdb-rusqlite and boxdb-sqlite-sys. It measures API/storage overhead and remains the harness for the revision regression gate. It is not the unmodified SQLite comparison above.

cargo bench --locked --bench sqlite_vs_boxdb -- --help
cargo bench --locked --bench sqlite_vs_boxdb -- --json /tmp/boxdb-client-benchmark.json

Concurrent binary clients

The process benchmark measures reads, autocommit updates and batches of 50 updates in BEGIN IMMEDIATE transactions, using one or four independent clients and schemas with one or 16 typed tables:

cargo bench --locked --bench concurrent_writes -- --json /tmp/boxdb-concurrent.json

Each case discards a warmup and retains seven samples. Process startup and connection opening finish before a start barrier. Timings include lock waits and durable commits with BoxDB’s normal WAL/FULL settings. Every sample checks the exact final counter and quick_check. --directory selects the filesystem; --writes, --reads, --batch and --samples control the workload. Throughput uses the longest worker’s elapsed time, excluding report encoding and process teardown.

The JSON contains throughput, raw latencies, p50/p95/p99 and busy retry counts. Autocommit latencies are per statement; transaction latencies are per complete batch divided by its write count, including commit. Standalone busy failures and rejected BEGIN commands are retried with 1 ms backoff for up to 30 seconds; their time is included. Transaction bodies are never replayed. These retries belong to the benchmark, not the BoxDB client.

To compare revisions, build and retain each executable before measuring, then alternate their runs on the same idle machine with identical arguments. Report the median across samples and retain raw results; filesystem flush times can dominate short writes.

Measurement

The shared harness generates identical deterministic integer/text rows, SQL and expected results outside timers. Both clients have an INTEGER primary key and an additional explicit ID index. Every workload is verified, including committed data after reopening.

Workload	Timed work	Latency unit
insert_transaction	Multi-value INSERTs inside one BEGIN/COMMIT	Inserted row
point_lookup	Equality queries on indexed IDs	Query
range_scan	Ordered ID ranges returning up to 50 rows	Query
aggregate	COUNT and SUM grouped by category	Query
update_transaction	Point UPDATEs inside one BEGIN/COMMIT	Updated row
update_autocommit	The same UPDATEs with a commit per statement	Updated row
delete_transaction	Point DELETEs inside one BEGIN/COMMIT	Deleted row
reopen_scan	Open the committed file and read all remaining rows	Database

Updates run twice on selected rows, then deletion removes them. --writes is capped at the dataset size. Read workloads run before mutations; every query is verified and warmed before timing. The warmed set is repeated according to --read-repetitions; latency is divided by the total executed queries. Schema creation and correctness checks are excluded. Writes include transaction and commit costs, and the initial preparation of previously unseen SQL.

One full warmup sample per client is discarded. Measured samples alternate client order. Reports contain raw durations, medians, configuration, SQLite version and OS/architecture. BoxDB/SQLite divides BoxDB’s median microseconds per unit by SQLite’s median for the same workload. Above 1 means BoxDB takes longer. The automated JSON exposes this as runs[].comparisons[].boxdb_over_sqlite; the direct harness uses comparisons[].

Storage sizes are sampled after insertion: database_bytes, wal_bytes and total_storage_bytes include the main file and WAL, excluding shared-memory and lock files. Text payloads include deterministic SHA-256 digests; sizes are specific to that dataset.

Comparable settings

Binary-mode defaults match both clients: WAL, FULL synchronization, fullfsync=ON, normal locking, foreign keys enabled, a five-second busy timeout, and a prepared statement cache of 512 entries. See SQLite’s synchronization, fullfsync, and locking references. The --sqlite-* switches change only the reference client; non-default reports must be interpreted using their saved configuration.

YAML mode executes SQL in memory and atomically replaces a complete YAML file on each commit, synchronizing the temporary file and its parent directory. Its reads do not include file access after opening. These are different storage architectures, so YAML ratios compare end-to-end behavior rather than client overhead alone. The SQLite reference settings are unchanged in YAML runs.

The current harness explicitly uses STRICT tables for both clients. Saved reports collected before strict-by-default storage used ordinary records tables; their timings are retained unchanged.

Both clients materialize owned Rust rows. BoxDB additionally returns column names, supports SQL batches, monitors external changes and decodes extended types. Table declarations apply BoxDB’s strict policy; other SQL executes in SQLite directly. Prepared SQL and column-type information are cached; external changes invalidate type caches.

With --typed, BoxDB declares the payload as VARCHAR(100), adding metadata and connection-local validation triggers. The reference uses TEXT with an equivalent length CHECK. Both enforce the length bound, while BoxDB also preserves its logical schema and validates values on reads. This measures one inexpensive type check, not UUID, JSON, decimal, date or array parsing costs.

Reopening includes a full scan because SQLite opens files lazily. The OS cache is not flushed, so results represent a warm cache. Run on an idle machine and compare multiple dataset and batch sizes on the same filesystem. Saved local timings are not universal performance claims or CI thresholds. CI checks correctness on a small dataset and runs the regression check described below.

Performance regression check

The CI performance job measures all eight workloads in binary and YAML modes, each with ordinary and VARCHAR columns. Pull requests compare against their target branch’s base SHA; pushes to existing branches compare against the previous branch tip. New-branch pushes have no baseline and skip this job.

Both revisions use the current benchmark harness, the same runner, filesystem, release toolchain and workload settings. Builds finish before measurement. Each report retains seven measured samples after warmup, with query repetition to reduce timer noise. Correct results and reopened data are still verified. CI artifacts retain both raw reports, revision IDs and the comparison output, including on failure.

To compare two reports collected under those conditions:

python3 tools/check_performance.py /tmp/base.json /tmp/current.json
python3 -m unittest discover -s tests -p 'test_performance.py'

The checker recomputes BoxDB median latency per unit from raw samples. It fails when the increase exceeds both 30% of the baseline and 0.5 microseconds per unit. The absolute allowance avoids failures from tiny timing differences in fast queries; it also means smaller changes can pass. Use --help to adjust tolerances. Exit codes are 0 for a pass, 1 for a regression and 2 for an invalid comparison.

Reports must match workload settings, platform and SQLite version/settings, and contain at least five complete samples for both engines. Output paths may differ. SQLite dependency upgrades require a new compatible baseline. The checker uses BoxDB latency directly, so a slowdown shared with SQLite is still visible.

Shared runners and filesystem synchronization can produce noisy measurements; inspect raw samples and repeat a failed comparison on an idle machine before changing tolerances. This gate detects substantial regressions in these workloads; it does not cover every SQL query, extended type, dataset size or memory usage.

Binary reports

These checked-in reports predate the automated unmodified SQLite comparison; interpret them using their recorded implementation and settings.

All three runs use seven measured samples, 100 read queries repeated 20 times per workload, INSERT batches of 100, and 20 writes on macOS ARM64 with matching default settings:

• 1,000 rows.
• 10,000 rows.
• 1,000 rows with VARCHAR validation.

Workload	1,000 rows	10,000 rows	1,000 rows, VARCHAR
insert_transaction	1.00x	1.05x	1.03x
point_lookup	1.23x	1.25x	1.22x
range_scan	1.10x	1.11x	1.11x
aggregate	1.00x	1.00x	1.02x
update_transaction	1.05x	1.18x	1.26x
update_autocommit	0.98x	1.01x	1.01x
delete_transaction	1.00x	0.96x	1.02x
reopen_scan	1.15x	1.12x	1.24x

These are BoxDB/SQLite ratios. In the 1,000-row ordinary-schema run, point lookups take 1.784 µs versus 1.452 µs, an added 0.332 µs. The largest measured ratio across these runs is 1.26x; richer type parsing can cost more than the VARCHAR case. Durable write timings also vary with filesystem flushes and background load.

After insertion, ordinary schemas use 144,208 bytes at 1,000 rows and 1,108,288 bytes at 10,000 rows for either client (main file plus WAL). The typed run uses 152,448 bytes for BoxDB versus 144,208 for SQLite, including BoxDB’s metadata.

YAML reports

The before/after runs use five measured samples, 100 queries repeated 20 times, INSERT batches of 100, and 20 writes on the same macOS ARM64 filesystem with the same SQLite reference settings. The before implementation is the YAML codec from 380942b, with the YAML benchmark option added.

• 1,000 rows: before, after.
• 10,000 rows: before, after.

The 10,000-row measurements below include all eight database workloads. Latency is microseconds per row, query or database as listed above. Speedup divides the old YAML latency by the new YAML latency; the last two columns divide each run’s YAML latency by its own SQLite reference latency.

Workload	YAML before µs	YAML after µs	YAML speedup	Before / SQLite	After / SQLite
insert_transaction	5.426	3.199	1.70x	3.37x	2.00x
point_lookup	0.832	0.931	0.89x	0.56x	0.62x
range_scan	8.972	9.794	0.92x	0.92x	1.04x
aggregate	1,293.989	1,302.257	0.99x	1.00x	1.00x
update_transaction	2,182.960	617.600	3.53x	6.49x	2.11x
update_autocommit	41,259.506	11,349.894	3.64x	9.08x	2.52x
delete_transaction	2,016.517	568.642	3.55x	8.48x	2.89x
reopen_scan	205,016.333	87,242.042	2.35x	78.98x	38.65x

At 1,000 rows, opening and scanning falls from 21.489 ms to 8.337 ms (2.58x), and an autocommit update from 14.050 ms to 10.044 ms (1.40x). The corresponding YAML/SQLite ratios fall from 32.40x to 12.11x and from 2.58x to 2.17x. Read workloads already execute in memory; these measurements show no read speedup. In the 10,000-row run, point and range queries are slightly slower, by 0.099 µs and 0.822 µs respectively. Opening remains much slower than SQLite, which loads pages on demand instead of parsing a complete text database.

Import reuses table layouts and prepared INSERTs. Export streams borrowed SQLite cells; publication retains encoded rows and regenerates only changed rows before writing the complete file. Both synchronization calls and atomic replacement are preserved. Single-row commits remain limited by full-file writes and filesystem synchronization; explicit transactions amortize these costs. See readable storage for cache memory costs and tables that require complete reserialization.

The YAML files remain 177,060 bytes at 1,000 rows and 1,787,072 bytes at 10,000 rows after insertion. The SQLite reference uses 144,208 and 1,108,288 bytes respectively, including WAL. This workload measures integer/text rows, not large BLOBs or expensive logical type validators.

Previous storage implementations

Earlier reports are retained unchanged for comparison. Their SQLite reference used a DELETE rollback journal and EXTRA synchronization, whereas the current clients both use SQLite WAL/FULL. Their ratios describe their own experiments; changes in the reference settings prevent treating them as a single controlled before/after speedup measurement.

• Before: v1 compressed JSONL, original SQLite settings (uncached, normal locking, fullfsync off).
• First optimization: v2 native snapshots, original SQLite settings.
• Optimized with historical SQLite settings: v3 native commit log and executor, original SQLite settings.
• Optimized with matched native-engine settings: v3, SQLite prepared statements, exclusive locking and fullfsync on.
• 10,000 native-engine rows: v3 with those matched settings.

Workload	v1 / historical SQLite	v3 / historical SQLite	v3 / matched SQLite
insert_transaction	21.14x	5.40x	0.34x
point_lookup	1.97x	0.07x	0.58x
range_scan	63.26x	0.30x	0.41x
aggregate	4.35x	0.23x	0.28x
update_transaction	76.03x	7.00x	0.32x
update_autocommit	44.93x	14.69x	0.24x
delete_transaction	138.97x	13.91x	0.27x
reopen_scan	18.76x	3.59x	2.35x

Historical reports also record compiler, Git base revision, collection time, storage format and source SHA-256. When source changes were uncommitted, the Git base alone does not identify the measured implementation. The fingerprint hashes sorted src/**/*.rs, benches/**/*.rs, Cargo.toml and Cargo.lock, concatenating each relative path, a NUL separator and its contents.