Hacker News Reader

Comment by QuadrupleA | Hacker News Reader

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.

View on HN

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.

Exactly. As Dr. Hipp writes on the web site, "SQLite does not compete with client/server databases. SQLite competes with fopen()."

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.

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.

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.

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.

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.

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.

> 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

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.

Reply on news.ycombinator.com