If that's not peak Amazon, I don't know what is.
It's one of the backends available in Splink, our FOSS record linkage software and it's revolutionary how it allows users to execute large scale probabilistic record linkage ridiculously cheaply. It wasn't long ago you needed very expensive proprietary software plus a big on prem cluster, costing in the hundreds of thousands, to achieve this.
A lot of the magic for me is on the infrastructure side: how they can read/write large datasets from s3 so quickly, so the value isn't just in the SQL engine.
Adding a new vendor to the mix needs to involve procurement, the legal team, vendor negotiations, while using a new AWS feature is just a matter of using it, even if it's not as good as the original ISV's version and doesn't support the long term viability of the project.
When your monthly Athena bill crosses whatever it would cost to have 5 or 10 EC2 machines it'll be cheaper to use Trino. At my previous workplace we moved from ~$40,000/month to ~$18,000/month by replacing Athena.
Athena is a very good tool to start with - unless you have super large scale you'll probably not outgrow it. But when you do there's Trino.
I do contribute to Trino - although I was merely a user when that cost reduction happened.
And that's all with the benefit of hindsight - it's hard to know a priori how much cheaper your own deployment will be compared to a managed service or how long it will take to implement. Of course, anecdotes like yours help with that, so thanks for sharing your experience!
trino> USE memory.default;
USE
trino:default> create table artist (artistid int);
CREATE TABLE
trino:default> create table album (albumid int, artistid int);
CREATE TABLE
trino:default> insert into artist values 1, 2;
INSERT: 2 rows
Query 20220804_182827_00005_n4rat, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
0.52 [0 rows, 0B] [0 rows/s, 0B/s]
trino:default> insert into album values (11, 1), (12, 1), (21, 2);
INSERT: 3 rows
Query 20220804_182857_00006_n4rat, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
0.18 [0 rows, 0B] [0 rows/s, 0B/s]
trino:default> select * from (select * from artist limit 2) a cross join lateral (select * from album where album.artistid = a.artistid limit 2);
artistid | albumid | artistid
----------+---------+----------
1 | 12 | 1
1 | 11 | 1
2 | 21 | 2
(3 rows)
Query 20220804_182930_00007_n4rat, FINISHED, 1 node
Splits: 41 total, 41 done (100.00%)
0.35 [8 rows, 232B] [22 rows/s, 661B/s]Note: the original Presto continues to run in production at Meta (fmr. Facebook), Uber, and recently ByteDance TikTok data platform talked about running 1M queries a day with tens of thousands of cores. Some reasons to stay with Presto: - Reliability and scalability per above - Cutting edge innovations only in later versions of Presto: multi-level caching (project RaptorX) to boost query performance by 10X+ and table scan improvements (project Aria) to name a few - Only PrestoDB is hosted by Linux Foundation, giving confidence to community users that future releases will remain open.
Full disclosure I work at Starburst.
Guy who goes by the name of "Randgalt" online builds some great Java libraries and works there too I believe.
https://stackoverflow.com/a/73129836/13485494
But man is it a huge PITA (especially when doing programmatic code generation of the SQL) compared to LATERAL joins
Someone familiar with the CockroachDB query planner showed me that a window function like this is what Cockroach turns LATERAL joins into for instance:
[email protected]:26257/movr> explain select * from abc, lateral (select * from xyz where x = a limit 2);
• filter
│ estimated row count: 1
│ filter: row_num <= 2
│
└── • window
│ estimated row count: 2
│
└── • hash join
│ estimated row count: 2
│ equality: (x) = (a)
│
├── • scan
│ estimated row count: 6 (100% of the table; stats collected 2 minutes ago)
│ table: xyz@xyz_pkey
│ spans: FULL SCAN
│
└── • scan
estimated row count: 1 (100% of the table; stats collected 3 minutes ago)
table: abc@abc_pkey
spans: FULL SCAN
It turns out that the dialect doesn't support LATERAL joins with a LIMIT in them. The below query only works if you remove the LIMIT clause.
https://i.stack.imgur.com/rdB1s.png
This makes saying things like "Fetch all artists where ..., for each artist fetch their first 3 albums where ..., and for each album fetch the top 10 tracks where ..." really difficult
Does Trino support this out of curiosity?