5 ms·
Performant database tree traversal with Rails
- mcqueenjordan 3y agoApologies for being the grumpy guy this morning. I don’t think “we added an in memory cache to reduce roundtrips to the DB” is “solving an interesting performance problem.”
- thegreatpeter 3y agoWhat you're missing: The famous serverless data hosting company uses rails
- regularfry 3y agoWhat's ironic here is that there's an awesome_nested_set gem for Rails which (if I've understood the problem right) makes the solution trivial.
- meepmorp 3y agoThey 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. Sigh. Fucking kids on my lawn.
- vector_spaces 3y agoMy 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?
- aarondf 3y agoWhat is an optimization fence?
- simcop2387 3y agoThe database and query planner can't look past it to see that it can simplify the operations tthat the query will do.
- orangepanda 3y agoI 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.
- zomgwat 3y agoAs of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.
- srcreigh 3y agoBy 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.
- tpetry 3y agoThey 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.
- snailtrail 3y ago[flagged]
- AYBABTME 3y agoWhy not use PlanetScale Boost?
- orangepanda 3y agoAs I understood, the problem was not that the queries were slow, but the sheer amount of them. Boost would not have helped much with that.
- itslennysfault 3y agoPerformant ... Rails. Nah.
- e12e 3y agoI'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...) or something?
- xupybd 3y agoWhat about the nested set model? https://en.m.wikipedia.org/wiki/Nested_set_model 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.