Vertical scaling (getting a beefier machine) is less preferable than horizontal scaling in the case of cloud providers, because a) it usually costs more to upgrade instance types than to run multiple smaller instance types, and b) eventually you hit a ceiling of available instance types as you grow.
But you can still make this work. In our case, we'd ended up going the first route, and we ended up adding AWS EBS as the filesystem block store for our Postgres database, which is easy to resize dynamically without incurring downtime or other issues.
The downside of a vertical scaling approach is, well, you don't get HA "for free". You have to manually configure followers and standby nodes for the sole machine. You have to worry about your own replication. If failover and management is abstracted out for you, as in RDS, then it's easy to live with - otherwise very painful.
tl;dr go with managed DBs if you just need a DB and don't particularly have to optimise query performance outside of DB config.
But you can still make this work. In our case, we'd ended up going the first route, and we ended up adding AWS EBS as the filesystem block store for our Postgres database, which is easy to resize dynamically without incurring downtime or other issues.
The downside of a vertical scaling approach is, well, you don't get HA "for free". You have to manually configure followers and standby nodes for the sole machine. You have to worry about your own replication. If failover and management is abstracted out for you, as in RDS, then it's easy to live with - otherwise very painful.
tl;dr go with managed DBs if you just need a DB and don't particularly have to optimise query performance outside of DB config.