>> vs. relying on a new backend instance per client in a cloud infrastructure
> You want to spin up an EC2 per client, and run an isolated copy of the application, isolated DB, etc. inside of it? That sounds like a nightmare to manage, especially if you want or need HA capabilities.
Just that utility at the database management layer is probably worth the price of entry for using cloud resources if you can't justify and cover the cost of say 5+ employees just for the data management infrastructure.
That’s going to be enormously expensive. If you need guaranteed tenant isolation, put them in separate schemas, with specific user grants. That scales up much better than you’d think.
Or use Citus Postgres, and get sharding by schema for free, so you have both isolation and more or less infinite growth.
I’m not sure why if you think it would take 5 employees to manage self-hosted DBs, that it won’t take close to that to manage cloud-hosted ones. The only real difference you’re going to have once both are set up is dealing with any possible hardware issues. The initial setup for backups, streaming replication, etc. is a one-time thing, and then it just works. Hire a contractor for that, optionally keeping them on retainer for emergencies if you want.
You still have to deal with DB issues with a managed service: things like schema management, table design, index maintenance, parameter tuning, query optimization are all your responsibility, not the cloud provider’s.