Performant database tree traversal with Rails | Hacker News Reader

Performant database tree traversal with Rails

planetscale.com

53 pointssamlambert16 comments

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.”

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.

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.

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.

As of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.

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.

What is an optimization fence?

The database and query planner can't look past it to see that it can simplify the operations tthat the query will do.

What you're missing:

The famous serverless data hosting company uses rails

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.

What about the 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.

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...)

or something?

Why not use PlanetScale Boost?

As 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.

Performant ... Rails. Nah.

Reply on news.ycombinator.com