They even mention CTEs at the bottom of the article, but just as a throwaway idea. I'm not too familiar with them in MySQL, but they're the obvious choice for doing something like this with other databases.
My understanding is that CTEs are an optimization fence in some databases so aren't great for web application queries? I think that this is no longer the case in Postgres, but I recall learning that like ~6 years ago when working with other databases. Or is that total nonsense?
I most commonly use CTEs for splitting ranges into individual values (1-5 into 1,2,3,4,5). It’s an order of magnitude faster than joining some utility table, which some still recommended.
By default it’s an optimization fence if a CTE is referenced 2+ times, and not an optimization fence if referenced 0-1 times. The MATERIALIZED and NOT MATERIALIZED overrides the default.
They couldn‘t use CTEs because PlanetScale, which is built on top of Vitess, does not support them. So the most straightforward solution is just not available for them.
I'm not sure I even understand the sample SQL, given:
> Each snapshot can have one or two parents. When merging branches, we perform a breadth-first search on the history of each change until we find the common ancestor between both branches. This is the merge base.
And the SQL:
select * from schema_snapshots where id = 20
(...)
schema_snapshots where id = 24
// *thousands more queries*
Why select star here? Surely they mean:
SELECT left_parent_id as parent_id FROM schema_snapshots )
UNION
(SELECT right_parent_id as parent_id FROM schema_snapshots)
WHERE id in (1,2,3...)
If you can't use recursive CTEs I thought the nested set was the older way to do this. There is a bit of work to maintain the structure but once you set it in place it works really well.
17 comments
[ 4.4 ms ] story [ 55.8 ms ] threadI don’t think “we added an in memory cache to reduce roundtrips to the DB” is “solving an interesting performance problem.”
The famous serverless data hosting company uses rails
Sigh. Fucking kids on my lawn.
> Each snapshot can have one or two parents. When merging branches, we perform a breadth-first search on the history of each change until we find the common ancestor between both branches. This is the merge base.
And the SQL:
Why select star here? Surely they mean: or something?https://en.m.wikipedia.org/wiki/Nested_set_model
If you can't use recursive CTEs I thought the nested set was the older way to do this. There is a bit of work to maintain the structure but once you set it in place it works really well.