Wouldn't be good for huge data sets but these days huge means like a terabyte or more. You don't really have to start thinking about sharding or smart fractional replication until you hit many terabytes.
Even many successful SaaS companies don't have databases that big. Several hundred gigs these days is not too big for a brute force replicate everywhere approach and it comes with some benefits like less locking and stronger fault tolerances. If you have 50 replicas the odds of them all being screwed at once gets very low.
(1) You can know exactly what code was running when debugging past issues
(2) Scaling can be done dynamically and relatively easy. Just turn on/off application nodes.
(3) Rollbacks are (relatively) trivial. Just go back to the know good immutable version
(4) Memory leaks can be "solved" by restarting nodes every so often
(5) If, for whatever reason, your application node dies you shrug your shoulders and start a new one
Once you add DBs with state to your application all of become much harder. You want that state in something that's rock solid and rarely changes to avoid all the above problems.
Also, the scaling characteristics of database nodes vs service nodes are very different. I might not want to put reliable storage into every node I have, but I'm probably willing to shell out a couple bucks for my database server's storage.
An example from my last project that used a lot of postgres. The database node was about ~20x the price as the workers.
From an operational perspective, having the database be maintained separately from the app also has its benefits.