Re:Performant database tree traversal with Rails
by thegreatpeter on Wednesday July 12, 2023 @03:47PM
(#36700618)
What you're missing:
The famous serverless data hosting company uses rails
Re:Performant database tree traversal with Rails
by regularfry on Wednesday July 12, 2023 @05:20PM
(#36701858)
What'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.
Re:Performant database tree traversal with Rails
by meepmorp on Wednesday July 12, 2023 @05:16PM
(#36701799)
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.
Sigh. Fucking kids on my lawn.
Re:Performant database tree traversal with Rails
by vector_spaces on Wednesday July 12, 2023 @05:40PM
(#36702062)
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?
Re:Performant database tree traversal with Rails
by aarondf on Wednesday July 12, 2023 @05:56PM
(#36702226)
What is an optimization fence?
Re:Performant database tree traversal with Rails
by simcop2387 on Wednesday July 12, 2023 @08:54PM
(#36703747)
The database and query planner can't look past it to see that it can simplify the operations tthat the query will do.
Re:Performant database tree traversal with Rails
by orangepanda on Wednesday July 12, 2023 @06:24PM
(#36702495)
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.
Re:Performant database tree traversal with Rails
by zomgwat on Wednesday July 12, 2023 @09:34PM
(#36704029)
As of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.
I don’t think “we added an in memory cache to reduce roundtrips to the DB” is “solving an interesting performance problem.”