what is your story for fault tolerance and replication (hopefully cross geo zone). Do you set and run everything yourself? How much effort(including learning curve) and expertise does it require?
Why wouldn't you use some cloud offering where you can get many of these for cheap if your data is not large?
What makes you think cloud is cheap, or reliable? AWS lambda does not provide any guarantees other than compensation if the uptime is less than 99.95 %. That is ~22 minutes/month. It's terrible. If it was reliable they would provide better guarantees. How long since a major outage? A couple of months.
Few people run everything themselves. Most use existing datacenters or rent the hardware. Then its basically the same as using a VPS from a cloud provider when it comes to difficulty and expertise.
> What makes you think cloud is cheap, or reliable?
"cheap" is relative, if your data is not large (gigabytes) then cost will likely be much smaller compared to expenses on engineer supporting custom solution.
> if the uptime is less than 99.95 %
It doesn't mean their services experience this downtime. They have their reputation supporting revenue on the line. Customers will go somewhere else if experience frequent downtime.
But the point is that with custom pgsql installation it is nontrivial to setup and support any kind of fault tolerance, plus you have a chance of all other kind of outages: network, your hosting provider, etc.
Buying cloud offering with one click looks like no brainer base line.
The only thing that should require very strict uptime is the database. Lambda (for your backends) says 99.95% per region, which does seem kinda low, more what I'd expect for an AZ than an entire region. Can you easily add a second region for more 9s?
The only potential issue I've seen with cloud managed DBMSes is latency between application and DB, since they might be far apart. But that's something you check out day 1 and move away from if it's not tolerable. I would totally start there, not try to set it up myself.
Well there's same region, same AZ (if AWS), and same machine. Different levels of location for GCP or whatever. Same machine is probably never doable on a cloud provider, but it's rare that you'll need that.
Why wouldn't you use some cloud offering where you can get many of these for cheap if your data is not large?