
Amazon RDS for PostgreSQL - teoruiz
http://aws.amazon.com/rds/postgresql/
======
adrianh
This is HUGE!

I'm using the AWS stack for
[http://www.soundslice.com/](http://www.soundslice.com/) and I've been using
MySQL instead of Postgres, purely because my hatred for MySQL is less than my
hatred for being a sysadmin. It was a tradeoff, and I miss Postgres dearly
every time I use MySQL.

This new Amazon offering solves that.

I wrote a little more about my AWS setup here:
[http://www.holovaty.com/writing/aws-
notes/](http://www.holovaty.com/writing/aws-notes/)

~~~
pc86
Let me just say that I absolutely love soundslice. It's a great application!

~~~
adrianh
Thanks! And it'll be even better once I migrate to Postgres. :-)

~~~
rabidonrails
Wow, Soundslice looks amazing! Excited to play around with it.

------
yumraj
I've heard about PostgreSQL and know that HN community raves about it, but am
currently using RDS with MySQL.

Does it make sense to migrate to PostgreSQL, I don't have a lot of data as I'm
in the early stage? What are the primary advantages that PostgreSQL provides
over MySQL? Any advise/pointers is appreciated.

~~~
adrianh
Postgres actually cares about your data.

Perhaps you mistakenly insert "2013-10-32" into a date column. MySQL will
silently convert this to "0000-00-00" (!!). Postgres will raise an error.

Perhaps you make an error in a transaction. MySQL lets you keep doing
subsequent things in the transaction. Postgres treats the transaction as
invalid and forces you to start over.

Perhaps you want to add a column to a table that has millions of rows. With
MySQL, you'll be waiting a looooong time (see
[http://stackoverflow.com/questions/463677/alter-table-
withou...](http://stackoverflow.com/questions/463677/alter-table-without-
locking-the-table)). With Postgres, it takes about a second.

Of course, there are things you can do to make MySQL less horrible, and this
is a generalization. But Postgres is just more respectful and more solid.

Oh, and PostGIS (Postgres' geo add-on) is by far the best open-source
geospatial database. If you're doing anything with geographic queries, you
need to be using it. MySQL's stuff is laughable in comparison.

Context: I've dealt extensively with both databases, both from the perspective
of a framework author (Django) and a developer making products. I've used both
databases on and off since 2001.

~~~
eli
Hey at least it throws a warning on the bogus date conversion (MySQL warnings
should almost always be treated as fatal errors).

The thing that kills me with MySQL (technically it's with InnoDB-based storage
enginges in MySQL) are the _subtle_ quirks. Like the thing where it insists on
writing temporary tables _to disk_ if you do a query that selects TEXT or BLOB
fields. Even if they could have easily fit in memory, it's not smart enough to
be able to determine that with variable length fields. A very non-obvious
performance killer unless you're specifically looking for it.

~~~
jacques_chester
I run a small Wordpress network. MySQL's insistence on going to disk for joins
on tables with a TEXT field (even if the query _doesn 't touch those fields_)
is probably the major performance bottleneck.

~~~
e12e
Ouch. Thanks for the explicit heads-up. I generally stay with PostgreSql, but
I honestly thought most of these things were "fixed" in recent versions of
MySQL (and assumed my subconcious dislike of MySql was at least partly
irrational/rooted in Ancient and Outdated Lore). I guess not:

[http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-
ta...](http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html)

I'm not sure what the status of TEXT-fields are in mariadb:

[https://mariadb.com/kb/en/optimizing-string-and-character-
fi...](https://mariadb.com/kb/en/optimizing-string-and-character-fields/)

~~~
jacques_chester
> _Thanks for the explicit heads-up._

It took me a long term to learn this one. I suppose if I'd read the MySQL docs
from cover to cover I would've found it earlier.

One other problem that popped up was ignoring indexes on tables with TEXT
fields during joins, which was a planner weakness. I understand it was fixed
in 5.6; I'm waiting for the Percona version to stabilise before I upgrade.

------
sillysaurus2
Will anyone please explain the tactical reasons why PostgreSQL won? It's
pretty obvious it has. I've basically ignored the database wars for a few
years, so it's kind of interesting to see that everyone's using PostgreSQL
now.

~~~
jes5199
Some engineers I worked with went and interviewed people at various San
Francisco startups about their experiences with their databases.

The MySQL startups tended to say "We love MySQL. We've gotten in the habit of
taking an hour or two of downtime in the middle of the night every week to run
all of our schema migrations, and we've had to build our process around that,
but one we had it in place, everything's been fine."

The PostgreSQL startups said "We love PostgreSQL. We run schema migrations in
real-time during the middle of our workday, and we don't have any problems."

~~~
ceejayoz
pt-online-schema-change from Percona addresses this particular point with
MySQL nicely for us, incidentally. We use their Percona XtraDB Cluster fork
and are quite happy.

~~~
daniloassis
I guess we are talking about RDS options though :)

~~~
ceejayoz
Entirely possible to use pt-online-schema-change on an RDS instance. It's just
running a bunch of SQL commands and is very happy to do them against a remote
database like an RDS instance.

------
falcolas
I have but one suggestion. Unless RDS for PostgreSQL is drastically different
from RDS for MySQL, you still need a DB administrator.

RDS removes remarkably little of the pain of running a database instance (most
of the pain that's removed is just the up front setup), and ends up adding a
lot of inconveniences for your day-to-day operations.

Also don't count on their replication as your backup.

OK. That's two suggestions, but I think it's OK.

~~~
bsaul
Any link to a page providing more info on those quircks ? I'm deciding between
a traditionnal ec2 instance running postgres or rds at the very precise moment
!

~~~
falcolas
The long and short of it is that you do not have an account with super access
(you can't perform normal actions like killing threads, viewing processlists,
change variables, etc). You instead have to use special stored procedures to
perform basic administrative functions.

Also, you can't take advantage of replication (aside from their own read
replicas within other RDS instances) or binary backups. Anything that requires
access to the machine itself is impossible except through Amazon's support
channels.

Its gotten better than it was, but it's still a headache to monitor and manage
as a DBA.

~~~
ptrf
For MySQL RDS instances, it's most certainly possible to do offsite
asynchronous replication without the use of read replicas, as described in
their documentation here:
[http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL....](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html)

The guide does mention using a read replica to replicate from, an intermediate
RDS instance between your offsite, but I've had no trouble replicating
directly from the master instance.

One thing they don't cover is replication over SSL. AWS has failed to mention
this shortcoming in the docs last time I checked. To have MySQL replicate over
SSL, the master and slave both need an SSL certificate signed by the same CA,
which would require you to obtain a cert+key signed by the AWS RDS CA.

Of course you have the option of tunneling the replication connection into a
haproxy or stunnel running on an ec2 instance, but that has it's other
shortcomings. You can't use the ELBs, since you can't register the RDS
instance with an ELB.

------
tracker1
I think this is great news... PostgreSQL is definitively my favorite open-
source database. It's also nice to see Amazon get into the game, as hosted pg
options have been fairly limited. I am slightly disappointed to not see the
server-side JS support baked in, and that apparently you can't do reads from
distributed replicas. Just the same, I think there will be a lot of progress
in this area.

Administering databases is a full-time type of responsibility. Yes, you can
get pretty sane defaults, and up and running without much difficulty with MS-
SQL, and mySQL has been a defacto standard in the LAMP stack. That said
PostreSQL has been a rock solid RDBMS. The commercial extensions for
replication have been cumbersome and expensive. Here's hoping that AWS will
grow/expand the replications options/features, and that they'll grow to
include JS procs as that feature stabilizes.

~~~
j-kidd
It looks like the Multi-AZ setup is using block level replication such as DRBD
instead of the built-in replication:

> Database updates are made concurrently on the primary and standby resources
> to prevent replication lag.

Makes me feels better for setting up my own pg cluster on EC2 a week ago,
which does allow reads from the replication slave. Plus, I can provision <1000
IOPS (provisioned IOPS is damn expensive with AWS), and get to use ZFS.

~~~
angrybits
The time spent researching and building that cluster was time not spent
building product. If you are low on capital then it was potentially a good
tradeoff, but I'd much rather just scale up my read capacity by adding
horsepower until they get around to adding read replicas. My goal is to spend
as little time as possible administering my infrastructure, and running my own
database cluster is the last thing on my mind.

------
frakkingcylons
I'd be most curious to know how the performance/dollar ratio stacks up to
Heroku's Postgres offering.

~~~
josephlord
From a very quick glance at the small end the Amazon options are a little more
than half the price (Ireland single AZ instance pricing and even less
reserved) but multi-AZ options are probably very similar.

I don't actually know whether Heroku can failover across an AZ failure.

~~~
craigkerstiens
disclaimer: Heroku PM here

By default our followers and HA is automatically cross AZ. You also have an
ability to create followers across region, but we do not automatically
failover on those due to latency.

~~~
giovannibajo1
Can you please explain the value proposition of using Heroku Postgres vs RDS
now?

Heroku's solution is 2 to 4 times more expensive for the same type of DB, and
RDS even allows for reserved instances to further lower the bill.

~~~
craigkerstiens
There are a variety of differences overall.

One area is we're focused more on delivering more guidance and expertise
around what you're doing with your database, in addition to ensuring your
database is healthy and running. An example of this is notifications that we
deliver around unused indexes, where you may benefit from other indexes, or
other places where you can quickly optimize your DB. This starts to free up a
DBA to add higher value tasks or for smaller shops lets you get by longer
without a need for a DBA.

Another big area is features we deliver on top of Postgres. This ranges from
followers which all you to easily scale read traffic, or allow for your
database to be replicated across not just AZs but also regions. There's the
other spectrum of this as well including dataclips. Dataclips make it easy to
share data in a simple way, as well as build richer dashboards by integrating
with google docs, or quickly prototyping APIs.

If you're curious on various technical details we'll be documenting that soon
but would be happy to correspond via email, craig at heroku.com

~~~
giovannibajo1
Like others, I also agree dataclips are very cool (I even blogged about them:
[http://giovanni.bajo.it/post/51299442521/making-
monitoring-g...](http://giovanni.bajo.it/post/51299442521/making-monitoring-
graphs-for-your-ceo-with-zero-code)).

I understand that "forks" and "follows" are easy concepts, stats are cools,
etc. but I personally wouldn't want to pay _double_ or _triple_ for that (and
I'm not a DBA, so I feel the pain). Not that my word counts much as I'm on the
smallest Heroku production plan, so I guess my bill isn't exactly interesting
for this kind of discussion. In my case, I would say that even saving $25 off
$50 can be offset by these additional niceties. But, I don't know what I would
think if I were a customer paying $1000/mo.

------
thinkalone
Here's the announcement on the AWS blog -
[http://aws.typepad.com/aws/2013/11/amazon-rds-for-
postgresql...](http://aws.typepad.com/aws/2013/11/amazon-rds-for-postgresql-
now-available.html)

------
rachbelaid
Great news and for people who ask them-self the questions but the version is
Pg 9.3.1.

Not all Pl are available, and it misses the PL/V8 and PL/Python at least.

And it seems that all fdw (Foreign Data Wrapper) extensions are missing.

But it's a great start, I'm looking forward to try.

If anybody know if we can still access the WAL log then it will be very useful

[http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_P...](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html)

~~~
aschreyer
PL/Python is very unlikely to appear soon unfortunately because it is an
untrusted language in PostgreSQL.

EDIT: you are right, PL/Python is supported as well. I only read the "Language
Extensions :PL/Perl, PL/pgSQL, PL/Tcl" part at the top.

~~~
bmaeser
quote: "Three language extensions are included with PostgreSQL to support
Perl, Python and Tcl"

([http://aws.amazon.com/de/rds/postgresql/#language-
ext](http://aws.amazon.com/de/rds/postgresql/#language-ext))

------
onedognight
This is great news, but unlike for Amazon's MySQL offering, they do not yet
support read replicas for PostgreSQL.

~~~
skyebook
Yeah I was disappointed to see this.. Can you configure pgpool externally to
talk to the replicas or are those multi-az instances a black box? I've never
used RDS before so I'm curious as to how much flexibility is afforded.

~~~
timdorr
Unfortunately, no. Amazon RDS instances don't provide console access and are
essentially black boxes.

~~~
yanivt
just to clarify, this isn't true ^. I log into my RDS mysql console all the
time.. mysql -h -u -p from a properly configured host..

------
ptio
Great news! Now I don't need to setup PostgreSQL myself.

Here is a related whitepaper if you still want to setup PostgreSQL yourself:
[http://media.amazonwebservices.com/AWS_RDBMS_PostgreSQL.pdf](http://media.amazonwebservices.com/AWS_RDBMS_PostgreSQL.pdf)

------
revetkn
Anyone know if the UUID type is supported? I see hstore and JSON are

Edit: It does, see
[http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_P...](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html)

------
noviceapp
Hmm, interesting development. But am I the only one to see a huge gap in
services between Amazon and Heroku, and one which most certainly bodes bad for
Heroku? Specifically, with Heroku's recent PG 2.0 service, their 'production
grade' Standard plan ranges from $50 - $3500, but "Up to 1 hr downtime per
mo." ?!?1! Huh, really, with up to an hour downtime /month, you couldn't be
serious about the product that runs atop this tier - a non starter me any most
other SaaS businesses. Heroku's cheapest "high availability option starts at
$200/month, still with "Up to 15 minutes of downtime per month."... still,
this is a concern for me...

Now, comparing to Amazon, their '1.7 GB memory Small DB, 1-year reserved,
multi-region' is around $28/month (with storage & transfer for my app no more
$35/ month). The equivalent Heroku plan, Tengu (1.7 GB mem) STARTS at
$350/month!!! Wow, not I'm really rethinking my hosting platform.... Amazon
looks more attractive, even if I have to do a bit of sys admin for my web
server/cloud server.

------
frankwiles
Been waiting for this for a long time, can finally nudge our clients that want
to use RDS (for various reasons) to use a sane DB. Thanks Amazon!

------
tzaman
I guess now's the time to start thinking about leaving Heroku...

~~~
mike_herrera
Heroku will still be my go-to for quick tests and experiments. But for larger
projects it would be foolish to not consider PostgresRDS + Docker on AWS.
*assuming they both leave beta.

------
jakozaur
Wondering what is the easiest way to convert from MySQL RDS to Postgres. Any
hints?

~~~
caioariede
I've used py-mysql2pgsql [1] sometime ago and it worked without any troubles.

I expect this helps you.

[1] [https://pypi.python.org/pypi/py-
mysql2pgsql/0.1.5](https://pypi.python.org/pypi/py-mysql2pgsql/0.1.5)

~~~
cjg_
Fully agree on py-mysql2pqsql, awesome little tool.

------
MrGando
I think that the 'simple monthly calculator' hasn't yet included PostgreSQL
instances?

~~~
runako
MySQL (free) and Oracle BYOL (no additional license cost) are the same cost at
RDS. Since PG is free, I'd guess the cost will be the same as MySQL.

~~~
MrGando
That's what I think too, they should state MySQL/PostgreSQL or something like
that in the calculator though.

~~~
amock
PostgreSQL costs more than MySQL according to
[http://aws.amazon.com/rds/pricing/](http://aws.amazon.com/rds/pricing/) .

~~~
eonil
Price difference seems huge. Looks more than Oracle? Can anyone explain why?

~~~
runako
Either they updated the table since you posted, or you're misreading it. MySQL
Large is $0.32/hr, PG Large is $0.336/hr, Oracle is $0.565/hr.

I don't know why the MySQL/PG prices are different.

~~~
eonil
My fault. I misread it. PostgreSQL has only heavy-utilization unit on reserved
instance. I thought it's light utilization unit.

Heavy/Large/1yr(reserved, multiAZ)/Tokyo: PostgreSQL = $1548, Oracle = $2440

Seems reasonable price. And I don't care MySQL price because it's not an
option to me.

------
michaelbuckbee
How does this compare to Heroku's PG offering?

~~~
NatW
Good question. Heroku offers an RDS plugin now only for mysql:
[https://devcenter.heroku.com/articles/amazon_rds](https://devcenter.heroku.com/articles/amazon_rds)

If/when a Heroku RDS plugin for Postgresql arrives, competing benchmarks, a
cost calculator, pros and cons would be very interesting, indeed.

Heroku pricing: [https://addons.heroku.com/heroku-
postgresql](https://addons.heroku.com/heroku-postgresql)

Amazon pricing:
[http://aws.amazon.com/rds/postgresql/](http://aws.amazon.com/rds/postgresql/)

~~~
cmelbye
Doesn't their RDS add-on merely set DATABASE_URL to the URL that you provide
when adding it? Are you sure it doesn't work with MySQL? There's no reason
you'd need to wait for Heroku to add support for this, just spin up an RDS
instance in us-east-1 and start using the URL Amazon gives you as your
database URL.

~~~
bjeanes
I'm on the Heroku Add-ons team. This is correct. The RDS "add-on" is there
simply for people who search for it. In reality, it's just setting
DATABASE_URL, and you can do the same yourself.

------
jeffdavis
Does anyone know if it's pure community PostgreSQL or if there are
modifications? If there are modifications, is it 100% compatible?

------
philliphaydon
OMG I LOVE POSTGRESQL!!!!

This is awesome news! I hope we can move to it at work!

------
bmaeser
this is awesome! still, i miss one essential feature - compared to mysql on
aws and heroku/postgres: there is no replication for read replicas. yes, you
can deploy a "hot standby" replica in another availability zone for failover,
but you cant read from it.

~~~
zwily
You can be sure that that will come.

~~~
oceanplexian
Probably not. Amazon has intentionally crippled external replication in MySQL
for years now.

It's a form of vendor lock-in and you shouldn't support it.

Without replication it's impossible to migrate out of RDS without taking
downtime (I've been through this and it was painful).

~~~
sendob
This seems to have changed:

[http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL....](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html)

------
simonebrunozzi
Useful tool to migrate from MySQL to PostgreSQL :
[https://github.com/lanyrd/mysql-postgresql-
converter](https://github.com/lanyrd/mysql-postgresql-converter)

~~~
spwestwood
py-mysql2pgsql is good too: [https://pypi.python.org/pypi/py-
mysql2pgsql](https://pypi.python.org/pypi/py-mysql2pgsql)

------
vocino
This will make the ease of Elastic Beanstalk much more appealing.

------
kvinnako
Somehow it seems pretty costly...Wouldn't it be cheaper for me to just run it
on my virtual instance that's hosting the app and probably just back it up
with S3.

~~~
jon-wood
If you place no value on you're time then yes, it would be cheaper to just run
it on your own EC2 instance. There's a lot to be said for not having to worry
about the day to day of maintaining a database server though.

~~~
bjeanes
Well said. It's frustrating how much people want to do price comparisons on
the commodity instead of the service.

You're paying for the "as a service" part of PaaS, IaaS, SaaS, *aaS.

------
victorhooi
I'm curious what the performance characteristics of this would be like.

A lot of people talk about how poor the storage performance on AWS is - but
this seems to offer provisioned IOPS up to 30,000 IOPS.

I'm curious what sort of hardware/setup that translates to in the real world?
Do you find your own dedicated setups have more throughput?

And there doesn't seem to be much info on the network bandwidth between RDS
and EC2 either.

------
taude
We've moved off AMZN for more custom hosting. But this is a big win for open
source devs. More deoployment options is always a good thing.

------
LVB
I've only ever used S3, but some of these AWS offerings do look interesting
for my little projects. My question is, how do hours get calculated for
billing? If I had my super low traffic blog using RDS, would I incur a few
microseconds of time per DB hit, or is it rounded up to an hour, or is it the
total time the DB is available period?

~~~
dangrossman
You would be paying for 24 hours each day. You are renting a managed virtual
server, and you pay for each hour this server is running _in addition to_
usage fees for storage and IO operations when it's actually being accessed.
It'd be $20-30/month to host the database for a very small blog.

~~~
chrismmccomas
To be fair, if you qualify for the AWS Free Usage Tier, you could run a small
blog with RDS for $0 for the first year.

------
benmathes
Oh sweet, now I can actually put this up for less than $ArmAndALeg
[https://github.com/benmathes/earthquakes](https://github.com/benmathes/earthquakes)

------
workhere-io
Note that AWS' PostgreSQL offering is more expensive than its MySQL
counterpart.

[http://aws.amazon.com/rds/pricing/](http://aws.amazon.com/rds/pricing/)

~~~
adenot
something wrong with the 'reserved' prices?

PostgreSQL RDS micro instance falls to $0.009 per hour when reserved, while
MySQL falls to $0.016 per Hour.

If it isn't a typo, Postgres reserved instances are 1/5 of the on-demand
price, which doesn't seem correct.

~~~
gfodor
up-front price is largely different between the two. psql looks to be 50%
cheaper when reserved for 3 years. weird

------
gfodor
Oh dear god yes. I just rigged up a PostgreSQL instance on Ec2 for a new
project, so excited to shut it down and let amazon deal with it. Hell yes.

------
nathanwdavis
It doesn't mention which version of PostgreSql. I hope it is 9.3. It's got to
be at least 9.2 since it supports the Json datatype.

~~~
wvanwazer
It is 9.3.1.

~~~
gingerlime
But it's strange that the page says that _PostgreSQL includes support for
‘JSON’ data type and two JSON functions_

In PG 9.2 there were only two json functions[1], but 9.3 introduced more[2]

[1][http://www.postgresql.org/docs/9.2/static/functions-
json.htm...](http://www.postgresql.org/docs/9.2/static/functions-json.html)
[2][http://www.postgresql.org/docs/9.3/static/functions-
json.htm...](http://www.postgresql.org/docs/9.3/static/functions-json.html)

------
adityamenon
Oh, cool stuff! I'll look into using this with the Openstreetmap deployment
I'm building.

------
pkmishra
This is so fucking awesome. I have been waiting for this since long time.

------
sjtgraham
FINALLY!

------
lazyant
Funny how it's a tiny bit more expensive than the mysql counterpart

------
chenster
Are they ever going to support DB2?

------
tomphoolery
This is so nice. Thank you Amazon!

------
eonil
Finally!

------
natural219
I hate PostgreSQL. Yes, I am a dummy application developer who doesn't
understand database software.

Every time I try to install PostgreSQL it fails. Every time I install MySQL it
installs successfully with no problems. Actually, that's the extent of my
experience with it, and I guess I'm fine dealing with a database that doesn't
validate date formats strictly if I can _use the damn database without
hassle_. I am totally fine using Postgres at a company or with another DB
developer who knows how to set up databases properly, but if I am starting a
new project, I am going to use MySQL, period.

~~~
jclem
+1 using Postgres.app (for Mac):
[http://postgresapp.com/](http://postgresapp.com/)

If you're on a Mac and don't want a menubar icon (I decided this recently),
`brew install postgres`, and then write yourself some functions to make
starting and stopping easier:

[https://github.com/jclem/dotfiles/blob/master/zsh/functions/...](https://github.com/jclem/dotfiles/blob/master/zsh/functions/pgstart)
[https://github.com/jclem/dotfiles/blob/master/zsh/functions/...](https://github.com/jclem/dotfiles/blob/master/zsh/functions/pgstop)

~~~
kylequest
EnterpriseDB has a nice set of PostgreSQL installers for all platforms. It
also bundles PgAdmin 3 if you need a GUI. It's not as hip as PG Commander, but
it's a real client that can do pretty much anything (vs PG Commander where you
can't even create a new db instance).

Postgres.app is nice when it works. When you install the latest version the
psql tool it bundles and exposes in the menu assumes you have a db that
matches your user name, which, of course, doesn't exist, so you can't connect
to the Postgres :-)

It also doesn't work that great if you already have another version setup
running on the default port.

