4 ms·

Agreed, if you have to have a web server and separate worker servers, then Postgres is likely better than a weird network layer on top of SQLite. But I'd questi

Agreed, if you have to have a web server and separate worker servers, then Postgres is likely better than a weird network layer on top of SQLite. But I'd question the true need for need separate worker servers. Is it for performance or functionality, or just hand-wavey stuff like "best practices" or "modern" or "scalability" without any hard numbers attached? If a single server can handle 100,000 simultaneous users, that's quite "scalable". I guess my point is, a single cloud VM with the (web) app server and a SQLite database will take you VERY far these days in terms of performance and concurrent users. The web server becomes your client-server layer, which can possibly eliminate the need for a separate client-server interface on the database end. Of course each app is different, but it's worth taking a hard look at whether you need that additional server & network connection (i.e. a bunch of app servers sharing the same database, that can't be done with an API). It's good to delete any part or process that you can. Cloud vendor developer-marketing and resume-driven-development has pushed the industry into ever more complicated distributed infrastructures, but I suspect the needs of 99% of businesses could be handled much more simply, with vertical scaling if needed (larger VM class) before diving into kubernetes, clustering, load balancing, lambda, microservices, replication, etc.

IgorPartola 2y ago
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.
- jeremyvisser 2y ago
  > 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. Excuse me? The only way you would be able to come to this conclusion is if you did no reading whatsoever and never tried it. This “confidently wrong” attitude really needs to stop.
  - IgorPartola 2y ago
    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.
- sgarland 2y ago
  > 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. [0]: https://www.sqlite.org/faq.html https://www.sqlite.org/faq.html
  - IgorPartola 2y ago
    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.

IgorPartola 2y ago

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.

jeremyvisser 2y ago
> 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. Excuse me? The only way you would be able to come to this conclusion is if you did no reading whatsoever and never tried it. This “confidently wrong” attitude really needs to stop.
- IgorPartola 2y ago
  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.
sgarland 2y ago
> 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. [0]: https://www.sqlite.org/faq.html https://www.sqlite.org/faq.html
- IgorPartola 2y ago
  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.