Previously reader and writer channels were operating entirely
independently, which yields the lowest overhead for pure read and pure
write benchmarks.
In mixed workloads like prod, however, this is not ideal since we're
running into SQLite's own file-locking, which has more overhead than
in-process locking.
With this change, we use an RwLock to reduce congestion while still
allowing parallel reads.
Together with a low-overhead kanal channel, we see little to no
regression for pure read and pure write benchmarks, while seeing about a
3x improvement for mixed workloads.
We also move ACL checks for Create/Update queries to the writer SQLite
connection, to reduce thrashing.
Note that this complicates the APIs, since it pushes the responsibility
of declaring a query a read or write to the user to then be scheduled
appropriately.
Add `.(read_|)query_row_f` APIs similar to rusqlites
`conn.query_row` accepting a `|row| -> Result<T>` to reduce the use of
`Row` and `Rows`.
Make benchmarks more isolated by not sharing a DB across runs
accumulating writes.
There's a couple of reasons:
* As for the rust bindings: they're sub-par to rusqlite, though
rusqlite is amazing. Even libsql-server uses rusqlite over their own
bindings. The bindings are missing features such as update hooks
and the hard-coded execution model suffers from lock congestion.
* We've fixed bugs (e.g. trivial null ptr accesses went unnoticed),
raised issues, and tried to add missing functionality such as update
hooks. It's unclear if the rust-bindings are a priority or covered by
the principles laid out in the libsql manifesto. From the outside it
looks like focus has shifted to https://github.com/penberg/limbo.
* As for the C-libsql fork for SQLite itself, it's getting more and
more outdated (2024-01-30 (3.45.1)) and it's unclear when and if the
ideas from the manifesto will manifest.
Looking forward this opens the door for TrailBase to:
* Bundle more recent versions of SQLite
* Implement more performant, better scaling execution models.
* Implement realtime APIs for subscribing to data changes.
