What are the reasons to continue using SQLite over MySQL/MariaDB when you start to require distributed architectures? Wouldn't it be better to switch at that point? Assuming that being able to read from a database on the same filesystem as the application doesn't provide any tangible benefits for 99.99% of applications that don't have such low latency requirements?
There's an operational argument to make, but fundamentally it's about performance, and secondarily about what having an ultra-fast local database does to your performance budget and thus how you build things. The premise is that in a typical web application, each request incurs multiple round trips to the database, and those round trips add up to eat a chunk of your budget. A database like SQLite can essentially eliminate that round-trip cost.
It's not just about making snappier applications; it's also that even ordinary apps burn engineering time (for most shops, the most expensive resource) on minimizing those database round trips --- it's why so much ink has been spilt about hunting and eliminating N+1 query patterns, for instance, which is work you more or less don't have to think about with SQLite.
This premise doesn't hold for all applications, or maybe even most apps! But there is a big class of read-heavy applications where it's a natural fit.
You appropriately answered the question that was asked. I would add a little extension that this question (about SQLite specifically) was asked in the context of a discussion about "Distributed SQLite". The considerations around round-trip costs for "Distributed SQLite" are very different than for SQLite.
SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.
SQLite works just fine for website backends. Writes are serialized, first from replicas to the central write leader (like in Postgres), and then with the WAL and transaction isolation.
The SQLite documentation itself mentions write concurrency as a limitation.
> Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
It goes on to say that other databases provide more concurrency.
> However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
Yes: sqlite3 only allows a single open write transaction per database file. Whether this matters or not depends on your application. For a large class of applications, this isn't a real issue at all. By way of example: until a few months ago, we were concurrently merging state updates from tens of thousands of VMs across our global worker fleet into a single SQLite database. As has been said across this thread, SQLite works well as a backend for read-heavy applications. A great many CRUD apps are read-heavy!
If you want more concurrency, you can break your schema up into multiple .db files. This sounds onerous but SQLite makes it very easy to do.
That's great it worked for you, but that's not a website backend. It would be foolish to run HN on SQLite for instance. I would go as far as to call it a vulnerability in such context, due to how easily it could be DoS'd.
Maybe you don't understand SQLite's locking issues? Having a bunch of different files that can be written to concurrently sounds like a better solution than SQLite for a social network backend.
