TrailBase: Sub-millisecond open-source application base with Rust, SQLite and V8
trailbase.io
trailbase.io
It is nice to see more backends utilizing SQLite. The benchmarks and the Comparisions section also seem well done.
Just a nitpick - list the versions of the tested platforms.
Based on your benchmarks repo it looks like that the tests were done against PocketBase < v0.23 but note that PocketBase v0.23+ (especially with the Create API rule dry submit removal in v0.24+) has introduced significant changes and performance improvements - ~4x times in high concurrent scenarios in our own benchmarks[0] (if you want to retest it note that the CGO driver is no longer loaded by default and will have to be registered manually; see the example "db_cgo.go" in the PocketBase benchmarks repo or in the "Custom SQLite driver" docs[1]).
[0]: https://github.com/pocketbase/benchmarks
[1]: https://pocketbase.io/docs/go-overview/#custom-sqlite-driver
Hey thanks for chiming it. Huge fan of PocketBase, has been a major inspiration :applause:. For anyone driving by, certainly a more mature product.
- Based on your benchmarks repo it looks like that the tests were done against PocketBase < v0.23 but note that PocketBase v0.23+ (especially with the Create API rule dry submit removal in v0.24+) has introduced significant changes and performance improvements - ~4x times in high concurrent scenarios in our own benchmarks[0] (if you want to retest it note that the CGO driver is no longer loaded by default and will have to be registered manually; see the example "db_cgo.go" in the PocketBase benchmarks repo or in the "Custom SQLite driver" docs[1]).
You're right. I did run v0.22.21, which simply was current when I ran the benchmarks first. I absolutely will add the information, rerun, and thanks for the pointers. Glad to hear you got such a boost :clap:
I still wanna get the mattn/go-sqlite3 driver to work and it's getting a bit late here for writing coherent text... I'll update the benchmarks ASAP
Anyway, w/o fog I managed to run with mattn/go-sqlite3. I'm not sure this is expected but it didn't seem to make much of a difference with my setup (For transparency, I do recently have some issues with repeatability likely due to btrfs). I'm certainly not seeing 4x but around 30-35%, which is still very impressive!
I expect it to be faster but it is also possible that maybe in your specific collection and execution scenario it somehow perform worst, or at least not the same as in my benchmarks, I'm not sure.
The ~4x mentioned speed up is based on the tests for Hetzner CAX41 with the CGO driver when creating 50k records with 500 concurrent clients:
Old (58.409064001s): https://github.com/pocketbase/benchmarks/blob/54140be5fb0102...
New (13.580098262s): https://github.com/pocketbase/benchmarks/blob/7df0466ac9bd62...
---
Edit: Based on your benchmarks repo (https://github.com/trailbaseio/trailbase-benchmark/tree/main...) note that compiling PocketBase with `CGO_ENABLED=1` is no longer enough to kick in the github.com/mattn/go-sqlite3 driver and you need to enable it manually as shown in the db_cgo.go in our benchmarks OR like in https://pocketbase.io/docs/go-overview/#github-commattngo-sq... (the reason for this change was to avoid the "multiple definition" linker errors).
FWIW, I did this locally (also happy to fork PB and check it in, certainly aids transparency). I've already updated the numbers in the benchmark doc but will continue to try squeeze more out of it.
As to 4x, my concurrency levels are significantly lower which could certainly explain it. Is PB juggling multiple connections increasing write lock congestion or are you serializing access, e.g. via a worker thread? Also happy to chat more (feel free to send me an email), I certainly want PB to have the best possible representation and maybe we can even speed things up on both sides
Yes I noticed that too so that it is very likely the reason.
In all cases my initial comment wasn't intended to "dispute", argue or anything like that, so please don't feel obligated to waste time updating the benchmarks. They are valid as they are!
My initial comment was more of a note/suggestion to simply list the used versions of the tested platforms (not just for PocketBase) because often they change over time and the shown results could be misleading if someone stumble on the article 2 years later for example.
FWIW, it never felt like a dispute and very much agree with your suggestion. I'm also just trying to do a decent enough job, both with the benchmarks and TrailBase itself. Either way, my offer to keep an open channel stands in case you want to share experiences or are in desperate need for a beverage :)
From my own experiments with Node and SQLite I've found synchronous sqlite libraries like https://www.npmjs.com/package/better-sqlite3 substantially faster, especially when making many simple queries (a pattern encouraged by SQLite.)
What you're saying makes a lot of sense. SQLite is sync and if you're program is alone accessing SQLite doing a single task, going sync is the way. If you're doing a lot of parallel work, both your JS event loop interleaving many tasks and several event loops accessing SQLite in parallel you have to make trade-offs. Specifically, `conn.query` may block for a long time w/o doing any work. Depending on your use-case it may or may not be ok to block the event-loop that entire period. TrailBase's setup is optimized to maximize throughput under highly concurrent loads, rather than minimizing latency in single-threaded workloads. That's not to say, TrailBase isn't quick. It's pretty low-latency even under load. However, if that's all you're after you're probably better off with better-sqlite3 or dropping down to C :).
Does that make sense?
> What you're saying makes a lot of sense. SQLite is sync and if you're program is alone accessing SQLite doing a single task, going sync is the way. If you're doing a lot of parallel work, both your JS event loop interleaving many tasks and several event loops accessing SQLite in parallel you have to make trade-offs. Specifically, `conn.query` may block for a long time w/o doing any work. Depending on your use-case it may or may not be ok to block the event-loop that entire period.
In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.)
It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to prevent writes from blocking reads.
> TrailBase's setup is optimized to maximize throughput under highly concurrent loads, rather than minimizing latency in single-threaded workloads. That's not to say, TrailBase isn't quick. It's pretty low-latency even under load. However, if that's all you're after you're probably better off with better-sqlite3 or dropping down to C :).
Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread.
Agreed.
> It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to preven> In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.)
Agreed.
> It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to prevent writes from blocking reads.
The v8 isolates run whatever you as a TrailBase user feed them. I would certainly expect writes to be a common occurrence.
> Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread.
Ultimately, it will depend a lot on the ratios. If you have mostly reads and the occasional write you're probably right. I did spend a bit of time exploring different execution models: https://github.com/ignatz/libsql_bench in case you're interested. There's also some prior works from the folks GIL'ed languages (especially ruby) around how to wrangle write congestion for multi-process workloads. Sadly for them, they don't have inter-thread comms in their arsenal :)
One big unknown for me is, how you'd clearly separate reads from writes. As far as I can think, you'd have to rely on users to pick the right sync or async funnel. Which may be ok at least for simple queries.
FWIW, the thing or elephant that bothered me more than inter-thread comms is the opportunity cost of not running reads in parallel. Then at the same time, the current setup does seem to manage to saturate the machines I've run on. Very high core-count machines would probably be a different story. It will certainly also depend on how much actual other work the server has to do, i.e. is it just a glorified SQLite accessor? I certainly would love to further optimize that aspect. You seem very well informed so I'd love to hear your thoughts. Hit me up, if you'd like to chat more.t writes from blocking reads.
The v8 isolates run whatever you as a TrailBase user feed them. I would certainly expect writes to be a common occurrence.
> Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread.
Ultimately, it will depend a lot on the ratios. If you have mostly reads and the occasional write you're probably right. I did spend a bit of time exploring different execution models: https://github.com/ignatz/libsql_bench in case you're interested. There's also some prior works from the folks GIL'ed languages (especially ruby) around how to wrangle write congestion for multi-process workloads. Sadly for them, they don't have inter-thread comms in their arsenal :)
One big unknown for me is, how you'd clearly separate reads from writes. As far as I can think, you'd have to rely on users to pick the right sync or async funnel. Which may be ok at least for simple queries.
FWIW, the thing or elephant that bothered me more than inter-thread comms is the opportunity cost of not running reads in parallel. Then at the same time, the current setup does seem to manage to saturate the machines I've run on. Very high core-count machines may be a different story. It will certainly also depend on how much actual other work the server has to do, i.e. is it just a glorified SQLite accessor? I certainly would love to further optimize that aspect. You seem very well informed so I'd love to hear your thoughts. Hit me up, if you're willing to chat more.
I feel I prefer to have a locked-down database, and implement everything "backend-side" with a kind of "admin API" which has access to everything, and checks user roles in the backend, it feels cleaner to me, is that also possible?
Did I misunderstand? Was there something that thew you off? - always keen to improve
I understand ACLs on the backend and API queries on the client, I just don't find it that practical to use
> I feel I prefer to have a locked-down database, and implement everything "backend-side" with a kind of "admin API" which has access to everything, and checks user roles in the backend, it feels cleaner to me, is that also possible?
is different from what FireBase or TrailBase does?
Are you saying that you'd prefer to run your own backend binary (as opposed to running in an integrated runtime), do your own ACL checking, and have more of a free-form SQL-like API with the DB layer?
It would have certainly been simpler to just plot the overall runtime (width of the graph), I did think that it was quite interesting that PB's goja integration takes a while before utilizing the entire machine.
Naively, I would argue that being idiomatic in the respective ecosystem is more important than perfect consistency. Only few users will likely use 2 or more languages and probably even then there's a balance to be struck.
You're likely to encounter people doing "fringe" stuff like transpiling Go or Rust into Wasm, but those folk are all most likely capable of dealing with idiomatic impedance mismatches themselves.
You can probably leverage something like https://linq2db.github.io/ and replace some parts of their SQLite provider with HTTP calls to avoid coding most complicated parts. This would be for the raw sql API, for the records API I'm not sure LINQ makes sense since from what I saw in the docs it would be a pretty small subset of LINQ anyway. An easy way to generate the model classes from the DB schema would be nice too (a source generator accessing the schema via some endpoint?).
Edit: it's nice seeing C# getting some love early in a project, I'm used to fallback to js/ts to try the new stuff :)
That said, you absolutely can generate code from the DB schema already. There are `/examples` (just none in C# yet).
I'm optimistic that C# will become increasingly important with respect to cross-platform mobile/desktop development and thus receive higher priority treatment in general.
Would it be faster? Maybe, I found that the dart and JS clients didn't reach their theoretical min latency of 3-5ms, so I'm inclined to believe that there's some bottlnecking on the server-side. I'd be very happy to be wrong on this.
From your perspective, would it make sense to just compare the respective dart and JS clients?
What’s the bottleneck at that point? That number seems small even when stacked up against much slower examples.
A few observations:
- Both benchmark driver and server are running locally on my laptop, completely saturating the machine.
- I've managed to achieve higher throughput with rust. At least part of it is probably that the client side is lighter on my already saturated machine.
- I've found that I'm getting roughly 3x the performance on a pretty humble 8700G desktop.
- I've found the file-system to have a non-trivial impact.
- The benchmark is only as fast as the bottleneck 50cm in front of the screen managed to make it run. The benchmark driver is here: https://github.com/trailbaseio/trailbase-benchmark/tree/main.... Dotnet is super swift, so I'd be surprised if it couldn't be further optimized (whereas Dart and Node are fairly client-side bottlenecked).