Regarding the upgrade, yes. Sort of.
To make a very long and painful story short - there was a narrow upgrade path that required multiple rounds of upgrading ArcGIS / Postgres / PostGIS in little increments in order to hop from one supported combination of versions to another. We had to do full Postgres dumps > reloads throughout, and ultimately ended up a major version behind in ArcGIS.
The problem is that one has little flexibility to manage the software versions when working with RDS compared to "normal" Postgres, and ArcGIS has specific requirements for what it supports. In particular, RDS only supports one version of each Postgres extension (e.g. PostGIS) per Postgres release. To Amazon's credit, they are constantly releasing new Postgres/extension versions, so there's lots to choose from. But, unless Esri also certifies that platform, you can get stuck on older releases.
If we'd been running our own Postgres instance on our own host (i.e. not RDS) we could have easily worked around our compatibility issue. But we would have had to do a lot of things for ourselves that RDS was doing for us. So, I don't want turn you off of RDS. It's not perfect - but neither is running your own database instance. Let me address your second question .
With respect to success with RDS, yes. I've used it a bunch (with and without ArcGIS) and it's generally been great.
Performance has been fine, in my experience. In fact, on one project we run a tiny instance most of the time to save money, then restart it with tons of processor / memory / fast disk to do big data processing jobs. When we're done, we restart it back on the tiny specs. If performance has been bad for you, maybe you need to provision more resources to your RDS instance? (I know that's obvious to say; I'm just guessing.)
More generally, RDS is a double-edged sword: you don't have to manage much, but you don't get to manage much.
I find that RDS is a great fit for clients who don't have a full-time database person. Relying on AWS for automatic backups, automatic minor version upgrades - and one-click replication! - is fantastic. It works great. The AWS Console UI is even easy enough for technical staff without database skills to clone databases for testing. This kind of stuff is going to put me out of a job 🙂
On the other hand, to ensure that all of this magic automation is stable, Amazon necessarily restricts your access to some administrative things. For example, as we discussed above, you can't install any old version of any old extension, because you don't have access to the underlying host on which your RDS instance is running.
Perhaps more importantly, you don't have access to SUPERUSER. Lots of admin-type stuff in Postgres depends on SUPERUSER access. Again, Amazon can't give that to us, lest we break something like backups or replication - the very things that we're paying them to do for us by choosing RDS.
The problem with this is that there's no real substitute for SUPERUSER. This isn't like the "DBA" role in Oracle, which is just some arbitrary role that happens to come with the software and which you can recreate yourself by granting the same privileges. As far as I can tell, SUPERUSER is baked-in to the Postgres software, kind of like "sysadmin" in SQL Server. You can't fake it with other privileges; you have to have the actual, built-in SUPERUSER role for certain operations that require it.
This lack of control is a pain. But, is it more or less of a pain than managing by hand all of the things that RDS does for you? It depends on how well you know Postgres and how much time you have.
RDS is neither inherently good nor bad, nor is there some generic answer about which platform, RDS or normal Postgres, is best. It's a matter of finding the best fit for the task at hand.
