[eterm]

SQLite definitely seems like a poor choice for dealing with many concurrent requests.

Maybe it's improved since I last used it, but to my knowledge SQLite essentially forces all writes to be serialised, at risk of data corruption otherwise.

There are tricks for improving the performance such as WALs, but that is merely a performance boost rather than genuine concurrency with things like row-level locks that you might find in other databases.

I guess if the whole thing is architected with a write-through cache that handles concurrent writes and deals with serialising all the writes, then it can be a single writer streaming changes through to the database, but then you still have a point of serialisation, it just will manifest itself slightly differently.

And SQLite is something that will give you constraints you will always have to consider.

Whereas running mariadb or postgres on the same machine would deliver similar benefits without a risky migration.

If your DB is small enough to run as a SQLite database, then it probably ought to have never been on a different machine in the first place.

There is a very happy medium between SQLite and a database on a different machine, one I am continually surprised to see people ignore.

[wild_egg]

> SQLite definitely seems like a poor choice for dealing with many concurrent requests

Can you qualify "many"? SQLite easily handles 100k+ writes per second and it's not hard to have app layer code serialize and batch writes to take advantage of that speed. Concurrent writes require a ton of overhead and your system and code can be quite a lot faster and simpler if you just skip the idea of them altogether.

[inigyou]

SQLite serialises all writes - only one can proceed at a time and others must wait until that one is finished

[victorbjorklund]

Writes are fast unless you are doing some complex transaction stuff.

[inigyou]

Each one includes a few fsyncs

[torginus]

Can't you do infinity read replicas? I don't think the write load on lobster.rs is severe - maybe 1 comment/sec, with an acceptable replication delay of 60 sec?

If you can do that, you can have infinity instances.

[eterm]

With WAL, you don't need read replicas, you can have lots of readers and they don't get blocked by writers.

As an aside, on replication, eventual consistency is not a panacea:

If I make this comment, it's absolutely fine if you don't see it for a minute.

If I make this comment, it's absolutely broken if I then don't see it on my subsequent requests. I'll think the site has broken, and try to resubmit my comment.

You can relatively easily work around that one by pinning people to instances, but that's still yet another thing to consider, and those kinds of considerations add up when you're dealing with distributed systems.

However, this a single instance, it's way too early to talk about replication.

Given that just hitting their front-page is taking 6+ seconds, they've got a performance problem that needs fixing, and my hunch is that they have some kind of "last accessed" database entry, which turns every request into a write.

[alfiedotwtf]

Depends on the architecture..

They could have one SQLite instance per user and then have a single sweeper that goes through all last writes and then replicates it to the main instance - eventual consistency fanned out across files

[eterm]

So now you're introducing even more processes, even more latency and complexity, and for what benefit?

Eventual consistency on users would make it difficult to do things like "WHERE !user.is_banned" when getting the stories, so you need to keep your users database tightly synchronised. Sure, you could pro-actively delete or mark comments when deleting users, but now you're risking having the ban itself fail, and have also now added a much longer write operation, as you have to mark all those comments deleted. And long running writes is the one thing you desperately need to avoid in SQLite.

And why go to all that effort when you could avoid all that effort by running a database which allows concurrent writes on the machine local to the web application?

All the benefits of machine-local latency, and all the benefits of concurrent writers and transaction isolation.

[veqq]

edit: misread requests as writes

[eterm]

I wouldn't expect that problem to be solved at the database layer, that's what http caches are for, then application level in-memory caches, then finally if neither of those hit, go to the database.

For example, one of the biggest optimisations that Hacker news does is that it serves logged out users from a cached copy of the front-page.

Logged out users don't care/notice about comment counts, they don't notice that it doesn't update as often, they can't be hiding articles so you can serve the same front-page to the millions of anonymous users and bots, and update that cached copy once and on a slower cadence than every request.