Genuine question because I agree that there are a lot of over complicated systems. I often see people say all you need is SQLite. Do you implement replication yourself? Or you are just accepting that if something happens to your server your data is just gone? I always default to managed Postgres and that seems to be the simplest most boring solution.
SQLite is absolutely not suitable if you need non-trivial amounts of write concurrency - SQLite locks the file when writing, and doesn't even notify the next writer when done - writers poll to see if it's unlocked yet. If you don't use WAL mode, then readers have to wait for writers to.
You can still back up your SQLite database file. You shouldn't do it in the middle of a write, or you should use the SQLite backup API to manage concurrency for you, or you can back it up in SQL dump format. This isn't one of the usual reasons you shouldn't use SQLite. If you need synchronous replication, then you shouldn't use SQLite.
SQLite is robust against process crashes and even operating system crashes if fsync works as it should (big if, if your data is important), but not against disk failure.
In most of the cases when you shouldn't use SQLite, you should still just upgrade one step to Postgres, not some random NoSQL thing or Google-scale thing.
