Interesting, I never had this issue but I also run it through Peewee (on Python) so that might manage that. Or maybe because I put
PRAGMA journal_mode = 'wal';
this helps by putting things in write ahead logs to avoid having to lock so excessive. Maybe this iwll help you scale?
About the Litestream to S3, how is that? I considered that but it seems very new so I am unsure how stable it is?
I am starting to think that the connection errors I have have to do with a combination of the crash recovery process, which takes an exclusive lock, and the fact that I am using ATTACH on multiple auxiliary databases. I have a suspicion that I have entered into less tested territory with the latter. I also might be getting the system into tight recovery/crash loops when systemd is restarting these processes due to "database locked" errors.