Switching DBs is very invasive. Switching your application base even more so. As long as you're happy happy with PocketBase and there's nothing you're critically missing, the answer will almost certainly be: no. Is there anything you're looking for?
(Similar disclaimer: I'm not an expert on PocketBase implementation details)
I don't think the referenced `Store` is used in the SQLite access path and PocketBase uses SQLite in WAL mode. Besides, in cases where the `Store` is used, it uses a read/write lock to allow for concurrent reads.
I'm guess but it probably depends on who's being choked here. If it's the caller, then it may be the overhead of individual deletions for each record when using the record APIs. If other users are being choked, it may refer to locking.
Naively, I would expect SQLite to be able to delete tens-of-thousands (or even hundreds) of records per seconds, since it's simply appending deletions to the WAL.
Self-hosted Supabase is pretty good. I don't think anyone argues with that. It didn't used to be as smooth and it's certainly hungrier with many more moving parts.
Could you elaborate a bit more on your scaling concerns? You can certainly have lock-congestion with SQLite. That, said Postgres - while awesome - isn't the most horizontally scalable beast.
> I guess I just have to accept that the term has lost it's meaning at this point and can be used for whatever whoever wants to use it for
It's maybe more like you point out: realtime in the OS context vs realtime in an event processing context. The latter is certainly not defined as strictly and often just means push-based. It has been a popular moniker, e.g. in kafka-land, for a while. I'm not sure it intrinsically takes away from the OS context - it doesn't need to be a deep dish pizza situation.
If you're comparing in-process SQLite to talking to SQLite over HTTP you'll probably get a small penalty for any language. When co-located on the same machine, you can probably expect something like ~5ms for JS (just for the event-loop to do the IO and getting back to you).
However, if you have multiple processes reading and writing from the same DB it may actually aid latency due to congestion.
I ran some benchmarks (TrailBase author here and big fan of PocketBase): https://trailbase.io/reference/benchmarks#insertion-benchmar..., where you can see, e.g. the single-process drizzle (i.e. JS with in-process SQLite) performance vs over-HTTP for TrailBase. PocketBase should be similar when not fully loaded. There's also some concrete latency percentile numbers when at full-tilt: https://trailbase.io/reference/benchmarks#read-and-write-lat.... On my machine you can expect p50 to be around 15-20ms.
Love seeing the love for SQLite. Personally, I've had a great experience with litestream, which will continuously replicate any changes. Are you using https://sqlite.org/rsync.html?
You won't get any argument here. PocketBase's is very polished and friendly. I fell back onto a popular pre-existing UI component system called shadcn (which does look a bit like bootstrap), not only because you gotta start somewhere but also because I'm not the caliber of UX designer Gani is :bow:. If you have the time, I would appreciate any feedback on how to improve.
I'm a bit surprised on the mobile comment, since last I checked PB's UI wasn't responsive, i.e. you had to scroll a lot horizontally on mobile. Despite it's missing polish, I tried to make TB's at least work well on mobile. Could you elaborate? - thanks
Hi! Depends on what you mean by "any JS". Many JS ecosystems depend on an environment. For example, there are browser environments where you get a common baseline with some vendor differences, there's the server where you get common baseline across nodejs, deno, bun with some differences and also proprietary APIs.
Long story short, any vanilla JS (ES5,ES6, probably even common) should be able to run. There's some standard WASI APIs to do I/O through the WASM runtime and a few TB specific APIs.
Got it. Sounds like you're in the market for a SQL database. In your setup, is there anything extra you'd want? I guess I'm merely wondering what FireBase and Co could even provide to you?
> 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 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.
> 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 it 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 :)
> 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?
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
Back from the dead. I also now vaguely remember that there was some hiatus (probably between v0.22 and v0.23) where you took some time to rewrite the DB layer to enable custom DB drivers.
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!
The raw query API is an admin-only API at least for now, since SQLite doesn't have a built-in concept of row-level security. From a client's perspective it's only RESTful APIs at the moment.
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.