5 ms·

TrailBase: Sub-millisecond open-source application base with Rust, SQLite and V8

73 points by trailbase 2y ago

twoodfin 2y ago
If I’m reading this right, the insertion benchmark for TrailBase C# is hitting about 4.4k inserts of relatively small records per second per core. What’s the bottleneck at that point? That number seems small even when stacked up against much slower examples.
- deleted 2y ago
  [deleted]
- trailbase 2y ago
  The C# insertion benchmark yields roughly 100000k inserts per 5.7s, so it's more like 17.5k inserts per second. 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/benchmarks/trailbase_dotnet 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).
K0IN 2y ago
what am I missing at the first benchmark? why is there no comparison to pocket base in golang (the language pocket base is written in, and is allowing extensions to be written in)?
- trailbase 2y ago
  First and foremost, PocketBase doesn't have an official client in go. There are third-party clients in many languages (more than TB for sure) but at this point I'm not sure how much it makes sense to promote it. 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?
randomwebdev 2y ago
(Disclaimer: I'm PocketBase author) 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 https://github.com/pocketbase/benchmarks [1]: https://pocketbase.io/docs/go-overview/#custom-sqlite-driver https://pocketbase.io/docs/go-overview/#custom-sqlite-driver
- trailbase 2y ago
  - It is nice to see more backends utilizing SQLite. 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:
  - trailbase 2y ago
    I'm happy to report that v0.25.0 already w/o CGO driver (fighting it right now) and GOOS=linux CGO_ENABLED=0 GOAMD64=v4 improved about 35% from 61.7s per 100k inserts to about 40s :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
Guillaume86 2y ago
Looks interesting, might play with it and the C# client soon, do you plan to build a LINQ provider eventually or keep the APIs similar across client languages?
- trailbase 2y ago
  I may plea the fifth, i.e. I'm not sure what this entails but would love to hear more. Also feel free to file a feature request. 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.
  - bigiain 2y ago
    Pretty sure you'll want to consider usage by teams writing in all of Swift, Java, and Javascript as relatively common. 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.
    - trailbase 2y ago
      Absolutely. In terms of new clients targeting mobile teams Swift and Java/Kotlin would make the most sense. I mostly meant that even folks who code in Swift, Java and JS, would likely prefer the respective clients to be idiomatic than the APIs exactly matching each other.
  - Guillaume86 2y ago
    I agree I would generally prefer clients to be idiomatic for the target language but in the case of LINQ providers I understand not doing them since they are quite involved to implement. You can probably leverage something like https://linq2db.github.io/ 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 :)
steve_adams_86 2y ago
I love the roadmap ideas for this. I'm a heavy user of pocketbase and I'm very happy with it, but I'd be more than happy to see this type of solution become more common and address needs like multi-tenancy. I'm also stoked to see the focus on performance (though pocketbase does excellent in this regard as it is)
- trailbase 2y ago
  Thanks! Happy to hear more ideas, if there's something you've always wanted :). PocketBase is great (and as I just learned, it has recently gotten even faster). One of my main goals was to expose more of the raw SQLite goodness.
laurencerowe 2y ago
Given the underlying SQLite calls are sync why is the query API async? From my own experiments with Node and SQLite I've found synchronous sqlite libraries like https://www.npmjs.com/package/better-sqlite3 https://www.npmjs.com/package/better-sqlite3 substantially faster, especially when making many simple queries (a pattern encouraged by SQLite.)
- trailbase 2y ago
  I'm not sure if you're referencing the client libs or the server-side v8 integration. Either way, both are async. The client is async because there's network in between. And the server-side v8 integration is async to schedule execution on a dedicated SQLite event loop. 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?
  - laurencerowe 2y ago
    Im referring to the server-side v8 integration. > 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.
bmacho 2y ago
That "JavaScript Performance" diagram with the vertical "CPU cores" axis is so weird. I am not sure if it is a mistake, it probably is.
- trailbase 2y ago
  You might be thinking JavaScript runs on a single threaded event loop? That's correct, however you can run N event loops in parallel (isolates in v8 lingo). `deno serve` even has a `--parallel` flag (https://docs.deno.com/runtime/reference/cli/serve/ https://docs.deno.com/runtime/reference/cli/serve/). 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.
  - bmacho 2y ago
    Oops, I was completely misinterpreting the chart, but I am cool now. I retract the previous comment, it looks okay.
oulipo 2y ago
So all the backend API is implemented using "ACL rules" in a list, and then directly on the client, like "client-side Firebase" right? 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?
- trailbase 2y ago
  IIUC, what you're asking for is what it is, i.e. TrailBase is like FireBase, as opposed to a FireBase running entirely on the client. We probably both agree that ACLs enforced by the client are no protection at all. Did I misunderstand? Was there something that thew you off? - always keen to improve
  - oulipo 2y ago
    No I meant, is it "just like Firebase (ACLs on the backend) but simpler"? I understand ACLs on the backend and API queries on the client, I just don't find it that practical to use
    - trailbase 2y ago
      Could you expand a bit on, how > 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?
      - oulipo 2y ago
        Yes, I prefer to have no direct access to the database from the client, just go through my API, and my API handles ACLs and do the SQL calls

73 points by trailbase 2y ago

If I’m reading this right, the insertion benchmark for TrailBase C# is hitting about 4.4k inserts of relatively small records per second per core. What’s the bottleneck at that point? That number seems small even when stacked up against much slower examples.

deleted 2y ago
[deleted]
trailbase 2y ago
The C# insertion benchmark yields roughly 100000k inserts per 5.7s, so it's more like 17.5k inserts per second. 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/benchmarks/trailbase_dotnet 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).

K0IN 2y ago

what am I missing at the first benchmark? why is there no comparison to pocket base in golang (the language pocket base is written in, and is allowing extensions to be written in)?

trailbase 2y ago
First and foremost, PocketBase doesn't have an official client in go. There are third-party clients in many languages (more than TB for sure) but at this point I'm not sure how much it makes sense to promote it. 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?

randomwebdev 2y ago

(Disclaimer: I'm PocketBase author) 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 https://github.com/pocketbase/benchmarks [1]: https://pocketbase.io/docs/go-overview/#custom-sqlite-driver https://pocketbase.io/docs/go-overview/#custom-sqlite-driver

trailbase 2y ago
- It is nice to see more backends utilizing SQLite. 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:
- trailbase 2y ago
  I'm happy to report that v0.25.0 already w/o CGO driver (fighting it right now) and GOOS=linux CGO_ENABLED=0 GOAMD64=v4 improved about 35% from 61.7s per 100k inserts to about 40s :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

Guillaume86 2y ago

Looks interesting, might play with it and the C# client soon, do you plan to build a LINQ provider eventually or keep the APIs similar across client languages?

trailbase 2y ago
I may plea the fifth, i.e. I'm not sure what this entails but would love to hear more. Also feel free to file a feature request. 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.
- bigiain 2y ago
  Pretty sure you'll want to consider usage by teams writing in all of Swift, Java, and Javascript as relatively common. 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.
  - trailbase 2y ago
    Absolutely. In terms of new clients targeting mobile teams Swift and Java/Kotlin would make the most sense. I mostly meant that even folks who code in Swift, Java and JS, would likely prefer the respective clients to be idiomatic than the APIs exactly matching each other.
- Guillaume86 2y ago
  I agree I would generally prefer clients to be idiomatic for the target language but in the case of LINQ providers I understand not doing them since they are quite involved to implement. You can probably leverage something like https://linq2db.github.io/ 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 :)

steve_adams_86 2y ago

I love the roadmap ideas for this. I'm a heavy user of pocketbase and I'm very happy with it, but I'd be more than happy to see this type of solution become more common and address needs like multi-tenancy. I'm also stoked to see the focus on performance (though pocketbase does excellent in this regard as it is)

trailbase 2y ago
Thanks! Happy to hear more ideas, if there's something you've always wanted :). PocketBase is great (and as I just learned, it has recently gotten even faster). One of my main goals was to expose more of the raw SQLite goodness.

laurencerowe 2y ago

Given the underlying SQLite calls are sync why is the query API async? From my own experiments with Node and SQLite I've found synchronous sqlite libraries like https://www.npmjs.com/package/better-sqlite3 https://www.npmjs.com/package/better-sqlite3 substantially faster, especially when making many simple queries (a pattern encouraged by SQLite.)

trailbase 2y ago
I'm not sure if you're referencing the client libs or the server-side v8 integration. Either way, both are async. The client is async because there's network in between. And the server-side v8 integration is async to schedule execution on a dedicated SQLite event loop. 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?
- laurencerowe 2y ago
  Im referring to the server-side v8 integration. > 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.

bmacho 2y ago

That "JavaScript Performance" diagram with the vertical "CPU cores" axis is so weird. I am not sure if it is a mistake, it probably is.

trailbase 2y ago
You might be thinking JavaScript runs on a single threaded event loop? That's correct, however you can run N event loops in parallel (isolates in v8 lingo). `deno serve` even has a `--parallel` flag (https://docs.deno.com/runtime/reference/cli/serve/ https://docs.deno.com/runtime/reference/cli/serve/). 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.
- bmacho 2y ago
  Oops, I was completely misinterpreting the chart, but I am cool now. I retract the previous comment, it looks okay.

oulipo 2y ago

So all the backend API is implemented using "ACL rules" in a list, and then directly on the client, like "client-side Firebase" right? 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?

trailbase 2y ago
IIUC, what you're asking for is what it is, i.e. TrailBase is like FireBase, as opposed to a FireBase running entirely on the client. We probably both agree that ACLs enforced by the client are no protection at all. Did I misunderstand? Was there something that thew you off? - always keen to improve
- oulipo 2y ago
  No I meant, is it "just like Firebase (ACLs on the backend) but simpler"? I understand ACLs on the backend and API queries on the client, I just don't find it that practical to use
  - trailbase 2y ago
    Could you expand a bit on, how > 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?
    - oulipo 2y ago
      Yes, I prefer to have no direct access to the database from the client, just go through my API, and my API handles ACLs and do the SQL calls