News for hackers. Stuff that matters.
Re:Performant database tree traversal with Rails by mcqueenjordan on Wednesday July 12, 2023 @03:10PM (#36700135)
Apologies 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.”

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.
Re:Performant database tree traversal with Rails by srcreigh on Wednesday July 12, 2023 @10:32PM (#36704372)
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.
Re:Performant database tree traversal with Rails by tpetry on Thursday July 13, 2023 @02:21AM (#36705616)
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.