If you have a server with 100 cores to serve 100 connections simultaneously - and really need this setup -, you should probably be using Postgres or smth else.
It's a made up example to clarify whether I understand potential congestion scenarios and limitations correctly, not my actual situation.
If I had a server with 100 cores to serve 100 connections, but each query took only 5ms, SQLite might be totally viable. There's no blanket solution.
Edit: More importantly, SQLite async limitations come into play when I have only 12 cores but 100 incoming connections, and on top of querying data from SQLite, I do have other CPU bound work to do with the results. If I had 100 cores, 100 connections to the database would be no problem at all since each core could hold a connection and block without problem.
If synchronous IO is blocking your CPU bound application code, this won't help you. My made up example was not about concurrent writes, and the concurrent reads I mentioned were not my main point. For all I care, you could have 100 different databases or even normal files in this scenario and you read them.
I was wondering how the async wrappers around SQLite work when SQLite itself only has synchronous IO. At least for the Rust example by Op, the async part is only used when awaiting a queue, but the IO itself still has the potential of blocking all your application code while idling.
How did you come to that conclusion? No, the synchronous IO is not blocking the application because the committer that actually does the writing to disk lives in an external process.
This implementayion turns synchronous IO to 100% async while still maintaining the chatty transaction api and the illusion of serail execution on the client side
Especially when using a modern storage medium, which most servers nowadays use, I doubt that filesystem I/O will be a bottleneck for the vast majority of use cases.
I/O is extremely fast and will be negligible compared to other stuff going on to serve those requests, even running queries themselves.
The CPU work done by SQLite will vastly outshine the time it takes to read/write to disk.
It might be a bottleneck to reading if you have a very large database file, though.
If I had a server with 100 cores to serve 100 connections, but each query took only 5ms, SQLite might be totally viable. There's no blanket solution.
Edit: More importantly, SQLite async limitations come into play when I have only 12 cores but 100 incoming connections, and on top of querying data from SQLite, I do have other CPU bound work to do with the results. If I had 100 cores, 100 connections to the database would be no problem at all since each core could hold a connection and block without problem.