I agree 100% with everything you wrote. It was very surprising to me that every transaction and every write operation blocks the whole database and not only the table it's performed upon (it makes sense since it's all a single file, but still).
The only workaround for this is splitting your main database into multiple databases, but this bleeds into your application logic and gets messy quickly. If you are in this position, it's best to switch to Postgres as you did!
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.
From what I know, the journal_size_limit PRAGMA still affects WAL mode, but it doesn't solve the issue of the WAL file potentially growing uncontrollably. Am I missing something?
Indeed on rereading my question I see it was not phrased correctly.
Yes, the `journal_size_limit` affects the maximum journal/wal file that remains on disk if larger than that — and by the way ensures that these files are not deleted once created.
Your setting it to ~25MiB while the default `wal_autocheckpoint` PRAGMA is set to 1000 pages (with the typical page size of 4KiB that means after ~4MiB the WAL file contents will get moved to the main database file if no other transaction is active) is what confused me. 25MiB seems very specific for a file size to keep in the occasion that the WAL file keeps growing beyond 4MiB. Perhaps you also meant to tinker with the `wal_autocheckpoint` PRAGMA but didn't?
>Journal size limit is measured in bytes and only applies to an empty journal file.
>The WAL file will grow without bounds until a checkpoint takes place that reaches the very end of the WAL file. Usually, a checkpoint is performed when a commit causes the WAL file to be longer than 1000 pages (PAGES, not bytes). There are conditions when running a checkpoint to completion is not possible, like disabled checkpointing, checkpoint starvation because of open read transactions and large write transactions.
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!
Hey HN! I'm the author of the blog post. I didn't mention this in the post, but I'll try to merge some of these settings into Django by making them the new default or the default for new projects created with the start project command.
Any feedback on all of this is greatly appreciated!
When you say " I'll try to merge some of these settings into Django by making them the new default or the default for new projects created with the start project command", you mean you are a Django core developer and you plan to make them as part of the Django project?
If you are, YES PLEASE! :D I would love to have those settings as my default configuration, because the majority of my projects are tiny to small to between-small-and-medium size, therefore SQLite is more than enough for me.
I'm not a Django core dev, but I have managed to get my changes merged into Django already (the transaction_mode setting in 5.1 was my contribution).
Carlton does seem to be onboard with my idea[0], so I'm optimistic that we can make it happen. Comments like yours will help me make my case so thank you for that!
Django is a critical project, and I am sure your contributions are of high quality. But making it too easy to get new contributions in, somehow raises other concerns around the project governance.
Don't get me wrong, the PR review is still rigorous and can take weeks. But everybody I interacted with during the process was very supportive and helpful, so the overall experience was great!
You've missed: pdm, uv, pip-tools, pipx, rye, and probably some others.
Only pdm and poetry generate cross-platform lock files by default as far as I know, but there are a lot of people trying to solve this problem right now.
It's not an easy problem to solve. Python's package management predates package managers from most other programming languages and Python itself predates Linux. There is a lot of baggage so change is very slow.
Shameless plug: don't forget the wonderful pip-chill for simplifying gigantic requirements files (and for stripping out version numbers to make canaries easier to do).
