Thursday, November 14, 2013

First Look at PostgreSQL RDS on Amazon

By now a lot of you have heard about the new Postgres RDS on Amazon Web Services.  This is something I've been anticipating for quite a while, so I decided to log in and try it out right away.  Summary is: the service is up, it's real, they did some nice things, the press release isn't completely accurate, and they did fork PostgreSQL.

First off, looking at the feature list and what they decided to include with PostgreSQL, they seem to have decided that Heroku was a good template for what they should do.  So each instance includes PostGIS, hstore, PL/perl, PL/python, etc.  Also they automate point-in-time-recovery in some way, although I need to test that out.  I was particularly interested to see how they could support PL/python, which requires superuser access to create functions.

So I logged into pgExperts' AWS account, and spun up an instance of Postgres RDS.

This gave me warm fuzzies:

Latest version of PostgreSQL, and "auto-minor version upgrade", which is on by default.   Yay!  No nagging AWS users to apply the darned updates, already.  There's also a bunch of options with configurations, backup configurations maintenance windows, security, etc.  Altogether somewhat more "gear-head" than Heroku; you have to configure these things, AWS doesn't do it for you.  By default, for example, port 5432 is blocked from the internet until you create a new "security group" to unblock it.

Deployment of my new instance took longer than I'm used to with AWS (around 15min), but that's possibly because the announcement today has meant a rush of people trying the service.  Besides, AWS has me spoiled for deployment times.

Connecting was simple enough:

What's the user setup?  Hmmm, that's interesting:

Looks like Amazon has created a "local superuser" concept who can install extensions and perform some other tasks.  I wonder if they're going to contribute that to mainstream PostgreSQL?  Anyway, let's try installing PL/python:

Huh!  So, it looks like Amazon hasn't worked out the whole PL/Python issue after all, since it's not actually on their list of provided extensions, despite the press release.  Everything in contrib is, though, as well as PostGIS.

So, the new PostgreSQL RDS is here and real.  It's more oriented towards the DBA, Devops staff or Sysadmin than Heroku's service is; if you want to "just not worry about the database at all", you still want to use Heroku.  However, for users who are already running PostgreSQL on EC2, RDS is worth a look to see if you can simplify your infrastructure.

They've also clearly created their own fork of PostgreSQL, so we'll see if they contribute back their changes to mainstream, or decide to maintain a fork indefinitely.

More review this weekend as I have time; I'm interested to see how this instance performs, but I need to devise a test which doesn't rely on network latency first, or maybe run pgbench from an EC2 instance.  I'll also test the performance of their provisioned IOPS instances.  Suggestions welcome. 

Shameless commercial plug: pgExperts is offering support for customer on Amazon RDS through our CloudDBA service.  Just in case you need a little help.


  1. This is great news but I'm very disappointed that they didn't build it with support for the xml type (--with-libxml). I suppose most people don't use that, but we use it in a couple of places so it will prevent us from using this service.

  2. As they told me in the Architecting class, they engineer everything for "The 80% use case." In AWS's opinion, the xml type is desired by less than 80% of Postgres users.

  3. I'm curious if you were ever able to benchmark the RDS instance with pgbench. My understanding is pgbench requires a superuser role, which I don't think AWS permits, is there a way around this?

    1. Huh? No, it doesn't -- pgbench can be run as any user who can create tables. My limitation on this has just been getting around to it.