KvSQL Cuts Commits to One Sync and Passes SQLite on Prepared Reads

KvSQL 0.2.0 is out. Two things changed. A commit now syncs to disk once instead of twice, and SQL has prepared statements that keep their compiled plan. Both came out of one question: does KvSQL hold up against the Rust stores people already pick, or only against SQLite?

So before writing any of it, the project benchmarked KvSQL against redb, fjall and GlueSQL, with SQLite as the reference. Same machine, same 100,000 keys, real files, 8 MiB caches, and the stores taking turns round by round so a slow patch on the machine doesn’t land on one of them. An independent reviewer checked the method first and caught one real flaw: GlueSQL’s reads were each doing a disk sync, which made it look about 90 times slower than it is. With that fixed, the numbers decided what 0.2 should be.

KvSQL 0.2 on the same machine as its neighbours. SQL point select by primary key: KvSQL prepared 1.86 µs, KvSQL from text 2.65 µs, SQLite prepared 2.10 µs, SQLite parsed each time 3.41 µs, GlueSQL 16.3 µs. One small write synced to disk: KvSQL 0.1 339 µs, KvSQL 0.2 280 µs, redb 328 µs, SQLite 161 µs, fjall 131 µs.

From the recorded runs on a Linux container with two virtual CPUs. Single figures there move by 20 to 30 percent between runs.

One Sync Per Commit

A 0.1 commit wrote its new pages, synced, then wrote a small meta page pointing at them and synced again. Two waits for the disk on every commit, and that was most of the gap to SQLite.

In 0.2 a commit also writes a commit record: a list of every page it wrote, each with its checksum. Pages, record and meta page all go out together, followed by one sync. If the power goes before that sync finishes, any of those writes might be missing. So opening the file now checks the newest commit’s record against the disk. If even one page is missing or wrong, the file opens at the commit before, which this commit never touched. The file format moves to version 2 for this.

A small synced write went from 339 µs to 280 µs on the recorded machine. That puts KvSQL level with redb, which uses the same kind of copy-on-write tree. SQLite’s WAL (161 µs) and fjall’s log (131 µs) are still quicker. Both write a log entry first and tidy up later, and that’s hard to beat on a single small write.

Prepared Statements

KvSQL already cached parsed SQL by its text, so parsing wasn’t the slow part. A profile showed where the time went: working out the tables and columns, binding the expressions and choosing the access path again on every call. A prepared statement now does that once and keeps the plan while the schema stays the same. Each run just fills in the parameters.

let by_id = db.prepare("SELECT name FROM users WHERE id = ?")?;
let r = db.execute_prepared(&by_id, &[Value::Int(42)])?;

A point select by primary key went from 2.65 µs to 1.86 µs. SQLite with its own prepared statement took 2.10 µs in the same run, and the two stores’ fastest and slowest rounds didn’t overlap. GlueSQL, the Rust SQL engine in the comparison, took 16.3 µs. To check the plans, the random-SQL test that already compares every answer with SQLite now also runs each query as a prepared statement, with its literal values passed as parameters, twice.

Where the Others Still Win

Plenty. redb reads faster inside an open read transaction (0.78 µs against 1.17 µs) and scans 100 keys about twice as fast. SQLite and fjall commit small writes faster, as above. Bulk loading 100,000 keys took KvSQL 201 ms, against 57 to 75 ms for fjall, redb and SQLite. The whole set of tables, and the method, is in neighbours.md and the benchmark’s README.

What the numbers do support is the reason KvSQL exists. It’s one store that does key lookups and SQL on the same records. A key read through get, one call at a time, is level with redb’s (1.19 µs against 1.32 µs). SQL on the same rows is now quicker than SQLite’s prepared statements, and six to nine times quicker than GlueSQL’s.

What the Review Found

An independent reviewer went through the release before it went out. The worst finding was a gap that 0.1 had too, now wider with one sync. A process killed inside a commit can leave that commit in the operating system’s cache. On reopen, the file looked complete. If the next commit was then cut short by a power failure, an earlier commit that had already returned could be lost. Opening a file now syncs it before anything else happens. A new test kills a process, reopens and then cuts the power; it failed before the fix and passes after.

Upgrading

0.2 opens 0.1 files and moves them to format version 2 at their first commit. After that, 0.1 refuses them, so keep a copy if you might go back. The engine is on the v0.2.0 tag, and the server and shell binaries are on the download page.