[HN Gopher] Performant database tree traversal with Rails
       ___________________________________________________________________
        
       Performant database tree traversal with Rails
        
       Author : samlambert
       Score  : 37 points
       Date   : 2023-07-12 17:41 UTC (5 hours ago)
        
 (HTM) web link (planetscale.com)
 (TXT) w3m dump (planetscale.com)
        
       | AYBABTME wrote:
       | Why not use PlanetScale Boost?
        
         | orangepanda wrote:
         | 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.
        
       | e12e wrote:
       | 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?
        
       | itslennysfault wrote:
       | Performant ... Rails. Nah.
        
       | mcqueenjordan wrote:
       | 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."
        
         | meepmorp wrote:
         | 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.
        
           | vector_spaces wrote:
           | 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?
        
             | aarondf wrote:
             | What is an optimization fence?
        
             | orangepanda wrote:
             | 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.
        
         | thegreatpeter wrote:
         | What you're missing:
         | 
         | The famous serverless data hosting company uses rails
        
           | regularfry wrote:
           | 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.
        
       ___________________________________________________________________
       (page generated 2023-07-12 23:01 UTC)