3 ms·

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are

Small aside based on some comments here - People frequently bring up write concurrency issues in SQLite, with the implied idea that if two physical people are using your app at once they'll get errors. But of course it's concurrency at a transaction level, vastly different - if your writes take 1ms (in SQLite they might even be faster), you can support 1,000 writes per second. And if your users are generating a write click every 5 seconds (a very engaged user base, in most apps more users are readers) you can support 5,000 simultaneous physical people before you need to start worrying about scaling, replication, and distributed shenanigans. If only 5% of those users are writers and the rest are readers / lurkers, you can support 100,000 users simultaneously. I suspect close to 99% of distributed app architectures are premature optimizations for a scale the company will never reach.

IgorPartola 2y ago
The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a network front end on it or setting up an API server in front of it that everything else uses. But at that point you have basically abstracted away SQLite enough that just using Postgres is easier. And with Postgres you get a lot more features that go hand in hand with multiple servers: replication, failover, etc. I am a huge fan of SQLite but its best use case is a local database for a local application. You can use it for a client-server setup in some special circumstances but in my mind you’d need a compelling reason to do that rather than using the standard solution of something like Postgres.
- SeenNotHeard 2y ago
  Exactly. As Dr. Hipp writes on the web site, "SQLite does not compete with client/server databases. SQLite competes with fopen()."
- deleted 2y ago
  [deleted]
- infogulch 2y ago
  Sure, but is this host incapable of performing 1k req/s? It's a crazy world if we have a DBMS that can parse a query, execute reads and writes against a database file, and return the results faster than it can be string concatenated into an html response.
  - IgorPartola 2y ago
    That’s not the point. And the 1k req/s is a made up number in this case. The point is that SQLite is a single process or at best a single process group if you implement locking. It’s not about performance. I wouldn’t be surprised if SQLite is faster for writes than Postgres, being simpler. The point is that one is a really good bicycle and another is a really good all terrain truck. Both have uses and for a certain type of getting from A to B there is overlap in their utility but they have different use cases.
- QuadrupleA 2y ago
  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

The issue isn’t quite just that. If you are running your app and database on one single server and don’t need to move past that, SQLite is a great solution. But let’s say you have a web server and a worker server or more than one of each. Now you can’t access SQLite from multiple hosts as it doesn’t have a network access model. So your architecture is somewhat limited. You can still utilize it by either putting a network front end on it or setting up an API server in front of it that everything else uses. But at that point you have basically abstracted away SQLite enough that just using Postgres is easier. And with Postgres you get a lot more features that go hand in hand with multiple servers: replication, failover, etc. I am a huge fan of SQLite but its best use case is a local database for a local application. You can use it for a client-server setup in some special circumstances but in my mind you’d need a compelling reason to do that rather than using the standard solution of something like Postgres.

SeenNotHeard 2y ago
Exactly. As Dr. Hipp writes on the web site, "SQLite does not compete with client/server databases. SQLite competes with fopen()."
deleted 2y ago
[deleted]
infogulch 2y ago
Sure, but is this host incapable of performing 1k req/s? It's a crazy world if we have a DBMS that can parse a query, execute reads and writes against a database file, and return the results faster than it can be string concatenated into an html response.
- IgorPartola 2y ago
  That’s not the point. And the 1k req/s is a made up number in this case. The point is that SQLite is a single process or at best a single process group if you implement locking. It’s not about performance. I wouldn’t be surprised if SQLite is faster for writes than Postgres, being simpler. The point is that one is a really good bicycle and another is a really good all terrain truck. Both have uses and for a certain type of getting from A to B there is overlap in their utility but they have different use cases.
QuadrupleA 2y ago
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.