Comment by rich_sasha
Comment by rich_sasha 7 hours ago
I was pushing SQLite quite hard. My DB was at peak 25GB or so. Occasional queries of O(1e6) rows while simultaneously inserting etc. Many readers and a few writers too. Id expect some degradation, sure, but Id say it wasn't very graceful.
I think, however, I was well within the parameters that SQLite maximalists would describe as within th envelope of heavy but fine usage. YMMV.
I found a very small number of people online with the exact same issues. Enough to know I'm not hallucinating, but not enough to find good support for this :/ but, TLDR, forcing WAL truncation regularly fixed it all. But I had to do it from an external process on a heartbeat, etc etc
You don't need to truncate the WAL, you can checkpoint PASSIVE and the WAL will be overwritten (so your queries won't slow). Generally if you're using litestream for backups it will do checkpointing for you. If you aren't depending on the after each batch (always be batching!) works well too.
I'd say the hardest part of using SQLite is its defaults are rough, and a lot of drivers don't handle batching for you.