The problem with Amazon RDS, and they freely admit it, is that it's not built for the scale at which some companies operate. If you need advanced features of your DB, or need to operate at large scales (the magnitude of scale differs by RDS engine), you'll end up wanting to run your own DB on an EC2 instance.
Well, ignorance also tends to fuel any number of flamewars...
In my experience with RDS as a MySQL admin, Amazon was not using sane defaults for quite some time, which resulted in a remarkably non-performant product. Thankfully, they listened to feedback, so over the course of a few years the MySQL RDS instances started to become much more dependable and useful. I'd be happy to use one today, which is something I couldn't have said too long ago.
And even with RDS, having a DBA available (even just as a consultant) is still quite useful - it's unrealistic to expect your developers to write ideal SQL. You can get a long ways with RDS if your queries and schemas are well tuned.
