Believe me, I am sad to stop using Postgres. What parts of RDS haven't been worth the effort? I didn't have to make much effort, beyond rewriting my Postgres triggers into MySQL syntax.
Inconsistent performance, significantly slower than MySQL on a real box, and failure of the ridiculously expensive automatic failover were the reasons I dumped RDS. I ate a big loss after reserving instances, but seeing the server fall off the planet, and no spare ever take over for it despite paying for MultiAZ, what other response would have been reasonable?
RDS is pretty expensive for what you get. You can't restore to a running instance from snapshots, you get very little control over the environment, and you can't replicate between geographic regions (only availability zones).
I know you said you're a two man shop, but in this case it may make more sense to leverage other IaaS DB services instead of RDS.
"You must create a DB snapshot before you can restore a DB instance from one. When you restore the DB instance, you provide the name of the DB snapshot to restore from, and then provide a name for the new DB instance that is created from the restore. You cannot restore from a DB snapshot to an existing DB instance; a new DB instance is created when you restore."
"you get very little control over the environment" also seems spurious. You get parameter groups to control just about everything you can from a standalone mysql instance. logs or things that load from disk (LOAD DATA ...) are the only things you can't touch, but a small price to pay for automated backups, failover and scaling (both up AND down...)
In my opinion, you don't get enough control over the environment for what you're paying on a per hour basis. Automated backups? Great, they aren't that difficult to being with. Failover? Sure, within the same AZ, when you need to be doing it between datacenters. Scaling? I will grant that it scales up and down fast automagically.
I've seen RDS have significant per-query overhead, and some very, very variable read times - in particular, some simple SELECTs randomly taking > 10s every so often.
RDS is subject to the exact same intra-region latency issues that other AWS instances have. Perhaps worse in some cases, since their failover appears to be DRBD based.
Is your ec2 instance in the exact same zone/region as your rds instance? An ec2 instance in us-east-1a will have extra latency dealing with an instance in any other zone, even us-east-1c.
I'm not the OP, but the thing that's always scared me off from those types of services is that I think there would be high latency, where in a DB you really need low latency. Is that not true?
Since Heroku runs on EC2, as long as OP kept his servers in the same availability zone as the DB, latency shouldn't be any worse than running it himself. But yes, in general I'd be wary of a database far away from my app servers.
That can depend on whether you can hit the internal IPs, or just the external IPs. If you can only hit the external IPs, you're subject to greater network latency, plus data transfer fees.
We came across this issue as well, we'd been wasting too much time setting up Postgres. RDS seemed liked a good choice, except that MySQL is just that much worse than Postgres.
We had just used things like concurrent indexes.
We ended up going with Heroku's hosted Postgres solution.
It costs the exact same we were spending w/ two High CPU instances w/ provisioned IOPS volumes.
Now we get fully managed, same price, and all the features of Postgres.
We still host our full application on AWS, the only thing is that we have a managed database. While Postgres makes it easy to setup replication and what not, AWS hardware just sucks. It takes time to properly tune it.
