Tuesday, 27 February 2018

Postgres10 in RDS, first impressions

As a firm believer of Postgres, and someone who runs Postgres10 in production and runs RDS in production, I've been waiting for Postgres10 on RDS to be announced ever since the release last fall. Well, today was not that day, but I was surprised to see that RDS is now sporting a "postgres10" instance you can spin up. I'm not sure if thats there on purpose, but you can be sure I jumped at the chance to get a first look at what the new Postgres 10 RDS world might look like; here is what I found..
The first thing that i wanted to test was logical replication. By default it was disabled with rds.logical_replication being set to 0. AWS console allowed me to change this, which also changed wal_level to logical so i started creating a simple table to replicate. I created a publication that included my table but thats where the party stopped. I can't create a role with replication privilege and i can't grant replication to any user :

mydb=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+---------------
 testuser      | rds_superuser
(1 row)

Time: 143.554 ms
omniti=> alter role testuser with replication;
ERROR:  must be superuser to alter replication users
Time: 163.823 ms

On top of that, create subscription requires superuser. Basically logical replication is there but i don't see how anyone could actually use it. It's well known that RDS replicas can't exist outside RDS. I was hoping that postgres 10 and logical replication would add more flexibility on replicating methods. I don't think this will change anytime soon but maybe they will add functionality in console menus that will control logical replication in their own terms using their rdsadmin user, who knows..

Next thing i wanted to check was parallelism. Remember how I said we run Postgres 10 in production? One thing we found is that there are significant bugs around parallel query, and the only safe way to work around them at this point is to disable.
I was surprised to not only see it enabled, but in fact they are only running 10.1, which does not include a bunch of fixes that we need in our prod instances (not to mention upcoming fixes in 10.3). Presumably they will fix this once it becomes officially released, hopefully on 10.3. For now, please be nice and don't crash their servers just because you can.

I tried a bunch of other features and it sure looked like Postgres10. The new partitioning syntax is there and it works, as well as 
scram-sha-256 . Obviously this is super new and they still have work to do, but I'm really excited about the chance to get a sneak peek and looking forward to seeing this get an official release date, maybe at pgconfus later this year?


Thanks for reading.

Vasilis Ventirozos
OmniTI Computer Consulting

Logical Replication in aws RDS

Recently Amazon annnounced that postgres 10 supports logical replication syntax and that it can work as either a publisher or a subscriber. ...