Amazon Aurora with PostgreSQL Compatibility | Hacker News Reader

Amazon Aurora with PostgreSQL Compatibility

aws.amazon.com

118 pointsjeffbarr71 comments

I ran the preview for ~2 months on a few instances. No compatibility issues, but we had queries that were significantly slower than on a vanilla Postgres instance and a problem with temp table bloat that never got reclaimed (480G as per \l+, being shown as 1050G billable in the AWS console). The preview product team never got back to us so I'm going to switch back to the regular RDS this week and hope my bill doesn't get hosed by the unreclaimed bloat. Gotta go with the devil I know... ʅ(ツ)ʃ

I'm sorry the product team never got back to you. We'll get back to the email you just sent us on the preview thread.

If you have any issues with your bill, let us know and we'll happily look into it. But I'd rather work with you to figure out what went on and get it fixed for you!

Sure thing, feel free to reply to my email. \l+ shows 485 GB, and hitting temp_bytes from pg_stat_database is 0, but billable space is 1050G. I'd like to promote Aurora to production, but some of my system's 'legacy' queries which live behind an ORM are grinding against the Aurora instances. I haven't had need to really investigate/optimize the queries (or even try to pick apart what the ORM is doing) as the performance on the vanilla Postgres RDS instance wasn't was great, but wasn't problematic either, even on the db.t2.large's I'd use in lower environments vs db.r4.large's I'm using with Aurora. It may be that I am just missing something obvious.

I hope that’s what happens too. Another project I work on switched to Aurora for MySQL and it’s been phenomenal.

Really hoping for a similar experience with Postgres.

Yeah, but AWS has a long history of "yeah, no problem - oops will just take that off your bill" -- so just ask them to do so.

In fact, they are even proactive to the point where they come back and transparently have offered credits once their bills get fully baked.

We had a launch loop happen when an AWS EC2 API went bonkers and so we couldnt query number of running instances, so our balancer logic thought it had none and launched thousands of machines... didnt cost a penny.

To be fair, amazon's actual cost in that case was probably predominately in the human time to review the case and credit the account. I doubt it cost them more than a few pennies in terms of cost of goods sold.

True, but the way they handled it was stellar.

Oh, indeed. The interaction that probably cost them a few cents kept a very profitable customer coming back for more -- makes perfect sense.

Can you test this on the production version? I know that many issues were addressed during the preview period.

=> select aurora_version(); aurora_version ---------------- 1.0.7 (1 row)

Maybe it was solved and I need to build a new instance from snapshot to reclaim the space as an engine upgrade might not have done it. shrug I'll wait for the team to get back to me.

Is this going to support IAM authentication like Aurora for MySQL does?

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingW...

The article says, "The [new instance class] db.r4.16xlarge will give you additional write performance." Does anyone know why this is the case? I thought one of the distinguishing characteristics of Aurora was that the read and write performance of its proprietary storage layer was independent of both the instance class of your database server and the size of your database.

It is true that Amazon Aurora is not limited by the bandwidth of the storage system, as it is huge and distributed across hundreds/thousands of nodes across an entire AWS region. And storage for each database instance is indeed only limited to 64TB, far above what most people need. But each single instance can still have limitations, such as CPU horsepower, memory, etc. The r4.16xl has 25Gbit networking, as opposed to the r4.8xl, which has 10Gbit network. So if your workload is write-intensive, such as streaming or IOT data, you will find the r4.16xl can have significantly better write performance. https://aws.amazon.com/ec2/instance-types/ I hope this helps!

Wanted to use this, but doesn't support small enough instance types.

Same here. Odd choices, but I'm assuming there's a reason for it.

Just a guess, but I would bet that they are looking for the people who want aurora+postgress on larger workloads (ostensibly housed in far fewer accounts/support-blast-radius) than to offer shards to thousands of one-off testers/smaller companies that may inundate the bandwidth of the actual human staff.

I thought the same, but the Aurora MySQL does allow smaller instances. So, waiting as well for smaller instances on Aurora Postgres...

Which particular types and combination of CPU/RAM would you like?

At a minimum, I'd like to see something half the size of the r4.large. The current r4.large is definitely bigger than I'd want to use for an experiment with a few users.

Jeff, that makes sense. We're working on it. Thanks for the information!

What instance types would you like to see? T2s? If so, which ones?

My team was hoping for db.t2.small and db.t2.medium so we could compare against similar sized regular Postgres instances.

Thanks for the replies - we're working on smaller instances, though we don't have a date yet!

We are also looking to test the small and medium t2s versus PostgresRDS on small and medium.

Where I work we currently run 2 RDS PostgreSQL Instances in production, one on T2.small and the other T2.medium.

We never run out of CPU credits. We are dying to move to Aurora but the current instance sizes prevent us :( until we fully move away from SQL Server where our load on PostgreSQL will increase, its a huge cost for 0 gain right now.

i have a postgres running on a t2.small in RDS right now

Does Amazon contribute cash to the open source projects that it turns into services?

Amazon has made contributions to open source in many ways, including bug fixes, spending hundreds of thousands/year to sponsor conferences, and even giving the PostgreSQL community a free testing account to run regression, soak, or functionality test suites on AWS for free. So yes, Amazon contributes cash to open source projects, at least to PostgreSQL, which we find to be a wonderful community that we are proud to be part of.

How much cash did AWS give to Postgres?

Amazon is a real parasite on open source projects, it never gives back.

Nope. For an example look st elasticsearch. Pretty predatory.

Uh, I wouldn't call it predatory. It's a shame they don't contribute more and they could do a lot better, but turning projects into services massively contributes to their ecosystem.

Maybe predatory is a strong word. Many OSS projects would characterize them that way though, I think. Your point about impact to the ecosystem is a fair one, but in some cases it is more co-opt rather than contribute.

I think a lot of that is perception. I know the AWS RDS team has a pretty close knit relationship with the Postgres team, it's just not super obvious.

Glad to see this is GA now. The cost benefits and monitoring benefits are huge for the consumer. So far, I haven't hit any compatibility issues either.

Is this a big deal? What are the noticeable offering here.

Aurora is very interesting especially in terms of removing storage management from the equation. I just wish they (and the regular postgres RDS offering) had support for TimescaleDB (http://www.timescale.com).

Mind givin some use case scenarios for timescale that you really like? (aside from what one may think obvious)

I want to replace InfluxDB with it. Here's what we gain:

- Postgres' reliability. Influx is awful and I absolutely do not trust it; I've had far too much data loss and consistency issues with it.

- Postgres' internal tooling. Simple things such as "What is my largest measurement on disk" are not possible in Influx

- Simplified stack. Timescale makes good on that promise: We no longer would have to ship influxdb libraries for internal apps. Permissions are simplified as well.

- Hosted, managed metrics. Our InfluxDB instance is managed by ourselves and is a source of issues. Our RDS instance, comparatively, has always purred.

- More advanced operations, including the full power of Postgres user-defined functions. Influx doesn't even support dividing one value by another.

- Grafana support! It's finally here!!! :)

I was thinking about the same use-case, but there are features in InfluxDB (downscaling, retention policies) that are cool when dealing with large amount of data. If Timescale bridges the gap, I would be happy to give a try...

(CEO TimescaleDB) We already allow for efficient manual deletion of old data[1] (2000x faster than PostgreSQL)[2] and rollups via standard PG aggregates. Automatic retention policies and downsampling are coming soon.

But as one of the commentors above points out, we benefit from the reliability (and broad ecosystem) of PostgreSQL, which has allowed various companies to easily deploy us in production with minimal integration efforts.

[1] http://docs.timescale.com/latest/api/data-retention

[2] https://blog.timescale.com/timescaledb-vs-6a696248104e

Weird, I've had really great experience with InfluxDB. I literally couldn't be happier. The early days were rough, 18 month ago or so. In the last year, it's been very solid.

I store metrics (largely from telegraf) on system performance from more than 100 physical/virtual machines into it, and have full resolution data since Feb 2016 stored in around 100GB across 3 Influx instances (dev, stg, prod).

Compared to our previous collectd/graphite infrastructure, this is a huge win. Much lower system utilization, easier to add custom metrics.

Don't get me wrong, I love Postgres, been using it since '96, but Influx/Telegraf/Grafana has given me the ease of Munin with the high resolution data of collectd while also providing low overhead and low maintenance.

It's certainly gotten better but I would not call it a great experience. Right this minute for example, my metrics machine's disk is filling up at higher than usual rate and I have no knowledge of what is causing this to happen, no tools to figure it out.

A week ago, I tried to set up a "successful writes per second" metric. I have a "time since start", and "successful writes since start", but no way to operate one with the other.

The HTTP input API is dog-slow with HTTPS, and the UDP input API requires a separate socket for every database.

It's lots of things like these...

"successful writes since start" has a time value associated with it, right? I think what you want is not to correlate "time since start" and "successful writes since start", but to just do a derivative of the "successful writes since start" metric. This is just like the networking stats on Linux hosts.

I have something like: FROM default net WHERE host =~ /$Host$/ SELECT field(bytes_recv) mean() derivative(1s) alias(In) GROUP BY time($interval) tag(host) tag(interface) fill(null) FORMAT AS Time series ALIAS BY [[col]] [[tag_host]]/[[tag_interface]]

That's in my Grafana query.

Aside: I sent the TimeseriesDB reference to a friend that just a couple days ago asked about options for storing metrics on storage servers. I had told him about my experiences with Influx, had mentioned Prometheus and also Sentry has some sort of internal TSDB they do on Postgres. In reply to the TimeseriesDB reference, he replied that he was all about InfluxDB now.

Came here to say the same - timescale support would be awesome!

Didn't know about timescale DB but it looks awesome and sounds amazing for what my team currently does.

It's a specially designed networked filesystem as the storage layer of a hosted postgres DB. From what I've read it has some interesting properties like built-in redundancy, expandable storage, you can instantly spin up replicas and copy-on-write clones of the db, plus they claim 2x+ performance compared to vanilla postgres. From what I've gathered it's kind of similar to the Spanner db that google released a few months ago in that there's a pretty tight integration of the software with the hardware & network layer that they control, which is what makes some of these improvements possible.

The other thing that I find promising, is RDS has always felt kind of awkward in terms of what's still vanilla postgres that you're on your own for and what AWS does for you. I haven't tried Aurora yet but it seems like it might feel more AWS-native (things like having better performance monitoring tools and using IAM roles to control the db users).

one cool feature is of this release is the Performance Insights dashboard which makes understanding and analyzing performance much easier https://www.youtube.com/watch?time_continue=1&v=4462hcfkApM

Nice. Does anyone know their policy about following major new versions of Postgres?

I want to launch an app using the new partitioning features of Postgres 10. I hope Aurora Postgres won't be indefinitely tied to a specific major version, as is the case with Redshift.

Judging by MySQL Aurora (which is still on MySQL 5.6, when 5.7 released 2 years ago) it will be a while.

Any ETA on making PostreSQL Aurora a HIPAA eligible service? We are currently using MySQL Aurora and would like to switch to PostreSQL, but we are storing ePHI, so we can't switch.

According to https://d0.awsstatic.com/whitepapers/compliance/AWS_HIPAA_Co... page 12 the PostreSQL flavor is HIPAA eligible now:

> Customers may use either the MySQL-compatible edition of Amazon Aurora or the PostgreSQL-compatible version as part of our BAA.

But according to https://aws.amazon.com/compliance/hipaa-eligible-services-re... only the MySQL flavor is:

> Amazon Aurora [MySQL]

I assume the latter simply hasn't been updated yet.

Well, the wait is over. Aurora PostgreSQL was added to the BAA today (10/26/17). You can see it on the list of HIPAA-eligible services here: https://aws.amazon.com/compliance/hipaa-eligible-services-re...

Really exciting news! Hope to see it added to the BAA quickly.

Good news! Aurora PostgreSQL was added to the BAA today (10/26/17). You can see it on the list of HIPAA-eligible services here: https://aws.amazon.com/compliance/hipaa-eligible-services-re...

Stay tuned!

Now the wait for our region to gain compatibility.

Which region? We're working very hard to add regions as quickly as possible!

I feel silly asking this, but https://aws.amazon.com/blogs/aws/now-available-amazon-aurora... says "four regions" -- when I go to look at the Aurora price page (https://aws.amazon.com/rds/aurora/pricing/), it lists what looks like more than four.

Which is correct?

What I really want to know is whether I can use this in ap-northeast-1. :)

https://aws.amazon.com/about-aws/whats-new/2017/10/announcin... shows the four regions we are currently available in.

On https://aws.amazon.com/rds/aurora/pricing/, if you go to the "PostgreSQL Compatible" dropdown, you will see those same four regions.

We're working on making that page easier to parse, and the fact that you just ran into this confusion reinforces our committment to fix it - thank you!

Thank you for the understanding response!

I would like to add my vote for looking forward to support in ap-northeast-1 as well, then. :)

Which one?

ap-southeast-2 for us.

waiting for us-west-1 :-)

The one in the cloud!

Anyone know if this supports PostGIS extensions?

They state that Aurora "supports the same set of PostgreSQL extensions that are supported with RDS for PostgreSQL 9.6." Which is a yes. PostGIS version 2.3.2, I believe.

Yes, this release of Amazon Aurora with PostgreSQL compatibility does support PostGIS extensions.

Yes. It does.

@omarforgotpwd, @purphase, @tengbretson: We're working on everything you just asked for - thanks for letting us know they are important! @jsmeaton: Which region?

Little old Australia (ap-southeast-2) would be insanely welcome.

Reply on news.ycombinator.com