If you need replication on the app level, SQLite doesn't make sense, because it's not built for networks.
But...
Many projects won't ever need that. A bare metal machine can give you dozens of cores handling thousands of requests per second, for a fraction of the cost of cloud servers. And a fraction of the operational complexity.
Single point of failure is really problematic if your system is mission critical. If not, most apps can live with the possibility of a few minutes of downtime to spin up a fail over machine.
But...
Many projects won't ever need that. A bare metal machine can give you dozens of cores handling thousands of requests per second, for a fraction of the cost of cloud servers. And a fraction of the operational complexity.
Single point of failure is really problematic if your system is mission critical. If not, most apps can live with the possibility of a few minutes of downtime to spin up a fail over machine.