Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Efficient GraphQL Queries in Ruby on Rails and Postgres

pganalyze.com

Post reply on HN
Print view View original

31–40 of 67 posts

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#31

Post by wolco »

ben_jones wrote: post #14
xyzzy_plugh wrote: post #2

> The following query produces the data that we want... we just need to figure out how to write a batch loader that generates the same result. This is where the abstraction breaks down. Should you care what the query ultimately looks like? For anything beyond a toy, you sure should. But how far should you go to ensure the abstraction produces the implementation you want? Why not just skip to the implementation you wa…

I agree but can't ignore this idiom > If you start without an ORM you end up writing a custom ORM eventually and often that custom ORM is inferior to the third party one you're considering Funny enough this engineering problem becomes an HR one: If your recruiting and training processes can consistently hire devs actually fluent in SQL and web application architecture you can completely avoid the ORM and just write g…

Remove ORMs and write raw queries for everything? Or write our own orm/query builder?

I didn't like orms much at first but with some practice you can shape your queries. Much neater and cleaner if that matters in your case.

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#32

Post by uhoh-itsmaciek »

e12e wrote: post #13

Somewhat on the side, but: > By using the includes method we've been able to knock our queries down from six to two: The first to find the events, and the second to find the categories for those events. Am I the only one that thinks rails/activerecord is crazy here for (often) preferring two trips to the db over a join on indexed primary keys? Are there really (sane) use-cases where that is better?

>Am I the only one that thinks rails/activerecord is crazy here for (often) preferring two trips to the db over a join on indexed primary keys?

You mean

  SELECT u.* , c.* FROM users u INNER JOIN comments c
    ON u.id = c.user_id WHERE u.id =
?

If the average user record includes a non-trivial amount of data and there are many comments, you're repeating a lot of information in the query results.

disclaimer: I'm currently doing some work for pganalyze.com (the blog host)

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#33

Post by SketchySeaBeast »

sandstrom wrote: post #30

We've looked at GraphQL but ended up going with Graphiti instead. Only a few weeks since we implemented, but happy so far. Feels like it's the best of REST and GraphQL. This page from Graphiti[1] explains the thinking behind it. Don't be discouraged by the loud pink colors ;) [1] https://www.graphiti.dev/guides/why

I was excited to look at a REST/GraphQL alternative, but that website doesn't fill me with a lot of excitement or enthusiasm. The styling choice is fairly off-putting, and the first line of the "why" has a misspelling.

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#34

Post by e12e »

uhoh-itsmaciek wrote: post #32
e12e wrote: post #13

Somewhat on the side, but: > By using the includes method we've been able to knock our queries down from six to two: The first to find the events, and the second to find the categories for those events. Am I the only one that thinks rails/activerecord is crazy here for (often) preferring two trips to the db over a join on indexed primary keys? Are there really (sane) use-cases where that is better?

>Am I the only one that thinks rails/activerecord is crazy here for (often) preferring two trips to the db over a join on indexed primary keys? You mean SELECT u.* , c.* FROM users u INNER JOIN comments c ON u.id = c.user_id WHERE u.id = ? If the average user record includes a non-trivial amount of data and there are many comments, you're repeating a lot of information in the query results. disclaimer: I'm currently…

Yes.

I'm not sure about "non-trivial amount of data" - especially if we're talking about a single row (single user) here? I suppose there might be a few blolbs of large json documents?

And I'm not sure I understand "repeating information in query results" - surely we're talking about receiving a query, transforming it and responding with the data?

In a rails+graphql ideal world, that'd maybe mean Parsing the json query, generate some simple ruby (active record) code, fire it off to the db (let ar /arel do its job; generate sql) - serialize to json and write the response?

If there truly is non-trivial data in fields not requested, it might be worth it to naemrrow the columns selected. But I doubt it, in the general case.

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#35

Post by m12k »

xyzzy_plugh wrote: post #2

> The following query produces the data that we want... we just need to figure out how to write a batch loader that generates the same result. This is where the abstraction breaks down. Should you care what the query ultimately looks like? For anything beyond a toy, you sure should. But how far should you go to ensure the abstraction produces the implementation you want? Why not just skip to the implementation you wa…

I've seen many mobile developers be frustrated at constantly needing to ask the backend developers to make new rest endpoints for them because the existing ones were not exposing the data in the way they needed it exposed. And similarly I've seen backend devs be frustrated at continually having to make new endpoints for those nagging mobile devs. If you have teams working independently of one another, where the backend team is providing a service to the frontend team(s), it makes sense to use a tool like GraphQL to able to go "here's access to anything you need - go ahead and use it however you need" instead of needing to keep coming back and building new endpoints again and again.

If you're the one both creating and consuming the endpoints, then sure, it doesn't matter where you write your query, and building abstractions to move the query all the way out to the client is a waste of time.

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#36

Post by tyre »

aantix wrote: post #10
xyzzy_plugh wrote: post #2

> The following query produces the data that we want... we just need to figure out how to write a batch loader that generates the same result. This is where the abstraction breaks down. Should you care what the query ultimately looks like? For anything beyond a toy, you sure should. But how far should you go to ensure the abstraction produces the implementation you want? Why not just skip to the implementation you wa…

>For some things, an abstraction suffices, but it's far from a universal solution Fair, but the ORM covers 80% of what you need. Mapping to objects, autoloading associations. These are simple selects and joins and make up a large portion of most information based systems. When something needs performance and the ORM isn't optimizing appropriately, sure, drop to raw SQL and optimize away. Even then, what did you gain?…

I agree with everything except the five seconds note. Web pages loading in fives seconds is an awful experience.

Optimizing expensive queries not only improves the user experience (be it web or api or whatever) but also can open up entire usecases that otherwise wouldn’t be practical. Five second queries are a primate candidate for DOS attacks, whether intentionally malicious or by users spamming refresh, retry, etc.

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#37

Post by lhorie »

m12k wrote: post #35
xyzzy_plugh wrote: post #2

> The following query produces the data that we want... we just need to figure out how to write a batch loader that generates the same result. This is where the abstraction breaks down. Should you care what the query ultimately looks like? For anything beyond a toy, you sure should. But how far should you go to ensure the abstraction produces the implementation you want? Why not just skip to the implementation you wa…

I've seen many mobile developers be frustrated at constantly needing to ask the backend developers to make new rest endpoints for them because the existing ones were not exposing the data in the way they needed it exposed. And similarly I've seen backend devs be frustrated at continually having to make new endpoints for those nagging mobile devs. If you have teams working independently of one another, where the backe…

> here's access to anything you need - go ahead and use it however you need

But that's almost never what you want: as the backend developer, it's one's job to care about security and the performance of queries. As far as I know there's no straightforward way of automagically translating every possible permutation of SQL query primitives into GraphQL primitives in a way that is intuitive to a frontend developer, reasonably performant and secure, so at some point the backend dev has to step in and do the work of connecting the GQL layer to the SQL layer.

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#38

Post by sandstrom »

SketchySeaBeast wrote: post #33
sandstrom wrote: post #30

We've looked at GraphQL but ended up going with Graphiti instead. Only a few weeks since we implemented, but happy so far. Feels like it's the best of REST and GraphQL. This page from Graphiti[1] explains the thinking behind it. Don't be discouraged by the loud pink colors ;) [1] https://www.graphiti.dev/guides/why

I was excited to look at a REST/GraphQL alternative, but that website doesn't fill me with a lot of excitement or enthusiasm. The styling choice is fairly off-putting, and the first line of the "why" has a misspelling.

Yeah, I know, it's subjective but I also think the design is awful. It's good software though!

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#39

Post by uhoh-itsmaciek »

e12e wrote: post #34
uhoh-itsmaciek wrote: post #32

Earlier quoted context omitted.

>Am I the only one that thinks rails/activerecord is crazy here for (often) preferring two trips to the db over a join on indexed primary keys? You mean SELECT u.* , c.* FROM users u INNER JOIN comments c ON u.id = c.user_id WHERE u.id = ? If the average user record includes a non-trivial amount of data and there are many comments, you're repeating a lot of information in the query results. disclaimer: I'm currently…

Yes. I'm not sure about "non-trivial amount of data" - especially if we're talking about a single row (single user) here? I suppose there might be a few blolbs of large json documents? And I'm not sure I understand "repeating information in query results" - surely we're talking about receiving a query, transforming it and responding with the data? In a rails+graphql ideal world, that'd maybe mean Parsing the json que…

>I'm not sure about "non-trivial amount of data" - especially if we're talking about a single row (single user) here? I suppose there might be a few blolbs of large json documents? > >And I'm not sure I understand "repeating information in query results" - surely we're talking about receiving a query, transforming it and responding with the data?

For a query like

  SELECT u.* , c.* FROM users u INNER JOIN comments c
    ON u.id = c.user_id WHERE u.id =
above, the user information is repeated for each comment, e.g.,

  u.id, u.name, c.id, c.text
  1     Bob     1     you are right
  1     Bob     2     you are wrong
  1     Bob     3     you are right again
If you have a hundred comments, and many user fields, this can be a significant amount of data to pass around. Or maybe this is not what you meant? I feel like we may be talking past each other. Can you give an example of what you think the ideal sql should be for "give me user with id = 1 and all their comments"?

Re: Efficient GraphQL Queries in Ruby on Rails and Postgres

#40

Post by Izkata »

tyre wrote: post #36
aantix wrote: post #10

Earlier quoted context omitted.

>For some things, an abstraction suffices, but it's far from a universal solution Fair, but the ORM covers 80% of what you need. Mapping to objects, autoloading associations. These are simple selects and joins and make up a large portion of most information based systems. When something needs performance and the ORM isn't optimizing appropriately, sure, drop to raw SQL and optimize away. Even then, what did you gain?…

I agree with everything except the five seconds note. Web pages loading in fives seconds is an awful experience. Optimizing expensive queries not only improves the user experience (be it web or api or whatever) but also can open up entire usecases that otherwise wouldn’t be practical. Five second queries are a primate candidate for DOS attacks, whether intentionally malicious or by users spamming refresh, retry, etc.

I think he meant shaving only 5 seconds off of the 200 second query.
Post reply on HN