"...8-node DC1.8XL Amazon Redshift cluster for the tests."
Well, yeah. That's 28,108.80 a month if you're running queries on demand and don't want a delay/coordination in Amazon instance creation/destruction.
BQ may or may not be as fast, but it's truly a managed service; I give it our data and it just works. I don't have to worry about instances, boot up time, maintenance, hourly costs, etc. It's silly to focus just on query speed when there's a whole layer of management and cost that comes with it.
maybe i'm completely out of touch, but i'm really wondering who can afford this kind of stuff without $20M series A money in the bank. and i'm also wondering what they're going to do when they run out of that money and have zero database expertise in-house because they outsourced everything to amazon.
Maybe I'm jaded, but 330k a year or so for the very backbone of your entire real time query analytics infrastructure just doesn't seem unreasonable? This is highly specialized software, tuned and architected for the purpose of running relatively complex analytics queries and aggregates fast enough to allow interactive real time exploratory analysis.
I dont think it's insane to believe that a profitable mature data informed business should be expected to spend 1/3 of a million a year for the ability to have all that data live and available without deep latency.
I can also agree that this is quite a bit for a startup, but if the unit economics of the startup require that this kind of data be available for interactive exploring, there's going to be a deep challenge down the road in scaling it I think?
To your point, if a startup runs through 20 million and their biggest problem is paying AWS bills, then they are probably a failed startup and folks should move on towards whatever next exciting thing is on the horizon.
there are DBA services that do an excellent job for a fraction of that price. they also do AWS stuff too.
full-time DBA's work for either enormous corporations, or database consulting firms. it's very rare you will see some random small-medium company with even one DBA on payroll.
I give you just one example: a security vendor collects multiple terabytes per day worth of antivirus data and sells the insight that can be gathered from this to large enterprises to support their security operations. I do not know exactly how much they make out of this but is guess it is in the 200K-500K / year / customer range.
Startups on the other side, rarely have the amount of data that could not be hosted on anything for more than 1000-2000 USD/month.
edit: My original comment's data was misleading due to conversion error. I edited out the comment to avoid confusion for anyone else who might not spot the error and be misinformed. Thank you for those who pointed it out!
As stated, on demand pricing was used. Both products have special pricing at higher usages and flat-pricing tiers. It would start to get compilicated to compare them at that point.
I figured. It's easy to forget that there's really no international/intercultural agreement on number punctuation, particularly combined with a service that wants to price down to tenths of cents.
You're math is wrong - DC1.8XL costs $4.80 per hour, not $4800 (which would be ridiculous pricing).
8 instances of DC1.8XL = $38.4 which means you can run the redshift cluster for 28 hours (edit: for $1100, which is the cost of scanning 220TB of data with BQ, used in previous comment).
Redshift does win on price when you are continuously running queries and scanning entire datasets, although there is enterprise pricing available for BQ if you hit a certain amount.
Completely correct! I apologize, used to seeing a different notation and the extra 0 in 4.800 threw me off. I removed the misleading data because judging by the points, not everyone caught it.
That is right. We had ~100K / month with on prem Hadoop (energy, networking, OPS, enterprise support, etc. included) that could have been moved to Redshift to save money. However, when we did the comparison a different cloud based solution came out as the winer for our DWH needs. The point is, showing a single number without showing how much it would be with other solutions is pointless. There are several companies that can easily afford a ~29K/month DWH in the cloud.
If you can't (or don't want to!) afford that kind of money for data analytics, please consider giving the FOSS alternative EventQL [0] a try some time.
It's super simple to set up and tries to be efficient on commodity hardware, so you can run large clusters (>100TB scale) for a couple hundred dollars a month.
How fast are group bys? Like say I have 120 billion rows, 25 not-sparse columns, and want to group by between 2-20 columns (5 of which are varchar), aggregating the other 5 columns?
What kind of hardware would I need to do that interactively? Or consistently sub-10 second, with 100s of queries per minute.
I have built a thing on Redshift that can do some of this, but it has been new territory for me and I am not sure I've done it "right". Constantly looking for alternatives.
What you're asking for is just really difficult. Even with a fairly good compression ratio (e.g. 57 bits / tuple, which I chose at random but is similar to what's achievable on real data according to http://i.stanford.edu/~adityagp/courses/cs598/papers/constan...) you're talking 83.4 GB / s of read memory bandwidth just to read the tuples at all within 10 seconds for a single query. The state of the art in terms of what you're likely to be able to actually buy (IBM Power8 machines) can do about 91.5 GB / s. in Stream Triad benchmarks. That's still nowhere close to supporting 100s of queries a minute (more like 6 or 7) and you haven't even started calculating anything yet (Triad is pretty simplistic). It also assumes you can actually achieve that data rate while keeping everything in memory (which is why GPUs probably won't help much for your use case, despite NVLink; they don't have enough RAM).
Your biggest issue with aggregation / group by is going to be the memory bandwidth to/from a hash table to store all the results. Once the hash table no longer fits in L3, its access time will rise dramatically, so your query's performance will depend heavily on how many buckets you need (if you're grouping by 2 columns, maybe not that many; if you're grouping by 20, you'll probably have almost no buckets with more than one entry). Another potential issue is going to be having to look up the values for that column in a hash table if they are highly compressed (since you need to perform aggregation on them, presumably something like sum; if it's count, this doesn't matter).
If you can find a total attribute order such that the columns you group by are always to the left of the columns you aggregate by, you can sort the rows to enable efficient delta encoding; you can also then perform aggregation in the same order as your scan, which eliminates the hash table lookup problem for output (not input, though). You can also pre-materialize the results for common subsets.
To do this with multiple queries at once, you'd probably need to batch query execution (because of the memory bandwidth issue I alluded to earlier). While the data access requirements would remain similar on read, they'd get worse and worse on write (again, depending on how many buckets you had and how cleverly sorted your data were).
Alternately, you could get a bunch of Power8s (or commodity machines, but to maximize bang for your hardware buck you really want stuff with tons of memory bandwidth) and give each of them a slice of the data (but still apply all the above optimizations). The commodity version of this is the Redshift solution. If you went this route, you could also look at specialized solutions like GPUs with NVLink or the KNL Xeon Phis, which have "fast" memory with tons of extra bandwidth which help mitigate the aforementioned hash table / query result access costs.
I still haven't talked about how you're supposed to actually get the results back. If you're trying to do ethernet through anything commodity you're going to be very limited in terms of data rate. Even 100 Gbps Infiniband only gets you 12.5 GB/s out, and even if you hook two of those up to each machine you're still only at 25 GB/s out. So either you get even more machines, your clients process the data on the machine, or you have to limit the output somehow... and if you're thinking "we'll sort it!" guess what that's going to be bound by (unless you can store the input data presorted)? Probably memory bandwidth (assuming radix sort)!
tl;dr One way or another, you're paying out the nose to satisfy the requirements you just outlined. Also worth noting that you're paying for this read performance on write.
Using a column store and doing a bunch of pre-aggregation is the only way we've been able to to come close to these requirements, but I keep hoping there is a system that will do what we've done without having to write code. Trading space for computation is the only thing that has worked reliably.
We also have the benefit that most queries just want a small subset of time and have things sorted to take advantage of that. But occasionally people want to do something over a large range and if isn't a set of group-bys we've pre-aggregated then they just have to be patient.
Have you tried this on BigQuery yet? It's built for this kind of extremely large dataset.
You can also look at MemSQL for a distributed relational database with a columnstore. Run enough nodes and you might be able to hit your performance goals.
BigQuery makes it pretty easy to calculate how much you'll spend based on your bandwidth needs; unfortunately, if you do the math for the above use case, the answer is pretty discouraging... $5 / TB data processed, and the size in this context is explicitly the uncompressed size. Even if we're extremely generous and assume 4 bytes / column (almost certainly an underestimate given how many bytes they reserve for integers and timestamps), that is potentially 12 TB for the database, so you're paying $60 for a single query that hits the entire table. If you have 500 queries per month that hit the entire table, you're already paying as much as you would be for Redshift on 8 nodes, without even looking at any of the other queries.
The flat rate may improve things dramatically, of course, but the documentation's ambiguity about what you get with a "slot" makes it hard to say. And none of this is taking required bandwidth into account, because AFAICT there are no promises made on query response time... so I don't know if any of this would satisfy the 10 second requirement.
Either way, no matter how Google, MemSQL, or anyone else might try to satisfy these requirements, they can't get around the required hardware costs. At best, they can amortize them by buying in bulk and partitioning all their clients across lots of servers.
I don't work for any of these database providers, BTW, or use any of their products; I have no skin in this game. And I'm not even saying that paying $60 for that kind of query is necessarily a bad deal (when you consider what may be required under the hood). I'm just saying if you're looking for a cheap solution here you're not going to find it.
> I'm just saying if you're looking for a cheap solution here you're not going to find it.
That's a given. I think in this context the user was asking how to meet performance goals with cost possibly secondary or not a concern... and in that case BQ has proven to be incredible at churning through large datasets within seconds.
Hadn't heard of EventQL before. Took a look at the documentation, so I have a brief idea of what it offers and how it works. But I couldn't find anything in way of performance numbers.
While I might try it out myself to see how well it performs, it would be nice if some figures were readily available.
I've been keeping an eye on Postgres-XL and CitusDB for distributed SQL. Would be interesting to compare.
Running an open source stack means you're not completely at the mercy of someone else. I'd cite Windows 10 desktop editions as a paragon of "you buy it, but you don't own it, then you suffer" proprietary products, but it's not a server component. So I have to cite FoundationDB, the proprietary database, which was seriously awesome, but so awesome that the downloadable binaries vanished overnight.
I have at least the assurance that such a thing cannot happen with Postgres (there are a multitude of for-profit companies working on it full-time, in addition to pro-bono community contributors), or with Apache Cassandra; and it's a similar assurance that keeps users of RethinkDB able to continue to operate despite the company being bought and folded.
----
Other advantages of open source: the very freedom to inspect and modify the software we run. This freedom is how OpenResty was born. In fact, Postgres itself comes from another GPLed database.
On a minor note: this freedom is what allowed us at my last job to create a patch for an internally required function in Nginx, in ten minutes. Had Nginx been closed source, we'd have had to request the makers, wait days/weeks/months, and if accepted they'd include it.
Before this thread turns into a vim-v-emacs-esque flame war.
I really think it's a good thing that AWS and GCP are punching each other in the cloud data warehousing market. It means that the market is maturing, and we are all benefiting from their arms race against each other.
I am of the opinion that Redshift and BigQuery are philosophically different enough that performance differences, while important, shouldn't be the deciding factor. I've written about this in a blog post awhile back, and it might be relevant for folks weighing their options
I agree that they were both created with different problem domains. I mainly take issue with the fact that they selected a single query in a large benchmarking suite and used that to claim that BigQuery was categorically better than redshift. Hell, I actually really like BigQuery! I do find the pricing for queries a tad opaque and difficult to estimate... and the variability in performance is fine for my pet projects but I imagine a larger enterprise would prefer something more predictable. I agree with your statement that they are two very very different products. I enjoyed reading your post.
Yeah, this is a great move. That said, again, here's the philosophical difference: with BQ, you need to "Contact your sales representative if you are interested in flat-rate pricing". There's nothing wrong with this, but it reflects their philosophy that compute should be something pay-per-use, not pay-to-procure.
Again, I expect this "talk to sales" part to change in the future as BQ matures, but I imagine this kind of CIO friendly thinking to be new (if not de-prioritized) at Google.
You're still dealing with query slots though -- which remains difficult to estimate. As far as I know the only way to figure that out is to actually run the query and then check how many slots it used. Am I wrong? I'm certainly not a BigQuery expert.
If you're downvoting do you mind leaving a comment on why I'm wrong? I'm interested in knowing more. The documentation for BigQuery doesn't really explain how slots are allocated and what they're capable of.
When you promote, or generally talk about a product, it is good manner to disclose your affiliation to it.
Nonsense. In what way does this help the reader? Facts are facts, whether they come from a Google employee or a clown. He/she didn't share an opinion that needs to be taken with a grain of salt.
the pricing models for bigquery and redshift are different enough that it is hard to compare on price. redshift is fixed cost (hourly per node like ec2) while on bigquery you pay proportional to data scanned during queries
if you do only occaisonal queries on mostly static data bigquery is probably fine but redshift is a fraction of the cost for more frequent use
Yeah, this didn't debunk much of anything (to me) and the "caveats" at the end were laughably minor. I'm not sure why they listed them other than to say "but they aren't ANSI SQL compatible!" as FUD.
+1. They'd have a much stronger case if they didn't drag in silly complaints such as "[Google BQ] does not support the ANSI SQL syntax of 'substring'. Need to change to 'substr'".
It might seem petty, but I can't see how it hurt their case. If you're migrating hundreds of queries or reports from a local SQL environment to the cloud, being 100% query compatible is nice.
Kinda funny that a +1 with no verbiage is still rated.
I will note I am enjoying reading these comments especially the ones that have real data points, real implementations and considerations of database structures/architectures on both of the products vs. gripes of the brands.
In order to run the benchmark they had to modify the SQL in it. I much prefer it when a benchmarking exercise is sufficiently transparent for another party to come along an replicate the result.
That implies that they should provide details on all SQL changes that they made. Some of the phrasing was a little petty - BigQuery in “Legacy SQL” mode supports this but then joins don’t really work - but being clear about what they had to change seems totally appropriate to me.
My team uses AWS for all of our servers, CDN, SQL and cache boxes but use BQ for all of our telemetry data - about 20 billion metrics a month.
Not having to manage a redshift cluster and just let BQ do all the work for us is worth the query time; even then, most of our complex queries run sub 30 seconds over hundreds of GB. If your data can be queried using a date-range then use date-partitioned tables. There is a 1000 table query limit so there are some pit-falls but BQ makes it easy for you to aggregate data into weekly and monthly tables to query against.
Note: Exasol has been dominating TPC-H for a while but it's kind of werid they don't publish much about what's under the hood (unlike Actian Vectorwise, for example, who has a huge library of amazing papers)
Yeah, and all queries. Good stuff. It'd be nice to have some kind of instructions to reproduce, though. And some explanation on the technology behind it. For example, do you guys scale indexed search or is it just partitioned without indexing?
Present the numbers. Show the trade-offs involved. Any good engineer will then be able to make up their own mind. The narrative was somewhat defensive for benchmarking analysis.
I don't have a pony in this race since either one at large enough scale will cost an arm and a leg and both seem to be within same order of magnitude with a constant factor here and there.
> You can’t just cherry-pick the one query where a given product is the fastest. The workloads in the database domain have broad requirements
Well you can if that test matches the use-case of the database. Big Data databases are not like transnational databases, tend to perform column oriented analytics and tend to not join very well (they may lack indexes altogether).
I find this post to be more misleading than Google - he seems to be conceitedly ignoring the fact that many specialised databases exist that do certain things really well - and selecting tests that match their use-case is entirely appropriate.
I'm primarily trying to point out that google's repeated claims that their performance on TPC-H #22 means that BQ is categorically better than Amazon Redshift are misleading.
Also I think you meant transactional not transnational
I'm not the parent poster, but I agree with his/her sentiment.
I understand what your purpose was, but the blog entry would have felt more balanced if you'd said something like:
It's true that for a small subset of queries, BigQuery out-performed RedShift. If your workload is similar to those queries then BigQuery may be a good match for you, but our testing shows that for general purpose datawarehousing / analytics workloads, RedShift is the better performing database and would be more suitable for most customers.
Seems that Periscope Data Research from 10/21/2016 agrees with the article's premise. Data driven information is always best when making any comparisons, and since these data points are from a 3rd party I figured this article would be great to share:
https://adtmag.com/articles/2016/10/21/periscope-touts-redsh...
We use BigQuery after trying all other options including running our own VMs with database software, 3rd party data vendors and the big managed cloud services.
BigQuery's no-ops model makes a huge difference. It saves a lot of effort when all you have to do is just load up your data and run queries, no other cluster starting, sizing, tweaking or maintenance necessary.
It has real-time streaming input, nested/repeated records, powerful RegEx support and UDFs so you can run some really complex queries that you can't do with traditional SQL. It also has the cheapest storage at $0.01/gb after 90 days. They even have DML statements in beta now to support update/delete statements.
If you have lots of data to store (TBs to PBs), which can archived by time or other dimension as it gets older, but also needs to be available at any time, and have a decent amount of queries or but also need to run massive scans, and don't need sub-second response times, and want really complex query logic, BQ wins.
We use reserved Redshift instances for close to 3 years now. There were a few moments that we were slightly screwed by new features and bugs in Redshift, but most of the time, we don't need to deal with any maintenance, and because we are fully on AWS, Redshift works way better with other systems we build or use (we have a few use cases of bigquery). We do have lots of regular queries running every hour, and we did lots of optimizations on Redshift. Overall, we are happy with Redshift.
I think in term of EDW solutions, it is really not a one size fits all situation. I am happy for the people (especially small companies who can't afford running Redshift 24/7) who went with BigQuery, but I am also happy that we chose Redshift and so far, it has been great.
Performance aside, we've found BigQuery to be far cheaper than Redshift.
With BQ you pay for the amount of data that flows through the system, as opposed to how many instances are running with Redshift.
We also don't really need Redshift running all the time so something like BQ or Azure SQL Datawarehouse where you can pause and scale up/down fits our usage patterns and budget much better.
Just FYI you can pause and scale with Redshift as well... but if you query very infrequently (or low volume) cost structure of BQ might be on your side.
We use Amazon Redshift a lot. The biggest pain point is its lack of elasticity and strong coupling between compute and storage. You can see this pattern emerging in warehouses from Azure and Snowflake. I fear that Redshift will never get there because it's based on old Paraccel tech that was never really designed for the cloud to begin with.
Is Redshift worth it? Yes, it's a powerful EDW system. That said, it's going to cost you more than BQ because of always-on nodes.
Is it better than BQ? It really depends on your analytics workload.
It would be great if AWS would publish the code necessary to reproduce this benchmark. They mentioned applying sort and dist keys; it matters A LOT how these are chosen. For example, if you know in advance how you're going to join, you can distribute both tables on the foreign key column so the join is a local operation. This isn't always an option in real data warehouses, where you do lots of different joins on the same tables. So it represents an opportunity to "cheat" the benchmark. It's also important whether they used BigQuery partitioned tables, which are the equivalent of SORTKEY in some ways.
Having said all this, my company is a fully-managed data pipeline that supports both data warehouses, and we find they both work extremely well for our customers real datasets.
George - fan of Fivetran. What, in your opinion, are common cases where customers choose BQ over Redshift? Or is it really a function of which cloud you're on?
If you are already familiar with one, that is actually a really good reason to use it. If you store a lot of data but rarely query it, BQ will be WAY cheaper because you pay for compute and storage separately. On the other hand, you will never see sub-second queries in BQ.
Another option absolutely worth checking out is Snowflake, which compromises between the approach of BQ and Redshift in a very interesting way.
Would have been much nicer to read if it wasn't written in such tactless way. Could have stayed classy, but they chose not to, and it came off as petty. For me at least.
While their raw benchmark data may be ok, the summary chart makes no sense. They calculated the average and median runtime of all the queries, without first normalizing the results, despite the fact that there is a very huge runtime variance between these queries. Therefore the final number comes mostly from a few most costly queries and almost totally ignores the others.
Google is pretty transparent about the underlying technologies such as Dremel X, ColumnIO, Capacitor, etc. Even if there is no source code, there is quite a bit in the form of papers, talks, peer OSS projects (e.g. Apache Drill), etc.
But I can't say the same about RedShift, it's a complete black box. (Other than that it's a mod of an ancient version of Postgres)
No link to the original comparison by Google? It was only presented on a private event?
Well, it's impossible to know the context in which this comparison was made. BigQuery is very different from the rest of the data warehouse solutions, I wouldn't think a standard benchmark suite would be appropriate for it.
95 comments
[ 2.9 ms ] story [ 181 ms ] threadWell, yeah. That's 28,108.80 a month if you're running queries on demand and don't want a delay/coordination in Amazon instance creation/destruction.
BQ may or may not be as fast, but it's truly a managed service; I give it our data and it just works. I don't have to worry about instances, boot up time, maintenance, hourly costs, etc. It's silly to focus just on query speed when there's a whole layer of management and cost that comes with it.
maybe i'm completely out of touch, but i'm really wondering who can afford this kind of stuff without $20M series A money in the bank. and i'm also wondering what they're going to do when they run out of that money and have zero database expertise in-house because they outsourced everything to amazon.
I dont think it's insane to believe that a profitable mature data informed business should be expected to spend 1/3 of a million a year for the ability to have all that data live and available without deep latency.
I can also agree that this is quite a bit for a startup, but if the unit economics of the startup require that this kind of data be available for interactive exploring, there's going to be a deep challenge down the road in scaling it I think?
To your point, if a startup runs through 20 million and their biggest problem is paying AWS bills, then they are probably a failed startup and folks should move on towards whatever next exciting thing is on the horizon.
On the other hand the Redshift cluster doesn't run itself, despite what Amazon says. You still need at least a part time DBA guy.
And as long as you were not into GPUs you could get some really decent HP servers at somewhere around $2000 - $7000 depending on your storage needs.
full-time DBA's work for either enormous corporations, or database consulting firms. it's very rare you will see some random small-medium company with even one DBA on payroll.
Startups on the other side, rarely have the amount of data that could not be hosted on anything for more than 1000-2000 USD/month.
I don't work for or have any relationship with either, but I do use Redshift.
Is this a typo? A single DC1.8XL is $4.80/hour. 8, like the article, would be $38.40/hour.
Numbers on https://aws.amazon.com/redshift/pricing/ used.
dc1.8xlarge - $4.800 per Hour
That's four point eight dollars, not four thousand eight hundred.
8 instances of DC1.8XL = $38.4 which means you can run the redshift cluster for 28 hours (edit: for $1100, which is the cost of scanning 220TB of data with BQ, used in previous comment).
Redshift does win on price when you are continuously running queries and scanning entire datasets, although there is enterprise pricing available for BQ if you hit a certain amount.
https://cloud.google.com/bigquery/pricing#flat_rate_pricing
That is right. We had ~100K / month with on prem Hadoop (energy, networking, OPS, enterprise support, etc. included) that could have been moved to Redshift to save money. However, when we did the comparison a different cloud based solution came out as the winer for our DWH needs. The point is, showing a single number without showing how much it would be with other solutions is pointless. There are several companies that can easily afford a ~29K/month DWH in the cloud.
If you can't (or don't want to!) afford that kind of money for data analytics, please consider giving the FOSS alternative EventQL [0] a try some time.
It's super simple to set up and tries to be efficient on commodity hardware, so you can run large clusters (>100TB scale) for a couple hundred dollars a month.
[0] https://eventql.io/
DISC: I'm one of the EventQL authors
What kind of hardware would I need to do that interactively? Or consistently sub-10 second, with 100s of queries per minute.
I have built a thing on Redshift that can do some of this, but it has been new territory for me and I am not sure I've done it "right". Constantly looking for alternatives.
Your biggest issue with aggregation / group by is going to be the memory bandwidth to/from a hash table to store all the results. Once the hash table no longer fits in L3, its access time will rise dramatically, so your query's performance will depend heavily on how many buckets you need (if you're grouping by 2 columns, maybe not that many; if you're grouping by 20, you'll probably have almost no buckets with more than one entry). Another potential issue is going to be having to look up the values for that column in a hash table if they are highly compressed (since you need to perform aggregation on them, presumably something like sum; if it's count, this doesn't matter).
If you can find a total attribute order such that the columns you group by are always to the left of the columns you aggregate by, you can sort the rows to enable efficient delta encoding; you can also then perform aggregation in the same order as your scan, which eliminates the hash table lookup problem for output (not input, though). You can also pre-materialize the results for common subsets.
To do this with multiple queries at once, you'd probably need to batch query execution (because of the memory bandwidth issue I alluded to earlier). While the data access requirements would remain similar on read, they'd get worse and worse on write (again, depending on how many buckets you had and how cleverly sorted your data were).
Alternately, you could get a bunch of Power8s (or commodity machines, but to maximize bang for your hardware buck you really want stuff with tons of memory bandwidth) and give each of them a slice of the data (but still apply all the above optimizations). The commodity version of this is the Redshift solution. If you went this route, you could also look at specialized solutions like GPUs with NVLink or the KNL Xeon Phis, which have "fast" memory with tons of extra bandwidth which help mitigate the aforementioned hash table / query result access costs.
I still haven't talked about how you're supposed to actually get the results back. If you're trying to do ethernet through anything commodity you're going to be very limited in terms of data rate. Even 100 Gbps Infiniband only gets you 12.5 GB/s out, and even if you hook two of those up to each machine you're still only at 25 GB/s out. So either you get even more machines, your clients process the data on the machine, or you have to limit the output somehow... and if you're thinking "we'll sort it!" guess what that's going to be bound by (unless you can store the input data presorted)? Probably memory bandwidth (assuming radix sort)!
tl;dr One way or another, you're paying out the nose to satisfy the requirements you just outlined. Also worth noting that you're paying for this read performance on write.
Using a column store and doing a bunch of pre-aggregation is the only way we've been able to to come close to these requirements, but I keep hoping there is a system that will do what we've done without having to write code. Trading space for computation is the only thing that has worked reliably.
We also have the benefit that most queries just want a small subset of time and have things sorted to take advantage of that. But occasionally people want to do something over a large range and if isn't a set of group-bys we've pre-aggregated then they just have to be patient.
You can also look at MemSQL for a distributed relational database with a columnstore. Run enough nodes and you might be able to hit your performance goals.
The flat rate may improve things dramatically, of course, but the documentation's ambiguity about what you get with a "slot" makes it hard to say. And none of this is taking required bandwidth into account, because AFAICT there are no promises made on query response time... so I don't know if any of this would satisfy the 10 second requirement.
Either way, no matter how Google, MemSQL, or anyone else might try to satisfy these requirements, they can't get around the required hardware costs. At best, they can amortize them by buying in bulk and partitioning all their clients across lots of servers.
I don't work for any of these database providers, BTW, or use any of their products; I have no skin in this game. And I'm not even saying that paying $60 for that kind of query is necessarily a bad deal (when you consider what may be required under the hood). I'm just saying if you're looking for a cheap solution here you're not going to find it.
That's a given. I think in this context the user was asking how to meet performance goals with cost possibly secondary or not a concern... and in that case BQ has proven to be incredible at churning through large datasets within seconds.
While I might try it out myself to see how well it performs, it would be nice if some figures were readily available.
I've been keeping an eye on Postgres-XL and CitusDB for distributed SQL. Would be interesting to compare.
I have at least the assurance that such a thing cannot happen with Postgres (there are a multitude of for-profit companies working on it full-time, in addition to pro-bono community contributors), or with Apache Cassandra; and it's a similar assurance that keeps users of RethinkDB able to continue to operate despite the company being bought and folded.
----
Other advantages of open source: the very freedom to inspect and modify the software we run. This freedom is how OpenResty was born. In fact, Postgres itself comes from another GPLed database.
On a minor note: this freedom is what allowed us at my last job to create a patch for an internally required function in Nginx, in ten minutes. Had Nginx been closed source, we'd have had to request the makers, wait days/weeks/months, and if accepted they'd include it.
Could you please talk a bit more about your use cases? what domain, volume of data, how you manage updates etc, if it is possible for you to share?
I really think it's a good thing that AWS and GCP are punching each other in the cloud data warehousing market. It means that the market is maturing, and we are all benefiting from their arms race against each other.
I am of the opinion that Redshift and BigQuery are philosophically different enough that performance differences, while important, shouldn't be the deciding factor. I've written about this in a blog post awhile back, and it might be relevant for folks weighing their options
https://blog.treasuredata.com/blog/2016/06/09/redshift-bigqu...
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
Disclosures: I wrote the parent article.
BigQuery just introduced flat-rate pricing for this exact reason: https://cloud.google.com/bigquery/pricing#flat_rate_pricing
Disclosure: I work for Google Cloud
Again, I expect this "talk to sales" part to change in the future as BQ matures, but I imagine this kind of CIO friendly thinking to be new (if not de-prioritized) at Google.
If you're downvoting do you mind leaving a comment on why I'm wrong? I'm interested in knowing more. The documentation for BigQuery doesn't really explain how slots are allocated and what they're capable of.
Disclosures: I don't disclose things when it makes no sense to.
Don't shit on people for being polite.
Edit: That said, it works a lot better on reddit with flairs.
Nonsense. In what way does this help the reader? Facts are facts, whether they come from a Google employee or a clown. He/she didn't share an opinion that needs to be taken with a grain of salt.
if you do only occaisonal queries on mostly static data bigquery is probably fine but redshift is a fraction of the cost for more frequent use
I will note I am enjoying reading these comments especially the ones that have real data points, real implementations and considerations of database structures/architectures on both of the products vs. gripes of the brands.
In order to run the benchmark they had to modify the SQL in it. I much prefer it when a benchmarking exercise is sufficiently transparent for another party to come along an replicate the result.
That implies that they should provide details on all SQL changes that they made. Some of the phrasing was a little petty - BigQuery in “Legacy SQL” mode supports this but then joins don’t really work - but being clear about what they had to change seems totally appropriate to me.
Not having to manage a redshift cluster and just let BQ do all the work for us is worth the query time; even then, most of our complex queries run sub 30 seconds over hundreds of GB. If your data can be queried using a date-range then use date-partitioned tables. There is a 1000 table query limit so there are some pit-falls but BQ makes it easy for you to aggregate data into weekly and monthly tables to query against.
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
See point 9
http://www.exasol.com/en/newsroom/blog/10-questions-the-tpc-...
Note: Exasol has been dominating TPC-H for a while but it's kind of werid they don't publish much about what's under the hood (unlike Actian Vectorwise, for example, who has a huge library of amazing papers)
Because Vectorwise is Marcin Zukowski's brainchild and started with his PhD theses. He's an academic through and through, looks like!
Any chance you could sure a link? I couldn't find this on Google. Thanks!
I don't have a pony in this race since either one at large enough scale will cost an arm and a leg and both seem to be within same order of magnitude with a constant factor here and there.
Well you can if that test matches the use-case of the database. Big Data databases are not like transnational databases, tend to perform column oriented analytics and tend to not join very well (they may lack indexes altogether).
I find this post to be more misleading than Google - he seems to be conceitedly ignoring the fact that many specialised databases exist that do certain things really well - and selecting tests that match their use-case is entirely appropriate.
Also I think you meant transactional not transnational
I understand what your purpose was, but the blog entry would have felt more balanced if you'd said something like:
It's true that for a small subset of queries, BigQuery out-performed RedShift. If your workload is similar to those queries then BigQuery may be a good match for you, but our testing shows that for general purpose datawarehousing / analytics workloads, RedShift is the better performing database and would be more suitable for most customers.
BigQuery's no-ops model makes a huge difference. It saves a lot of effort when all you have to do is just load up your data and run queries, no other cluster starting, sizing, tweaking or maintenance necessary.
It has real-time streaming input, nested/repeated records, powerful RegEx support and UDFs so you can run some really complex queries that you can't do with traditional SQL. It also has the cheapest storage at $0.01/gb after 90 days. They even have DML statements in beta now to support update/delete statements.
If you have lots of data to store (TBs to PBs), which can archived by time or other dimension as it gets older, but also needs to be available at any time, and have a decent amount of queries or but also need to run massive scans, and don't need sub-second response times, and want really complex query logic, BQ wins.
I think in term of EDW solutions, it is really not a one size fits all situation. I am happy for the people (especially small companies who can't afford running Redshift 24/7) who went with BigQuery, but I am also happy that we chose Redshift and so far, it has been great.
With BQ you pay for the amount of data that flows through the system, as opposed to how many instances are running with Redshift.
We also don't really need Redshift running all the time so something like BQ or Azure SQL Datawarehouse where you can pause and scale up/down fits our usage patterns and budget much better.
Is Redshift worth it? Yes, it's a powerful EDW system. That said, it's going to cost you more than BQ because of always-on nodes.
Is it better than BQ? It really depends on your analytics workload.
Having said all this, my company is a fully-managed data pipeline that supports both data warehouses, and we find they both work extremely well for our customers real datasets.
Another option absolutely worth checking out is Snowflake, which compromises between the approach of BQ and Redshift in a very interesting way.
http://www.zdnet.com/article/will-snowflake-spark-a-cloud-da...
But I can't say the same about RedShift, it's a complete black box. (Other than that it's a mod of an ancient version of Postgres)
Well, it's impossible to know the context in which this comparison was made. BigQuery is very different from the rest of the data warehouse solutions, I wouldn't think a standard benchmark suite would be appropriate for it.
Indeed it is, I wonder if that benchmark claim is being taken out of context of that private talk.