I'm not thinking very careful in answering this, but I think there's a subtlety I'm not capturing well here, and that it's not the case that this pattern has poorer fail-over than clustered Postgres. I think there are more things that can go wrong with Postgres.
For a real-world example, some time ago I wrote an admin tool as part of a production monolith, for a rare (but frequent-enough) internal use case where a user could put in a URL, the app server would fetch the remote HTML, process it, and save the results to a database. A few months later, we start getting weird error reports that the server is crashing. Sure enough, a malformed document could cause enough recursion to exceed the Python maximum recursion depth and crash the process.
If this had been the single process running server-side SQLite, even if multithreaded and/or concurrent (with e.g. something like gevent), the crash would have taken down our entire site for however long it would take to restart the process. But since there were other app server processes eager and ready to take ongoing requests, the only disrupted user was the one trying to use that admin tool, not all the other customers using the product at the time. Said user was confused, but was very glad they didn't disrupt the critical work our customers were doing at the time!
Of course, one size doesn't fit all, and there are many services that are limited enough in scope, working with known and battle-tested components, that this wouldn't be a problem. But if I make the conservative assumption that any process that is running code that I myself wrote, no matter how simple-seeming, has a much higher chance of segfaulting or otherwise crashing than a standalone professional database... then that late-night ping is much more likely to be something that can be handled the following morning.