Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Amazon RedShift vs. local PostgreSQL

rarestblog.com

Post reply on HN
Print view View original

21–30 of 34 posts

Re: Amazon RedShift vs. local PostgreSQL

#21

Post by rarestblog »

lcampbell wrote: post #20

I really don't understand what's going on here. * You're measuring request latency. What part of that (for RedShift) is due to the network? (EDIT: I re-read and saw you're using `SELECT 1` as a gauge for round-trip latency and subtracting it from the results. Are you only doing this for RedShift, or also for local PostgreSQL? To me, it seems like that heuristic is over broad -- it encapsulates not only network latenc…

* I noted in post that "SELECT 1" was done in 100ms round-trip, so I subtracted that from all points where it was reasonable. (No point to subtract it from 8 seconds).

* Not sure about the index support. Didn't try.

My idea was quite simple. I have some data at work (databases up to 30GB). Sometimes we hope to find something better. The main question was - will RedShift help, will it be radically faster? Will it be radically easier?

The answer for me - no, it won't help in my case, we need that 30GB data in real time, it looks like RedShift is more when you have 1TB+ data. Yes, it is radically easier.

Re: Amazon RedShift vs. local PostgreSQL

#22

Post by iblaine »

What this test is essentially doing is comparing Postgres against a single node of Redshift. It is not surprising that Postgres is faster. But Redshift is not meant to be used on a single node.

What Postgres & Redshift represent are are two different products for two very different problems. Postgres is good for small sets of transactional data like orders in a shopping cart system (less than 1TB). Redshift is good for big sets of data involving user behavior and clickstream analysis (greater than 1TB). I would not want to manage clickstream data on a single instance of Postgres nor would I want to manage an order system in Redshift.

A better test of Redshift would be to see how it compares to Asterdata...particularly with both in AWS. That should be telling.

Re: Amazon RedShift vs. local PostgreSQL

#23

Post by lcampbell »

rarestblog wrote: post #21
lcampbell wrote: post #20

I really don't understand what's going on here. * You're measuring request latency. What part of that (for RedShift) is due to the network? (EDIT: I re-read and saw you're using `SELECT 1` as a gauge for round-trip latency and subtracting it from the results. Are you only doing this for RedShift, or also for local PostgreSQL? To me, it seems like that heuristic is over broad -- it encapsulates not only network latenc…

* I noted in post that "SELECT 1" was done in 100ms round-trip, so I subtracted that from all points where it was reasonable. (No point to subtract it from 8 seconds). * Not sure about the index support. Didn't try. My idea was quite simple. I have some data at work (databases up to 30GB). Sometimes we hope to find something better. The main question was - will RedShift help, will it be radically faster? Will it be r…

Thanks for the reply. I figured for a dataset of that size, the main bottleneck might be not indexing -- maybe RedShift stores row data in a higher-latency medium while keeping indexes in-memory. Curious, I checked the documentation[1] and found this:

> Amazon Redshift doesn’t require indexes or materialized views and so uses less space than traditional relational database systems.

Reading through the rest of their FAQ, it sounds like they echo your conclusion -- RedShift shines the most for use-cases where the dataset is large enough that, to use PostgreSQL, you'd have to shard out multiple instances.

--

[1] http://aws.amazon.com/redshift/faqs/#0030

Re: Amazon RedShift vs. local PostgreSQL

#24

Post by scotth »

amalag wrote: post #12

You need to run this with a column store database like Infobright. Postgres is more of a transactional database, Infobright is suited towards the similar large dataset analytics that this is aimed towards.

Redshift isn't Postgres. It's built on top of Postgres, similar to how Infobright is built on top of MySQL.

Re: Amazon RedShift vs. local PostgreSQL

#25

Post by eksith »

iblaine wrote: post #22

What this test is essentially doing is comparing Postgres against a single node of Redshift. It is not surprising that Postgres is faster. But Redshift is not meant to be used on a single node. What Postgres & Redshift represent are are two different products for two very different problems. Postgres is good for small sets of transactional data like orders in a shopping cart system (less than 1TB). Redshift is good f…

We don't run a shopping cart, but one of our databases at present is at 11.3TB on PostgreSQL 9.1 and we're by no means dealing with small sets. We routinely juggle several Gigs at a time when we need to do analytics. We didn't see a reason to put this on a cloud since bandwidth + electricity is still cheaper for us than bandwidth + storage in the cloud at present.

Re: Amazon RedShift vs. local PostgreSQL

#26

Post by nieksand »

rarestblog wrote: post #21
lcampbell wrote: post #20

I really don't understand what's going on here. * You're measuring request latency. What part of that (for RedShift) is due to the network? (EDIT: I re-read and saw you're using `SELECT 1` as a gauge for round-trip latency and subtracting it from the results. Are you only doing this for RedShift, or also for local PostgreSQL? To me, it seems like that heuristic is over broad -- it encapsulates not only network latenc…

* I noted in post that "SELECT 1" was done in 100ms round-trip, so I subtracted that from all points where it was reasonable. (No point to subtract it from 8 seconds). * Not sure about the index support. Didn't try. My idea was quite simple. I have some data at work (databases up to 30GB). Sometimes we hope to find something better. The main question was - will RedShift help, will it be radically faster? Will it be r…

I don't see your python code defining a distribution or sort key for Redshift which is an important design consideration. (For my own use case of log analysis, I sort on datetime and use an "even" distribution). Also doesn't look like you ran "vacuum" or "analyze" after doing the loads to Redshift. So the query optimizer has no statistics to drive its decisions.

And as others have pointed out, your 30 GB data set is pretty tiny. You could look at some of the in-memory DB options out there if you need to speed things up.

Re: Amazon RedShift vs. local PostgreSQL

#27

Post by zwily »

scotth wrote: post #24
amalag wrote: post #12

You need to run this with a column store database like Infobright. Postgres is more of a transactional database, Infobright is suited towards the similar large dataset analytics that this is aimed towards.

Redshift isn't Postgres. It's built on top of Postgres, similar to how Infobright is built on top of MySQL.

Redshift is built on ParAccel, not Postgres. It speaks the Postgres API, but the storage engine is not Postgres.

Re: Amazon RedShift vs. local PostgreSQL

#28

Post by dragontamer »

rarestblog wrote: post #5
csummers wrote: post #4

I'd like to see some more information about the local setup, including hardware and the postgresql.conf. Otherwise, this tells me very little in terms of comparison.

In anyway this test won't tell you much, just how different systems behave to bigger load. The local setup was quite usual: PostgreSQL 9.2, Mint 13, default conf in VirtualBox in iMac i5 12GB. (read: home computer, no tuning) For me the result is that mostly RedShift is on par with local PostgreSQL, sometimes even winning for Also the big deal was that RedShift scaled linearly.

At least on the small scale, I'd expect realistic "local" hosting to outperform any offering from Amazon.

For example, until your data scales above 20 GB, you'd be able to host it on a $5 SSD-based server from Digital Ocean. Most databases are bottlenecked by I/O. So switching to SSDs gives you the biggest performance boost.

On the high end, Amazon's offer probably will be better. (After all, the major draw to Amazon is "automatic scaling", so that you don't have to worry about Replication or other server administration duties at the high end). But considering how powerful a $5 SSD-virtual machine is today, I think a more realistic test would be with some sort of SSD-based cloud server.

Re: Amazon RedShift vs. local PostgreSQL

#29

Post by monstrado »

I don't think comparing RedShift to Postgres is accurate, RedShift was not designed for transactions, it was designed to store/query billions of rows using a columnar storage format...it's more like an analytic database (Greenplum, Teradata). Also, these databases are designed to scale out, and so you usually don't really see compelling performance gains until you start adding a few nodes to help influence parallelization.

With that being said, I'd be interested to see how RedShift compares to Impala.

Re: Amazon RedShift vs. local PostgreSQL

#30

Post by ramarnat »

crazydoggers wrote: post #10

Data warehousing often involves star schemas, which means lots of joins in your queries. I'd love to see how a real world OLAP tool performs on this.

While you could use Redshift as a source for OLAP, most OLAP tools will have their own data store. But if you are referring to ROLAP, then it can perform well if tuned properly for the star schema. This would include BI tools like Microstrategy and Mondrian with Jaspersoft.

The main issue with Redshift is the lack of multiple sort orders on a table. Take a look at our blog post on first impressions gleaned during the preview. Disclosure: we are one of a couple of systems integrator partners for Redshift.

http://www.full360.com/2013/02/14/aws-redshift-full360-first...

Post reply on HN