[icemelt8]

Beware of pocketbase! I am running my startup wetarseel.ai. You'll be badly locked into one instance with one sqlite file, plus its queries are not mapping to SQL, try a bulk delete and it will choke your entire system, plus other footguns.

[coolgoose]

I am so confused about this message

Sounds like an sqllite performance tuning issue than anything else.

[trailbase]

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.

[icemelt8]

check the benchmarks here https://trailbase.io/reference/benchmarks/ its clear thats a lot of overhead on top of sqlite

[Yokohiii]

https://github.com/pocketbase/pocketbase/blob/master/tools/s...

I wouldn't be surprised if that's the performance issue. It seems to be used as in memory cache for all (?) collections and uses a mutex to access them, even on reads. Most (properly set up) databases are pretty good keeping things cached and with a local socket the latency is low. With this setup I'd be very careful to do my own general purpose caching. The solution here is likely very sub optimal.

[icemelt8]

Thanks this is very helpful, can you please look at the code more, specially around inserts and reads why its so slow?

I am a lowly frontend developer who got into the SQLite hype due to twitter and DHH from Rails, didn't know how bad SQLite was.

[Yokohiii]

I've just started with go a few weeks ago, so take my info with a grain of salt.

Pocketbase uses a mutex for all the data reads and writes. That means that every write will block any readers for the write duration. If you do a lot of writes this will become quite inefficient quickly. You need some kinds of locking to write data, but if badly implemented it will pretty much roll back all concurrency advantages, you pile up goroutines that just wait to do something.

A good database does go very sophisticated steps to minimize locking, essentially you should expect from a modern database that most reads are lock free. Still it helps to understand how they coordinate locking and concurrency to optimize your applications. I.e. usually you design reads to be ultra fast and do writes in healthy bulk sizes which is also still fast but will net out to have minimum lock time. Slow reads should be done read only replicas or at times there isn't any load. So sqlite isn't slow/bad at all, if you use it correct, like any other decent database.

Below the sqlite WAL mode is explained. It is magnitudes more sophisticated then what pocketbase does. Pocketbase literally negates all that by their own locking strategy. It would likely be more efficient if the just remove their cache layer and fine tune sqlite for their use case.

https://sqlite.org/wal.html

Also if you require a very high amount of writes, that is realtime writes, as opposed to writes that can be delayed, you pretty much enter tough terrain. You need to make very deliberate choices and pay a lot of attention to system design depending on your requirements. So if something falls over from a high amount of writes, it because it's hard.

[trailbase]

(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.

[cweagans]

This has not been my experience. I've definitely done bulk deletes and things have been fine. Are you sure the problem is pocketbase and/or sqlite?