"The second advantage of WAL-mode is that writers do not block readers and readers to do not block writers. This is mostly true" - coming to you live from the part where this is not true. #sqlite3 https://sqlite.org/wal.html
@bert_hubert also not entirely true in my experience. I guess it might depend on whether the writer accesses the same table as readers or not.
In Signal Desktop (since recently) we have a pool of threads with one writer thread and multiple readers, and it seems mostly optimal, but still writing blocks reads from time to time.
@bert_hubert even with the different tables I sometimes think that creating a separate database might be more performant. Need to do some benchmarks to quantify that, though.
@indutny would probably work well - for my setup I have 3 separate sqlite databases already and it is a big win, they don't even see each other. And you can still ATTACH them together if you need to. I other places I've also sharded data over arrays of databases - they are all free!
@indutny @bert_hubert yeah can confirm, we also went with a multi-threaded approach in the end and it’s still not ideal.
@indutny trying to get a handle on it, it mostly seems related to opening and closing databases. Or running a pretty old sqlite (in Debian). Probably going to vendor a newer release. At least this makes it easier to complain on the (very responsive) forum!
@bert_hubert if you actually need that non-blocking behavior you should use SQLite backed by LMDB. Sqlightning or lumosql.