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

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

brandur.org

Post reply on HN
Print view View original

11–20 of 76 posts

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

#11

Post by Ozzie_osman »

lainga wrote: post #3
andrscyv wrote: post #2

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

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

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.

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

#12

Post by sgarland »

andrscyv wrote: post #2

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

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.

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

#14

Post by stephen »

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

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.

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

#15

Post by stephen »

stephen wrote: post #14

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 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 individ…

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

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 vs. Loaded 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. :-)

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

#16

Post by stephen »

sgarland wrote: post #12
andrscyv wrote: post #2

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

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.

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

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

#17

Post by unrealhoang »

sgarland wrote: post #12
andrscyv wrote: post #2

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

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.

TFA is using raw SQL (via sqlc) if you read carefully. And no, raw SQL doesn't get rid of N+1 issues.

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

#18

Post by ec109685 »

sgarland wrote: post #12
andrscyv wrote: post #2

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

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.

Without two phases: figure out what you need for the resource, execute query, how can you avoid N+1 problem, raw sql or not.
Post reply on HN