I also experience that 1 bad use case: heavy writes. I've always used SQLite first for any project, and when I did an in-house analytics tool for tracking user-initiated events (visits, button clicks, hovers, etc) I thought SQLite could handle it well. Unfortunately, even with heavy tuning, we saw WAL overruns and missing data.

Sadly, we had to move to Postgres and eat all that scaling complexity. :(