Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file.
And it’s not that it’s best practice. It is because sometimes you legitimately do need separate processes. Imagine you have a web UI for a service that transcodes video. You aren’t running transcodes on the same CPU cores as your web requests. And chances are you need a lot more power for the worker side than the web side.
For toy projects you can get away with almost any setup. For a project where people are paying money for a service you really need to start engineering your infrastructure because you have actual trade offs.
> Even if you are running it all on the same machine, a separate worker queue process and a web server process cannot share an SQLite database file.
From SQLite’s FAQ page, yes they can [0]. Two processes cannot simultaneously write to it, but they can both have it open, and read. With a modicum of tuning and application error handling, you can quite easily have multiple processes writing.
> …you really need to start engineering your infrastructure because you have actual trade offs.
Step one is fully understanding the abilities and limitations of available tooling.
Correct. You cannot have concurrent writers even to two independent tables. That page also clearly states the limitations of even this global lock system. I should have been more precise with my language in that while it’s possible it functionally can get to being useless pretty quickly.
SQLite is a wonderful database engine. But it isn’t the end all be all and no it doesn’t scale in the same way as something like Postgres. You cannot stretch it but you hit diminishing returns fairly quickly.
If you read the relevant docs you will see how this is implemented and maybe realize that locking your entire database for a write transaction isn’t something that works for a whole lot of cases. Of course multiple readers are allowed, but multiple writers even to different tables still contend for the same lock (that doesn’t work on all file systems). There isn’t table-level locking, let alone row level locking.
And it’s not that it’s best practice. It is because sometimes you legitimately do need separate processes. Imagine you have a web UI for a service that transcodes video. You aren’t running transcodes on the same CPU cores as your web requests. And chances are you need a lot more power for the worker side than the web side.
For toy projects you can get away with almost any setup. For a project where people are paying money for a service you really need to start engineering your infrastructure because you have actual trade offs.