If care is taken to cache query results and clear on insert/update, one can get stupendous performance from sqlite. I used Apache, mod_lua, and redis to implement a system that does this for example.
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.