SQLite is wonderful for some things but write concurrency is not one of them. This means you really need to segregate reads from writes, and use a different path for that.

I actually think SQLite in general is fairly underrated, but there are important limits.

PostgreSQL is my go-to db, but I love the ability to have the callbacks to SQL functions being in my main code. For lots of data analysis work, the way custom functions and custom aggregates work in SQLite is wonderful.

But again, a chasing hammer is not the same as a machine hammer.