Amazon Redshift re-invented
amazon.science
amazon.science
Basically, it worked like this:
- All of our data lived in compressed SQLite DBs on S3.
- Upon receiving a query, Postgres would use a custom foreign data wrapper we built.
- This FDW would forward the query to a web service.
- This web service would start one lambda per SQLite file. Each lambda would fetch the file, query it, and return the result to the web service.
- This web service would re-issue lambdas as needed and return the results to the FDW.
- Postgres (hosted on a memory-optimized EC2 instance) would aggregate.
It was straight magic. Separated compute + storage with basically zero cost and better performance than Redshift and Vertica. All of our data was time-series data, so it was extraordinarily easy to partition.
Also, it was also considerably cheaper than Athena. On Athena, our queries would cost us ~$5/TB (which hasn't changed today!), so it was easily >$100 for most queries and we were running thousands of queries per hour.
I still think, to this day, that the inevitable open-source solution for DWs might look like this. Insert your data as SQLite or DuckDB into a bucket, pop in a Postgres extension, create a FDW, and `terraform apply` the lambdas + api gateway. It'll be harder for non-timeseries data but you can probably make something that stores other partitions.
We had to balance between making the files too big (which would be slow) and making them too small (too many lambdas to start)
I _think_ they were around ~10 GB each, but I might be off by an order of magnitude.
- instead of S3, we now use R2.
- instead of Postgres+Sqlite3, we use DuckDB+CSV/Parquet.
- instead of Lambda, we use AWS AppRunner (considering moving it to Fly.io or Workers).
It worked gloriously for variety of analytical workloads, even if slower had we used Clickhouse/Timescale/Redshift/Elasticsearch.
Granted DuckDB's is in constant development, but it doesn't yet have native cross-version export/import feature (since its developers claim DuckDB hasn't reached maturity to stabilise its on-disk format just yet).
I also keep an eye on https://h2oai.github.io/db-benchmark/ As for Arrow-backed query engines, Pola.rs and DataFusion in particular sound the most exciting to me.
It also remains to be seen how DataBrick's delta.io develops (might come in handy for much much larger data-warehouses).
SQLite -> parquet (for columnar instead of row storage) Lambda -> Worker Tasks FDW -> Connector Postgres Aggregation -> Worker Stage
We run it in Kubernetes (EKS) with auto-scaling, so that works sort of like lambda.
I've been thinking about building systems that store SQLite in S3 and pull them to a lambda for querying, but I'm nervous about how feasible it is based on database file size and how long it would take to perform the fetch.
I honestly hadn't thought about compressing them, but that would obviously be a big win.
* Assuming you can parse faster than you read from S3 (true for most workloads?) that read throughput is your bottleneck.
* Set target query time, e.g 1s. That means for queries to finish in 1s each record on S3 has to be 90MB or smaller.
* Partition your data in such a way that each record on S3 is smaller than 90 MBs.
* Forgot to mention, you can also do parallel reads from S3, depending on your data format / parsing speed might be something to look into as well.
This is somewhat of a simplified guide (e.g for some workloads merging data takes time and we're not including that here) but should be good enough to start with.
[0] - https://bryson3gps.wordpress.com/2021/04/01/a-quick-look-at-...
From an operational perspective we've had almost 0 issues with BQ, whereas with Redshift we had to constantly keep giving it TLC. Right from creating users /schemas to WLM tuning, structuring files as Parquet for Spectrum access, understanding why and how spectrum performs in different scenarios, etc. everything was a chore. All this redshift specific specialization I was learning was not really contributing to the product in a meaningful way.
Switched to Bq, a year ago, and it's been mostly self driving. the only thing we had to tend to was the slots and bit of a learning curve for the org about partitioning keys (there is a setting in BQ that fails your query IF partition key is not specified)
Having switched to BQ it's really hard for me to imagine going back to Redshift. It almost feels antiquated.
I generally prefer Bigquery, and between it and Bigtable, I actually prefer GCP over AWS because their offerings for hard-to-do things are really good. I'd honestly pick GCP just for those two products.
That said, both AWS and GCP have some real rough edges.
We're constantly working on improving BigQuery performance over open file formats on cloud storage. Some of these features will be specific to BigLake. Please stay tuned.
As another commenter noted, Redshift in my experience was an operational hassle.
Snowflake and BigQuery just work.
Why choose Redshift at this point?
I welcome this
Everything that's been added in terms of functionality is just window dressing in IMHO. BigQuery and in particular Snowflake have a superior architecture, with the separation of storage and compute.
Also, someone mentions in on the thread, the Redshift marketing was horrible.
Features, features, features, features, etc.
Compare that to Snowflake, and how their message evolved:
"The Cloud Warehouse" "Data Cloud"
So much more compelling. Also, Snowflake had a killer sales and marketing team.
Many other, little things. The Redshift team was constrained by what the AWS Console would give them. Snowflake could build more, better admin features. Redshift tried to mitigate that by acquiring a client (Datarow), but from what I heard, the acquisition never got integrated.
Having said that, set-up and configured the right way, Redshift was faster and cheaper than any other data warehouse on the market. Except - nobody wanted to spend the time on properly configuring their cluster. People just wanted their warehouse to work, and that's what BigQuery and Snowflake delivered. Even when that meant paying more.
The time you spent tuning Redshift for performance, you spent on tuning Snowflake and BigQuery for cost. Pick your poison. But again, people didn't care about the money - they just wanted things to work.
I didn't really see BigQuery as competition, simply because that meant switching clouds.
What I did hear is that analytics teams preferred GCP overall, and I think that has driven a lot of cloud workloads from AWS and GCP, because in the last few years analytics teams started to be decision market in many companies.
IMHO, GCP has the much better analytics portfolio than AWS. By now, also the better sales team. It's been really smart by GCP to bet on data science and analytics because of data gravity. Once you have the data in your cloud, it attracts workloads.
Snowflake is what eventually killed our performance tuning business. You can find my post-mortem on our company on Medium.
As you can probably tell, I know more about the whole analytics ecosystem than I bargained for.
That’s a mouthful of mega-scale, dynamic, marketing-grade lingo salad.
I would love to see more competition in this space as having large amounts of data with Google always makes me feel uneasy for all kinds of reasons.