anze3db
Joined 2 years ago
Karma
61
Submissions
37
Member since
Dec 13
Recent submissions
Comment · 2 years ago
Great point. I thought that `vacuum into` blocks writes, but it's only `vacuum` that blocks! I've updated the blog post, thanks!
Comment · 2 years ago
I'm in a few Discord servers, but I have no idea how to find you there :)
Comment · 2 years ago
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!
Comment · 2 years ago
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!
Comment · 2 years ago
Stephen has a whole series of blog posts on SQLite (in Rails) that I highly recommend. I've learned most of what I know about SQLite from him!
https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t...
Also, hello Lee! I miss being in a Slack with you!
Comment · 2 years ago
From what I understand, you still need the experimental WAL2 mode[0] to not have this problem.
Comment · 2 years ago
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.
Comment · 2 years ago
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?
Comment · 2 years ago
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!
Comment · 2 years ago
I can confirm. My experience contributing has been very positive!
Comment · 2 years ago
That's rough. Is your code using transactions? If so, making sure you use `begin immediate` will help!
Comment · 2 years ago
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!
Comment · 2 years ago
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!
Comment · 2 years ago
This joke makes some good points.