Hacker News Reader

Comment by simonw | Hacker News Reader

The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point.

But... actual guidance as to how to use it there is still pretty thin on the ground!

Short version: use WAL mode (which is not the default). Only send writes from a single process (maybe via a queue). Run your own load tests before you go live. Don't use it if you're going to want to horizontally scale to handle more than 1,000 requests/second or so (though vertically scaling will probably work really well).

I'd love to see more useful written material about this. I hope to provide more myself at some point.

Here are some notes I wrote a few months ago: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/

View on HN

> Only send writes from a single process

depending on your use case, using multiple writer processes can be fine, e.g. multiple writer processes storing periodic metrics (once per minute) into a single sqlite database. the occasional "database is locked" error (it does happen a few times a day) is handled by simply returning and trying again at the next interval. that's fine for this particular use case - the metrics for the last interval are just delayed a bit.

One thing that confused me initially with sqlite - if you want it to wait for writing locks to be free instead of erroring on write contention, you have to start write transactions with BEGIN IMMEDIATE. Since if you start with a read transaction that gets upgraded to write in the middle of it, you can't really wait for locks because you cant have a consistent snapshot of an old version of the DB that is read/write.

yes, we use BEGIN IMMEDIATE with a 2 second busy timeout. that has a worst case wait time of 2 seconds, but the 95th percentile wait time is around 100 to 1000 microseconds and the 99th percentile is around 1 to 10 milliseconds. for a high throughput and/or low latency service, a worst case wait time of 2 seconds might be unacceptable, but it's fine in our case.

> The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point

I think it's still very relevant. I mean you have service XYZ replicated two times behind a load balancer, how do you use SQLite out of the box? You can't, you have to use one of those service that will proxy / queue queries, so then why even trying to use SQLite.

The fact that it's just a file on disk make it a bad solution for a lot of simple use cases.

You could partition your data to 2 parts, and direct your users to whichever service serves their partition of that data. Every database turns out to be just a set of files on disk, but for using SQLite you have to think differently than most databases due to the extreme locality of the data.

You only need a pool of servers behind a load balancer for extreme reliability or for scaling out.

Most small to medium web applications can handle:

- Only being reliable for 99.99% of the time (using a single cloud VM where the cloud provides the hardware level failover).

- Scaling up instead of out (adding CPU, RAM and faster disk).

Sure, if that's a requirement then it's not a great fit. But it might not be a requirement.

If you're worried about high availability should your instance fail (which is something that's worth worrying about, though many smaller web apps will likely work perfectly fine on a single instance with good scheduled backups) you should absolutely investigate https://litestream.io - it's the missing piece of the SQLite-for-web-apps story.

Did any one tried https://github.com/rqlite/rqlite How well it can handle horizontal scalability. With added distributed features, it is not good as any other RDBMS (postgres or mysql)

rqlite[1] author here, happy to answer any questions.

rqlite offers horizontal and vertical real scalability, but only vertical for writes. See https://rqlite.io/docs/guides/performance/

[1] https://rqlite.io/

Reply on news.ycombinator.com