What AWS instance to choose for write-heavy PostgreSQL database server

Question

We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At the moment we are using a smaller cloud service for the server hosting. Currently we are looking at the possibility to move our server to AWS. The problems we are having are mainly related to our database server and therefore we would like to have AWS-instance which is significantly better than the one we are using now.

The stats for the cloud server where the database server is located are as follows:

• 8 CORES
• 16GB RAM
• 100 GB, RAID-10 * SAS 15.000 RPM

Is there anything in particular you should think about when choosing what amazon instance to use for a PostgreSQL database server?

• xenoterracide: keep in mind that Virtual machines in general aren't designed for IO. If you're having problems it might be time to leave the cloud and get a hardware cluster.
• Craig Ringer: BTW, what is powering that RAID setup? Is it a hardware RAID controller with power-protected cache in write-back mode? 'cos if it isn't, for your workload adding a write-through caching RAID controller will boost your performance like you won't believe.

Answer 1

Asking "What AWS instance to choose for write-heavy PostgreSQL database server" is somewhat like asking "which spoon should I use to cut up this loaf of bread".

The answer is: don't use a spoon, use a knife. Or in this case, real hardware like a dedicated server with a batch of good quality SSDs and a decent raid controller that has a battery-backed write-back cache. Or at least a VPS with very strong I/O performance guarantees.

If in doubt, benchmark.

That said, you can make AWS perform significantly better than the defaults by using provisioned I/O EBS volumes on EBS-optimized instances, improving performance while retaining data durability. Alternately, you can sacrifice durability in exchange for more performance by using a high I/O instance with the database on the instance store; in this case you will need a careful backup and replication scheme because all your data will be lost if your instance stops or crashes.

If you need more assistance, contact me directly.

• lorgartzor: Thanks for all the help Craig! We are seriously considering using a dedicated server instead.
• Craig Ringer: @lorgartzor Glad to help. It'd be really useful if you'd respond to comments seeking clarification, though. You never did say what your RAID setup was, and that's very relevant to this and to your other question.
• Craig Ringer: @lorgartzor I have revised this answer based on new information.