What I have ultimately realised is that having a middle path to software architecture development is the optimal way i.e. build critical sections on your own and outsource others. For example, at the start do not think about horizontal scalability, it is a nightmare scenario to handle even if outsourced. So concentrate of vertical scalability with the help of a hosting provider. Horizontal scalability is a luxury that you can afford when you have dedicated people to manage it. There is a reason people developing Postgres have resisted the temptation to support horizontal scalability. It is a very hard problem to solve.
So if you are starting small and at an early stage then concentrate on how you will be able to own the data, how you will be able to own the business logic, and how you will be able to migrate from one service provider to another avoiding any vendor lock-in.
High availability, fault tolerance, always-on architectures are very difficult to achieve and maintain. There is a reason why FAANG spend so much resources on this and still get it wrong.
In case you want to go that route, then for some practical advise refer to https://sre.google
After evaluating lots of HA and fault-tolerant solutions and thinking about it for some time, personally I have come to the conclusion that having a single node with postgres, backed by streaming replication for backup, with dynamic vertical scaling using a hosting service where I can easily migrate my setup to other hosting provider is the way to go.
If your journey has made you to come to some other conclusion then please do share.