
Amazon Aurora Update – PostgreSQL Compatibility - jasonlotito
https://aws.amazon.com/blogs/aws/amazon-aurora-update-postgresql-compatibility/
======
akurilin
What are some downsides one should keep in mind when considering moving from
RDS Postgres to RDS Aurora? What's the catch?

Is it a lot harder to get predictable performance out of this at scale?

Do you optimize the db the same way you would optimize a single Postgres
instances, or do you need to learn a whole new set of rules for the Aurora
query planner? Getting a well known single node DB to perform great at scale
is already a fairly specialized skill, what does it mean to do the same for a
proprietary black box multi-node cluster that only exists in the cloud?

Are there any success stories of people making the switch at scale that one
can read more about?

~~~
ozgune
(Ozgun from Citus Data. I compiled the following from Aurora talks and
tutorials I've attended. Happy to edit it with more info.)

I don't think you need to worry about tuning things for the planner. Aurora
Postgres and Postgres use the same planner, executor, and storage engine. The
two differ in how write-ahead logs and consequently replication works.

[http://www.slideshare.net/AmazonWebServices/amazon-aurora-
am...](http://www.slideshare.net/AmazonWebServices/amazon-aurora-amazons-new-
relational-database-engine)

Slide 20 has a good technical comparison. TL;DR: RDS replicates data/logs to a
standby instance and EBS. Aurora replicates logs to a highly replicated
storage engine.

This slide also hints why Aurora MySQL highlights performance improvements,
and Aurora Postgres doesn't. RDS MySQL replicates a lot more data and logs to
the standby. Postgres has a better replication engine and therefore doesn't
consume nearly as much resources. (red and yellow arrows in Slide 20)

On a personal note, I was curious about Aurora's architecture to understand
how it compared to other solutions, such Citus. Aurora focuses on better
replicating your logs/data and scaling storage. Citus scales out through
sharding/distributing the underlying data and spreading the work across
instances. I also love that Citus is open source. Then again, I'm biased. :)

~~~
akurilin
Nice, thank you for the links and the writeups! What would you say are the
downsides then of going from RDS PG to this? Sounds like there are basically
none, but I'm still skeptical. Should I not be?

We've definitely heard of Citus many times as well, it will be interesting to
see what competition in this space will look like soon.

~~~
craigkerstiens
At this point it's a lot of guess on what the downsides will be. On the pages
for Aurora they mention they're working on support for extensions and various
PLs, but it's unclear if they're supported in the beta at this time. It's also
unclear if they had to fork off in some way, if they did it will most likely
lag behind what core Postgres at each new release. Of course right now it's a
lot of guesswork. It should be much more clear when it gets into GA on what
the trade-offs are.

~~~
akurilin
Got it, thanks for clarifying. Hopefully if anybody from AWS sees this they
can perhaps do extra nudging on their end about making SLAs more transparent
to end users.

------
koolba
(Hopefully jeffbarr will read this)

How does one get access to try this out? The article mentions it's available
in us-east-1 (N. Virginia) however I checked the RDS tab in us-east-1 and I
don't see it listed. Only the MySQL version is available.

The "... you can sign up now for access!" link points to
[https://pages.awscloud.com/amazon-aurora-with-postgresql-
com...](https://pages.awscloud.com/amazon-aurora-with-postgresql-
compatibility-preview.html) but it just redirects to
[https://aws.amazon.com](https://aws.amazon.com).

~~~
kjerniga
Below is the correct link to sign up for the preview of the PostgreSQL
compatible edition of Amazon Aurora; we are fixing the link in Jeff Barr's
blog post now, and thanks for letting us know!

[https://pages.awscloud.com/amazon-aurora-with-postgresql-
com...](https://pages.awscloud.com/amazon-aurora-with-postgresql-
compatibility-preview-form.html)

~~~
koolba
Wonderful. Thanks!

------
bpicolo
Well that sure is a killer feature. AWS is far ahead of Google Cloud for
postgres users.

~~~
JoshGlazebrook
I wish Azure offered managed Postgres hosting.

~~~
saosebastiao
I wish every cloud company had a managed Postgres offering. The potential pain
of managing my own database is what keeps me locked in, and I'd be able to
switch at a moments notice if that weren't the case.

------
unknownsavage
So this looks pretty amazing. As a heavy postgres user, is there any reason
why I _shouldn 't_ be using this? Or actually it's strictly superior in all
regards?

~~~
snuxoll
From the article: On the stored procedure side, we are planning to support
Perl, pgSQL, Tcl, and JavaScript (via the V8 JavaScript engine). We are also
planning to support all of the PostgreSQL features and extensions that are
supported in Amazon RDS for PostgreSQL.

Since they listed "planning to" I assume these aren't available yet, so if you
use any extensions or stored functions you'll probably need to hold back until
these are available.

~~~
SEJeff
So weird that you're not supporting python for stored procedures.

~~~
rpedela
RDS doesn't support untrusted languages which includes Python [1].

1\.
[https://www.postgresql.org/docs/9.5/static/plpython.html](https://www.postgresql.org/docs/9.5/static/plpython.html)

~~~
snuxoll
This is actually a TIL for me, I've always just use pl/pgsql for sprocs as
I've never had a need for anything beyond what it provides - I didn't realize
the 'u' in python was for 'untrusted'.

------
dhd415
I'm curious how much of the underlying pgsql storage engine was rewritten
since Aurora/MySQL uses a completely different engine than the default InnoDB.

~~~
scosman
I doubt much. Aurora looks more like those engines running on an EFS disk than
a major rewrite of the storage engines themselves.

They need to disable some features to make concurrent readers on the same
network disk work. I'm sure it's harder than I'm making it sound, but I don't
think it's a new storage engine beyond the disk level.

~~~
dhd415
I suspect you're right. I wonder if that's part of the reason why they claim a
lower performance improvement of 2x over "standard" pgsql installations rather
than the 5x improvement of Aurora/MySQL over standard MySQL installations.

If so, though, hopefully there's a silver lining of faster incorporation of
new pgsql features into the Aurora/PG service.

------
ris
And _this_ is how software becomes closed-source in the cloud era.

~~~
lima
And why SaaS companies hate the AGPL.

------
scrollaway
Netflix must be biting their fingers :)

[https://news.ycombinator.com/item?id=11950811](https://news.ycombinator.com/item?id=11950811)

------
redwood
Sorry to see for Enterprise DB and Citus. Tough biz to be in.

~~~
mslot
Don't worry ;)

Citus users typically have a working set of a few hundred GB or more and run
into memory and CPU limitations on a single machine on both the write and the
read side.

Provided your data has a natural sharding dimension (e.g. tenant ID in a
multi-tenant application), Citus can distribute tables across many servers to
scale out memory, CPU and storage. It can also parallelise queries, index
creation, aggregations, and deletion across the cluster, and it can
horizontally scale out write throughput. This gives you the necessary tools to
deal with very large data volumes.

e.g. 500k writes/sec [https://www.citusdata.com/blog/2016/09/22/announcing-
citus-m...](https://www.citusdata.com/blog/2016/09/22/announcing-citus-mx/)

~~~
ddorian43
Any chance that multi-master will be open sourced ?

~~~
craigkerstiens
Many of the underlying bits that make Citus MX possible will in time be rolled
into the open source version. At the same time some of the ways we make it
possible, such as by managing the streaming replication and high availability
may take longer to productize and it's unclear in what form they'll evolve.
For now Citus MX is available on Citus Cloud–due to the way we're able to take
care of all of those things for you.

------
timdorr
Does this include the full set of json/jsonb features and benefits of GIN
indexes?

~~~
ranman
yes.

------
koolba
Infinitely scalable[1] Postgres? Yes please!

[1]: _For all practical purposes for mortals._

~~~
paulddraper
DynamoDB and S3 are infinitely scalable.

Aurora is scalable to 64TB. For 10 million users, that's 6MB per user.

------
mrwnmonm
if anyone form google cloud reading this, are there any plans for adding
postgres support on GC SQL?

~~~
manigandham
they already have an alpha in the works... try emailing support or posting in
the discussion forums

------
2-m3m3n70
What's the advantage to using this over RDS?

~~~
ozgune
(From AWS Aurora's website)

We’re now previewing the addition of PostgreSQL compatibility to Amazon
Aurora. This edition has the benefits customers have come to expect from
Aurora, including high durability, high availability, and the ability to
quickly deploy low latency read replicas while supporting the full SQL dialect
and functionality of PostgreSQL 9.6.

~~~
wmfiv
Storage cost, performance and auto-scaling are also big advantages.

~~~
joesmo
Auto scaling in RDS? That'd require a multi-master cluster. As far as I know,
that's impossible on RDS, Aurora or not.

~~~
wmfiv
I should have been more clear. I was referring to storage auto-scaling. Aurora
gives you access to up to 64TB of disk billed in 10GB increments. You don't
need to preallocate and manage the scaling of your disk as you do with RDS.

------
haney
Very cool, can anyone tell if it supports PostGIS?

~~~
jasonlotito
From the blog post, they say that's a planned feature.

"We are also planning to support all of the PostgreSQL features and extensions
that are supported in Amazon RDS for PostgreSQL[1]."

[1]
[https://aws.amazon.com/rds/postgresql/](https://aws.amazon.com/rds/postgresql/)

------
brightball
Well...that's game changing.

------
joemccall86
I'd like to do some cost projections. When will this (along with the
db.t2.medium instances) be added to the simple monthly calculator?

------
wcdolphin
What sort of transactional support is there? Are Serializable transactions
supported?

~~~
awsdavew
Aurora will support the same isolation levels as community PostgreSQL: up to
Serializable on the writer, up to Repeatable Read on the replicas.

------
fosils
From the article I am not 100% sure whether support for AWS Lambda calls from
stored procedures is planned, does anyone else also interpret that it is?

------
sorenbs
Had really hoped they would announce full mysql 5.7 support.

~~~
dhd415
For Aurora, you mean? They support 5.7 for RDS, but a consequence of forking
MySQL for Aurora is that it's unlikely that new MySQL features will quickly be
incorporated into Aurora/MySQL.

~~~
sorenbs
For Aurora, yes. I understand the tradeoff and happy to stay on Aurora. Found
this forum post stating that 5.7 compat will come next year

------
alexgaribay
How does this compare to an offering similar to Postgres on Compose.com?

------
mrwnmonm
is Aurora different from
[https://aws.amazon.com/rds/postgresql/](https://aws.amazon.com/rds/postgresql/)
?

~~~
brightball
No need for multi AZ. Faster, more cost effective. Downtime of less than a
minute in the event of a crash. Geographically distributed storage that
autoscales up to 64TB.

~~~
vasco
This isn't true, you still need a read replica if you want it to failover
under a minute. Otherwise it'll try to create another DB.

~~~
brightball
From the FAQ

"Q: How does Aurora improve recovery time after a database crash?

Unlike other databases, after a database crash Amazon Aurora does not need to
replay the redo log from the last database checkpoint (typically 5 minutes)
and confirm that all changes have been applied, before making the database
available for operations. _This reduces database restart times to less than 60
seconds in most cases_. Amazon Aurora moves the buffer cache out of the
database process and makes it available immediately at restart time. This
prevents you from having to throttle access until the cache is repopulated to
avoid brownouts."

[https://aws.amazon.com/rds/aurora/faqs/](https://aws.amazon.com/rds/aurora/faqs/)

~~~
rpedela
Also from the FAQ (you are both right)

=========================================================

Q: What happens during failover and how long does it take?

Failover is automatically handled by Amazon Aurora so that your applications
can resume database operations as quickly as possible without manual
administrative intervention.

If you have an Amazon Aurora Replica, in the same or a different Availability
Zone, when failing over, Amazon Aurora flips the canonical name record (CNAME)
for your DB Instance to point at the healthy replica, which is in turn is
promoted to become the new primary. Start-to-finish, failover typically
completes within a minute.

If you do not have an Amazon Aurora Replica (i.e. single instance), Aurora
will first attempt to create a new DB Instance in the same Availability Zone
as the original instance. If unable to do so, Aurora will attempt to create a
new DB Instance in a different Availability Zone. From start to finish,
failover typically completes in under 15 minutes.

Your application should retry database connections in the event of connection
loss.

------
plunchete
No more VACUUM then?

~~~
awsdavew
Vacuum is still there. Though in some cases it is significantly faster.

------
Dunedan
URL of the announcement: [https://aws.amazon.com/blogs/aws/amazon-aurora-
update-postgr...](https://aws.amazon.com/blogs/aws/amazon-aurora-update-
postgresql-compatibility/)

~~~
sctb
Thank you, we've updated the link from
[https://aws.amazon.com/rds/aurora](https://aws.amazon.com/rds/aurora).

