I love this story because it shows you really should read some docs about your storage layer before you jump in head first and just assume it’ll work forever and/or gracefully handle any load including overload. Postgres is the same, it’ll work until it won’t, in some case it’ll break in a way you can’t recover from without stopping production for a long time. (You can guess how I know - you’re right, I didn’t read the relevant part of the manual.)

Read your database’s manual, people! Even just going through the table of contents will put you in the top 20%.

Well, maybe the database should have more suitable defaults? Arguably, slowing down a bit once when the WAL grows beyond X MB to clean it up is better behavior than having it grow unboundedly, but maybe that's not possible.

There is no one-size-fits-all solution in the SQL world, and using SQLite in such a scenario is not a good idea. SQLite is an embedded database for embedded use. Even PostgreSQL is not universal, as many people assume. For use cases like analytics, there are many better-suited database engines.

Sure, but there are one-size-fits-95% solutions, and they're better than one-size-fits-90% solutions.

Absolutely agree. As most projects are small or medium in size, PostgreSQL is a good default choice. Problems arise when the problem does not fit the solution or tool. For example, using SQLite for high-volume transactions and then complaining that it doesn't work.

> you can’t recover from without stopping production for a long time.

Should that not be addressed at the level of Architectural design of your System? Everything fails all the time...bla bla....and all that?

If the system is small enough for a single postgres (which let's be honest 99% of them are) and short maintenance downtimes are easily tolerated by customers it's easy to forget about some failure modes and especially if you haven't been on the receiving end of such an incident. An experienced DBA would also notice the problem if only because they've been resolving a few of them in their professional past (hence 'experienced').

I've heard this is a potential issue but I've never encountered it. Do you know about the experimental WAL2 branch[0] that splits the WAL file into two to circumvent this problem? You'd have to compile SQLite yourself from the WAL2 branch to try it out, but it might be worth it at your scale.

Python is much slower than Go, so I don't think we could get 14k RPS as easily with Django, but I do have to see if I can reproduce the problem in Django. Topic for a future blog post!

Thanks for sharing this, I really appreciate knowing where the limits of SQLite are!

[0] https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

> I ran a stress test of 14k RPS

My built-in Devil's Advocate feels compelled to ask: do you have any reason to believe that your production site would get even a small fraction (single-digit percentage) of that traffic?

As a point of comparison, i have it on good authority that sqlite3's own sites typically get far less than 0.1% of that traffic. Two of those sites (sqlite.org/src and sqlite.org/forum) are database-driven applications.

Sure I don't expect my application to have 14k RPS all the time. However, some spikes can happen, and it can leave you with a large WAL file which doesn't shrink by itself by default. 14k RPS is the maximum my PC could pull off, I think it can happen with a more modest RPS. I ran the stress test with 12 parallel threads, so technically it can happen with just 12 RPS too, if your read or write transactions take a lot of time each (say, it has to read a large table, or you forgot to add indexes, or write transactions update many rows). In fact, a single open read transaction already can trigger checkpoint starvation. Also, it's not only about HTTP requests, my application is event-driven so a few event handlers processing events in the background can easily trigger it, too. A system can accidentally produce many events without having a lot of user requests (say, you forgot to batch events and generate N separate events for N items).

While this blog post was at the #2 spot on HN it was getting 0.138 rps, so yeah 14k RPS is A LOT!

That said it's still good to know when your tools will break! As far as I understand it's not really about RPS but more about constant write operations preventing the WAL file from being flushed. You can reproduce this issue with a lot fewer RPS.

I think there's a workaround if I understood the official documentation correctly: https://www.sqlite.org/wal.html#avoiding_excessively_large_w...

Yeah, when implementing this hack, I took the advice from this exact page:

>This scenario can be avoided by ensuring that there are "reader gaps": times when no processes are reading from the database and that checkpoints are attempted during those times

I create reader/writer gaps by calling sleep() (before opening transactions) and activate the checkpointer manually in the checkpointer goroutine.

From what I remember reading 1k rps is the recommended limit for sqlite right?

Not an issue for WAL2 mode, which mitigates this issue.

—-

> In wal2 mode, wal files do not grow indefinitely

https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

Isn't journal_size_limit is exactly for this? From the doc:

"Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit."

From what I understand, you still need the experimental WAL2 mode[0] to not have this problem.

[0] https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

If there's checkpoint starvation, the WAL file cannot be reset, and it will still grow indefinitely even with this setting.

I would argue that if you have this problem, SQLite is not a good database for your use-case.

maybe this could help: https://turso.tech/blog/something-you-probably-want-to-know-...