Yes, the sqlite concurrency model is a bad choice if you have a high degree of concurrent writes. However for many applications that simply isn't true. When it comes to websites i think people significantly overestimate the amount of concurrent writes.
We're all using WAL mode these days, right?
The only difference between SQLite and Postgres write locking is the granularity.
But it’s talked about as if it’s a categorical limitation, the app will fail if there is concurrency. But it’s just a question of how much time will be spent locking.
A website with a 16 process pool for handling requests will be fine.
Even if you don’t use message passing to use one thread to perform all updates/writes, it still performs very solidly for many use cases.
If you are sharing your database between processes or machines, you are probably doing "the fancy new SQLite thing" wrong.
If you need to share information contained in a SQLite database with other processes or machines, you can write application-level code to handle this concern.
For example, LAMP stack applications could swap the M for SQlite (and I think it would have been better historically if they did).
I think it is a bit unfair to argue against SQLite on performance grounds but then not explore ways to utilize instances of it within a single process scope where it is most effective.
Sharing a single SQLite connection across all threads within a process is where you get the best performance outcomes. Everything is serialized within the SQLite provider - assuming you are using a mainstream build. So, if your storage subsystem is fast (NVMe & friends), you will achieve very good outcomes. Any utilization model that requires a file open/close operation every time you want to touch the data is a complete non-starter by comparison. The "unit of work" paradigm that hosted providers recommend is catastrophic for performance with SQLite.
One option for the "I absolutely must share this SQLite instance with 30+ services" scenario is to simply wrap it with a REST API or similar.
You mean the one where it locks on write? It’s totally fine, if you wrote any cross process code yourself it’s probably going to do similar locking.
The only point I’m disagreeing about is the blanket statement “your SQLite website won’t work if it has concurrent writers”. It will.
https://www.sqlite.org/lockingv3.html
In the single process access model, you can connect exactly once and remain in a reserved lock state the entire time.
There are advantages and disadvantages to using WAL instead of a rollback journal. Advantages include:
WAL is significantly faster in most scenarios.
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
Disk I/O operations tends to be more sequential using WAL.
WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.*To me the concept of SQLite in these scenarios, without the WAL, is just nuts.