PostgreSQL is great, but I've gone back to MySQL because of the ease of which I can setup replication (which I like to use when possible in addition to automated/periodic backups).
Postgres replication is probably not as easy to setup, but can be extremely powerful especially when use tools like WAL-E as well.
We have a setup where our WAL log is rotated every minute and backed up to S3. Replica databases can either load from S3 or connect to the master for streaming - so they will catch up using S3 and then connect, reducing load on the master.
I'd love a honest, MODERN comparison of both systems. Sometimes I feel like the cargoculting with PostgreSQL is as bad now as it was with MySQL 10 years ago and MySQL is being badmouthed for mistakes from the MyISAM age.
MySQL has some features that I'm really missing in PostgreSQL, like more flexible compression, and batched index writeout.
RDS makes it trivial to have high availability if you can't afford a DBA worth their salt. You don't need 10x replication to beat the uptime of your shitty web app.
I thought so too, until I decided to use RDS for storing zabbix.
It looks great on paper, but unless you're doing development I would discourage its use. That said your original point about the replication is true, but there are shortcomings that come together with it.
Some things that you will learn if you use RDS:
- if you decide to increase volume size, change type to SSD or use provisioned IOPS, you might have database down for an hour or more. Regardless whether you use single instance or HA.
- want to upgrade 9.3.x to 9.4.x? Tough luck you have stop dump the data and provision a new instance. You can't use postgres' inplace upgrade method
- you have limited control regarding tuning, many settings require rebooting the whole thing, when normally you would just restart the process (with HA there's still several seconds of interruption)
- you can only use extensions that they provide, there's almost no extension to monitor performance
- you can't login to use shell to monitor the process (obviously, but it's a still shortcoming)
- can't replicate data across regions or outside of RDS (it could resolve some of the issues above)
- it can fail, we had two times a failure caused by AWS, it happened during backup period. Normally AWS does backup on the secondary database, but we learned that in those instances it got confused and was attempting to do it on primary, rebooted it and for about 15 min the database was unavailable.
First off, thanks for sharing your experiences. I am standing up a system right now that will use PostgreSQL RDS and it's good to know what kinds of bumps in the road to expect.
I think I am fine with most of those, since I am saving on labor costs. Periodically taking an app down for maintenance is par for most courses. And even on the last bullet, I've seen really talented database guys make mistakes and have small amounts of downtime. Perhaps what I should have said was "high enough availability". If I needed 5 9's, I agree that RDS is probably not the tree I want to be barking up.
Yeah RDS is ok as long as you are ok with the limitation.
I was more concerned about relying on it for 24/7 operation. Such as for transactions on a webpage. The zabbix scenario in our case also requires 24/7 uptime, but if it goes down our site is still up we just won't be alerted when something else breaks at the same time.
