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

71–76 of 76 posts

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

#71

Post by slotrans »

fragmede wrote: post #41
philosopher1234 wrote: post #39

Earlier quoted context omitted.

SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.

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?

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.

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

#72

Post by fragmede »

slotrans wrote: post #71
fragmede wrote: post #41

Earlier quoted context omitted.

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?

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.

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

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

#73

Post by superice »

dventimi wrote: post #66
superice wrote: post #55

Earlier quoted context omitted.

Happy to name a concrete use case: showing a manifest of containers to load/unload from a ship. Search needs to happen client-side for many reasons, ballpark figures may be 1000 loads, 1000 unloads, the info about the shipvisit, and a stowage plan of 1000 records loading/unloading (separate from the manifest). Give you 4001 individual entities, clocking in at at least 20 attributes per entity (container number, type,…

If I'm reading you correctly you're saying there's an industrial grade application with a UI that routinely shows up to 80000 attributes to a user, or at least needs that information for client-side processing. Setting aside whether that's justified for this particular use case, do you think that's a common use case in let's say consumer grade web and mobile applications?

No, probably not, but SQL and ORMs aren’t used just for consumer grade applications ;)

I’m sure N+1 is not a big problem in those use cases, but saying that thereforr the problem doesn’t exist is overgeneralizing a bit. B2B is not a small market.

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

#74

Post by dventimi »

superice wrote: post #73
dventimi wrote: post #66

Earlier quoted context omitted.

If I'm reading you correctly you're saying there's an industrial grade application with a UI that routinely shows up to 80000 attributes to a user, or at least needs that information for client-side processing. Setting aside whether that's justified for this particular use case, do you think that's a common use case in let's say consumer grade web and mobile applications?

No, probably not, but SQL and ORMs aren’t used just for consumer grade applications ;) I’m sure N+1 is not a big problem in those use cases, but saying that thereforr the problem doesn’t exist is overgeneralizing a bit. B2B is not a small market.

I don't believe I said the N+1 problem doesn't exist. I said that I believe it's "overblown". I'll grant that's vague, and I can be more precise, but one thing I mean by "overblown" is that it's not necessarily a big problem in all cases, even if it's sometimes a problem in some cases. I definitely do not mean that it's never a big problem in any cases.

Anyway, it's cool. No worries.

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

#75

Post by lukaseder »

superice wrote: post #35
Izkata wrote: post #33

Earlier quoted context omitted.

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

Interesting. Does it do this separate query for all entities in scope, or per entiry like Hibernates SELECT FetchMode? I find a separate SELECT is usually possible, but doesn’t quite solve this in the general case. Perhaps I missed Django, but as far as I could tell, Hibernate and jooq can’t do this, Ecto can’t do it, ActiveRecord can’t, and the entirety of ORMs for JS can’t (TypeORM, Prisma, Drizzle, Sequelize and a…

jOOQ doesn't get involved in any such prefetch/eager fetch shenanigans, which are often wrong. They work for some cases and do too little/too much for others. So, specifying the exact data to fetch in every query is a much better approach, ideally with nested collections: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-...

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

#76

Post by Izkata »

superice wrote: post #35
Izkata wrote: post #33

Earlier quoted context omitted.

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

Interesting. Does it do this separate query for all entities in scope, or per entiry like Hibernates SELECT FetchMode? I find a separate SELECT is usually possible, but doesn’t quite solve this in the general case. Perhaps I missed Django, but as far as I could tell, Hibernate and jooq can’t do this, Ecto can’t do it, ActiveRecord can’t, and the entirety of ORMs for JS can’t (TypeORM, Prisma, Drizzle, Sequelize and a…

I'm not sure I understand the question. If I'm reading the Hibernate examples right, I already answered that in my first comment: Lazy loading (separate query for each row, 1+N problem) is the default, one additional query per each field with prefetch_related() (1+1 if you only need 1 relationship, 1+2 for 2 relationships, etc).

Django also specifies foreign keys on the model, which are used by name when doing queries. From their examples in prefetch_related() [0], this query would involve 2 levels of lazy loading, except prefetch_related() tells Django to do 3 total queries and join them together in code:

  Restaurant.objects.prefetch_related("best_pizza__toppings")
[0] https://docs.djangoproject.com/en/5.0/ref/models/querysets/#...
Post reply on HN