5 ms·

Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

64 points by gmcabrita 2y ago

andrscyv 2y ago
So a super complicated work around instead of just doing sql queries or using a query builder ???
- lainga 2y ago
  Or else, if the post is talking about a "public-facing API resource", can someone tell me why the API wouldn't implement querying for multiple of the same record type at once? It just seems to me that choosing between getting 1 owner, and "get ALL owners" (as TFA puts it), is like a law of the excluded middle
  - metadat 2y ago
    It's tricky because in some cases you might be able to batch all the queries up front, but in others you will only know the IDs you need to fetch after you get one or more intermediate results back and apply business logic to arrive at a decision. As of today there's no silver bullet beyond having a solid and principles-first understanding of your database and related infrastructure.
  - Ozzie_osman 2y ago
    This might be true for some APIs but if you're letting the caller control what they fetch, you don't really have that luxury. An extreme example is Graphql-based APIs but even Rest-based APIs might allow some flexibility to decide what they fetch.
- sgarland 2y ago
  That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.
  - stephen 2y ago
    I can assure you that brandur knows SQL. :-) (I don't know him personally, but have been following his blog for years.) What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'". You want to write this logic in your preferred backend language, Go/TS/C/etc. This works fine, in the /account/X endpoint (just load the account, and apply the logic). But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts) As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic. Your options are: 1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins) 2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious) 3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do). Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).
    - d0100 2y ago
      Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)
    - xarope 2y ago
      for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)". however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.
      - 2y ago
- deleted 2y ago
  [deleted]
- tedunangst 2y ago
  Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-another-table-containing-tags https://stackoverflow.com/questions/8201462/join-with-anothe... For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it work particularly well if you're joining against something that isn't a single string column. So I just bang the rocks together like a caveman and write a second query to go back and fetch all the tags, then loop around assign them to the matching posts.
  - Mavvie 2y ago
    I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.
  - superice 2y ago
    This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations. Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched. The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.
    - Izkata 2y ago
      Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch. It's also had this for over a decade so I have to wonder how rare it actually is in ORMs in general...
gregwebs 2y ago
Jet can automatically load joined objects into embedded Go structs: https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM) https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM... Depending on what you are doing there might be some duplication that you could remove by creating hash lookups as in this post, but I would reach for Jet first. sqlc supports embedding but not embedded slices?
- sbstp 2y ago
  How does jet compare to sqlboiler? I've been using sqlboiler quite successfully on a work project.
  - gregwebs 2y ago
    They seem quite comparable in terms of features. The way they do relationships is a little different though. Sqlboiler is a little more ORM in its approach with relationships- it adds methods to retrieve relationships and it adds optional fields to its models. Jet on the other hand expects you to write out a type-safe join and then will load relationships into your struct automatically if you define a struct with the appropriate fields. I prefer the Jet approach- there's not ORM, just SQL and something that loads SQL results into structs. There is down-side that the loading into structs is not fully type-safe once you write custom structs rather than use generated models although I am not sure if that is even possible in sqlboiler. sqlboiler shows a lot of examples of using strings for queries which is odd because they seem to support type-safe queries as well.
Groxx 2y ago
For detecting rather than preventing duplicates, I'm fond of this pattern in Go: At "entry" to your code, add a mutable "X was called" tracker to your context. Anywhere you want to track duplicates, insert/increment something in that tracker in the context. And then when you exit, log the duplicates (at the place where you put the tracker in). It's reasonably implicit, works for both tracking and implicitly deduplicating (turn it into a cache rather than a counter and voila, lazy deduplication*), and it's the sort of thing that all your middle layers of code don't need to know anything about. As long as they forward contexts correctly, which you REALLY want to do all the time anyway, it Just Works™. *: obviously you can go overboard with this, there are read-after-write concerns in many cases, etc which this article's "prevent by design" structure generally handles better by making the phases of behavior explicit. but when it works, it's quite easy.
hinkley 2y ago
I've always been sort of fond of 1 + 1. It's too often the case that there's a popular query that doesn't even need the child data to function, and unless you have some elaborate caching mechanism it would be a shame to pay the full cost of the join or however you want to implement it. Making one query that returns the base data and a second that pulls all of the associated data works often enough. Then it's only when you need to pull M individual records and the associated data that might put you into M + 1 queries, if you can't work out client side grouping for some esoteric reason. But you've reduced the exponent of the fanout by 1, which can hold you for a long time. Years even.
- dventimi 2y ago
  Agreed. The N+1 problem is not as big as people make it out to be.
sigmonsays 2y ago
everything about this screams wtf to me engineering around deficiencies sometimes yields interesting results but this isn't one of them. I'd say this code is spaghetti.
dfee 2y ago
This seems to be the dataloader pattern. There are implementations in many languages, but the idea is that you have a bunch of threads which declare their I/O needs, and then you 1) debounce and merge the requests (uniform access) and 2) cache the results so that later in the graph of calls you don’t need to fetch already loaded data. Here’s one impl: https://github.com/graphql/dataloader https://github.com/graphql/dataloader
- Ozzie_osman 2y ago
  Yup! It plays quite nicely with graphql, where you are "resolving" rather than "rendering". It's also a nice place to add any caching logic, because a data loader is essentially saying "give me IDs for as many objects of type X, and I will batch load them for you".
stephen 2y ago
Ah, I look forward to every brandur post! :-) If he can give up Go, we've got a TypeScript ORM that will de-N+1 basically everything* that is not a paginated/limit-offset query: https://joist-orm.io/docs/goals/avoiding-n-plus-1s https://joist-orm.io/docs/goals/avoiding-n-plus-1s This works even in adhoc loops, i.e. if you have a lifecycle hook** of "after an author changes, do x/y/z logic", and you update 100 authors, every SQL operation invoked by those ~100 individual hooks is auto-batched. We've been running this in production for ~4 years at this point, and haven't had an N+1 since then (although we didn't initially support auto-batch find queries; that came later). Of course kudos to dataloader. *everything --> any queries our "find" API supports, which doesn't do aggregates, sums, havings, etc. **lifecycle hooks --> yes, a blessing and a curse; we're always attempting to find better/higher-level abstractions for declaring the intent of business logic, than raw/imperative hooks.
- stephen 2y ago
  Besides pitching Joist, going through OP, I'm not following how verbose the nested examples would get, i.e. ProductLoadBundle is loading "products & widgets". But what if I need "products & widgets & widget orders". And like sometimes I want "just products & widgets" (2 levels) and sometimes I want "products & widgets & widget orders (3 levels)"? Would these be the same "ProductLoadBundle" with some conditionals to the Product.LoadBundle method? Would the result of those conditionals be seen in the type-safe as, i.e. sometimes the 3rd level is available, sometimes it is not? Or would it be two separate bundles, a 2-level Product.LoadTwoLevelBundle and a 3-level Product.LoadWidgetsAndOrdersBundle, which has the pro of better type-safety, but a con of repeating the bundle boilerplate for each unique shape/tree of data your app needs to load. My guess/hope is that it's the 2nd, if only because I assume brandur also values type-safety over verbosity. It took me awhile to find again, but this OG scala library in theory handled these adhoc shapes (called "enrichments" in their readme) somewhat elegantly: https://index.scala-lang.org/getclump/clump https://index.scala-lang.org/getclump/clump Mea culpa another Joist pitch, but TypeScript makes this "give me an adhoc type based on the call-site specific tree of data" super-pleasant, i.e. Loaded<Product, "widgets"> vs. Loaded<Product, { widgets: "orders" }> gets you the two different 2-level vs. 3-level types. ...granted, you just have to accept non-Go/non-native levels of performance., but tradeoffs. :-)
dventimi 2y ago
I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
- kstrauser 2y ago
  I think it’s underblown. If more people were properly horrified about how much more needless work it causes the DB server, the network, and the client to do, they’d burn it with fire. In my experience it’s the biggest culprit behind random system fallovers that “work on my machine”. Well sure. Your test page load only resulted in 8 round trips to the server with a hidden O(n^2) in-process merge. The same page on the fully populated prod server required 137 round trips, and it still fails under load even though you can’t physically fit more RAM in the thing.
- saghm 2y ago
  > The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small. Isn't that the point? Repeated network calls will cause latency even if the data is minimal; the total amount of data returned will always be the same, so the "N" extra network calls are pure overhead. For applications where the amount of data is usually small, network calls will likely be the bottleneck.
  - dventimi 2y ago
    Again, the number of network calls will scale with the total data payload of the overall request, but the requested total data payload should never be large. It's effectively capped. Therefore, the number of network calls is effectively capped. The N+1 SQL call multiplying factor may be 2, or 10, but it shouldn't be 100 or 1000. That's what I mean when I say I think the problem is overblown. It's not that it isn't a problem--for some people--but that it's not a problem that can grow without bounds. Put another way, while the N+1 problem scales the number of SQL calls exponentially, it also scales the data payload the same way. Large payloads shouldn't occur and won't occur if we take steps to ensure that they don't. If we do, then we're also pretty much guaranteeing large batches of SQL statements will likewise be limited.
peter_l_downs 2y ago
If you're using Go, sqlboiler can do this for you in most common cases (e.g., fetch all the Users matching this filter, and for each User, fetch the related Company) https://github.com/volatiletech/sqlboiler https://github.com/volatiletech/sqlboiler
deleted 2y ago
[deleted]
erikpukinskis 2y ago
As a side note, I think I am adding “render” to my list of words which are meaningless and therefore banned from using in code: - render - container - context - base - model - action - component - bake - build - generate - view - control - process - resource Any I should add/remove?
slotrans 2y ago
ORMs are such a toxic idea, we'd be better off if we could un-invent them. Look how thoroughly this poor author's mind has been poisoned by long-term exposure. Please get out there and write some SQL. I promise it won't hurt you.
- fragmede 2y ago
  Little Bobby Tables would love that to pieces.
  - philosopher1234 2y ago
    SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.
    - fragmede 2y ago
      Sure, if you do it right, you can avoid the problem, but why not use a tool that prevents the problem from happening in the first place?
      - ddalex 2y ago
        Because you could do the data computations at the layer closest to the data ?
      - slotrans 2y ago
        So, parameters? You absolutely 1000% do not need an ORM to prevent SQL injection. You just need to Not Be Flagrantly Incompetent. As the psycopg2 docs say: never use string concatenation, not even at gunpoint.
        fragmede 2y ago
        > Not Be Flagrantly Incompetent You and I may not be, but, not to besmirch coding bootcamps, there are some great employees coming out of them, but not all of them are great. Some of them don't even know SQL, and training them up on it would take an inordinate amount of time, given their background. So it's a lot of trust to put in a team of building the hottest new CRUD app, when "use an ORM" is an easy enough answer. For queries in the hot path, you can apply a senior dev to optimize, but their time is limited and wants to be used most effectively. From the pushback I've received, this isn't a universally held belief, but the right tool in the right place, there are places for it. You're absolutely right that you don't need one to prevent SQL injection, but just like using Rust simply avoids a class of memory access bugs compared to C++, ORMs just avoid the problem from happening in the first place.
superice 2y ago
I see all these comments stating 'oh ORMs are bad' and 'just write some SQL'. Yes, you should probably not be afraid of SQL, and yes, using an ORM for everything is probably not great, and no, ORMs aren't a full replacement for writing SQL occasionally, but taking an extremist pro-SQL point-of-view is not doing any favors to this debate. There are very real reasons why writing raw SQL is a pain. You can make arguments about typesafety of queries, maintainability, mapping into application level data structures/types yadayada. Imho the primary argument is that you cannot efficiently write a query that doesn't suffer from duplication of data. If you have an entity A that has many B's, and that same entity A also has many C's, then if you join on both you now are loading A * B * C rows. This is going to be slow too, and is difficult to detangle. ORMs in their current form are terrible at tackling this problem, and people are right to call them out for that. "Just" lazy loading or "just" eager loading both do not scale, nor does doing firing event listeners on a per-entity basis. But rawdogging SQL is not a viable solution either, and I wish people advocating that would stop this attitude of 'oh you prissy dev using your puny ORM, not willing to write a line of SQL, look at me with my glorious hair on my chest and superior intellect writing pristine queries'.
- jiggawatts 2y ago
  The fundamental problem is with the wire protocol. It’s inherently tabular, but should actually be more like a binary JSON format, with hierarchical representation for joins to avoid repeating the data. Better yet, the wire formats should have efficient representations for things like column store compression.
  - superice 2y ago
    Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain. If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results. My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations). Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.
janci 2y ago
To work around N+1 is to write all your database layer functions in form of getFoosByIds(id[]) instead of getFooById(id). This allows you to easily compose the loads when you have subresources. It's similar to what the author is doing, but does not tear apart the subresources from the parent object. Pushing the subresource fetching down to the database requires using JOINs and fails badly when you have multiple one-to-many relations in one fetch. Just do a single, separate query per table.
marcus_holmes 2y ago
The problem is not in Go's endless verbosity. The problem is the basic concept of ORMs - that the model you need for efficiently rendering data is the same model that you need for efficiently storing data. ORMs map them 1:1 and that's what results in N+1 queries and all the other problems. Go's endless verbosity and lack of dynamic features is a blessing, not a curse. Because you have to write your own data access layer, you can break this blatantly false assumption that what you need in the UI is the same as what you need in the database. To break the N+1 problem, you can do funky stuff like pull back sub-components as array fields in the main component, or pull multiple result sets, or concatenate the sub-component data into a single text field, whatever your UI requires. Because you're not forcing the databases query to map 1:1 with whatever structs you've got inside your application, you can get creative with how you structure the database storage, and how you query that storage. You can create funcs in the database that do crazy shit with the data to return exactly what the UI requires in a custom struct. Because it all runs on the database itself it's fast and painless. Because you have to manually map all the structs in your application yourself (thanks Go!) then you are not constrained to mirror the database structure in your code. Your UI code does what it needs, your database does what it needs, and you can map between them however you want. ActiveRecord is easy to use, and very concise, which is what it optimises for. Go is not optimising for the same thing. The author is trying to recreate ActiveRecord in Go without realising that ActiveRecord is a straightjacket, and obviously struggling. If you free yourself from those constraints, the world becomes a simpler, better, place.
austinpena 2y ago
I'm very grateful to this post for introducing me to sliceutils to create a map from a slice. I think that's a very elegant way to create nested models given a parent and child struct.

64 points by gmcabrita 2y ago

andrscyv 2y ago

So a super complicated work around instead of just doing sql queries or using a query builder ???

lainga 2y ago
Or else, if the post is talking about a "public-facing API resource", can someone tell me why the API wouldn't implement querying for multiple of the same record type at once? It just seems to me that choosing between getting 1 owner, and "get ALL owners" (as TFA puts it), is like a law of the excluded middle
- metadat 2y ago
  It's tricky because in some cases you might be able to batch all the queries up front, but in others you will only know the IDs you need to fetch after you get one or more intermediate results back and apply business logic to arrive at a decision. As of today there's no silver bullet beyond having a solid and principles-first understanding of your database and related infrastructure.
- Ozzie_osman 2y ago
  This might be true for some APIs but if you're letting the caller control what they fetch, you don't really have that luxury. An extreme example is Graphql-based APIs but even Rest-based APIs might allow some flexibility to decide what they fetch.
sgarland 2y ago
That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.
- stephen 2y ago
  I can assure you that brandur knows SQL. :-) (I don't know him personally, but have been following his blog for years.) What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'". You want to write this logic in your preferred backend language, Go/TS/C/etc. This works fine, in the /account/X endpoint (just load the account, and apply the logic). But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts) As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic. Your options are: 1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins) 2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious) 3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do). Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).
  - d0100 2y ago
    Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)
  - xarope 2y ago
    for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)". however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.
    - 2y ago
deleted 2y ago
[deleted]
tedunangst 2y ago
Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-another-table-containing-tags https://stackoverflow.com/questions/8201462/join-with-anothe... For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it work particularly well if you're joining against something that isn't a single string column. So I just bang the rocks together like a caveman and write a second query to go back and fetch all the tags, then loop around assign them to the matching posts.
- Mavvie 2y ago
  I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.
- superice 2y ago
  This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations. Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched. The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.
  - Izkata 2y ago
    Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch. It's also had this for over a decade so I have to wonder how rare it actually is in ORMs in general...

gregwebs 2y ago

Jet can automatically load joined objects into embedded Go structs: https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM) https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM... Depending on what you are doing there might be some duplication that you could remove by creating hash lookups as in this post, but I would reach for Jet first. sqlc supports embedding but not embedded slices?

sbstp 2y ago
How does jet compare to sqlboiler? I've been using sqlboiler quite successfully on a work project.
- gregwebs 2y ago
  They seem quite comparable in terms of features. The way they do relationships is a little different though. Sqlboiler is a little more ORM in its approach with relationships- it adds methods to retrieve relationships and it adds optional fields to its models. Jet on the other hand expects you to write out a type-safe join and then will load relationships into your struct automatically if you define a struct with the appropriate fields. I prefer the Jet approach- there's not ORM, just SQL and something that loads SQL results into structs. There is down-side that the loading into structs is not fully type-safe once you write custom structs rather than use generated models although I am not sure if that is even possible in sqlboiler. sqlboiler shows a lot of examples of using strings for queries which is odd because they seem to support type-safe queries as well.

Groxx 2y ago

For detecting rather than preventing duplicates, I'm fond of this pattern in Go: At "entry" to your code, add a mutable "X was called" tracker to your context. Anywhere you want to track duplicates, insert/increment something in that tracker in the context. And then when you exit, log the duplicates (at the place where you put the tracker in). It's reasonably implicit, works for both tracking and implicitly deduplicating (turn it into a cache rather than a counter and voila, lazy deduplication*), and it's the sort of thing that all your middle layers of code don't need to know anything about. As long as they forward contexts correctly, which you REALLY want to do all the time anyway, it Just Works™. *: obviously you can go overboard with this, there are read-after-write concerns in many cases, etc which this article's "prevent by design" structure generally handles better by making the phases of behavior explicit. but when it works, it's quite easy.

hinkley 2y ago

I've always been sort of fond of 1 + 1. It's too often the case that there's a popular query that doesn't even need the child data to function, and unless you have some elaborate caching mechanism it would be a shame to pay the full cost of the join or however you want to implement it. Making one query that returns the base data and a second that pulls all of the associated data works often enough. Then it's only when you need to pull M individual records and the associated data that might put you into M + 1 queries, if you can't work out client side grouping for some esoteric reason. But you've reduced the exponent of the fanout by 1, which can hold you for a long time. Years even.

dventimi 2y ago
Agreed. The N+1 problem is not as big as people make it out to be.

sigmonsays 2y ago

everything about this screams wtf to me engineering around deficiencies sometimes yields interesting results but this isn't one of them. I'd say this code is spaghetti.

dfee 2y ago

This seems to be the dataloader pattern. There are implementations in many languages, but the idea is that you have a bunch of threads which declare their I/O needs, and then you 1) debounce and merge the requests (uniform access) and 2) cache the results so that later in the graph of calls you don’t need to fetch already loaded data. Here’s one impl: https://github.com/graphql/dataloader https://github.com/graphql/dataloader

Ozzie_osman 2y ago
Yup! It plays quite nicely with graphql, where you are "resolving" rather than "rendering". It's also a nice place to add any caching logic, because a data loader is essentially saying "give me IDs for as many objects of type X, and I will batch load them for you".

stephen 2y ago

Ah, I look forward to every brandur post! :-) If he can give up Go, we've got a TypeScript ORM that will de-N+1 basically everything* that is not a paginated/limit-offset query: https://joist-orm.io/docs/goals/avoiding-n-plus-1s https://joist-orm.io/docs/goals/avoiding-n-plus-1s This works even in adhoc loops, i.e. if you have a lifecycle hook** of "after an author changes, do x/y/z logic", and you update 100 authors, every SQL operation invoked by those ~100 individual hooks is auto-batched. We've been running this in production for ~4 years at this point, and haven't had an N+1 since then (although we didn't initially support auto-batch find queries; that came later). Of course kudos to dataloader. *everything --> any queries our "find" API supports, which doesn't do aggregates, sums, havings, etc. **lifecycle hooks --> yes, a blessing and a curse; we're always attempting to find better/higher-level abstractions for declaring the intent of business logic, than raw/imperative hooks.

stephen 2y ago
Besides pitching Joist, going through OP, I'm not following how verbose the nested examples would get, i.e. ProductLoadBundle is loading "products & widgets". But what if I need "products & widgets & widget orders". And like sometimes I want "just products & widgets" (2 levels) and sometimes I want "products & widgets & widget orders (3 levels)"? Would these be the same "ProductLoadBundle" with some conditionals to the Product.LoadBundle method? Would the result of those conditionals be seen in the type-safe as, i.e. sometimes the 3rd level is available, sometimes it is not? Or would it be two separate bundles, a 2-level Product.LoadTwoLevelBundle and a 3-level Product.LoadWidgetsAndOrdersBundle, which has the pro of better type-safety, but a con of repeating the bundle boilerplate for each unique shape/tree of data your app needs to load. My guess/hope is that it's the 2nd, if only because I assume brandur also values type-safety over verbosity. It took me awhile to find again, but this OG scala library in theory handled these adhoc shapes (called "enrichments" in their readme) somewhat elegantly: https://index.scala-lang.org/getclump/clump https://index.scala-lang.org/getclump/clump Mea culpa another Joist pitch, but TypeScript makes this "give me an adhoc type based on the call-site specific tree of data" super-pleasant, i.e. Loaded<Product, "widgets"> vs. Loaded<Product, { widgets: "orders" }> gets you the two different 2-level vs. 3-level types. ...granted, you just have to accept non-Go/non-native levels of performance., but tradeoffs. :-)

dventimi 2y ago

I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.

kstrauser 2y ago
I think it’s underblown. If more people were properly horrified about how much more needless work it causes the DB server, the network, and the client to do, they’d burn it with fire. In my experience it’s the biggest culprit behind random system fallovers that “work on my machine”. Well sure. Your test page load only resulted in 8 round trips to the server with a hidden O(n^2) in-process merge. The same page on the fully populated prod server required 137 round trips, and it still fails under load even though you can’t physically fit more RAM in the thing.
saghm 2y ago
> The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small. Isn't that the point? Repeated network calls will cause latency even if the data is minimal; the total amount of data returned will always be the same, so the "N" extra network calls are pure overhead. For applications where the amount of data is usually small, network calls will likely be the bottleneck.
- dventimi 2y ago
  Again, the number of network calls will scale with the total data payload of the overall request, but the requested total data payload should never be large. It's effectively capped. Therefore, the number of network calls is effectively capped. The N+1 SQL call multiplying factor may be 2, or 10, but it shouldn't be 100 or 1000. That's what I mean when I say I think the problem is overblown. It's not that it isn't a problem--for some people--but that it's not a problem that can grow without bounds. Put another way, while the N+1 problem scales the number of SQL calls exponentially, it also scales the data payload the same way. Large payloads shouldn't occur and won't occur if we take steps to ensure that they don't. If we do, then we're also pretty much guaranteeing large batches of SQL statements will likewise be limited.

peter_l_downs 2y ago

If you're using Go, sqlboiler can do this for you in most common cases (e.g., fetch all the Users matching this filter, and for each User, fetch the related Company) https://github.com/volatiletech/sqlboiler https://github.com/volatiletech/sqlboiler

deleted 2y ago

[deleted]

erikpukinskis 2y ago

As a side note, I think I am adding “render” to my list of words which are meaningless and therefore banned from using in code: - render - container - context - base - model - action - component - bake - build - generate - view - control - process - resource Any I should add/remove?

slotrans 2y ago

ORMs are such a toxic idea, we'd be better off if we could un-invent them. Look how thoroughly this poor author's mind has been poisoned by long-term exposure. Please get out there and write some SQL. I promise it won't hurt you.

fragmede 2y ago
Little Bobby Tables would love that to pieces.
- philosopher1234 2y ago
  SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.
  - fragmede 2y ago
    Sure, if you do it right, you can avoid the problem, but why not use a tool that prevents the problem from happening in the first place?
    - ddalex 2y ago
      Because you could do the data computations at the layer closest to the data ?
    - slotrans 2y ago
      So, parameters? You absolutely 1000% do not need an ORM to prevent SQL injection. You just need to Not Be Flagrantly Incompetent. As the psycopg2 docs say: never use string concatenation, not even at gunpoint.
      - fragmede 2y ago
        > Not Be Flagrantly Incompetent You and I may not be, but, not to besmirch coding bootcamps, there are some great employees coming out of them, but not all of them are great. Some of them don't even know SQL, and training them up on it would take an inordinate amount of time, given their background. So it's a lot of trust to put in a team of building the hottest new CRUD app, when "use an ORM" is an easy enough answer. For queries in the hot path, you can apply a senior dev to optimize, but their time is limited and wants to be used most effectively. From the pushback I've received, this isn't a universally held belief, but the right tool in the right place, there are places for it. You're absolutely right that you don't need one to prevent SQL injection, but just like using Rust simply avoids a class of memory access bugs compared to C++, ORMs just avoid the problem from happening in the first place.

superice 2y ago

I see all these comments stating 'oh ORMs are bad' and 'just write some SQL'. Yes, you should probably not be afraid of SQL, and yes, using an ORM for everything is probably not great, and no, ORMs aren't a full replacement for writing SQL occasionally, but taking an extremist pro-SQL point-of-view is not doing any favors to this debate. There are very real reasons why writing raw SQL is a pain. You can make arguments about typesafety of queries, maintainability, mapping into application level data structures/types yadayada. Imho the primary argument is that you cannot efficiently write a query that doesn't suffer from duplication of data. If you have an entity A that has many B's, and that same entity A also has many C's, then if you join on both you now are loading A * B * C rows. This is going to be slow too, and is difficult to detangle. ORMs in their current form are terrible at tackling this problem, and people are right to call them out for that. "Just" lazy loading or "just" eager loading both do not scale, nor does doing firing event listeners on a per-entity basis. But rawdogging SQL is not a viable solution either, and I wish people advocating that would stop this attitude of 'oh you prissy dev using your puny ORM, not willing to write a line of SQL, look at me with my glorious hair on my chest and superior intellect writing pristine queries'.

jiggawatts 2y ago
The fundamental problem is with the wire protocol. It’s inherently tabular, but should actually be more like a binary JSON format, with hierarchical representation for joins to avoid repeating the data. Better yet, the wire formats should have efficient representations for things like column store compression.
- superice 2y ago
  Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain. If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results. My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations). Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.

janci 2y ago

To work around N+1 is to write all your database layer functions in form of getFoosByIds(id[]) instead of getFooById(id). This allows you to easily compose the loads when you have subresources. It's similar to what the author is doing, but does not tear apart the subresources from the parent object. Pushing the subresource fetching down to the database requires using JOINs and fails badly when you have multiple one-to-many relations in one fetch. Just do a single, separate query per table.

marcus_holmes 2y ago

The problem is not in Go's endless verbosity. The problem is the basic concept of ORMs - that the model you need for efficiently rendering data is the same model that you need for efficiently storing data. ORMs map them 1:1 and that's what results in N+1 queries and all the other problems. Go's endless verbosity and lack of dynamic features is a blessing, not a curse. Because you have to write your own data access layer, you can break this blatantly false assumption that what you need in the UI is the same as what you need in the database. To break the N+1 problem, you can do funky stuff like pull back sub-components as array fields in the main component, or pull multiple result sets, or concatenate the sub-component data into a single text field, whatever your UI requires. Because you're not forcing the databases query to map 1:1 with whatever structs you've got inside your application, you can get creative with how you structure the database storage, and how you query that storage. You can create funcs in the database that do crazy shit with the data to return exactly what the UI requires in a custom struct. Because it all runs on the database itself it's fast and painless. Because you have to manually map all the structs in your application yourself (thanks Go!) then you are not constrained to mirror the database structure in your code. Your UI code does what it needs, your database does what it needs, and you can map between them however you want. ActiveRecord is easy to use, and very concise, which is what it optimises for. Go is not optimising for the same thing. The author is trying to recreate ActiveRecord in Go without realising that ActiveRecord is a straightjacket, and obviously struggling. If you free yourself from those constraints, the world becomes a simpler, better, place.

austinpena 2y ago

I'm very grateful to this post for introducing me to sliceutils to create a map from a slice. I think that's a very elegant way to create nested models given a parent and child struct.