The paragraph I quoted is talking about two things, one right after the other: concurrency and acidity.
First, it talks about concurrency. It says that a naive solution to concurrency is file locking. It says this is slow. So I first trot out SQLite as an example that is not slow.
It says, "Not only are these workarounds slow, but they won't scale up to allow us to make our database fully ACID-compliant." To me it sounded like he said choosing file-locking for concurrency will mop you into a corner, that you won't ever become ACID, regardless of what you tack on, like a journal.
One other clarification about concurrency: SQLite says it's fast enough for most websites. Then it turns around and says that if you need a lot of concurrency, you should go with another database. The contradiction evaporated when it dawned on me that when database makers say "concurrency," they're talking about many users at a time connecting to a database directly: picture a bunch of SQL gurus connecting directly to an SQLite file and issuing raw SQL statements. Or replace the gurus with several "application" servers that all write to a database on yet another server. For that, SQLite would be bad, we all agree, including SQLite.
But that's not what most of us, the 99% of app developers, are thinking of when we hear "concurrency." We're thinking of several people hitting our website. In that case, SQLite would still hold up (https://sqlite.org/whentouse.html, see Server-side database).
Anyway, again, I'm not against Postgres. It's what I use. I'm not talking about SQLite because I'm invested in it but because I admire it. It's just an example of a simple solution that meets many people's needs, without resorting to more complexity.
> But that's not what most of us, the 99% of app developers, are thinking of when we hear "concurrency." We're thinking of several people hitting our website. In that case, SQLite would still hold up (https://sqlite.org/whentouse.html, see Server-side database).
It doesn’t. At all.
The quassel project supports PostgreSQL and SQLite as backends.
And, as expected when you use a database as log storage for an IRC bouncer, you end up with many concurrent threads writing and reading to the same table.
The result is that if a user is reading a table right now, no one can write to it - and that, in turn, means that the thread receiving messages (and responding to PINGs, which is required to keep the connection to the IRC server alive!) gets blocked, and has to wait on the threads currently requesting messages.
So if you connect and set your client to, on connection to the bouncer, request the last 500 messages for each channel, you might actually time out. Yes. When you have enough data, then connecting to the bouncer can kill the bouncers other connections.
If you have more than one thread doing writes or reads – in a usual web application you’ll have dozens or hundreds of worker threads reading and writing concurrently – then SQLite just isn’t useful.
Disclaimer: Not speaking for the quassel project here. This comment is just my opinion.
> > 99% [...] SQLite would still hold up
> an IRC bouncer
I would consider a chat client one of the 1%. Although there are many chat clients, and other examples that we could think of, there are thousands of websites and apps of the "small to medium" size that SQLite might be a good fit for.
> I would consider a chat client one of the 1%. Although there are many chat clients, and other examples that we could think of, there are thousands of websites and apps of the "small to medium" size that SQLite might be a good fit for.
The issue with those is still that if you have a too high writing load, SQLite will become a bad fit. It’s perfect for any low writing, high read situations, as many websites are, but for more dynamic applications it seems to be less usable.
Also, the FAQ of SQLite still lists
> SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
I admire SQLite as well. It's a fantastic piece of software with many use cases.
You're correct that there are some mostly-ready-only situations where a file-lock isn't a problem (perhaps a small CMS, blog or similar).
But using coarse grained locking often does mop you into a corner when it comes to concurrency. Just look at MongoDB (older versions had a global write lock), Python (GIL), etc.. The sibling post of this comment gives a real-world SQLite example. Anyway, you're right that coarse grained locking has nothing to do with atomicity itself. I may have misread your first comment, in retrospect you may have meant to say exactly that.
> in retrospect you may have meant to say exactly that
Yes, but in retrospect I can see that maybe I should have more clearly separated the two questions. Thank you for the video. I watched it. I didn't know that SQLite looked so often at Postgres. But other parts of it were vaguely familiar. Maybe I saw it a long time ago and forgot about it.
> there are some mostly-ready-only situations where a file-lock
> isn't a problem (perhaps a small CMS, blog or similar)
I haven't used SQLite much, but it promises greater size and speed than most people would guess. As you can see from its website and the video you shared, D. Richard Hipp keeps tabs on where it's being used. "Developers report that SQLite is often faster than a client/server SQL database engine [when used as the database back-end on an application server]" (https://www.sqlite.org/whentouse.html) --- which is exactly what most people use a database for.
> The sibling post of this comment gives a real-world SQLite example
--- of an application that I wouldn't use SQLite for. His mention of the writer blocking readers suggests that he wasn't using it in WAL mode. I had thought that the rollback journal and WAL were two names for the same thing. But SQLite didn't come out with the WAL until 2010. As it explains on its website, the rollback journal and WAL are alike, just backwards versions of each other. In rollback-journal mode (still the default) SQLite writes changes directly to its original database file but first backs up a snapshot of the data to the journal. With WAL mode turned on, the database first writes to the WAL, which eventually writes back to the original database (https://www.sqlite.org/wal.html).
So it's not really all that different from PostgreSQL in that regard and the authors point remains valid :).
You might also enjoy this talk by the main author of SQLite: https://www.youtube.com/watch?v=ZvmMzI0X7fE (SQLite: Protégé of PostgreSQL)